Gmail API Email Processing with MySQL connection pooling (hackathon)

quickstart.js

var fs = require('fs');
var readline = require('readline');
var google = require('googleapis');
var googleAuth = require('google-auth-library');
var _ = require('underscore');
var Promise = require('promise/setimmediate');

var SCOPES = [
'https://www.googleapis.com/auth/gmail.compose',
'https://www.googleapis.com/auth/gmail.readonly', 
'https://www.googleapis.com/auth/gmail.modify'
];
var TOKEN_DIR = (process.env.HOME || process.env.HOMEPATH ||
    process.env.USERPROFILE) + '/.credentials/';
var TOKEN_PATH = TOKEN_DIR + '...';

// Load client secrets from a local file.
fs.readFile('client_secret.json', function processClientSecrets(err, content) {
  if (err) {
    console.log('Error loading client secret file: ' + err);
    return;
  }
  // Authorize a client with the loaded credentials, then call the
  // Gmail API.
  authorize(JSON.parse(content), listMail);
});

//mysql cnxn
var mysql      = require('mysql');

var pool  = mysql.createPool({
  connectionLimit : 10,
  host     : 'localhost',
  user     : '...',
  password : '....',
  database : '.....'
});

/**
 * Create an OAuth2 client with the given credentials, and then execute the
 * given callback function.
 *
 * @param {Object} credentials The authorization client credentials.
 * @param {function} callback The callback to call with the authorized client.
 */
function authorize(credentials, callback) {
  var clientSecret = credentials.installed.client_secret;
  var clientId = credentials.installed.client_id;
  var redirectUrl = credentials.installed.redirect_uris[0];
  var auth = new googleAuth();
  var oauth2Client = new auth.OAuth2(clientId, clientSecret, redirectUrl);

  // Check if we have previously stored a token.
  fs.readFile(TOKEN_PATH, function(err, token) {
    if (err) {
      getNewToken(oauth2Client, callback);
    } else {
      oauth2Client.credentials = JSON.parse(token);
      callback(oauth2Client);
    }
  });
}

/**
 * Get and store new token after prompting for user authorization, and then
 * execute the given callback with the authorized OAuth2 client.
 *
 * @param {google.auth.OAuth2} oauth2Client The OAuth2 client to get token for.
 * @param {getEventsCallback} callback The callback to call with the authorized
 *     client.
 */
function getNewToken(oauth2Client, callback) {
  var authUrl = oauth2Client.generateAuthUrl({
    access_type: 'offline',
    scope: SCOPES
  });
  console.log('Authorize this app by visiting this url: ', authUrl);
  var rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout
  });
  rl.question('Enter the code from that page here: ', function(code) {
    rl.close();
    oauth2Client.getToken(code, function(err, token) {
      if (err) {
        console.log('Error while trying to retrieve access token', err);
        return;
      }
      oauth2Client.credentials = token;
      storeToken(token);
      callback(oauth2Client);
    });
  });
}

/**
 * Store token to disk be used in later program executions.
 *
 * @param {Object} token The token to store to disk.
 */
function storeToken(token) {
  try {
    fs.mkdirSync(TOKEN_DIR);
  } catch (err) {
    if (err.code != 'EEXIST') {
      throw err;
    }
  }
  fs.writeFile(TOKEN_PATH, JSON.stringify(token));
  console.log('Token stored to ' + TOKEN_PATH);
}

/**
 * Lists the labels in the user's account.
 *
 * @param {google.auth.OAuth2} auth An authorized OAuth2 client.
 */
function listLabels(auth) {
  var gmail = google.gmail('v1');
  gmail.users.labels.list({
    auth: auth,
    userId: 'me',
  }, function(err, response) {
    if (err) {
      console.log('The API returned an error: ' + err);
      return;
    }
    var labels = response.labels;
    if (labels.length == 0) {
      console.log('No labels found.');
    } else {
      console.log('Labels:');
      for (var i = 0; i < labels.length; i++) {
        var label = labels[i];
        console.log('- %s', label.name);
      }
    }
  });
}

function listMail(auth) {
  var gmail = google.gmail('v1');
  gmail.users.messages.list({
    auth: auth,
    userId: 'me',
  }, function(err, response) {
    if (err) {
      console.log('The API returned an error: ' + err);
      return;
    }
    // console.log(JSON.stringify(response, null, 2));

    _(response.messages).each(function(message) {
      getMessage(auth, message.id);
    });

    setTimeout(function() { listMail(auth); }, 20000);
  });
}

function getMessage(auth, id) {
  var gmail = google.gmail('v1');
  gmail.users.messages.get({
    auth: auth,
    userId: 'me',
    id: id
  }, function(err, message) {
    if (err) {
      console.log('The API returned an error: ' + err);
      return;
    }
    processMessage(auth, message);
  });
}

function processMessage(auth, message) {
  // console.log(JSON.stringify(message, null, 2));
  //do we have a message we care about?
  var toHeader = getHeader(message, 'To');
  if (toHeader && toHeader.value.indexOf('+') > -1) {
    var userId = toHeader.value.split('+')[1].split('@')[0];
    console.log('userId', userId);

    var plainPart = _(message.payload.parts).find(function(part) { 
      return part.mimeType == 'text/plain';
    });

    if (plainPart && plainPart.body.data) {
      var body = new Buffer(plainPart.body.data, 'base64').toString('ascii');
      var applicationLink = getLinkFromEmailBody(body)[0];
      var applicationPin = getPinFromEmailBody(body)[1];

      console.log('link from email: ' + applicationLink, 'pin', applicationPin);

      getUsersInfo(userId).then(function(response) {
        console.log('email: ', response);
        updateUserDetail(userId, applicationPin, applicationLink).then(function(res) {
          sendMemberMail(auth, response.first_name, response.email, applicationLink).then(function(res) {
            console.log('sent!', res);
            deleteMessage(auth, message.id);
          });
        });
      });
    }
  }
}

function sendMemberMail(auth, firstName, email, link) {
  var promise = new Promise(function (resolve, reject) {
    
    var gmail = google.gmail('v1');

    email_lines = [];
    email_lines.push("From: \"...\" <...@gmail.com>");
    email_lines.push("To: " + email);
    email_lines.push('Content-type: text/html;charset=iso-8859-1');
    email_lines.push('MIME-Version: 1.0');
    email_lines.push("Subject: Your Application");
    email_lines.push("Message-ID: <1234@local.machine.example>");
    email_lines.push("Date: Fri, 21 Nov 1997 09:55:06 -0600");
    email_lines.push("Hello " + firstName + ",<br/><br/>");
    email_lines.push("Your application is ready to be finalized.  Please visit your Dashboard at  https://localhost/member/dashboard to complete the application process.<br/><br/>");
    email_lines.push("PLEASE NOTE:  YOUR APPLICATION IS INCOMPLETE UNTIL YOU COMPLETE THIS LAST STEP.<br/><br/>");
    email_lines.push("Sincerely,<br/><br/>");
    email_lines.push("...");


    var emailMsg = email_lines.join("\r\n").trim();

    console.log('Sent email to', emailMsg);

    var base64EncodedEmail = new Buffer(emailMsg).toString('base64').replace(/\//g,'_').replace(/\+/g,'-');

    try {
      gmail.users.messages.send({
        auth: auth,
        userId: "me",
        resource: {
          raw: base64EncodedEmail
        }           
      }, function(err, response) {
        if (err) {
          console.log('The API returned an error: ' + err);
          reject(err);
          return;
        }
        resolve(response);
      });
    } catch (e) {
      //The API returned an error: Error: Insufficient Permission
      console.log('error!', e);
      reject(err);
    }
  });

  return promise;
}

function deleteMessage(auth, messageId) {
  console.log('delete');
  var gmail = google.gmail('v1');
  try {
    gmail.users.messages.trash({
      auth: auth,
      userId: "me",
      id: messageId
    }, function(err, response) {
      if (err) {
        console.log('The API returned an error: ' + err);
        return;
      }
      console.log('email deleted', response);
    });
  } catch (e) {
    //The API returned an error: Error: Insufficient Permission
    console.log('error!', e);
  }
}

function getHeader(message, desiredHeader) {
  return _(message.payload.headers).find(function(header) {
    return header.name === desiredHeader;
  });
}

var urlRegex = new RegExp('(http|ftp|https)://[a-z0-9\-_]+(\.[a-z0-9\-_]+)+([a-z0-9\-\.,@\?^=%&;:/~\+#]*[a-z0-9\-@\?^=%&;/~\+#])?', 'i');
var pinRegex = new RegExp(/(?:PIN: )(\d{5,})/);

function getLinkFromEmailBody(body) {
  return urlRegex.exec(body);
}

function getPinFromEmailBody(body) {
  return pinRegex.exec(body);
}

function getUsersInfo(userId) {
  var promise = new Promise(function (resolve, reject) {

    pool.getConnection(function(err, connection) {

      // Use the connection
      connection.query( 'SELECT u.username as email, p.first_name FROM user u, person p where u.id = ' + userId + ' and u.id = p.user_id', function(err, rows) {

        connection.release();

        if (err || !rows) {
          console.log('rejected');
          reject(err, rows);
        }

        console.log('resolve', rows[0]);
        resolve(rows[0]);
      });
    });
  });

  return promise;

}

function updateUserDetail(userId, pin, url) {
  var promise = new Promise(function (resolve, reject) {

    pool.getConnection(function(err, connection) {
      // Use the connection
      connection.query( 'update person set pending_application_url = \'' + url +  '\', pending_application_pin = \'' + pin + '\' where user_id = ' + userId, function(err, rows) {

        connection.release();

        if (err || !rows) {
          console.log('not added', err);
          reject(err, rows);
        }

        console.log('added', rows);
        resolve(rows);
      });
    });
  });

  return promise;

}