router.use('/', jwtCheck);

router.put('/', function(req, res) {
  var sql="UPDATE stagiaire SET nom = "+mysql.escape(req.body.nom)+","+
          "prenom = "+mysql.escape(req.body.prenom)+","+
          "datenaissance = "+mysql.escape(req.body.datenaissance)+","+
          "numeromatricule = "+mysql.escape(req.body.matricule)+","+
          "titre = "+mysql.escape(req.body.civilite)+" "+
          "WHERE stagiaire.idstagiaireextranet = "+mysql.escape(req.body.id);
  connection.query(sql, function(err, rows, fields) {
    if (!err)
      res.send('The solution is: ', rows);
    else
      res.send('Error while performing Query.',err);
  });
});

router.delete('/:idsession/:idparticipant', function(req, res){
  var sql="DELETE FROM stagestagiaire WHERE stagestagiaire.idstagiaireextranet = '"+mysql.escape(req.params.idparticipant)+"' "+
          "AND stagestagiaire.idsession = '"+mysql.escape(req.params.idsession)+"'";
  connection.query(sql, function(err, rows, fields) {
    if (!err)
      res.send('The solution is: ', rows);
    else
      res.send('Error while performing Query.',err);
  });
});

router.post('/session/', function(req,res){
  var sql="INSERT INTO stagestagiaire (idclientprospect, idsession, idsessionidclientprospect, idstagiaireextranet) "+
          "VALUES ("+mysql.escape(req.user.id)+","+mysql.escape(req.body.idsession)+","+mysql.escape(req.body.idsession+req.user.id)+","+
          mysql.escape(req.body.idparticipant)+")";
  connection.query(sql, function(err, rows, fields) {
    if (!err)
      res.send('The solution is: ', rows);
    else
      res.send('Error while performing Query.',err);
  });
});

router.post('/', function(req,res){
  var sql="INSERT INTO stagiaire (nom, prenom, datenaissance, datecreation, numeromatricule, titre, idclientprospect) "+
          "VALUES ("+mysql.escape(req.body.nom)+","+mysql.escape(req.body.prenom)+","+mysql.escape(req.body.datenaissance)+
          ", CURDATE(), "+mysql.escape(req.body.matricule)+","+mysql.escape(req.body.civilite)+","+mysql.escape(req.user.id)+"); "+
          "INSERT INTO stagestagiaire (idclientprospect, idsession, idsessionidclientprospect, idstagiaireextranet) "+
          "VALUES ("+mysql.escape(req.user.id)+","+mysql.escape(req.body.idsession)+","+mysql.escape(req.body.idsession+req.user.id)+
          ", LAST_INSERT_ID()); SELECT max(idstagiaireextranet) as id FROM stagiaire;";
  connection.query(sql, function(err, rows, fields) {
    if (!err)
     res.send('The solution is: ', rows);
    else
      res.send('Error while performing Query.'+err,err);
  });
});

module.exports = router;