router.post('/usuarios', (req, res) =>{
    const nome = req.body.nome.substring(0,255);
    const email = req.body.email.substring(0,255);
    const senha = req.body.senha.substring(0,255);
    execSQLQuery(`INSERT INTO usuario(nome,email,senha) VALUES('${nome}','${email}','${senha}')`, res);
    res.sendStatus(200);
});

const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const port = 3000; //porta padrão
const mysql      = require('mysql');

//configurando o body parser para pegar POSTS mais tarde
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());

//definindo as rotas
const router = express.Router();
router.get('/', (req, res) => res.json({ message: 'Funcionando!' }));

router.get('/filmes/:id?', (req, res) =>{
    let filter = '';
    if(req.params.id) filter = ' WHERE ID=' + parseInt(req.params.id);
    execSQLQuery('SELECT * FROM filme' + filter, res);
});

router.get('/usuarios/:id?', (req, res) =>{
    let filter = '';
    if(req.params.id) filter = ' WHERE ID=' + parseInt(req.params.id);
    execSQLQuery('SELECT * FROM usuario' + filter, res);
});

router.delete('/usuarios/:id', (req, res) =>{
    execSQLQuery('DELETE FROM usuario WHERE ID=' + parseInt(req.params.id), res);
    res.sendStatus(200);
});

router.post('/usuarios', (req, res) =>{
    const nome = req.body.nome.substring(0,255);
    const email = req.body.email.substring(0,255);
    const senha = req.body.senha.substring(0,255);
    execSQLQuery(`INSERT INTO usuario(nome,email,senha) VALUES('${nome}','${email}','${senha}')`, res);
    res.sendStatus(200);
});

router.patch('/usuarios/:id', (req, res) =>{
    const id = parseInt(req.params.id);
    const nome = req.body.nome.substring(0,255);
    const email = req.body.email.substring(0,255);
    const senha = req.body.senha.substring(0,255);
    execSQLQuery(`UPDATE usuario SET Nome='${nome}', Email='${email}', Senha='${senha}' WHERE ID=${id}`, res);
    res.sendStatus(200);
});

app.use('/', router);

//inicia o servidor
app.listen(port);
console.log('API funcionando!');

function execSQLQuery(sqlQry, res){
  const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'locadora'
  });

  connection.query(sqlQry, function(error, results, fields){
      if(error)
        res.json(error);
      else
        res.json(results);
      connection.end();
      console.log('executou!');
  });
}