//const config = require('config');
const app = require('express')();
const PORT = 5101;
var mysql = require('mysql2');


/*
mysql -u root -p -h 10.10.33.60
SET GLOBAL validate_password.policy=LOW;
CREATE USER 'wroot'@'10.0.0.52' IDENTIFIED BY 'wroot@#Chi3fW0rks.52';
GRANT ALL PRIVILEGES ON coregrade_blog.* TO 'wroot'@'10.0.0.52';
GRANT ALL PRIVILEGES ON blog_mermsemr_com.* TO 'wroot'@'10.0.0.52';
*/


  var con = mysql.createConnection({
      host: "10.10.33.60",    // ip address of server running mysql
      user: "wroot",    // user name to your mysql database
      password: "wroot@#Chi3fW0rks.52",    // corresponding password
      database: "blog_mermsemr_com"  // use this database to querying context
    });


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

    let findPostsQry = `SELECT p1.id AS id,  p1.*, wm2.meta_value FROM  wp_mermsemrposts p1 
                        LEFT JOIN wp_mermsemrpostmeta wm1 ON 
                        (wm1.post_id = p1.id AND wm1.meta_value IS NOT NULL AND wm1.meta_key = '_thumbnail_id' )  
                        LEFT JOIN wp_mermsemrpostmeta wm2 ON 
                        (wm1.meta_value = wm2.post_id AND wm2.meta_key = '_wp_attached_file' AND wm2.meta_value IS NOT NULL   ) 
                        WHERE p1.post_status='publish' AND p1.post_type='post' 
                        ORDER BY p1.post_date DESC LIMIT 9`;

      con.query(findPostsQry, function (err, result, fields) {
      // if any error while executing above query, throw error
      if (err) throw err;
      // if there is no error, you have the result
      console.log(result);
      res.status(200).json( [{payload: result}]);
    });
  })

  app.get('/wp/coregrade', function (req, res) {

    let findPostsQry = `SELECT p1.id AS id,  p1.*, wm2.meta_value FROM  wp_mermsemrposts p1 
                        LEFT JOIN wp_mermsemrpostmeta wm1 ON 
                        (wm1.post_id = p1.id AND wm1.meta_value IS NOT NULL AND wm1.meta_key = '_thumbnail_id' )  
                        LEFT JOIN wp_mermsemrpostmeta wm2 ON 
                        (wm1.meta_value = wm2.post_id AND wm2.meta_key = '_wp_attached_file' AND wm2.meta_value IS NOT NULL   ) 
                        WHERE p1.post_status='publish' AND p1.post_type='post' 
                        ORDER BY p1.post_date DESC LIMIT 9`;

      con.query(findPostsQry, function (err, result, fields) {
      // if any error while executing above query, throw error
      if (err) throw err;
      // if there is no error, you have the result
      console.log(result);
      res.status(200).json( [{payload: result}]);
    });
  })

  app.get('/wp/chiefsoft', function (req, res) {

    let findPostsQry = `SELECT p1.id AS id,  p1.*, wm2.meta_value FROM  wp_mermsemrposts p1 
                        LEFT JOIN wp_mermsemrpostmeta wm1 ON 
                        (wm1.post_id = p1.id AND wm1.meta_value IS NOT NULL AND wm1.meta_key = '_thumbnail_id' )  
                        LEFT JOIN wp_mermsemrpostmeta wm2 ON 
                        (wm1.meta_value = wm2.post_id AND wm2.meta_key = '_wp_attached_file' AND wm2.meta_value IS NOT NULL   ) 
                        WHERE p1.post_status='publish' AND p1.post_type='post' 
                        ORDER BY p1.post_date DESC LIMIT 9`;

      con.query(findPostsQry, function (err, result, fields) {
      // if any error while executing above query, throw error
      if (err) throw err;
      // if there is no error, you have the result
      console.log(result);
      res.status(200).json( [{payload: result}]);
    });
  })

 
app.listen(PORT,
    ()=>{
        console.log(`WD Connect is alive on port:${PORT}`);
    });