const aws = require('aws-sdk');
const nodemailer = require('nodemailer');
const mysql = require('mysql');
const json2csv = require('json2csv').parse;
const fs = require('fs');

// create Nodemailer SES transporter
var transporter = nodemailer.createTransport({
    SES: new aws.SES({
        apiVersion: '2010-12-01'
    })
});

var connection = mysql.createConnection({
    host: "localhost",
    user: "dbuser",
    password: "dbpass",
    database: "students",
    port: 3306
});


exports.handler = (event, context, callback) => {
    connection.query('select user, password from user', function (error, results, fields) {
        if (error) {
            connection.destroy();
            throw error;
        } else {
            // connected!
            var fields = ['user', 'password'];
            const opts = {
                fields
            };
            const csv = json2csv(results, opts);
            var filename = Date.now();
            var path = '/tmp/' + filename + '.csv';
            fs.writeFile(path, csv, function (err, data) {
                if (err) {
                    throw err;
                } else {
                    console.log('file Created');
                    var attachments = [{ // file on disk as an attachment
                        filename: filename+'.csv',
                        path: '/tmp/'+filename+'.csv' // stream this file
                    }]
                    transporter.sendMail({
                        from: 'diegof.cornejo@gmail.com',
                        to: 'diegof.cornejo@gmail.com',
                        subject: 'Users',
                        text: 'Users',
                        html: '<b>Users</b>',
                        attachments: attachments
                    }, (err, info) => {
                        if (err) {
                            console.log(err)
                        } else {
                            console.log(info.envelope);
                            console.log(info.messageId);
                        }
                    });
                }
            });
            callback(error, results);
            connection.end(function (err) {
                callback(err, results);
            });
        }
    });
};