var express = require("express");
const router = express.Router();
const db = require("../../utils/database");

router.get("/selectDefect", (req, res) => {
  var status = req.query.status;
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      throw err;
    }
    console.log("get Connection");
    var query = `select * from finehhdi_eDefect.defect order by id desc`;
    connection.query(query, (err, result) => {
      connection.release();
      if (err) {
        console.log(err);
        res.status(401).json({
          message: "Some error in order selection",
          results: "Failed",
          data: [],
        });
      } else {
        res.status(201).json({
          message: "Order Created Successfully",
          results: "Success",
          data: result,
        });
      }
    });
    console.log("release Connection");
  });
});

//select machine
router.post("/selectMachineForReportDefect", (req, res) => {
  const areaName = req.body.areaName;
  console.log("selectMachine api ===>", areaName);

  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      throw err;
    }
    console.log("get Connection");
    let query = `SELECT * FROM finehhdi_eDefect.defect where department= "${areaName}"`;
    console.log("qury from selectMachine ====>", query);
  
    connection.query(query, (err, data) => {
        connection.release()
      if (err) {
        res.status(500).send({
          msg: "err getting result",
          data: [],
        });
      } else {
        res.status(200).send({
          msg: "success",
          data: data,
        });
      }
    });
    console.log("release Connection");
  });

});

//select machine with areaname and machine for filtering
router.post("/selectMachineForReportFilteringWithAreaName", (req, res) => {
  // const machine = req.body.machine
  // console.log(machine)

  // db.query(`SELECT * FROM finehhdi_eDefect.defect WHERE  machine = "${machine}"`, (err, data) => {
  const department = req.body.department;
  const machine = req.body.machine;

  console.log(
    "selectMachine with area and machine  ===>",
    department,
    "---",
    machine,
    "------"
  );
  // db.query =( `select machineTypes from finehhdi_eDefect.machineTypes where department="${areaName}" `,(err,data)=>{
  let query = `SELECT * FROM finehhdi_eDefect.defect where department= "${department}" and machine="${machine}"`;
  console.log("qury from selectMachine ====>", query);

  db.query(query, (err, data) => {
    if (err) {
      res.status(500).send({
        msg: "err getting result",
        data: [],
      });
    } else {
      res.status(200).send({
        msg: "success",
        data: data,
      });
      console.log("data", data);
    }
  });
});

// Select Individual Defect Feedback
router.post("/SelectIndiFeedback/:userId", (req, res) => {
  const userId = req.params.userId;
  console.log(userId);
  let query = `SELECT * FROM finehhdi_eDefect.defect where userId= "${userId}"`;
  console.log(query);
  db.query(query, (err, data) => {
    if (err) {
      res.status(500).send({ msg: "Error While Selection" });
    } else {
      res.status(200).send({
        msg: "Seleted Successfully",
        data: data,
      });
    }
  });
});

router.get("/openDefect", (req, res) => {
  var OpenQuery = `SELECT anamoly, COUNT(anamoly) as Total FROM finehhdi_eDefect.defect WHERE defectStatus='Open' GROUP BY anamoly HAVING COUNT(anamoly) > 0`;
  db.query(OpenQuery, (err, result) => {
    if (err) console.log(err);
    else {
      res.send(JSON.stringify(result));
    }
  });
});

//new changes 30-sep
router.get("/createdDefect", (req, res) => {
  // var CreatedQuery = `SELECT createdBy, COUNT(createdBy) AS Total FROM defect GROUP BY createdBy HAVING COUNT(createdBy) > 0`
  var CreatedQuery = `SELECT wfound, COUNT(wfound) AS Total FROM finehhdi_eDefect.defect GROUP BY wfound HAVING COUNT(wfound) > 0`;
  db.query(CreatedQuery, (err, result) => {
    if (err) console.log(err);
    else {
      res.send(JSON.stringify(result));
    }
  });
});
//SELECT anamoly ,TIMEDIFF(CURRENT_TIMESTAMP, createdOn) AS TimeDiff FROM defect where defectStatus='Open'
router.get("/OpenDuration", (req, res) => {
  var OpenDurQuery = `SELECT anamoly ,DATEDIFF(CURRENT_TIMESTAMP, createdOn) AS DAYS FROM finehhdi_eDefect.defect where defectStatus='Open' `;
  db.query(OpenDurQuery, (err, result) => {
    if (err) console.log(err);
    else {
      res.send(JSON.stringify(result));
    }
  });
});

// SELECT defectStatus, COUNT(defectStatus) FROM defect GROUP BY defectStatus HAVING COUNT(defectStatus) > 0
router.get("/CountStatus", (req, res) => {
  var CountStatus = `SELECT defectStatus, COUNT(defectStatus) as Total FROM finehhdi_eDefect.defect GROUP BY defectStatus HAVING COUNT(defectStatus) > 0 `;
  db.query(CountStatus, (err, result) => {
    if (err) console.log(err);
    else {
      res.send(JSON.stringify(result));
    }
  });
});

router.get("/CountAnamoly", (req, res) => {
  var CountAnamoly = `SELECT anamoly, COUNT(anamoly) as Total FROM finehhdi_eDefect.defect GROUP BY anamoly HAVING COUNT(anamoly) > 0 `;
  db.query(CountAnamoly, (err, result) => {
    if (err) console.log(err);
    else {
      res.send(JSON.stringify(result));
    }
  });
});

// SELECT department, COUNT(department) FROM defect GROUP BY department HAVING COUNT(department) > 0
router.get("/CountDepartment", (req, res) => {
  var CountDept = `SELECT department, COUNT(department) as Total FROM finehhdi_eDefect.defect GROUP BY department HAVING COUNT(department) > 0 `;
  db.query(CountDept, (err, result) => {
    if (err) console.log(err);
    else {
      res.send(JSON.stringify(result));
    }
  });
});

// SELECT machine, COUNT(machine) FROM defect GROUP BY machine HAVING COUNT(machine) > 0
router.get("/CountMachine", (req, res) => {
  var CountMac = `SELECT machine, COUNT(machine) as Total FROM finehhdi_eDefect.defect GROUP BY machine HAVING COUNT(machine) > 0 `;
  db.query(CountMac, (err, result) => {
    if (err) console.log(err);
    else {
      res.send(JSON.stringify(result));
    }
  });
});

// for total defect part
router.get("/CountDefectPart", (req, res) => {
  var CountDefectPart = `SELECT defectPart, COUNT(defectPart) as Total FROM finehhdi_eDefect.defect GROUP BY defectPart HAVING COUNT(defectPart) > 0`;
  db.query(CountDefectPart, (err, result) => {
    if (err) console.log(err);
    else {
      res.send(JSON.stringify(result));
    }
  });
});

// for total solved with user
router.get("/CountSolvedDefect", (req, res) => {
  var CountSolvedDefect = `SELECT createdBy AS Person,anamoly, COUNT(anamoly) as Total FROM finehhdi_eDefect.defect WHERE defectStatus='Solved' GROUP BY anamoly HAVING COUNT(anamoly) > 0`;
  db.query(CountSolvedDefect, (err, result) => {
    if (err) console.log(err);
    else {
      res.send(JSON.stringify(result));
    }
  });
});

module.exports = router;
