require("dotenv").config();

const bodyParser = require("body-parser");
const pgp = require("pg-promise")();
const express = require("express");
const app = express();
const db = pgp({
  host: "localhost",
  port: 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD
});

app.use(bodyParser.json());

// app.get('/api/songs', function(req, res){
//     db.any('SELECT * FROM song')
//       .then(function(data) {
//           res.json(data);
//       })
//       .catch(function(error) {
//           res.json({error: error.message});
//       });
// });

/*
app.get('/api/songs/:id', function(req, res){
    const id = req.params.id;
      db.any('SELECT * FROM song WHERE id=$1', [id])
        .then(function(data) {
            res.json(data);
        })
        .catch(function(error) {
            res.json({error: error.message});
        });
  });
  */

app.get("/api/songs/:id", function(req, res) {
  const id = req.params.id;
  db.any(
    `SELECT song.id,artist.name, song.title
      FROM song, artist
      WHERE artist.id = song.artist_id
      AND song.id = $1`,
    [id]
  )
    .then(function(data) {
      res.json(data);
    })
    .catch(function(error) {
      res.json({ error: error.message });
    });
});

app.post("/api/songs", function(req, res) {
  // using destructing assignment to
  // extract properties into variables
  const { artistId, title, year } = req.body;
  // ES6 strings for multiline
  db.one(
    `INSERT INTO song(artist_id, title, year)
        VALUES($1, $2, $3) RETURNING id`,
    [artistId, title, year]
  )
    .then(data => {
      db.one(
        `SELECT song.id, song.title, artist.name AS artist
            FROM song, artist
            WHERE artist.id = song.artist_id
            AND song.id = $1`,
        [data.id]
      ).then(data => {
        res.json(Object.assign({}, { id: data.id }, req.body));
      });
      // let's combine returned id with submitted data and
      // return object with id to user
    })
    .catch(error => {
      res.json({
        error: error.message
      });
    });
});

app.get("/api/artists", function(req, res) {
  db.any("SELECT * FROM artist")
    .then(function(data) {
      res.json(data);
    })
    .catch(function(error) {
      res.json({ error: error.message });
    });
});

app.post("/api/artists", function(req, res) {
  const { name, email } = req.body;
  // ES6 strings for multiline
  db.one(
    `INSERT INTO artist (name, email)
            VALUES($1, $2) RETURNING id`,
    [name, email]
  )
    .then(data => {
      res.json(Object.assign({}, { id: data.id }, req.body));
    })
    .catch(error => {
      res.json({
        error: error.message
      });
    });
});

// Add new playlist to the db
app.post("/api/playlists", function(req, res) {
  const { name } = req.body;
  db.one(
    `INSERT INTO playlist (name)
            VALUES($1) RETURNING id`,
    [name]
  )
    .then(data => {
      res.json(Object.assign({}, { id: data.id }, req.body));
    })
    .catch(error => {
      res.json({
        error: error.message
      });
    });
});

// Posting a song to a playlist

app.post("/api/playlists/:playlistId/songs", function(req, res) {
  const { songId } = req.body;
  const playlistId = req.params.playlistId;

  db.one(
    `INSERT INTO song_playlist (song_id, playlist_id) VALUES ($1, $2) RETURNING id`,
    [songId, playlistId]
  )
    .then(playlist => {
      return db
        .any(
          `SELECT playlist.name, song.title, artist.name
                FROM song_playlist, playlist, artist, song
                WHERE song_playlist.playlist_id=$1
                  AND song_playlist.song_id = song.id
                  AND song.artist_id = artist.id
                  AND song_playlist.playlist_id = playlist.id`,
          [playlistId, playlist]
        )
        .then(data => res.json(data));
    })
    .catch(error => {
      res.status(404).json({
        error: error.message
      });
    });
});

app.get("/api/songs", function(req, res) {
  db.any(
    "SELECT song.id, song.title, song.year, artist.name AS artist FROM song, artist WHERE artist.id = song.artist_id"
  )
    .then(function(data) {
      res.json(data);
    })
    .catch(function(error) {
      res.json({ error: error.message });
    });
});

app.get("/api/playlists", function(req, res) {
  db.any("SELECT * FROM playlist")
    .then(function(data) {
      res.json(data);
    })
    .catch(function(error) {
      res.json({ error: error.message });
    });
});

// DELETE /playlists/:id/songs/:songId should delete the song with songId from the relevant playlist

app.delete("/api/playlists/:id/songs/:songId", function(req, res) {
  db.any(
    `
    DELETE FROM song_playlist
    WHERE
        song_playlist.song_id = $1
        AND song_playlist.playlist_id = $2`,
    [req.params.songId, req.params.id]
  )
    .then(function(data) {
      res.json(
        `DELETED song ID ${req.params.songId} of ${req.params.id} playlist`
      );
    })
    .catch(function(error) {
      res.json({ error: error.message });
    });
});

// DELETE /playlists/:id should delete the playlist with relevant id from the database. Before deleting a playlist, you will need to delete all rows from song_playlist table that reference the playlist being deleted.

app.delete("/api/playlists/:id", function(req, res) {
  db.one(`SELECT name FROM playlist WHERE id = $1`, [req.params.id])
    .then(data => {
      db.any(
        `DELETE FROM song_playlist WHERE song_playlist.playlist_id = $1; DELETE FROM playlist WHERE id = $1`,
        [req.params.id]
      );
      return data;
    })
    .then(function(playlistName) {
      res.json(`Destroyed your ${playlistName.name} playlist`);
    })
    .catch(function(error) {
      res.json({ error: error.message });
    });
});

// PATCH /artists/:id should receive an artist object with name and/or email and update the corresponding entry in the database
app
  .patch("/api/artists/:id", function(req, res) {
    if (req.body.name) {
      db.one(
        `UPDATE artist SET name = $2 WHERE id = $1 RETURNING name`,
        [req.params.id, req.body.name ]
      )
      .then(({ name }) =>
      res.json(
        `${name} is your artist's new name`
      )
    )
    .catch(function(error) {
        res.json({ error: error.message });
      })
    }
    if (req.body.email) {
      db.one(
        `UPDATE artist SET email = $2 WHERE id = $1 RETURNING email`,
        [req.params.id ,req.body.email]
      )
      .then(({email }) =>
      res.json(
        `Your artist's new email address is ${email}`
      )
    )
    .catch(function(error) {
        res.json({ error: error.message });
      })
    }

  })


app.listen(8080, function() {
  console.log("Listening on port 8080!");
});