var _ = require('underscore');
var crypto = require('crypto');

var LineByLineReader = require('line-by-line');
var lr = new LineByLineReader('pubmed_result.csv');

var mysql = require('mysql');
var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'iv@n1008'
});

var counter = 0;

function processLine(line) {

    var sql1;
    var sql2;
    var hash;
    var uid;

    var regex = /"(.*)","(.*)","(.*)","(.*)","(.*)","(.*)","(.*)","(.*)","(.*)","(.*)","(.*)",/gi;
    var match;
    var autors;
    var autor;

    var pubTitle;
    var pubJurnal;
    var pubMedID;
    var DOI;

    match = regex.exec(line);
    if (match !== null) {

        pubTitle = match[1].trim();
        pubMedID = match[2].trim().replace("/pubmed/", "");
        
        DOI =  match[4].split("doi: ");
        DOI =  DOI[DOI.length - 1].trim();

        if (DOI.substring(DOI.length-1) == "."){
            DOI = DOI.substring(0, DOI.length-1);
        }

        pubJurnal = match[5].trim();
        autors = match[3].split(",");
        autor = autors[autors.length - 1].trim();
        // If there are more then one authors lets insert that last author
        if (autors.length > 1 && autor.length > 0 && autor !== 'et al.') {

            connection.beginTransaction(function(err) {
                if (err) {
                    throw err;
                }
                hash = crypto.createHash('md5').update(autor).digest('hex');
                sql1 = "INSERT INTO `pubmed`.`autors` (`id`, `name`, `hash`, `count`) VALUES (NULL, " + connection.escape(autor) + ",  " + connection.escape(hash) + ", '1')  ON DUPLICATE KEY UPDATE count=count+1;";
                
                connection.query(sql1, function(err, result) {
                    if (err) {
                        connection.rollback(function() {
                            throw err;
                        });
                    }
                    if (pubTitle.length > 0 && pubJurnal.length > 0 && pubMedID.length > 0) {
                        uid = result.insertId;
                        sql2 = "INSERT INTO `pubmed`.`papers` (`id`, `uid`, `title`, `jurnal`, `pubmedid`, `doi`) VALUES (NULL, " + connection.escape(uid) + ", " + connection.escape(pubTitle) + ", " + connection.escape(pubJurnal) + ", " + connection.escape(pubMedID) + ", " + connection.escape(DOI) + ");";
                        
                        connection.query(sql2, function(err, result) {
                            if (err) {
                                connection.rollback(function() {
                                    throw err;
                                });
                            }
                            connection.commit(function(err) {
                                if (err) {
                                    connection.rollback(function() {
                                        throw err;
                                    });
                                }

                                counter++;
                            });
                        });
                    }
                });
            });
        }
    }
}

lr.on('line', function(line) {
    processLine(line);
});



