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

//get area
router.get("/getArea", (req, res) => {
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    let query = `SELECT area FROM finehhdi_CIL.CILConfig`;
    console.log("get area entered query", query);
    connection.query(query, (err, data) => {
      connection.release();
      console.log("error from area name", err);
      console.log("data from area name", data);
      if (err) {
        res
          .status(500)
          .send({ msg: "Error While Selection for Department name" });
      } else {
        res.status(200).send({
          msg: "Seleted for area name Successfully",
          data: data,
        });
      }
    });
  });
});

router.post("/getSubAssembly", (req, res) => {
  const area = req.body.area;
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    let query = `SELECT subassembly FROM finehhdi_CIL.CILConfig where area="${area}"`;
    console.log("get area entered query", query);
    connection.query(query, (err, data) => {
      connection.release();
      console.log("error from area name", err);
      console.log("data from area name", data);
      if (err) {
        res
          .status(500)
          .send({ msg: "Error While Selection for Department name" });
      } else {
        res.status(200).send({
          msg: "Seleted for area name Successfully",
          data: data,
        });
      }
    });
  });
});

router.post("/getSetCILData", (req, res) => {
  const areaName = req.body.areaName;
  const subAssemblyName = req.body.subAssemblyName;
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    let query = `select * from finehhdi_CIL.SetCIL where areaName="${areaName}" and subAssemblyName="${subAssemblyName}" ORDER BY id desc`;
    console.log("Query for getting data on SetCIL", query);

    connection.query(query, (err, data) => {
      connection.release();
      if (err) {
        res.status(500).send("error while getting data");
      } else {
        res.status(200).send({
          message: "selected data successfully",
          data: data,
        });
      }
    });
  });
});

router.post("/getFeedbackCIL", (req, res) => {
  const areaName = req.body.areaName;
  const machineName = req.body.machineName;
  const subAssemblyName = req.body.subAssemblyName;
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    let query = `select * from finehhdi_CIL.FeedbackCIL where areaName="${areaName}" and machineName="${machineName}" and subAssemblyName="${subAssemblyName}" order by id desc`;
    console.log("Query for getting data on SetCIL", query);

    connection.query(query, (err, data) => {
      connection.release();
      if (err) {
        res.status(500).send("error while getting data");
      } else {
        res.status(200).send({
          message: "selected data successfully",
          data: data,
        });
      }
    });
  });
});
router.post("/getFeedbackCILNew", (req, res) => {
  const areaName = req.body.areaName;
  const machineName = req.body.machineName;
  // const subAssemblyName = req.body.subAssemblyName;
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    let query = `select * from finehhdi_CIL.FeedbackCIL1 where areaName="${areaName}" and machineName="${machineName}"  order by id desc`;
    console.log("Query for getting data on SetCIL", query);

    connection.query(query, (err, data) => {
      connection.release();
      if (err) {
        res.status(500).send("error while getting data");
      } else {
        res.status(200).send({
          message: "selected data successfully",
          data: data,
        });
      }
    });
  });
});

router.post("/getSubAssemblyData", (req, res) => {
  const machineType = req.body.machineType;
  console.log("reqdata====>", machineType);
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    var query = `select * from finehhdi_eDefect.DefectParts where machineType= "${machineType}"`;
    console.log("query from getDefectpartsFromMachine ===>", query);
    connection.query(query, (err, result) => {
      connection.release();
      if (err) {
        console.log("error from getDefectPartsFromMachne ===>", err);
        res.status(401).json({
          message: "Some error in type selection",
          results: "Failed",
          defectPart: [],
        });
      } else {
        res.status(201).json({
          message: "type Successfully",
          results: "Success",
          defectPart: result,
        });
      }
    });
  });
});

//get CIL According to user
router.post("/getFeedbackCILForUser", (req, res) => {
  console.log("req.body ===>", req.body);

  const userId = req.body.id;
  // const areaName = req.body.areaName;
  // const machineName = req.body.machineName;
  console.log("id===>", userId);
  console.log("type of userId ", typeof userId);
  // if(userId.length==0){
  if (!userId) {
    console.log("hi no userId");
    return;
  } else {
    db.getConnection((err, connection) => {
      if (err) {
        console.log("database not connected error: >>>", err);
        res.send(500).status({
          msg: "Error from database",
          err: err,
        });
      }
      let query = `select * from finehhdi_CIL.FeedbackCIL1 where userId = ${userId}  order by id desc`;
      console.log(
        "Query for getting data on SetCIL for user====================>",
        query
      );

      connection.query(query, (err, data) => {
        connection.release();
        if (err) {
          res.status(500).send("error while getting data");
        } else {
          res.status(200).send({
            message: "selected data successfully",
            data: data,
          });
        }
      });
    });
  }
});

router.post("/getFilterDataForAdmin", (req, res) => {
  const startDate = req.body.startDate;
  const endDate = req.body.endDate;
  const areaName = req.body.areaName;
  const machineName = req.body.machineName;
  const subAssembly = req.body.subAssembly;
  console.log("start dat and endDate", startDate, endDate);
  console.log("area name ===>", areaName);
  console.log("machine name ===>", machineName);
  console.log("subassembly name ===>", subAssembly);
  // let query = `SELECT * FROM finehhdi_CIL.FeedbackCIL WHERE DATE_FORMAT(STR_TO_DATE(submittedOn,'%d-%m-%Y'), '%Y-%m-%d') >= "${startDate}" AND DATE_FORMAT(STR_TO_DATE(submittedOn,'%d-%m-%Y'), '%Y-%m-%d') <= "${endDate}" and areaName ="${areaName}",machineName = "${machineName}" and subAssemblyName="${subAssembly}"`;
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    let query = `SELECT * FROM finehhdi_CIL.FeedbackCIL  where areaName ="${areaName}"and machineName = "${machineName}" and subAssemblyName="${subAssembly}" and submittedOn between "${startDate}" and "${endDate}"`;

    console.log("query ==>", query);

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

router.post("/getFilterDataForUser", (req, res) => {
  const userId = req.body.id;
  const startDate = req.body.startDate;
  const endDate = req.body.endDate;
  console.log("start dat and endDate", startDate, endDate);
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    connection.query(
      `SELECT * FROM finehhdi_CIL.FeedbackCIL WHERE userId = ${userId} and DATE_FORMAT(STR_TO_DATE(submittedOn,'%d-%m-%Y'), '%Y-%m-%d') >= "${startDate}" AND DATE_FORMAT(STR_TO_DATE(submittedOn,'%d-%m-%Y'), '%Y-%m-%d') <= "${endDate}"`,
      (err, data) => {
        connection.release();
        if (err) {
          res.status(500).send({
            msg: "err getting result",
            data: [],
          });
        } else {
          res.status(200).send({
            msg: "success",
            data: data,
          });
        }
      }
    );
  });
});
router.post("/getFilterDataForUser", (req, res) => {
  const startDate = req.body.startDate;
  const endDate = req.body.endDate;
  console.log("start dat and endDate", startDate, endDate);
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }

    db.query(
      `SELECT * FROM finehhdi_OFS.feedbacks WHERE DATE_FORMAT(STR_TO_DATE(submittedOn,'%Y-%m-%d'), '%Y-%m-%d') >= "${startDate}" AND DATE_FORMAT(STR_TO_DATE(submittedOn,'%Y-%m-%d'), '%Y-%m-%d') <= "${endDate}"`,
      (err, data) => {
        connection.release();
        if (err) {
          res.status(500).send({
            msg: "err getting result",
            filter: [],
          });
        } else {
          res.status(200).send({
            msg: "success",
            filter: data,
          });
        }
      }
    );
  });
});
// router.post("/getMultipleDepartData", async (req, res) => {
//   // SELECT * FROM finehhdi_eDefect.machineTypes where department IN ("B4","B3")
//   const departdata = req.body.department;
//   var DeptData = []
//   console.log('Checking',DeptData)
//   for (let i = 0; i < departdata.length; i++) {
//     let getQuery = `SELECT * FROM finehhdi_eDefect.machineTypes where department = "${departdata[i]}"`
//    let data1 =  db.query (getQuery,  (err, data)  => {
//       if (err) {
//         res.status(500).send({
//           msg: "err getting result",
//           filter: [],
//         })
//       } else {
//         return data
//       }
//       console.log(data1)
//     });
//   }
//     // res.status(200).send({
//     //   msg: "success",
//     //   data: DeptData,
//     // });

// });

// getting machine name for multiselectdropdown

router.post("/getMultipleDepartData", async (req, res) => {
  let depart = req.body.department;
  let dataModification = depart.join('","');
  console.log(dataModification);
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    let getDataQuery = `SELECT machineTypes FROM finehhdi_eDefect.machineTypes where department IN ("${dataModification}")`;
    console.log(getDataQuery);
    connection.query(getDataQuery, (err, data) => {
      connection.release();
      if (err) {
        console.log(err);
      } else {
        console.log("success");
        res.status(200).send({
          data: data,
        });
      }
    });
  });
});

// getting subAsemlyName
router.post("/getSubAssembliesBySelectingMultipleDepart", async (req, res) => {
  let machine = req.body.machine;
  let dataModification = machine.join('","');
  console.log(dataModification);
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    let getDataQuery = `SELECT defectPart FROM finehhdi_eDefect.DefectParts where machineType IN ("${dataModification}")`;
    console.log(getDataQuery);
    connection.query(getDataQuery, (err, data) => {
      connection.release();
      if (err) {
        console.log(err);
      } else {
        console.log("success");
        res.status(200).send({
          data: data,
        });
      }
    });
  });
});
//getting operation describtion for multiple subassemblies machine and area

router.post("/getSettedCIL", (req, res) => {
  let areaName = req.body.AreaNames;
  console.log("area Name===>", areaName);
  let machineName = req.body.machineNames;
  console.log("machine Name===>", machineName);

  let subAssemblyName = req.body.subAssemblyNames;
  console.log("subassembly Name===>", subAssemblyName);

  let areaNameModificatiom = areaName.join('","');
  let machineNameModificatiom = machineName.join('","');
  let subAssemblyModificatiom = subAssemblyName.join('","');
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    let query = `select * from finehhdi_CIL.FeedbackCIL where areaName IN ("${areaNameModificatiom}") and machineName IN ("${machineNameModificatiom}") and subAssemblyName IN ("${subAssemblyModificatiom}") order by id desc`;
    console.log("Query for getting data on SetCIL", query);

    connection.query(query, (err, data) => {
      connection.release();
      if (err) {
        res.status(500).send("error while getting data");
      } else {
        res.status(200).send({
          message: "selected data successfully",
          data: data,
        });
      }
    });
  });
});

//this api is only for testing
router.post("/getFeedbackCILTest", (req, res) => {
  const areaName = req.body.areaName;
  const machineName = req.body.machineName;
  const subAssemblyName = req.body.subAssemblyName;
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    let query = `select * from finehhdi_CIL.FeedbackCIL where areaName="${areaName}" and machineName="${machineName}" and subAssemblyName="${subAssemblyName}" order by id desc`;
    console.log("Query for getting data on SetCIL", query);

    connection.query(query, (err, data) => {
      connection.release();
      if (err) {
        console.log("err==>", err);
        res.status(500).send("error while getting data");
      } else {
        res.status(200).send({
          message: "selected data successfully",
          data: data,
        });
      }
    });
  });
});

//new work for view ecil
router.get("/getCILData", (req, res) => {
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    let getQuery = "Select * from finehhdi_CIL.FeedbackCIL1 ORDER BY id DESC";
    connection.query(getQuery, (err, data) => {
      connection.release();
      if (err) {
        console.log("err==>", err);
        res.status(500).send("error while getting data");
      } else {
        res.status(200).send({
          message: "selected data successfully",
          data: data,
        });
      }
    });
  });
});

// new work for cil select by date
router.post("/getCILDataByDate", (req, res) => {
  const date = req.body.date;
  console.log("date ===>", date);
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    let dateQuery = `SELECT * FROM finehhdi_CIL.FeedbackCIL1 WHERE SubmittedOn = '${date}'`;
    console.log("dataQuery ====>", dateQuery);
    connection.query(dateQuery, (err, data) => {
      connection.release();

      if (err) {
        console.log("err==>", err);
        res.status(500).send("error while getting data");
      } else {
        res.status(200).send({
          message: "selected data successfully",
          data: data,
        });
      }
    });
  });
});

//new work for filter data w.r.t area and machine
// SELECT * FROM `FeedbackCIL1` WHERE areaName = 'D5' AND machineName = 'SAP MODULE'

router.post("/getCILDataByAandM", (req, res) => {
  const area = req.body.area;
  const machine = req.body.machine;
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    let dateQuery = `SELECT * FROM finehhdi_CIL.FeedbackCIL1 WHERE areaName = '${area}' AND machineName = '${machine}'`;
    console.log(dateQuery);
    connection.query(dateQuery, (err, data) => {
      connection.release();
      if (err) {
        console.log("err==>", err);
        res.status(500).send("error while getting data");
      } else {
        res.status(200).send({
          message: "selected data successfully",
          data: data,
        });
      }
    });
  });
});

// latest work samad
router.post("/specificUserData", (req, res) => {
  const data = req.body;
  const areaName = data.areaName;
  const machineName = data.machineName;
  const userId = data.userId;
  const submittedOn = data.submittedOn;

  console.log("req.body done", data);
  db.getConnection((err, connection) => {
    if (err) {
      console.log("database not connected error: >>>", err);
      res.send(500).status({
        msg: "Error from database",
        err: err,
      });
    }
    const selectFeedback = `SELECT * FROM finehhdi_CIL.FeedbackCIL1 where areaName="${areaName}" AND machineName="${machineName}" AND userId=${userId} AND SubmittedOn="${submittedOn}" And OperDesVal="${""}"`;
    connection.query(selectFeedback, (error, selectResult) => {
      connection.release();
      console.log("selectFeedback", selectFeedback);
      if (error) {
        console.log("error==>", error);
        res.status(500).send("error while getting data");
      } else {
        res.status(200).send({
          message: "selected data successfully",
          data: selectResult,
        });
      }
    });
  });
});

module.exports = router;
