const express = require('express');
const app = express();
var bodyParser = require('body-parser');
var multer = require('multer');
var upload = multer();
var fs = require("fs");
const mysql = require('mysql');
const dauria = require('dauria');

const con = mysql.createConnection({
    host : "localhost",
    port : "3306",
    user : "root",
    password : "irena",
    database : "apitest"
});

con.connect(function(err){
    if(err) throw err;
    console.log("Connected!");
});

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

app.get('/api/post',function(req,res){

    con.query("SELECT post_id,title,content,create_on,file_id,path,categories FROM post,image,categories WHERE post.post_id=image.file_id AND post.c=categories.id",function(err,results,fields){
        if(err) throw err;

        console.log(results);

        res.send(results);

    });

}); //-OK-

app.post('/api/post',upload.array(),function(req,res,next){

    const title = req.body.title;
    const content = req.body.content;
    const Categories = "\"" + req.body.categories + "\"";
    const uri = req.body.uri;
    const time = Math.floor(new Date().valueOf() / 1000);
    var Cid;

    con.query("SELECT MAX(post_id) AS id FROM post",function(err,results,fields){
        if(err) throw err;

        var id = results[0].id + 1;
        console.log(id);
        con.query("SELECT id FROM categories WHERE categories = ?",Categories,function(err1,results1,fields1){
            if (err1) throw err1;
            if(!results){

                con.query("SELECT MAX(id) AS Cid FROM categories",function(err2,results2,fields2){
                    if(err2) throw err2;
                    Cid = results2[0].Cid + 1;
                    const paramC = [Cid,Categories];

                    con.query("INSERT INTO categories (id,categories) VALUES (?,?)",paramC,function(err3,results3,fields3){
                        if(err3) throw err3;
                        else console.log("categories insert OK!");
                    });

                });

            }
            else {
                Cid = results1;
                console.log("categories 已經存在!");
            }

            const paramP = [id,title,content,time,Cid];

            con.query("INSERT INTO post (post_id,title,content,create_on,c) VALUES (?,?,?,?,?)",paramP,function(err1,results1,fields1){
                if(err1) throw err1;
                else console.log("post insert OK!");
            });

        });


        if(uri){
            const path = "image" + id +".png";
            fs.writeFile(path,dauria.parseDataURI(uri).buffer,function(err1){
                if(err1) throw err1;
                const path1 = "\"" +path + "\"" ;
                const uri1 =  "\"" + uri + "\"" ;
                const paramI = [id,uri1,path1];
                con.query("INSERT INTO image (file_id,uri,path) VALUES (?,?,?)",paramI,function(err2,results1,fields1){
                    if(err2) throw err2;
                    else console.log("image insert OK!");
                });
            });
        }
        else {
            console.log("No image!");
            con.query("INSERT INTO image (file_id) VALUES (?)",id,function(err2,results1,fields1){
                if(err2) throw err2;
                else console.log("image insert OK!(only id)");
            });
        }

    });

});

app.get('/api/post/:id',function(req,res){

    const sql = "SELECT post_id,title,content,create_on,file_id,path,categories FROM post,image,categories WHERE post.post_id = image.file_id AND image.file_id = ? AND post.c = Categories.id";

    con.query(sql,req.params.id,function(err,results,fields){
        if(err) throw err;

        console.log(results);
        res.send(results);
    });

});//-OK-

app.patch('/api/post/:id',function(req,res){

    const id = req.params.id;
    const title = req.body.title;
    const content = req.body.content;
    const Categories = req.body.categories;
    const uri = req.body.uri;

    if (title) {
        const sql = "UPDATE post SET title = \""+title+"\" WHERE post_id = " + id;
        con.query(sql,function(err,results,fields){
            if(err) throw err;
            console.log("title 更改OK!");
        });
    } else {
        console.log("title 沒有變動!");
    }

    if (content) {
        const sql = "UPDATE post SET content = \""+content+"\" WHERE post_id = " + id;
        con.query(sql,function(err,results,fields){
            if(err) throw err;
            console.log("content 更改OK!");
        });
    } else {
        console.log("content 沒有變動!");
    }

    if (Categories) {
        const sql = "SELECT id FROM categories WHERE categories = \"" + Categories + "\"";
        con.query(sql,function(err,results,fields){
            if(err) throw err;
            if(results){
                console.log(results);
                console.log(results[0].id);
                console.log(typeof results[0].id);
                con.query("UPDATE post SET c = ? WHERE post_id = ?",results[0].id,id,function(err1,results1,fields1){
                    if(err1) throw err1;
                    console.log("Categories 更改OK!(Categories存在)");
                });

            }
            else{
                con.query("SELECT MAX(id) FROM categories",function(err1,results1,fields1){

                    const Cid = results1 +1;
                    con.query("INSERT INTO categories (id,categories) VALUES (?,?)",Cid,Categories,function(err2,results2,fields2){
                        if(err) throw err;
                        console.log("Categories 新增OK!");
                    });

                    con.query("UPDATE post SET c = ? WHERE id = ?",Cid,id,function(err2,results2,fields2){
                        if(err) throw err;
                        console.log("Categories 更改OK!");
                    });
                });
            }
        });
    } else {
        console.log("categories 沒有變動!");
    }

    if (uri) {
        const path = "image" + id +".png";
        fs.writeFile(path,dauria.parseDataURI(uri).buffer,function(err1){
            if(err1) throw err1;
            const sql = "UPDATE image SET uri = \""+uri+"\",path = \""+path+"\" WHERE file_id = " + id;
            con.query(sql,function(err2,results2,fields2){
                if(err2) throw err2;
                else console.log("image 更改OK!");
            });
        });
    } else {
        console.log("image 沒有變動!");
    }

    const sql = "SELECT post_id,title,content,create_on,file_id,path,categories FROM post,image,categories WHERE post.post_id = image.file_id AND image.file_id = ? AND post.c = Categories.id";

    con.query(sql,req.params.id,function(err,results,fields){
        if(err) throw err;

        console.log(results);
        res.send(results);
    });

}); //-少categories-

app.delete('/api/post/:id',function(req,res){
    const id = req.params.id;
    con.query("DELETE FROM post WHERE post_id =?",id,function(err,results,fields){
        if(err) throw err;
        console.log("delete post!");
    });
    con.query("DELETE FROM image WHERE file_id =?",id,function(err,results,fields){
        if(err) throw err;
        console.log("delete image!");
    });
});//-OK-
app.get('/api/page/post',function(req,res){
    con.query("SELECT post_id,title,content,create_on,uri,categories FROM post,image,categories WHERE post.post_id=image.file_id AND post.c=categories.id",function(err,results,fields){
        if(err) throw err;

        console.log(results);

        res.send(results);

    });
});//-OK-
app.get('/api/page/post/:id',function(req,res){

    const sql = "SELECT post_id,title,content,create_on,uri,categories FROM post,image,categories WHERE post.post_id = image.file_id AND image.file_id = ? AND post.c = Categories.id";

    con.query(sql,req.params.id,function(err,results,fields){
        if(err) throw err;

        console.log(results);
        res.send(results);
    });

});//-OK-
app.get('/api/post/categories',function(req,res){
    con.query("SELECT * FROM categories",function(err,results,fields) {
        if(err) throw err;

        console.log(results);

        res.send(results);

    });
});

const server = app.listen(3000, function () {
    console.log("connected localhost:3000");
});