const req = require('request');
const fs = require('fs');
const async = require('async');

const lineByLine = require('n-readlines');
let liner = new lineByLine('./SOidsWithGH.txt');
let line;

const LANGUAGES_ARRAY = ["JavaScript", "Java", "Python", "Ruby", "PHP"];
const LANGUAGES_ARRAY_LOWCASE = LANGUAGES_ARRAY.map((lang) => {return lang.toLowerCase()})

const mysql = require('promise-mysql');
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'ghtorrentuser',
    password: 'ghtorrentpassword',
    database: 'ghtorrent_restore'
});

connection.connect();

const getUserQuery = function(GHlogin) {
    return [`SELECT id as GHid FROM ghtorrent_restore.users WHERE login="?"`,
        [GHlogin],
    ];
}
const getFollowersCountQuery = function(GHid) {
    return [`SELECT count(user_id) as followersCount
        FROM ghtorrent_restore.followers
        WHERE follower_id IN (?)`,
        [GHid],
    ];

}
const getReposList = function(GHid) {
    return [`SELECT t1.project_id as project_id, t2.language as project_lang
        FROM ghtorrent_restore.commits as t1
        JOIN ghtorrent_restore.projects as t2 on t2.id = t1.project_id
        WHERE t1.author_id=? and t2.language IN ('?')
        GROUP BY project_id, project_lang`,
        [GHid, LANGUAGES_ARRAY.join("','")],
    ];
}
const getForksCountQuery = function(GHrepoId) {
    return [`SELECT count(id) as forksCount
        FROM ghtorrent_restore.projects
        WHERE forked_from IN (?)`,
        [GHrepoId],
    ];
}
const getStarsCountQuery = function(GHrepoId) {
    return [`SELECT count(repo_id) as starsCount
        FROM ghtorrent_restore.watchers
        WHERE repo_id IN (?)`,
        [GHrepoId],
    ];
}
const getAllCommitsCountQuery = function(GHrepoId) {
    return [`SELECT count(id) as allCommitsCount
        FROM ghtorrent_restore.commits
        WHERE project_id IN (?)`,
        [GHrepoId],
    ];
}
const getUserCommitsCountQuery = function(GHrepoId, GHid) {
    return [`SELECT count(id) as usersCommitsCount
        FROM ghtorrent_restore.commits
        WHERE project_id IN (?) AND author_id=?`,
        [GHrepoId, GHid],
    ];
}
const getClosedPRCountQuery = function(GHrepoId, GHid) {
    return [`SELECT count(pr.id) as closedPRCount
        FROM ghtorrent_restore.pull_request_history as prh
        JOIN ghtorrent_restore.pull_requests as pr on prh.pull_request_id=pr.id
        where pr.base_repo_id IN (?) and prh.actor_id=? and prh.action="closed"`,
        [GHrepoId, GHid],
    ];
}
const getAllPRCountQuery = function(GHrepoId, GHid) {
    return [`SELECT count(pr.id) as openedPRCount
        FROM [ghtorrent-bq:ght_2017_01_19.pull_request_history] as prh
        JOIN [ghtorrent-bq:ght_2017_01_19.pull_requests] as pr on prh.pull_request_id=pr.id
        where pr.base_repo_id IN (?) and prh.actor_id=?`,
        [GHrepoId, GHid]
    ];
}
const getMergedPRCountQuery = function(GHrepoId, GHid) {
    return [`SELECT count(pr.id) as mergedPRCount
        FROM ghtorrent_restore.pull_request_history as prh
        JOIN ghtorrent_restore.pull_requests as pr on prh.pull_request_id=pr.id
        where pr.base_repo_id IN (?) and prh.actor_id= ? and prh.action="merged"`,
        [GHrepoId, GHid],
    ];
}

const mysqlConnect = function (sueryString, queryParams) {
    connection.query(sueryString, queryParams, function (error, results, fields) {
        if (error) throw error;
        // ...
    });
}

async function asyncQuery (sqlQuery) {
    const bigquery = BigQuery();
    const options = {
        query: sqlQuery,
        useLegacySql: true
    };

    let results = await bigquery.startQuery(options);
    let job = results[0];
    await job.promise();
    let rows = await job.getQueryResults();
    return rows[0];
}

let init = function()  {

(function loop() {
    if (line = liner.next()) {
        const GHlogin = line.toString("ascii")
        console.log(GHlogin);

        mysql.createConnection({
            host: "localhost",
            user: "ghtorrentuser",
            password: "ghtorrentpassword",
            database: "ghtorrent_restore",
        }).then((conn) => {
            connection = conn;
            let queryParams = getUserQuery(GHlogin);
            return connection.query(queryParams[0], queryParams[1]);
        }).then((rows)=>{
            queryParams = getUserQuery(GHlogin);
            const GHid = rows[0].GHid;
            queryParams = getReposList(GHid);

            return connection.query(queryParams[0], queryParams[1]);
        }).then(function(rows){
            if(rows.length) {
                let ghData = {};
                reposList.forEach((repo) => {
                    if( !ghData.hasOwnProperty(repo.project_lang) ){
                        ghData[repo.project_lang] = [];
                    }
                    ghData[repo.project_lang].push(repo.project_id);
                });
            }
            console.log(rows);
        });

          asyncQuery(getUserQuery(GHlogin)).then((ghUser) => {
              console.log(ghUser);
              if(ghUser.length) {
                  let GHid = ghUser[0].GHid;
                  console.log(GHid);
                  asyncQuery(getReposList(GHid)).then((reposList) => {
                      if(reposList.length) {
                          //return;
                          let ghData = {};
                          console.log(reposList.map((repo) => {return repo.project_lang}))
                          reposList.forEach((repo) => {
                              if( !ghData.hasOwnProperty(repo.project_lang) ){
                                ghData[repo.project_lang] = [];
                              }
                              ghData[repo.project_lang].push(repo.project_id);
                              let SOresult = {};
                              req.get({ "url": `http://api.stackexchange.com/2.2/users/${SOid}/top-tags?page=1&order=desc&site=stackoverflow`,
                                  json: true,
                                  headers: {'accept-encoding': 'gzip'},
                                  gzip: true
                                },  (err, httpResponse, body) => {
                                  let tags = body.items;
                                  let i = 0;
                                  async.whilst( () => {
                                      let tagItemsLength = i < tags.length && tags[i].answer_score > 0;
                                      return i < tagItemsLength;
                                  },
                                  (next) => {
                                      req.get({
                                          "url": `http://api.stackexchange.com/2.2/tags/${tags[i].tag_name}/related?site=stackoverflow`,
                                          json: true,
                                          headers: {'accept-encoding': 'gzip'},
                                          gzip: true
                                      }, (err, httpResponse, body) => {
                                          let related_tags = body.items.slice(0, 5);


                                          for(let j = 0; j < related_tags.length; j++ ) {
                                              let tag = related_tags[j].name.toLowerCase();
                                              if(LANGUAGES_ARRAY_LOWCASE.indexOf(tag) >=0) {
                                                  if( !SOresult.hasOwnProperty(tag) ){
                                                      SOresult[tag] = 0;
                                                  }
                                                  SOresult[tag] += tags[i].answer_score;
                                                  break;
                                              }
                                          }
                                          i++;
                                          next();
                                      });
                                  },
                                (err, n) => {
                                      Object.keys(ghData).forEach((lang) => {
                                          let resultObj = {};
                                          let q1 = asyncQuery(getForksCountQuery(ghData[lang].join()));
                                          asyncQuery(getForksCountQuery(ghData[lang].join())).then(forks => {
                                              resultObj.forksCount = forks[0].forksCount;

                                              asyncQuery(getStarsCountQuery(ghData[lang].join())).then(stars => {
                                                  resultObj.starsCount = stars[0].starsCount;

                                                  asyncQuery(getUserCommitsCountQuery(ghData[lang].join(), GHid)).then(allCommits => {
                                                      resultObj.allCommitsCount = allCommits[0].allCommitsCount;

                                                      asyncQuery(getUserCommitsCountQuery(ghData[lang].join(), GHid)).then(userCommits => {
                                                          resultObj.userCommitsCount = userCommits[0].userCommitsCount;

                                                          asyncQuery(getClosedPRCountQuery(ghData[lang].join(), GHid)).then(closedPR => {
                                                              resultObj.closedPRCount = closedPR[0].closedPRCount;

                                                              asyncQuery(getAllPRCountQuery(ghData[lang].join(), GHid)).then(openedPR => {
                                                                  resultObj.openedPRCount = openedPR[0].openedPRCount;

                                                                  asyncQuery(getMergedPRCountQuery(ghData[lang].join(), GHid)).then(mergedPR => {
                                                                      resultObj.mergedPRCount = mergedPR[0].mergedPRCount;

                                                                      asyncQuery(getFollowersCountQuery(ghData[lang].join(), GHid)).then(followers => {
                                                                          resultObj.followersCount = followers[0].followersCount;
                                                                          resultObj.eval = SOresult[lang.toLocaleLowerCase()] || 0;
                                                                          console.log("so ");
                                                                          console.log(SOresult);
                                                                          // console.log(resultObj);
                                                                          // console.log(`${lang}.json`)
                                                                          var json = JSON.stringify(resultObj);
                                                                          fs.appendFile(`${lang}.json`, `${json} \n`, function (err) {
                                                                              if (err) throw err;
                                                                              setTimeout(loop, 1500)
                                                                          });
                                                                      })
                                                                  })
                                                              })
                                                          })
                                                      })
                                                  })
                                              })
                                          })
                                      });
                                  });
                              });
                          });
                      }
                      else {
                          console.log("after if(reposList.length)")
                          loop();
                      }
                      //
              })
            }
            else {
                console.log("after if(ghUser.length)")
                loop();
            }
            //console.log("after if(ghUser.length)")
            //loop();

            //  setTimeout(() => {

            //  }, 50000)

          });
      }
  }());
}

init();