//With thanks to Brad Traversy!

const express = require('express');
const mysql = require('mysql');
//https://www.youtube.com/watch?v=EN6Dx22cPRI&t=677s
//https://nodesource.com/blog/running-your-node-js-app-with-systemd-part-1/
// Create connection
const db = mysql.createConnection({
    host     : 'localhost',
    user     : 'xxxx',
    password : 'xx',
   database : 'xxxxx'
});

// Connect
db.connect((err) => {
    if(err){
        throw err;
    }
    console.log('MySql Connected...');
});

const app = express();

 
app.get('/createdb', (req, res) => {
    let sql = 'CREATE DATABASE nodemysql';
    db.query(sql, (err, result) => {
        if(err) throw err;
        console.log(result);
        res.send('Database created...');
    });
});

 
app.get('/createpoststable', (req, res) => {
    let sql = 'CREATE TABLE posts(id int AUTO_INCREMENT, title VARCHAR(255), body VARCHAR(255), PRIMARY KEY(id))';
    db.query(sql, (err, result) => {
        if(err) throw err;
        console.log(result);
        res.send('Posts table created...');
    });
});



// add  MUST use quotes on URL line around new body
app.get('/add/:body', (req, res) => {
   // let sql = `SELECT * FROM posts WHERE id = ${req.params.body}`;
   let sql = `INSERT INTO posts SET body = ${req.params.body}`;
    let query = db.query(sql, (err, result) => {
        if(err) throw err;
        console.log(result);
        res.send('Post added...');
    });
});
 

// Insert post -hard coded
app.get('/addpost1', (req, res) => {
    let post = {title:'Post One', body:'This is post number 8'};
    let sql = 'INSERT INTO posts SET ?';
    let query = db.query(sql, post, (err, result) => {
        if(err) throw err;
        console.log(result);
        res.send('Post 1 added...');
    });
});

 

// Select posts
app.get('/getposts', (req, res) => {
    let sql = 'SELECT name, body FROM posts';
    let query = db.query(sql, (err, results) => {
        if(err) throw err;
        console.log(results);
       // res.send('Posts fetched ok 4...');
	   res.send (results);
		
    });
});

// Select single post
app.get('/getpost/:id', (req, res) => {
    let sql = `SELECT * FROM posts WHERE id = ${req.params.id}`;
    let query = db.query(sql, (err, result) => {
        if(err) throw err;
        console.log(result);
        res.send(result);
    });
});

// Update post
app.get('/updatepost/:id', (req, res) => {
    let newTitle = 'Updated Title';
    let sql = `UPDATE posts SET title = '${newTitle}' WHERE id = ${req.params.id}`;
    let query = db.query(sql, (err, result) => {
        if(err) throw err;
        console.log(result);
        res.send('Post updated...');
    });
});

// Delete post
app.get('/deletepost/:id', (req, res) => {
    let newTitle = 'Updated Title';
    let sql = `DELETE FROM posts WHERE id = ${req.params.id}`;
    let query = db.query(sql, (err, result) => {
        if(err) throw err;
        console.log(result);
        res.send('Post deleted...');
    });
});

app.listen('3000', () => {
    console.log('Server started on port 3000');
});