WCS Express quest 6

const connection = require('./db-config');
connection.connect(function (err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

const express = require('express');
const app = express();
app.use(express.json());
// const movies = require('./movies');
const port = 3000;

app.get('/', (request, response) => {
  response.send('Welcome to my favourite movie list');
});

// SEND DETAIL OF ERRORS
app.get('/api/movies', (req, res) => {
  let sql = 'SELECT * FROM movies';
  const sqlValues = [];
  if (req.query.max_duration && req.query.color) {
    sql += ' WHERE duration <= ? AND color = ?';
    sqlValues.push(req.query.max_duration);
    sqlValues.push(req.query.color);
  } else if (req.query.color) {
    sql += ' WHERE color = ?';
    sqlValues.push(req.query.color);
  } else if (req.query.max_duration) {
    sql += ' WHERE duration <= ?';
    sqlValues.push(req.query.max_duration);
  }
  connection.query(sql, sqlValues, (err, results) => {
    if (err) {
      res.status(500).send('Error retrieving data');
    } else {
      res.status(200).json(results);
    }
  });
});

app.get('/api/movies/:id', (req, res) => {
  connection.query('SELECT * from movies WHERE id=?', [req.params.id], (err, results) => {
    if (err) {
      console.log(err);
      res.status(500).send('Error retrieving data');
    } else if (results.length === 0) {
      res.status(404).send('user not found');
    } else {
      res.status(200).json(results);
    }
  });
});

app.get('/api/search', (request, response) => {
  const time = request.query.maxDuration;
  connection.query('SELECT * from movies WHERE duration<=?', [time], (err, results) => {
    if (err) {
      console.log(err);
      response.status(500).send('Error retrieving data');
    } else {
      response.status(200).json(results);
    }
  });
});

app.get('/api/users', (req, res) => {
  let sql = 'SELECT * FROM users';
  const sqlValues = [];
  if (req.query.language) {
    sql += ' WHERE language = ?';
    sqlValues.push(req.query.language);
  }
  connection.query(sql, sqlValues, (err, results) => {
    if (err) {
      res.status(500).send(`An error occurred: ${err.message}`);
    } else {
      res.json(results);
    }
  });
});

app.get('/api/users/:id', (req, res) => {
  connection.query('SELECT * from users WHERE id=?', [req.params.id], (err, results) => {
    if (err) {
      console.log(err);
      res.status(500).send('Error retrieving data');
    } else if (results.length === 0) {
      res.status(404).send('user not found');
    } else {
      res.status(200).json(results[0]);
    }
  });
});

app.post('/api/movies', (req, res) => {
  const { title, director, year, color, duration } = req.body;
  connection.query(
    'INSERT INTO movies(title, director, year, color, duration) VALUES (?, ?, ?, ?, ?)',
    [title, director, year, color, duration],
    (err, result) => {
      if (err) {
        res.status(500).send('Error saving the movie');
      } else {
        res.status(201).send('Movie successfully saved');
      }
    }
  );
});

app.post('/api/users', (req, res) => {
  const { firstname, lastname, email } = req.body;
  connection.query(
    'INSERT INTO users(firstname, lastname, email) VALUES (?, ?, ?)',
    [firstname, lastname, email],
    (err, result) => {
      if (err) {
        res.status(500).send('Error saving the user');
      } else {
        res.status(201).send('User successfully saved');
      }
    }
  );
});

// This route will update a user in the DB
app.put('/api/users/:id', (req, res) => {
  // We get the ID from the url path :
  const userId = req.params.id;
  // We get the new attribute values for the user from req.body
  const userPropsToUpdate = req.body;
  // We send a UPDATE query to the DB
  connection.query('UPDATE users SET ? WHERE id = ?', [userPropsToUpdate, userId], (err) => {
    // Once the DB operation is over, we can respond to the HTTP request
    if (err) {
      console.log(err);
      res.status(500).send('Error updating a user');
    } else {
      res.status(200).send('User updated successfully 🎉');
    }
  });
});

// This route will update a movie in the DB
app.put('/api/movies/:id', (req, res) => {
  const movieId = req.params.id;
  const moviePropsToUpdate = req.body;
  connection.query('UPDATE movies SET ? WHERE id = ?', [moviePropsToUpdate, movieId], (err) => {
    if (err) {
      console.log(err);
      res.status(500).send('Error updating a movie');
    } else {
      res.status(200).send('Movie updated successfully 🎉');
    }
  });
});

// The ID is passed as a route parameter
app.delete('/api/users/:id', (req, res) => {
  const userId = req.params.id;
  connection.query('DELETE FROM users WHERE id = ?', [userId], (err) => {
    if (err) {
      console.log(err);
      res.status(500).send('😱 Error deleting an user');
    } else {
      res.status(200).send('🎉 User deleted!');
    }
  });
});

// The ID is passed as a route parameter
app.delete('/api/movies/:id', (req, res) => {
  const movieId = req.params.id;
  connection.query('DELETE FROM movies WHERE id = ?', [movieId], (err) => {
    if (err) {
      console.log(err);
      res.status(500).send('😱 Error deleting an movie');
    } else {
      res.status(200).send('🎉 Movie deleted!');
    }
  });
});

app.listen(port, () => {
  console.log(`Server is running on ${port}`);
});