var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('boardDB');

var multer = require('multer');
var upload = multer();

var bodyParser = require('body-parser')
var jsonParser = bodyParser.json()

var express = require('express');
var restapi = express();

restapi.use(bodyParser.json({limit: '1mb'}));
restapi.use(bodyParser.urlencoded({extended : true}));


//add a table and some test data
db.serialize(function() {
 db.run("CREATE TABLE IF NOT EXISTS boards (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, description TEXT, category TEXT, image TEXT)");
 db.run("CREATE TABLE IF NOT EXISTS reviews (id INTEGER PRIMARY KEY AUTOINCREMENT, boardID INTEGER, username TEXT, review TEXT, rating INTEGER,reviewDate TEXT)");  
});


restapi.use(function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
    next();
  });


   
//default
restapi.get('/', function(req, res){
  res.send('HELLO WORLD');
});

//add review to DB
restapi.post('/addreview', upload.array(), function(req, res, next){
  console.log(req.body);
  db.run("INSERT INTO reviews (boardID, username, review, rating, reviewDate) VALUES (?,?,?,?,?)", req.body.boardID, req.body.username, req.body.review, req.body.rating, req.body.reviewDate,
   function(error){
      if (error){        
          console.err(error);        
          res.status(500);      
          } else {        
             res.status(202);
          } res.end();    
  }); 
 });

 //display all review rows in the table and return as json
restapi.get('/allreviews', function(req, res){
  db.all("select * from reviews", function(err, rows){
  res.json(rows);
  });
});

//add board to DB
restapi.post('/addboard', upload.array(), function(req, res, next){
  console.log(req.body);
  db.run("INSERT INTO boards (name, description, category, image) VALUES (?,?,?,?,?)", req.body.name, req.body.descrition, req.body.category, req.body.image,
   function(error){
      if (error){        
          console.err(error);        
          res.status(500);      
          } else {        
             res.status(202);
          } res.end();    
  }); 
 });


//display all rows in the table and return as json
restapi.get('/allboards', function(req, res){
  db.all("select * from boards", function(err, rows){
  res.json(rows);
  });
});

//display all rows where category surfboard in the table and return as json
restapi.get('/allsurfboards', function(req, res){
  db.all("select * from boards where category='surfboard'", function(err, rows){
  res.json(rows);
  });
 });

//display all rows where category snowboard in the table and return as json
restapi.get('/allsnowboards', function(req, res){
  db.all("select * from boards where category='snowboard'", function(err, rows){
  res.json(rows);
  });
 });

restapi.listen(3000);
console.log("Up and running..");