const express = require('express');
const router = express.Router();
const mysqlConnection = require('../database');
var md5 = require('md5');
var http = require("http");
const nodemailer = require("nodemailer");

const accountSid = 'ACd78bf191838dec795b139d5a736ed5c0';
const authToken = 'dcef18b7b7b9898277187a62d4fc3102';
const client = require('twilio')(accountSid, authToken);


//FCM END

//Offnet OTP

//Offnet OTP end

console.log('server online');

router.all('/', function(req, res, next) {
  res.header("Access-Control-Allow-Origin", "*");
  res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
  res.header('Access-Control-Allow-Methods', 'GET,PUT,POST,DELETE');
  next();
 });

//viewing all users data
router.get('/',(req,res)=>{
  mysqlConnection.query('SELECT * FROM users',(err, rows, fields)=>{
      if(!err){
          res.json(rows);
      } else {
          console.log(err);
          //mysqlConnection.end();
      }
  });
});


router.get('/transinfo',(req, res)=>{
  const userid_fk = req.query.userid_fk;
  var query = 'SELECT * FROM cimbbankdb.transactions WHERE transactionid = (SELECT MAX(transactionid) FROM cimbbankdb.transactions WHERE userid_fk = "'+userid_fk+'")';
  mysqlConnection.query(query, (err, rows, fields)=>{
    if(!err){
      res.json(rows);
    }else{
      console.log(err);
    }
  })
})


//username check if exist
router.get('/userinfo',(req,res)=>{
  const username = req.query.username;
  const password = req.query.password;
  var query = 'SELECT * FROM cimbbankdb.users WHERE username = "'+username+'"';
  mysqlConnection.query(query, (err, rows, fields)=>{
    if(!err){
        res.json(rows);
    } else {
        console.log(err);
    }
  });
  });


//select userinfo with UUID
router.get('/uuidinfo',(req,res)=>{
  const uuid = req.query.uuid;
  var query = 'SELECT * FROM cimbbankdb.users WHERE uuid = "'+uuid+'"';
  mysqlConnection.query(query, (err, rows, fields)=>{
    if(!err){
        res.json(rows);
    } else {
        console.log(err);
    }
  });
});
//
//viewing a specific user's data via ID & password
router.get('/userlogin',(req,res)=>{
  const username = req.query.username, password = req.query.password;
  var query = 'SELECT * FROM cimbbankdb.users WHERE username = "'+username+'" AND password ="'+password+'"';
  mysqlConnection.query(query, (err, rows, fields)=>{
    if(!err){
        res.json(rows);
    } else {
        console.log(err);
    }
  });
});

//loginattempt
var timestamp;
var timestamparray = [];
var hasharray = [];
var clienthash;
router.get('/loginattempt', (req,res) => {

const genesisblock = req.query.genesisblock;
const clienttimestamp = req.query.clienttimestamp;

var query = 'SELECT * FROM cimbbankdb.users WHERE genesisblock = "'+genesisblock+'"';
mysqlConnection.query(query, (err, rows, fields)=>{


	if(!err){
 	if(!(rows.length > 0)){
	console.log(genesisblock);
	console.log(clienttimestamp);
	console.log(rows);
	res.json({status: "tampered"});
	} else {
	console.log('hey');
	clienthash = md5(rows[0].uuid + clienttimestamp + rows[0].username);
	timestamp = Math.floor((new Date).getTime()/1000);
	console.log(timestamp);


	for(i=1; i<6; i++){

	timestamparray[i] = timestamp + i;
	timestamparray[i+5] = timestamp - i;
}



	for(i=0; i<11; i++){
	hasharray[i]= md5(rows[0].uuid + timestamparray[i] + rows[0].username);

	if(i == 10){
	hasharray[i]= md5(rows[0].uuid + timestamp + rows[0].username);
	}
console.log(hasharray[i]);

if(clienthash == hasharray[i]){
	var newgenquery = 'UPDATE cimbbankdb.users SET genesisblock  = "'+hasharray[i]+'" WHERE genesisblock = "'+genesisblock+'"';

	mysqlConnection.query(newgenquery, (err, rows, fields)=>{
    	if(!err){
        	console.log('successful');
   	 } else {
        	console.log(err);
   	 }
  });


	res.json({status: "1", newgen: hasharray[i]});
	return;
}

}

res.json({status:"0"});
}
	} else {
	Console.log(err);
}
});
});

//getFunction
router.get('/getdetails', (req,res) => {
const genesisblock = req.query.genesisblock;
var query = 'SELECT username, account_balance, qrlogin FROM cimbbankdb.users WHERE genesisblock ="'+genesisblock+'"';
mysqlConnection.query(query, (err, rows, fields)=>{
	if(!err){
	res.json(rows);
	} else {
	console.log(err);
}
});
});

//qrLoginSwitch
router.get('/qrswitch', (req,res) => {
const genesisblock = req.query.genesisblock;
var query = 'SELECT qrlogin FROM cimbbankdb.users WHERE genesisblock ="'+genesisblock+'"';
var updatequery;
mysqlConnection.query(query, (err, rows, fields)=>{
	if(!err){

	if (rows[0].qrlogin == '1'){
	 updatequery = 'UPDATE cimbbankdb.users SET qrlogin = "0" WHERE genesisblock = "'+genesisblock+'"';
	} else {
	 updatequery = 'UPDATE cimbbankdb.users SET qrlogin = "1" WHERE genesisblock = "'+genesisblock+'"';
	}

	mysqlConnection.query(updatequery, (err, rows, fields)=>res.json({status:'1'}));
	console.log('success');
	} else {
	console.log(err);
}
});
});


//transactionhistory
router.get('/transactionhistory', (req,res) => {
const genesisblock = req.query.genesisblock;
var relateduser;
var query = 'SELECT * FROM cimbbankdb.users WHERE genesisblock = "'+genesisblock+'"';

mysqlConnection.query(query, (err, rows, fields)=>{
	if(!err){
    relateduser = rows[0].username;
    var selecttransaction= 'SELECT * FROM cimbbankdb.transaction WHERE relateduser = "'+relateduser+'"';
    mysqlConnection.query(selecttransaction, (err, rows, fields)=>{
        res.json(rows);
    })
	console.log('success');
	} else {
	console.log(err);
}
});
});

//nfcTransaction
router.get('/nfctransaction', (req,res) => {
const genesisblock = req.query.genesisblock;
const receipienthash = req.query.recipienthash;
const amount = req.query.amount;

var email;
var senderaccbal;
var receipientaccbal;
var newsend, newrec, usdconamt, usdamt;

var query = 'SELECT username, account_balance, email FROM cimbbankdb.users WHERE genesisblock = "'+genesisblock+'"';
var senderquery = 'SELECT username, account_balance FROM cimbbankdb.users WHERE genesisblock = "'+receipienthash+'"';


mysqlConnection.query(query, (err, rows, fields)=>{
	if(!err){
	console.log(rows);
  username = rows[0].username;
	senderaccbal = rows[0].account_balance;
  email = rows[0].email;
  var request = http.get('http://www.apilayer.net/api/live?access_key=fd6e268488adf2c8da2ef53fd57af530&currencies=MYR', function (response) {
      // data is streamed in chunks from the server
      // so we have to handle the "data" event
      var buffer = "",
          data,
          route;

      response.on("data", function (chunk) {
          buffer += chunk;
      });

      response.on("end", function (err) {
          // finished transferring data
          // dump the raw data
          data = JSON.parse(buffer);
          usdconamt = data.quotes.USDMYR;
          usdamt = amount / usdconamt;
          if (usdamt >= 1000) {
            //EMAIL START
            var transporter = nodemailer.createTransport({
             service: 'gmail',
             auth: {
                    user: 'noreplyuniqon@gmail.com',
                    pass: 'krazykodingklub123'
                }
            });
            const mailOptions = {
              from: 'noreplyuniqon@gmail.com', // sender address
              to: 'asleywhtan99@gmail.com', // list of receivers
              subject: 'Transaction Notification', // Subject line
              html: '<p>A transaction was made with the amount of USD'+usdamt+'</p>'// plain text body
            };
            transporter.sendMail(mailOptions, function (err, info) {
               if(err)
                 console.log(err)
               else
                 console.log(info);
            });

            client.api.accounts.create().then(account => console.log(account.sid)).done();

            client.messages
              .create({
                 body: 'A recent transaction was made with the amount of USD'+usdamt+'',
                 from: '+601117223186',
                 to: '+60164420075'
               })
              .then(message => console.log(message.sid))
              .done();

            //EMAIL END
            //SMS START

            //SMS END
          }
      });
  });

	mysqlConnection.query(senderquery, (err, rows, fields)=>{
    var receiverusername = rows[0].username;
		receipientaccbal = rows[0].account_balance;
		newrec = parseInt(receipientaccbal)+parseInt(amount);
		newsend = senderaccbal-amount;
		console.log(newrec);
		console.log(newsend);
		console.log(amount);

    var insertquery = 'INSERT INTO cimbbankdb.transaction(amount,recipient, relateduser) VALUES("'+amount+'","'+receiverusername+'","'+username+'")';
		var updatequery1 = 'UPDATE cimbbankdb.users SET account_balance = "'+newsend+'" WHERE genesisblock = "'+genesisblock+'"';
		var updatequery2 = 'UPDATE cimbbankdb.users SET account_balance = "'+newrec+'" WHERE genesisblock = "'+receipienthash+'"';

		mysqlConnection.query(updatequery1, (err, rows, fields)=>console.log('success1'));
		mysqlConnection.query(updatequery2, (err, rows, fields)=>{
      mysqlConnection.query(insertquery, (err, rows, fields)=>{
        console.log(insertquery);
        console.log('success2');
        res.json({status: "1"});
      })
		});



	});

	} else {
	console.log(err);
}
});
});

//famtagcreate
router.get('/famtagcreate', (req,res) => {
const genesisblock = req.query.genesisblock;
const amount = req.query.amount;
const name = req.query.name;
const recipientgenblock = req.query.recipientgenblock;
var relatedusername, relatedamount, newamount;

var query = 'SELECT username, account_balance FROM cimbbankdb.users WHERE genesisblock = "'+genesisblock+'"';
mysqlConnection.query(query, (err, rows, fields)=>{
	if(!err){
	if (rows.username !== ''){
	relatedusername = rows[0].username;
	relatedamount = rows[0].account_balance;
	newamount = relatedamount - amount;
	var insertquery = 'INSERT INTO cimbbankdb.familytag(famgenesisblock, amount, name, relatedusername) VALUES("'+recipientgenblock+'", "'+amount+'","'+name+'","'+relatedusername+'")';
	var deductquery = 'UPDATE cimbbankdb.users SET account_balance = "'+newamount+'" WHERE genesisblock = "'+genesisblock+'"';
	mysqlConnection.query(insertquery, (err, rows, fields)=>{
	if(!err){
	mysqlConnection.query(deductquery, (err, rows, fields)=>{
		res.json({status: "1"});
	});
	} else {
	console.log(err);
	}
});
}
	} else {
	Console.log(err);
}
});
});

//famtagget
router.get('/famtagget', (req,res) => {
const genesisblock = req.query.genesisblock;

var query = 'SELECT * FROM cimbbankdb.familytag WHERE famgenesisblock = "'+genesisblock+'"';
mysqlConnection.query(query, (err, rows, fields)=>{
	if(!err){
	res.json(rows);
	} else {
	Console.log(err);
}
});
});

//famtagget
router.get('/famtaggetrelated', (req,res) => {
const genesisblock = req.query.genesisblock;
var relatedusername;
var query = 'SELECT username FROM cimbbankdb.users WHERE genesisblock = "'+genesisblock+'"';
mysqlConnection.query(query, (err, rows, fields)=>{
	if(!err){
    relatedusername = rows[0].username;
	  var selectrelatedquery = 'SELECT * FROM cimbbankdb.familytag WHERE relatedusername = "'+relatedusername+'"';
    mysqlConnection.query(selectrelatedquery, (err, rows, fields)=>{
        res.json({response: 'John+Adam+Lucas'});
    })
	} else {
	Console.log(err);
}
});
});





//gethash

router.get('/gethash', (req,res) => {
const genesisblock = req.query.genesisblock;

var query = 'SELECT genesisblock FROM cimbbankdb.users';
mysqlConnection.query(query, (err, rows, fields)=>{
	if(!err){
	res.json(rows);
	} else {
	Console.log(err);
}
});
});



//chow new addition

router.post('/userregister', (req,res)=>{
  const username = req.body.username;
  const genesisblock = req.body.genesisblock;
  const uuid = req.body.uuid;
  const query = 'INSERT INTO cimbbankdb.users ( username, genesisblock, uuid ) VALUES ( ?,?,?)';
  mysqlConnection.query(query,[username, genesisblock, uuid ],(err, rows, fields) => {
    if(!err) {
        res.json({ Status: 'loolololl'});
	console.log('this is fine');
    } else {
        console.log(err);
    }
  });
});


//test manipulation
var reply = md5('abcdef');
router.post('/datamanipulation', (req,res)=>{
  const username = req.body.username;
  const password = req.body.password;
  const uuid = req.body.uuid;
  const query = 'INSERT INTO cimbbankdb.users ( username, password, uuid ) VALUES ( ?,?,?)';
  mysqlConnection.query(query,[username, password, uuid ],(err, rows, fields) => {


    if(!err) {
	reply = md5('yes');
	console.log(reply);
        res.json({ Status: 'lol', Reply: reply});
    } else {
	console.log(reply);
        console.log(err);
    }
  });
});






//Adding a new user
//router.post('/userregister', (req,res)=>{
  //const username = req.body.username;
  //const password = req.body.password;
  //const account_balance = req.body.account_balance;
  //const email = req.body.email;
  //const uuid = req.body.uuid;
  ///const securityquestion = req.body.securityquestion;
  //const securityanswer = req.body.securityanswer;
  //const fcmtoken = req.body.fcmtoken;
   //const  { username, password, email, uuid, securityquestion, securityanswer }  = req.body;
  //const query = 'INSERT INTO cimbbankdb.users ( username, password, account_balance, email,  securityquestion, securityanswer, uuid, fcmtoken ) VALUES ( ?,?,?,?,?,?,?,?)';
  //mysqlConnection.query(query,[username, password, account_balance, email,  securityquestion, securityanswer, uuid, fcmtoken],(err, rows, fields) => {
   // if(!err) {
       // res.json({ Status: 'lol'});
   // } else {
   //     console.log(err);
 //   }
 // });
//});

//update
router.put('/tapproval/:userid_fk',(req,res)=>{
  const approval = req.body.approval;
  const userid_fk = req.params.userid_fk;
  const query ='UPDATE transactions SET approval = ? WHERE transactionid = (SELECT MAX(transactionid) FROM (SELECT transactionid FROM transactions WHERE userid_fk =?) as transtable)';
  mysqlConnection.query(query, [approval , userid_fk],(err, rows, fields)=>{
    if(!err){
      res.json({Status:'succeeded'});
    }else{
      console.log(err);
    }  })
})

//update qrverification
router.put('/qrverification/:genesisblock',(req,res)=>{
const verification = req.body.verification;
  const genesisblock = req.params.genesisblock;
  const query ='UPDATE qrrequest SET verification = ? WHERE id = (SELECT MAX(id) FROM (SELECT id FROM qrrequest WHERE genesisblock =?) as qrtable);';
  mysqlConnection.query(query, [verification, genesisblock],(err, rows, fields)=>{
    if(!err){
      res.json({Status:'succeeded'});
    }else{
      console.log(err);
    }
  })
})


//update sercure
router.put('/usecure/:id',(req,res)=>{

  const secure = req.body.secure;
  const id = req.params.id;
  const query = 'UPDATE cimbbankdb.users SET users.secure = ? WHERE users.id = ?';
  mysqlConnection.query(query, [secure , id], (err, rows, fields)=>{
    if(!err){
      res.json({Status:'succeeded'});
      console.log(fields);
    }else{
      console.log(err);
    }
  })
})

//Change QRLogin Status
router.put('/uqrlogin/:id', (req,res) => {

  // const { qrlogin, username } = req.body;
  const qrlogin = req.body.qrlogin;
  const id = req.params.id;
  const query = 'UPDATE cimbbankdb.users SET users.qrlogin = ? WHERE users.id = ?';
  mysqlConnection.query(query, [ qrlogin, id], (err, rows, fields) =>{
    if(!err){
      res.json({Status: 'succeeded'});
      console.log(fields);
    } else {
      console.log(err);
    }
  });
});

router.put('/urelease/:id',(req,res)=>{
const id = req.params.id;
const uuid = req.body.uuid;
const fcmtoken = req.body.uuid;

const query = 'UPDATE users SET uuid = ? , fcmtoken = ? WHERE id = ?';
mysqlConnection.query(query,[uuid, fcmtoken,  id ], (err, rows, fields) =>{
if(!err){
res.json({Status: 'succeeded'});
} else {
console.log(err);
}
});
});

//updatefcmtoken when needed
router.put('/ufcmtoken/:id', (req,res) => {
  // const { qrlogin, username } = req.body;
  const fcmtoken = req.body.fcmtoken;
  const id = req.params.id;

  const query = 'UPDATE cimbbankdb.users SET fcmtoken = ? WHERE id = ?';
  mysqlConnection.query(query, [ fcmtoken, id ], (err, rows, fields) =>{
    if(!err){
      res.json({Status: 'succeeded'});
    } else {
      console.log(err);
    }
  });
});

router.put('/uuuid/:id',(req,res)=>{
  const uuid = req.body.uuid;
  const id = req.params.id;
  const query = 'UPDATE cimbbankdb.users SET uuid = ? WHERE id = ?';
  mysqlConnection.query(query,[uuid, id],(err, rows, fields)=>{
    if(!err){
      res.json({Status:'uuid Updated'});
    } else {
      console.log(err);
    }
  });
});

//Updating user data
router.put('/:id', (req,res) => {
  // const { username, password, account_balance } = req.body;
  const username = req.body.username;
  const password = req.body.password;
  const account_balance = req.body.account_balance;
  const  id  = req.params.id;
  const query = 'UPDATE cimbbankdb.users SET username = ?, password = ?, account_balance = ? WHERE id = ?';
  mysqlConnection.query(query, [ username, password, account_balance, id ], (err, rows, fields) =>{
    if(!err){
      res.json({Status: 'User Successfully Updated'});
    } else {
      console.log(err);
    }
  });
});

//deleting user database
router.delete('/:id', (req,res) =>{
  const id  = req.params.id;
  //const id = req.body.id;
  const query = 'DELETE FROM cimbbankdb.users WHERE id = ?';
  // const query = 'DELETE FROM cimbbankdb.users WHERE id = "'+id+'"';
  mysqlConnection.query(query, [ id ], (err, rows, fields) => {
    if (!err) {
      res.json({status:'Data Successfully Deleted'})
    } else {
      console.log(err);
    }
  });
});



module.exports = router;