const { Op } = require("sequelize");
const dbSAPPS = require("../../helpers/db");
require("dotenv").config();
const moment = require("moment");
const redisClient = require("../../../../redisClient");

module.exports = {
  getAll,
  getByApprover,
  filter,
  option,
  count,
  create,
  update,
  submitToWorkflow,
  actionToWorkflow,
  delete: _delete,
};

async function invalidateCallPlanCaches() {
  try {
    await redisClient.delByPrefix("cp:list:v2:");
  } catch (e) {
    // do not block main flow on cache errors
  }
}



async function getAll() {
  try {
    const workflows = await dbSAPPS.Workflow.findAll({
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.DocType,
          as: "doctype",
          attributes: ["id", "name", "display_name"],
        },
      ],
    });

    return {
      workflows,
      success: true,
      message: "workflow loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load workflow!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function getByApprover(params) {
  try {
    const filterConditionsApprover = {};
    const filterConditionsApproverOr = [];

    if (params.designation_id) {
      filterConditionsApproverOr.push({
        designation_id: {
          [Op.eq]: parseInt(params.designation_id),
        },
      });
    }

    if (params.territory_id) {
      filterConditionsApproverOr.push({
        territory_id: {
          [Op.eq]: parseInt(params.territory_id),
        },
      });
    }

    if (filterConditionsApproverOr.length > 0) {
      filterConditionsApprover[Op.or] = filterConditionsApproverOr;
    }

    const workflowApprovers = await dbSAPPS.WorkflowApprover.findAll({
      // where: filterConditionsApprover,
      // where: { ...filterConditionsApprover, status: params.status }, // agar sesuai hirarki proses nya
      where: { ...filterConditionsApprover },
      attributes: ["territory_id", "designation_id", "status", "workflow_id"],
    });

    const workflowIds = workflowApprovers.map((wa) => wa.workflow_id);

    const filterConditions = {};

    if (params.status === "Need Approval") {
      filterConditions.status = {
        [Op.eq]: params.status,
      };
    }

    if (params.status === "Approved") {
      filterConditions.status = {
        [Op.and]: {
          [Op.in]: ["Approved", "Saved"],
        },
      };
    }

    if (
      params.access_read_all_data === true ||
      params.access_read_all_data === "true" ||
      params.administrator_of_hr === true ||
      params.administrator_of_hr === "true"
    ) {
      filterConditions.id = {
        [Op.ne]: null,
      };
    } else {
      if (workflowIds.length > 0) {
        filterConditions.id = {
          [Op.in]: workflowIds,
        };
      } else if (workflowIds.length === 0 || workflowIds === "" || workflowIds === null) {
        filterConditions.id = {
          [Op.eq]: null,
        };
      }
    }

    if (params.doctype_id) {
      filterConditions.doctype_id = {
        [Op.like]: `%${params.doctype_id}%`,
      };
    }

    const workflows = await dbSAPPS.Workflow.findAll({
      order: [["modified", "DESC"]],
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.DocType,
          as: "doctype",
          attributes: ["id", "name", "display_name"],
        },
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: ["id", "workflow_id", "designation_id", "territory_id", "status"],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
            },
          ],
        },
      ],
    });

    const workflowsWithDoctype = await getWorkflowsWithDoctype(workflows);

    return {
      workflows: workflowsWithDoctype,
      success: true,
      message: "Workflow loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load workflow!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function filter(params) {
  const filterConditions = {};

  if (params.doctype_id) {
    filterConditions.doctype_id = {
      [Op.like]: `%${params.doctype_id}%`,
    };
  }

  try {
    const workflow = await dbSAPPS.Workflow.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.DocType,
          as: "doctype",
          attributes: ["id", "name", "display_name"],
        },
      ],
    });

    return {
      workflow,
      success: true,
      message: "workflow loaded successfully!",
    };
  } catch (error) {
    console.error("Error retrieving workflow: ", error);
    const errorMessage = error.message || "Failed to retrieve workflow. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function option() {
  try {
    const workflow = await dbSAPPS.Workflow.findAll({
      attributes: ["id", "doctype_id", "status", "doc_id"],
      order: [["modified", "ASC"]],
      include: [
        {
          model: dbSAPPS.DocType,
          as: "doctype",
          attributes: ["id", "name", "display_name"],
        },
      ],
    });

    return {
      workflow,
      success: true,
      message: "workflow loaded successfully!",
    };
  } catch (error) {
    console.error("Error retrieving workflow: ", error);
    const errorMessage = error.message || "Failed to retrieve workflow. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function count() {
  try {
    const count = await dbSAPPS.Workflow.count();

    return {
      count,
      success: true,
      message: "workflow loaded successfully!",
    };
  } catch (error) {
    console.error("Error counting workflow: ", error);
    return {
      success: false,
      message: "Failed to retrieve workflow count. Please try again later.",
    };
  }
}

async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    if (!params.doctype_id || !params.doc_id)
      return {
        success: false,
        message: "doctype_id and doc_id are required!",
      };

    const existing = await dbSAPPS.Workflow.findOne({
      where: {
        doctype_id: params.doctype_id,
        doc_id: params.doc_id,
      },
    });

    if (existing)
      return {
        success: false,
        message: `workflow doctype_id "${params.doctype_id}" and doc_id "${params.doc_id}" is already taken.`,
      };

    const workflow = await dbSAPPS.Workflow.create(params, { transaction });

    if (params.workflow_approver) {
      params.workflow_approver = JSON.parse(params.workflow_approver);
    }

    if (Array.isArray(params.workflow_approver) && params.workflow_approver.length > 0) {
      const workflowApproverData = params.workflow_approver.map((workflowApprover) => ({
        ...workflowApprover,
        workflow_id: workflow.id,
      }));

      await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproverData, {
        transaction,
      });
    }

    await transaction.commit();
    return {
      success: true,
      message: "workflow data has been successfully added!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error.message || "An error occurred during create workflow processing.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function update(id, params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const workflow = await dbSAPPS.Workflow.findByPk(id);
    if (!workflow)
      return {
        success: false,
        message: "workflow not found!",
      };

    if (
      params.doctype_id &&
      params.doctype_id !== workflow.doctype_id &&
      params.doc_id &&
      params.doc_id !== workflow.doc_id
    ) {
      const existing = await dbSAPPS.Workflow.findOne({
        where: {
          doctype_id: params.doctype_id,
          doc_id: params.doc_id,
        },
      });

      if (existing) {
        return {
          success: false,
          message: `workfolw doctype_id "${params.doctype_id}" and doc_id "${params.doc_id}" is already taken. Try again!`,
        };
      }
    }

    await workflow.update(params, { transaction });

    if (params.workflow_approver) {
      params.workflow_approver = JSON.parse(params.workflow_approver);
    }

    if (Array.isArray(params.workflow_approver) && params.workflow_approver.length > 0) {
      await dbSAPPS.WorkflowApprover.destroy({
        where: { workflow_id: id },
        transaction,
      });

      const workflowApproverData = params.workflow_approver.map((workflowApprpver) => ({
        ...workflowApprpver,
        workflow_id: id,
      }));

      await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproverData, {
        transaction,
      });
    } else {
      await dbSAPPS.WorkflowApprover.destroy({
        where: { workflow_id: id },
        transaction,
      });
    }

    await transaction.commit();
    await invalidateCallPlanCaches();
    return {
      success: true,
      message: "Data workflow has been saved!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error.message || "Failed to update workflow data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function submitToWorkflow(id, params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const workflow = await dbSAPPS.Workflow.findByPk(id);
    if (!workflow)
      return {
        success: false,
        message: "workflow not found!",
      };

    if (
      params.doctype_id &&
      params.doctype_id !== workflow.doctype_id &&
      params.doc_id &&
      params.doc_id !== workflow.doc_id
    ) {
      const existing = await dbSAPPS.Workflow.findOne({
        where: {
          doctype_id: params.doctype_id,
          doc_id: params.doc_id,
        },
      });

      if (existing) {
        return {
          success: false,
          message: `workfolw doctype_id "${params.doctype_id}" and doc_id "${params.doc_id}" is already taken. Try again!`,
        };
      }
    }

    const workflowData = {
      id: parseInt(id),
      doc_id: params.doc_id,
      doctype_id: params.doctype_id,
      modified: params.modified,
      modified_by: params.modified_by,
      status: params.status,
      workflow_id: params.workflow_id,
    };

    await workflow.update(workflowData, { transaction, individualHooks: true });

    if (Array.isArray(params.workflowapprovers) && params.workflowapprovers.length > 0) {
      await dbSAPPS.WorkflowApprover.destroy({
        where: { workflow_id: id },
        transaction,
      });
      const workflowApproverData = params.workflowapprovers.map((workflowApprover) => ({
        ...workflowApprover,
        workflow_id: id,
      }));
      await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproverData, {
        transaction,
      });
    } else {
      await dbSAPPS.WorkflowApprover.destroy({
        where: { workflow_id: id },
        transaction,
      });
    }

    // Attendance from Absence Clarification adn Permit Ease
    let createdAttendanceIds = [];
    let updatedAttendanceIds = [];

    // ABSENCE CLARIFICATION
    if (params.status === "Approved" && params.doctype_id === 104) {
      if (Array.isArray(params.attendances) && params.attendances.length > 0) {
        // revisi tambahkan filter kalau sabtu dan minggu tidak termasuk filter ini
        const validationAttendance = params.attendances.filter(
          (attendance) =>
            (attendance.status_absenceclarification === "true" || attendance.status_absenceclarification === true) &&
            (!attendance.in_time || !attendance.out_time || !attendance.reason)
        );

        if (validationAttendance.length > 0) {
          return {
            success: false,
            message: `in_time, out_time and reason are required for attendance ${validationAttendance
              .map((attendance) => attendance.id)
              .join(", ")}`,
          };
        }

        const attendances = params.attendances.map((attendance) => ({
          ...attendance,
          owner:
            attendance.owner === 0 || attendance.owner === "0" || !attendance.owner
              ? parseInt(params.modified_by)
              : attendance.owner || 1,
          creation: attendance.creation || Date.now(),
          modified: attendance.modified || Date.now(),
          id: attendance.id ? parseInt(attendance.id) : null,
          modified_by: params.modified_by ? parseInt(params.modified_by) : null,
          absenceclarification_id: attendance.absenceclarification_id
            ? parseInt(attendance.absenceclarification_id)
            : null,
          attendance_id: attendance.attendance_id ? parseInt(attendance.attendance_id) : null,
          company_id: attendance.company_id ? parseInt(attendance.company_id) : null,
          employee_id: attendance.employee_id ? parseInt(attendance.employee_id) : null,
          shifttype_id: attendance.shifttype_id ? parseInt(attendance.shifttype_id) : null,
          total_hour: attendance.total_hour ? parseInt(attendance.total_hour) : null,
        }));

        const foundAttendanceId = attendances.filter(
          (attendance) => attendance.attendance_id && attendance.attendance_id !== 0
        );

        const notFoundAttendanceId = attendances.filter((attendance) => !attendance.attendance_id);

        const foundStatusAbsenceClarification = notFoundAttendanceId.filter(
          (attendance) => attendance.status_absenceclarification === true
        );

        try {
          if (foundStatusAbsenceClarification.length > 0) {
            const attendanceData = foundStatusAbsenceClarification.map((attendance) => {
              const { id, ...attendanceWithoutId } = attendance;
              return {
                creation: Date.now(),
                modified: Date.now(),
                absenceclariciation_id: params?.doc_id,
                absenceclarificationchild_id: attendance?.id,
                ...attendanceWithoutId,
              };
            });

            const createdAttendances = await dbSAPPS.Attendance.bulkCreate(attendanceData, { transaction });

            const createdAttendanceIds = createdAttendances.map((attendance) => attendance.id);

            const absenceclarificationchildIds = foundStatusAbsenceClarification.map((attendance) => attendance.id);

            for (let i = 0; i < absenceclarificationchildIds.length; i++) {
              await dbSAPPS.AbsenceClarificationChild.update(
                { attendance_id: createdAttendanceIds[i] },
                { where: { id: absenceclarificationchildIds[i] }, transaction }
              );
            }
          }

          const updatedAttendance = foundAttendanceId.map((attendance) => ({
            modified: Date.now(),
            ...attendance,
            absenceclariciation_id: parseInt(attendance.absenceclarification_id),
            absenceclarificationchild_id: parseInt(attendance.id),
            modified_by: parseInt(params.modified_by),
            id: parseInt(attendance.attendance_id),
          }));

          if (updatedAttendance.length > 0) {
            const updatedAttendances = await dbSAPPS.Attendance.bulkCreate(updatedAttendance, {
              updateOnDuplicate: [
                "modified",
                "modified_by",
                "owner",
                "company_id",
                "employee_id",
                "shifttype_id",
                "total_hour",
                "in_time",
                "out_time",
                "notes",
                "reason",
                "status_absenceclarification",
                "absenceclarification_id",
                "absenceclarificationchild_id",
              ],
              individualHooks: true,
              returning: true,
              transaction,
              ignoreDuplicates: true,
            });

            updatedAttendanceIds = updatedAttendances.map((attendance) => attendance.id);
          } else {
            console.error("No updated attendance records to process.");
          }
        } catch (error) {
          return {
            success: false,
            message: error.message || "Failed to create or update attendance.",
          };
        }
      }
    }

    // PERMIT EASE
    if (params.status === "Approved" && params.doctype_id === 70) {
      if (params.permiteases) {
        if (params.permiteases.from_date && params.permiteases.to_date) {
          if (params.permiteases.to_date < params.permiteases.from_date) {
            return {
              success: false,
              message: "to_date cannot be less than from_date. please try again.",
            };
          } else {
            const startDate = moment(params && params.permiteases && params.permiteases.from_date);
            const endDate = moment(params && params.permiteases && params.permiteases.to_date);
            const dates = [];

            let totalHour = 0;
            const inTimeHour = 9;
            const inTimeMinute = 0;
            const outTimeHour = 18;
            const outTimeMinute = 0;

            const inTimeTotalMinute = inTimeHour * 60 + inTimeMinute;
            const outTimeTotalMinute = outTimeHour * 60 + outTimeMinute;

            totalHour = (outTimeTotalMinute - inTimeTotalMinute - 60) / 60;
            totalHour = Math.round(totalHour);

            while (startDate <= endDate) {
              dates.push(startDate.format("YYYY-MM-DD"));
              startDate.add(1, "day");
            }

            const foundAttendances = await dbSAPPS.Attendance.findAll({
              where: {
                employee_id: params.permiteases.employee_id,
                date: {
                  [Op.in]: dates,
                },
              },
              transaction,
            });

            const foundDate = foundAttendances.find((attendance) => dates.includes(attendance.date));

            if (foundDate) {
              return {
                success: false,
                message: `Attendance on date ${foundDate.date} already exist.`,
              };
            }

            const employee = await dbSAPPS.Employee.findByPk(params.permiteases.employee_id, {
              attributes: ["shifttype_id"],
              transaction,
            });

            if (!employee) {
              return {
                success: false,
                message: "Employee not found!",
              };
            }

            const shifttype_id = employee.shifttype_id;

            if (foundAttendances && foundAttendances.length) {
              const updatedAttendance = foundAttendances.map((attendance) => ({
                ...attendance,
                creation: attendance.creation || Date.now(),
                modified: Date.now(),
                modified_by: params.permiteases.modified_by,
                permitease_id: params.permiteases.id ? params.permiteases.id : attendance.permitease_id,
                status_permitease: true,
                in_time: "09:00:00",
                out_time: "18:00:00",
                in_status: "On Time",
                out_status: "On Time",
                reason: params.permiteases.reason,
                notes: params.permiteases.notes,
                filename: params.permiteases.filename,
                id: attendance.id,
                total_hour: totalHour,
              }));

              const filteredUpdatedAttendance = updatedAttendance.filter((updatedAttendanceItem) => {
                const foundAbsenceClarification = foundAttendances.find(
                  (attendance) =>
                    attendance.id === updatedAttendanceItem.id &&
                    attendance.absenceclarification_id &&
                    attendance.status_absenceclarification
                );
                if (foundAbsenceClarification) return false;
                return true;
              });

              if (filteredUpdatedAttendance && filteredUpdatedAttendance.length === 0) {
                return {
                  success: false,
                  message: "All attendance dates have been given absence clarification, please check again!",
                };
              }

              await dbSAPPS.Attendance.bulkCreate(updatedAttendance, {
                updateOnDuplicate: true,
                individualHooks: true,
                returning: true,
                lock: transaction.LOCK.UPDATE,
                transaction,
              });

              for (let i = 0; i < updatedAttendance.length; i++) {
                await dbSAPPS.PermitEase.update(
                  { attendance_id: updatedAttendance[i].id },
                  { where: { id: params.permiteases.id }, transaction }
                );
              }
            } else {
              const createAttendances = dates.map((date) => ({
                date: date,
                status: "Permit Ease",
                employee_id: params.permiteases.employee_id,
                permitease_id: params.permiteases.id,
                status_permitease: true,
                in_time: "09:00:00",
                out_time: "18:00:00",
                in_status: "On Time",
                out_status: "On Time",
                company_id: params.permiteases.company_id,
                shifttype_id: shifttype_id,
                reason: params.permiteases.reason,
                notes: params.permiteases.notes,
                filename: params.permiteases.filename,
                creation: Date.now(),
                modified: Date.now(),
                modified_by: params.permiteases.modified_by,
                owner: params.permiteases.owner,
                total_hour: totalHour,
              }));

              const createdAttendances = await dbSAPPS.Attendance.bulkCreate(createAttendances, {
                lock: transaction.LOCK.UPDATE,
                transaction,
              });

              createdAttendanceIds = createdAttendances.map((attendance) => attendance.id);

              for (let i = 0; i < createdAttendanceIds.length; i++) {
                await dbSAPPS.PermitEase.update(
                  { attendance_id: createdAttendanceIds[i] },
                  { where: { id: params.permiteases.id }, transaction }
                );
              }
            }
          }
        } else if (
          params.permiteases.half_day_date &&
          (params.permiteases.half_day === true || params.permiteases.half_day === "true")
        ) {
          const foundAttendance = await dbSAPPS.Attendance.findOne({
            where: {
              date: params.permiteases.half_day_date,
              employee_id: params.permiteases.employee_id,
            },
            transaction,
          });

          if (foundAttendance) {
            const updatedAttendance = {
              ...foundAttendance.dataValues,
              modified: Date.now(),
              modified_by: params.permiteases.modified_by,
              permitease_id: params.permiteases.id ? params.permiteases.id : foundAttendance.dataValues.permitease_id,
              status_permitease: true,
              in_time: foundAttendance.dataValues.in_time ? foundAttendance.dataValues.in_time : "09:00:00",
              out_time: foundAttendance.dataValues.out_time ? foundAttendance.dataValues.out_time : "18:00:00",
              in_status: "On Time",
              out_status: "On Time",
              reason: params.permiteases.reason,
              notes: params.permiteases.notes,
              filename: params.permiteases.filename,
              id: foundAttendance.dataValues.id,
              total_hour: 8,
            };

            const foundAbsenceClarification = foundAttendance.dataValues
              ? foundAttendance.dataValues.absenceclarification_id &&
                foundAttendance.dataValues.status_absenceclarification
              : false;
            const filteredUpdatedAttendance = !foundAbsenceClarification ? updatedAttendance : null;

            if (!filteredUpdatedAttendance) {
              return {
                success: false,
                message: "The attendance date has been given absence clarification, please check again!",
              };
            }

            if (updatedAttendance) {
              const updatedAttendances = await dbSAPPS.Attendance.upsert(updatedAttendance, {
                updateOnDuplicate: [
                  "modified_by",
                  "owner",
                  "company_id",
                  "employee_id",
                  "shifttype_id",
                  "total_hour",
                  "in_time",
                  "out_time",
                  "notes",
                  "reason",
                  "filename",
                  "notes",
                  "status_permitease",
                  "permitease_id",
                ],
                individualHooks: true,
                returning: true,
                transaction,
              });

              updatedAttendanceIds = [updatedAttendances.id];
            } else {
              console.error("No updated attendance records to process.");
            }

            await dbSAPPS.PermitEase.update(
              { attendance_id: updatedAttendance.id },
              { where: { id: params.permiteases.id }, transaction }
            );
          } else {
            const createAttendances = {
              date: params.permiteases.half_day_date,
              status: "Permit Ease",
              employee_id: params.permiteases.employee_id,
              permitease_id: params.permiteases.id,
              status_permitease: true,
              in_time: attendance.in_time ? attendance.in_time : "09:00:00",
              out_time: attendance.out_time ? attendance.out_time : "18:00:00",
              in_status: "On Time",
              out_status: "On Time",
              company_id: params.permiteases.company_id,
              shifttype_id: shifttype_id,
              reason: params.permiteases.reason,
              notes: params.permiteases.notes,
              filename: params.permiteases.filename,
              creation: Date.now(),
              modified: Date.now(),
              modified_by: params.permiteases.modified_by,
              owner: params.permiteases.owner,
              total_hour: 8,
            };

            const createdAttendances = await dbSAPPS.Attendance.bulkCreate(createAttendances, {
              lock: transaction.LOCK.UPDATE,
              transaction,
            });

            createdAttendanceIds = createdAttendances.map((attendance) => attendance.id);

            for (let i = 0; i < createdAttendanceIds.length; i++) {
              await dbSAPPS.PermitEase.update(
                { attendance_id: createdAttendanceIds[i] },
                { where: { id: params.permiteases.id }, transaction }
              );
            }
          }
        }
      }
    }

    await transaction.commit();
    await invalidateCallPlanCaches();
    return {
      createdAttendanceIds,
      updatedAttendanceIds,
      success: true,
      message: "workflow has been saved!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error.message || "Failed to update workflow data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function actionToWorkflow(id, params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const workflow = await dbSAPPS.Workflow.findByPk(id);
    if (!workflow)
      return {
        success: false,
        message: "workflow not found!",
      };

    if (
      params.doctype_id &&
      params.doctype_id !== workflow.doctype_id &&
      params.doc_id &&
      params.doc_id !== workflow.doc_id
    ) {
      const existing = await dbSAPPS.Workflow.findOne({
        where: {
          doctype_id: params.doctype_id,
          doc_id: params.doc_id,
        },
      });

      if (existing) {
        return {
          success: false,
          message: `workfolw doctype_id "${params.doctype_id}" and doc_id "${params.doc_id}" is already taken. Try again!`,
        };
      }
    }

    await workflow.update(params, { transaction });
    if (Array.isArray(params.workflowapprovers) && params.workflowapprovers.length > 0) {
      await dbSAPPS.WorkflowApprover.destroy({
        where: { workflow_id: id },
        transaction,
      });

      const workflowApproverData = params.workflowapprovers.map((workflowApprover) => ({
        ...workflowApprover,
        workflow_id: id,
      }));

      await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproverData, {
        transaction,
      });
    } else {
      await dbSAPPS.WorkflowApprover.destroy({
        where: { workflow_id: id },
        transaction,
      });
    }

    // attendance from Absence Clarification
    let createdAttendanceIds = [];
    let updatedAttendanceIds = [];

    if (params.status === "Approved" && params.doctype_id === 104) {
      if (Array.isArray(params.attendances) && params.attendances.length > 0) {
        const attendances = params.attendances.map((attendance) => ({
          ...attendance,
          id: parseInt(attendance.id),
          modified_by: parseInt(params.modified_by),
          owner: parseInt(attendance.owner),
          absenceclarification_id: parseInt(attendance.absenceclarification_id),
          attendance_id: parseInt(attendance.attendance_id),
          company_id: parseInt(attendance.company_id),
          employee_id: parseInt(attendance.employee_id),
          shifttype_id: parseInt(attendance.shifttype_id),
          total_hour: parseInt(attendance.total_hour),
        }));

        const foundAttendanceId = attendances.filter(
          (attendance) => attendance.attendance_id && attendance.attendance_id !== 0
        );

        const notFoundAttendanceId = attendances.filter((attendance) => !attendance.attendance_id);

        const foundStatusAbsenceClarification = notFoundAttendanceId.filter(
          (attendance) => attendance.status_absenceclarification === true
        );

        try {
          if (foundStatusAbsenceClarification.length > 0) {
            const attendanceData = foundStatusAbsenceClarification.map((attendance) => {
              const { id, ...attendanceWithoutId } = attendance;
              return {
                creation: Date.now(),
                modified: Date.now(),
                absenceclariciation_id: params?.doc_id,
                absenceclarificationchild_id: attendance?.id,
                ...attendanceWithoutId,
              };
            });

            const createdAttendances = await dbSAPPS.Attendance.bulkCreate(attendanceData, { transaction });

            const createdAttendanceIds = createdAttendances.map((attendance) => attendance.id);

            const absenceclarificationchildIds = foundStatusAbsenceClarification.map((attendance) => attendance.id);

            for (let i = 0; i < absenceclarificationchildIds.length; i++) {
              await dbSAPPS.AbsenceClarificationChild.update(
                { attendance_id: createdAttendanceIds[i] },
                { where: { id: absenceclarificationchildIds[i] }, transaction }
              );
            }
          }

          const updatedAttendance = foundAttendanceId.map((attendance) => ({
            modified: Date.now(),
            ...attendance,
            absenceclariciation_id: parseInt(attendance.absenceclarification_id),
            absenceclarificationchild_id: parseInt(attendance.id),
            modified_by: parseInt(params.modified_by),
            id: parseInt(attendance.attendance_id),
          }));

          if (updatedAttendance.length > 0) {
            const updatedAttendances = await dbSAPPS.Attendance.bulkCreate(updatedAttendance, {
              updateOnDuplicate: [
                "modified_by",
                "owner",
                "absenceclarification_id",
                "absenceclarificationchild_id",
                "company_id",
                "employee_id",
                "shifttype_id",
                "total_hour",
                "in_time",
                "out_time",
                "notes",
                "reason",
                "status_absenceclarification",
                "absenceclarificationchild_id",
              ],
              individualHooks: true,
              returning: true,
              transaction,
            });

            updatedAttendanceIds = updatedAttendances.map((attendance) => attendance.id);
          } else {
            console.error("No updated attendance records to process.");
          }
        } catch (error) {
          return {
            success: false,
            message: error.message || "Failed to create or update attendance.",
          };
        }
      }
    }

    await transaction.commit();
    await invalidateCallPlanCaches();
    return {
      success: true,
      message: "workflow has been saved!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error.message || "Failed to update workflow data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function _delete(id) {
  try {
    const workflow = await dbSAPPS.Workflow.findByPk(id);
    if (!workflow) return { success: false, message: "workflow not found!" };

    await workflow.destroy();
    return {
      success: true,
      message: "workflow successfully deleted!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to delete workflow.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

const getWorkflowsWithDoctype = async (workflows) => {
  const workflowsWithDoctype = await Promise.all(
    workflows.map(async (workflow) => {
      const { doctype_id, doc_id } = workflow;

      // CALL PLAN
      if (doctype_id === 22) {
        const callPlans = await dbSAPPS.CallPlan.findOne({
          where: { id: doc_id },
          attributes: ["id", "doc_no", "territory_id", "year_id", "period_id"],
        });

        const data = await dbSAPPS.EmployeeTerritory.findOne({
          where: {
            territory_id: callPlans?.territory_id ? callPlans?.territory_id : null,
          },
          attributes: ["employee_id", "territory_id"],
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["id", "employee_name"],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          callPlans,
          doc_no: callPlans ? callPlans.doc_no : null,
          employee_name: data?.employee?.employee_name || null,
        };
      }

      // REALIZATION PERMISSION
      if (doctype_id === 117) {
        const realizationPermission = await dbSAPPS.CallPlanRealization.findOne({
          where: { id: doc_id },
          attributes: ["id", "start_date", "note", "callplan_id"],
          include: [
            {
              model: dbSAPPS.CallPlan,
              as: "callplan",
              attributes: ["id", "doc_no", "territory_id", "year_id", "period_id"],
            },
          ],
        });

        const data = await dbSAPPS.EmployeeTerritory.findOne({
          where: {
            territory_id: realizationPermission?.callplan ? realizationPermission?.callplan?.territory_id : null,
          },
          attributes: ["employee_id", "territory_id"],
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["id", "employee_name"],
            },
          ],
        });

        const details = {
          doc_no: realizationPermission?.callplan ? realizationPermission?.callplan?.doc_no : null,
          note: realizationPermission?.note,
          date: realizationPermission?.start_date,
        };
        return {
          ...workflow.toJSON(),
          realizationPermission,
          doc_no: realizationPermission?.callplan ? realizationPermission?.callplan?.doc_no : null,
          employee_name: data?.employee?.employee_name || null,
          details,
        };
      }

      // CALL REALIZATION DAILY
      if (doctype_id === 93) {
        const callPlan = await dbSAPPS.CallPlan.findOne({
          where: { id: doc_id },
          attributes: ["id", "doc_no", "territory_id", "year_id", "period_id"],
        });

        const data = await dbSAPPS.EmployeeTerritory.findOne({
          where: {
            territory_id: callPlan?.territory_id ? callPlan?.territory_id : null,
          },
          attributes: ["employee_id", "territory_id"],
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["id", "employee_name"],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: callPlan ? callPlan?.doc_no : null,
          employee_name: data?.employee?.employee_name || null,
        };
      }

      // EMPLOYEE REQUEST
      if (doctype_id === 109) {
        const employeeRequest = await dbSAPPS.EmployeeRequest.findOne({
          where: { id: doc_id },
          attributes: ["id", "no_fpk"],
          include: [
            {
              model: dbSAPPS.User,
              as: "user",
              attributes: ["id", "email", "full_name", "phone_number"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["employee_name"],
                },
              ],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: employeeRequest ? employeeRequest?.no_fpk.replace(/\//g, "-") : null,
          employee_name: employeeRequest?.user?.employee?.employee_name,
        };
      }

      // ABSENCE CLARIFICATION
      if (doctype_id === 104) {
        const absenceClarification = await dbSAPPS.AbsenceClarification.findOne({
          where: { id: doc_id },
          attributes: ["id", "periode_month", "periode_year", "employee_id"],
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["id", "employee_name"],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: absenceClarification
            ? "AC-" +
              absenceClarification?.id +
              "-" +
              absenceClarification?.periode_month +
              "-" +
              absenceClarification?.periode_year +
              "-" +
              absenceClarification?.employee_id
            : null,
          employee_name: absenceClarification?.employee?.employee_name,
        };
      }

      // PERMIT EASE
      if (doctype_id === 70) {
        const permitEase = await dbSAPPS.PermitEase.findOne({
          where: { id: doc_id },
          attributes: ["id", "from_date", "to_date", "half_day_date", "employee_id", "half_day"],
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["id", "employee_name"],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: permitEase ? "PE-" + permitEase?.id + "-" + permitEase?.employee_id : null,
          employee_name: permitEase?.employee?.employee_name,
        };
      }

      // UDK
      if (doctype_id === 107) {
        const udk = await dbSAPPS.Udk.findOne({
          where: { id: doc_id },
          attributes: ["id", "name"],
          include: [
            {
              model: dbSAPPS.User,
              as: "user",
              attributes: ["id", "email", "full_name", "phone_number"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["employee_name"],
                  include: [
                    {
                      model: dbSAPPS.Designation,
                      as: "designation",
                      attributes: ["display_name"],
                    },
                  ],
                },
              ],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: udk ? udk?.name.replace(/\//g, "-") : null,
          employee_name: udk?.user?.employee?.employee_name,
        };
      }

      // CLIENT KS
      if (doctype_id === 25) {
        const clientKS = await dbSAPPS.ClientCollab.findOne({
          where: { id: doc_id },
          attributes: ["id", "doc_no", "territory_id"],
        });

        const data = await dbSAPPS.EmployeeTerritory.findOne({
          where: {
            territory_id: clientKS?.territory_id ? clientKS?.territory_id : null,
          },
          attributes: ["employee_id", "territory_id"],
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["id", "employee_name"],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: clientKS ? clientKS?.doc_no : null,
          employee_name: data?.employee?.employee_name,
        };
      }

      // SURVEY
      if (doctype_id === 23) {
        const survey = await dbSAPPS.Survey.findOne({
          where: { id: doc_id },
          attributes: ["id", "doc_no", "territory_id"],
        });

        const data = await dbSAPPS.EmployeeTerritory.findOne({
          where: {
            territory_id: survey?.territory_id ? survey?.territory_id : null,
          },
          attributes: ["employee_id", "territory_id"],
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["id", "employee_name"],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: survey ? survey?.doc_no : null,
          employee_name: data?.employee?.employee_name,
        };
      }

      // UDP
      if (doctype_id === 24) {
        const udp = await dbSAPPS.Udp.findOne({
          where: { id: doc_id },
          attributes: ["id", "doc_no", "territory_id"],
        });

        const data = await dbSAPPS.EmployeeTerritory.findOne({
          where: {
            territory_id: udp?.territory_id ? udp?.territory_id : null,
          },
          attributes: ["employee_id", "territory_id"],
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["id", "employee_name"],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: udp ? udp?.doc_no : null,
          employee_name: data?.employee?.employee_name,
        };
      }

      // DELIVERY TRIP
      if (doctype_id === 72) {
        const deliveryTrip = await dbSAPPS.DeliveryTrip.findOne({
          where: { id: doc_id },
          attributes: ["id", "delivery_no"],
          include: [
            {
              model: dbSAPPS.User,
              as: "user",
              attributes: ["id", "email", "full_name", "phone_number"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["employee_name"],
                },
              ],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: deliveryTrip ? deliveryTrip?.delivery_no.replace(/\//g, "-") : null,
          employee_name: deliveryTrip?.user?.employee?.employee_name,
        };
      }

      // NC PRODUCT
      if (doctype_id === 149) {
        const ncProduct = await dbSAPPS.NcProduct.findOne({
          where: { id: doc_id },
          attributes: ["id", "nc_no"],
          include: [
            {
              model: dbSAPPS.User,
              as: "user",
              attributes: ["id", "email", "full_name", "phone_number"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["employee_name"],
                },
              ],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: ncProduct ? ncProduct?.nc_no : null,
          employee_name: ncProduct?.user?.employee?.employee_name,
        };
      }

      // PRODUCT RETURN
      if (doctype_id === 150) {
        const productReturn = await dbSAPPS.ProductReturn.findOne({
          where: { id: doc_id },
          attributes: ["id", "barp_company"],
          include: [
            {
              model: dbSAPPS.User,
              as: "user",
              attributes: ["id", "email", "full_name", "phone_number"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["employee_name"],
                },
              ],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: productReturn ? productReturn?.barp_company : null,
          employee_name: productReturn?.user?.employee?.employee_name,
        };
      }

      // FSE
      if (doctype_id === 155) {
        const fse = await dbSAPPS.Fse.findOne({
          where: { id: doc_id },
          attributes: ["id", "name"],
          include: [
            {
              model: dbSAPPS.User,
              as: "user",
              attributes: ["id", "email", "full_name", "phone_number"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["employee_name"],
                },
              ],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: fse ? fse?.name : null,
          employee_name: fse?.user?.employee?.employee_name,
        };
      }

      // GIMMICK REQUEST
      if (doctype_id === 176) {
        const gimmickRequest = await dbSAPPS.GimmickRequest.findOne({
          where: { id: doc_id },
          attributes: ["id", "doc_no", "territory_id"],
        });

        const data = await dbSAPPS.EmployeeTerritory.findOne({
          where: {
            territory_id: gimmickRequest?.territory_id ? gimmickRequest?.territory_id : null,
          },
          attributes: ["employee_id", "territory_id"],
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["id", "employee_name"],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: gimmickRequest ? gimmickRequest?.doc_no : null,
          employee_name: data?.employee?.employee_name,
        };
      }

      // UP COUNTRY
      if (doctype_id === 194) {
        const upcountry = await dbSAPPS.UpCountry.findOne({
          where: { id: doc_id },
          attributes: ["id", "doc_no", "territory_id"],
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["id", "employee_name"],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_no: upcountry ? upcountry?.doc_no : null,
          employee_name: upcountry?.employee?.employee_name,
        };
      }

      // UC COST REALISASI
      if (doctype_id === 224) {
        const costRealization = await dbSAPPS.UcCostRealization.findOne({
          where: { id: doc_id },
          attributes: ["id", "filename", "territory_id", "upcountry_id"],
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["id", "employee_name"],
            },
          ],
        });

        return {
          ...workflow.toJSON(),
          doc_id: costRealization?.upcountry_id,
          doc_no: costRealization ? costRealization?.filename : null,
          employee_name: costRealization?.employee?.employee_name,
        };
      }
    })
  );

  return workflowsWithDoctype;
};
