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

module.exports = {
  getAll,
  getOne,
  show,
  filterByID,
  filter,
  filterApproved,
  option,
  count,
  create,
  createWorkflow,
  update,
  enabled,
  delete: _delete,
};

async function getAll(params) {
  try {
    const filterConditions = {};

    // if (params?.usersId) {
    //     filterConditions.id = {
    //         [Op.in]: params?.usersId
    //     };
    // }
    if (params.doc_id) {
      filterConditions.id = params.doc_id;
    }

    const employeeRequests = await dbSAPPS.EmployeeRequest.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.User,
          as: "user",
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["employee_name"],
              include: [
                {
                  model: dbSAPPS.Designation,
                  as: "designation",
                  attributes: ["display_name"],
                },
              ],
            },
          ],
        },
        {
          model: dbSAPPS.Applicant,
          as: "applicants",
          attributes: [
            "id",
            "modified",
            "modified_by",
            "applicant_status",
            "name",
          ],
          include: [
            {
              model: dbSAPPS.User,
              as: "modifier",
              attributes: ["full_name"],
            },
          ],
        },
        {
          model: dbSAPPS.Department,
          as: "department",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "required_designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "superior_designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "new_territory",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "new_designation",
          attributes: ["id", "display_name"],
          include: [
            {
              model: dbSAPPS.Department,
              as: "department",
              attributes: ["id", "display_name"],
            },
          ],
        },
        {
          model: dbSAPPS.Employee,
          as: "replace_employee",
          attributes: ["id", "employee_name", "employee_number"],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.Department,
              as: "department",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.Territory,
              as: "territories",
              attributes: ["id", "display_name"],
            },
          ],
        },
        {
          model: dbSAPPS.Employee,
          as: "internal_employee",
          attributes: ["employee_name"],
        },
        { model: dbSAPPS.Company, as: "company", attributes: ["display_name"] },

        {
          model: dbSAPPS.Province,
          as: "province",
          attributes: ["display_name"],
        },
        { model: dbSAPPS.City, as: "city", attributes: ["display_name"] },
      ],
    });

    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: employeeRequests.map((item) => item.id),
        },
        doctype_id: 109,
      },
      attributes: [
        "id",
        "doc_id",
        "doctype_id",
        "status",
        "modified_by",
        "modified",
      ],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: [
            "id",
            "workflow_id",
            "designation_id",
            "territory_id",
            "status",
            "modified",
          ],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["id", "employee_name"],
                },
              ],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employees",
                  attributes: ["id", "employee_name"],
                  include: [
                    {
                      model: dbSAPPS.Designation,
                      as: "designation",
                      attributes: ["display_name"],
                    },
                  ],
                },
              ],
            },
          ],
        },
      ],
    });

    const data = employeeRequests.map((item) => {
      const workflow = workflows.find((wf) => wf.doc_id === item.id);
      return {
        ...item.dataValues,
        workflow: workflow ? workflow.dataValues : null,
      };
    });
    return {
      employeeRequests: data,
      success: true,
      message: "EmployeeRequests has been loaded!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load employeeRequests!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function getOne(id) {
  const employeeRequest = await dbSAPPS.EmployeeRequest.findByPk(id, {
    include: [
      {
        model: dbSAPPS.User,
        as: "user",
        include: [
          {
            model: dbSAPPS.Employee,
            as: "employee",
            attributes: ["employee_name"],
            include: [
              {
                model: dbSAPPS.Designation,
                as: "designation",
                attributes: ["display_name"],
              },
            ],
          },
        ],
      },
      {
        model: dbSAPPS.Applicant,
        as: "applicants",
        attributes: [
          "id",
          "modified",
          "modified_by",
          "applicant_status",
          "name",
        ],
        include: [
          {
            model: dbSAPPS.User,
            as: "modifier",
            attributes: ["full_name"],
          },
        ],
      },
      {
        model: dbSAPPS.Department,
        as: "department",
        attributes: ["display_name"],
      },
      {
        model: dbSAPPS.Designation,
        as: "required_designation",
        attributes: ["display_name"],
      },
      {
        model: dbSAPPS.Designation,
        as: "superior_designation",
        attributes: ["display_name"],
      },
      {
        model: dbSAPPS.Employee,
        as: "replace_employee",
        attributes: ["employee_name"],
      },
      {
        model: dbSAPPS.Employee,
        as: "internal_employee",
        attributes: ["employee_name"],
      },
      { model: dbSAPPS.Company, as: "company", attributes: ["display_name"] },
      { model: dbSAPPS.Province, as: "province", attributes: ["display_name"] },
      { model: dbSAPPS.City, as: "city", attributes: ["display_name"] },
    ],
  });

  const workflow = await dbSAPPS.Workflow.findOne({
    where: { doc_id: id },
    attributes: [
      "id",
      "doc_id",
      "doctype_id",
      "status",
      "modified_by",
      "modified",
    ],
    include: [
      {
        model: dbSAPPS.WorkflowApprover,
        as: "workflowapprovers",
        attributes: [
          "id",
          "workflow_id",
          "designation_id",
          "territory_id",
          "status",
          "modified",
        ],
        include: [
          {
            model: dbSAPPS.Designation,
            as: "designation",
            attributes: ["id", "name", "display_name"],
            include: [
              {
                model: dbSAPPS.Employee,
                as: "employee",
                attributes: ["id", "employee_name"],
              },
            ],
          },
          {
            model: dbSAPPS.Territory,
            as: "territory",
            attributes: ["id", "name", "display_name"],
            include: [
              {
                model: dbSAPPS.Employee,
                as: "employees",
                attributes: ["id", "employee_name"],
                include: [
                  {
                    model: dbSAPPS.Designation,
                    as: "designation",
                    attributes: ["display_name"],
                  },
                ],
              },
            ],
          },
        ],
      },
    ],
  });

  const data = {
    ...employeeRequest.dataValues,
    workflow: workflow ? workflow.dataValues : null,
  };

  const success = true;
  const message = "Loaded Successfully!";

  return { employeeRequest: data, success, message };
}

async function show() {
  try {
    const employeeRequests = await dbSAPPS.EmployeeRequest.findAll({
      where: {
        enabled: true,
        request_reason: {
          [Op.in]: [1, 2], // hanya ambil yang request_reason-nya 1 atau 2
        },
      },
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.User,
          as: "user",
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["employee_name"],
            },
          ],
        },
        {
          model: dbSAPPS.Department,
          as: "department",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "required_designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "replace_employee",
          attributes: ["employee_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "internal_employee",
          attributes: ["employee_name"],
        },
        { model: dbSAPPS.Company, as: "company", attributes: ["display_name"] },
        {
          model: dbSAPPS.Province,
          as: "province",
          attributes: ["display_name"],
        },
        { model: dbSAPPS.City, as: "city", attributes: ["display_name"] },
      ],
    });
    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: employeeRequests.map((item) => item.id),
        },
        doctype_id: 109,
      },
      attributes: ["id", "doc_id", "doctype_id", "status", "modified"],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: [
            "id",
            "workflow_id",
            "modified",
            "designation_id",
            "territory_id",
            "status",
          ],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["id", "employee_name"],
                },
              ],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employees",
                  attributes: ["id", "employee_name"],
                },
              ],
            },
          ],
        },
      ],
    });

    const data = employeeRequests
      .map((item) => {
        const workflow = workflows.find((wf) => wf.doc_id === item.id);
        return {
          ...item.dataValues,
          workflow: workflow ? workflow.dataValues : null,
        };
      })
      .filter((item) => item.workflow && item.workflow.status === "Approved");

    return {
      employeeRequests: data,
      success: true,
      message: "EmployeeRequests has been loaded!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load employeeRequests!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

  // Company filtering/scoping
  // Supports:
  // - company_id=<id>
  // - companiesId=<id>|<id1,id2>|companiesId[]=<id>
  if (params?.company_id != null && String(params.company_id).trim() !== "") {
    const companyId = Number(params.company_id);
    if (!Number.isNaN(companyId)) {
      filterConditions.company_id = { [Op.eq]: companyId };
    }
  } else {
    const rawCompaniesId =
      params?.companiesId ??
      params?.["companiesId[]"] ??
      params?.companies_id ??
      params?.["companies_id[]"];

    let companiesId = [];
    if (Array.isArray(rawCompaniesId)) {
      companiesId = rawCompaniesId;
    } else if (typeof rawCompaniesId === "string") {
      companiesId = rawCompaniesId.split(",");
    } else if (typeof rawCompaniesId === "number") {
      companiesId = [rawCompaniesId];
    }

    const parsedCompaniesId = companiesId
      .map((v) => Number(String(v).trim()))
      .filter((v) => !Number.isNaN(v));

    if (parsedCompaniesId.length) {
      filterConditions.company_id = { [Op.in]: parsedCompaniesId };
    }
  }

  if (params?.month && params?.year) {
    // Konversi bulan dan tahun menjadi rentang tanggal
    const startDate = new Date(
      `${params.year}-${params.month}-01T00:00:00.000Z`
    );
    const endDate = new Date(startDate);
    endDate.setMonth(endDate.getMonth() + 1); // Naik ke bulan berikutnya

    filterConditions.creation = {
      [Op.gte]: startDate, // Lebih besar atau sama dengan awal bulan
      [Op.lt]: endDate, // Kurang dari awal bulan berikutnya
    };
  }

  if (params.enabled === "1" || params.enabled === "0") {
    const filterValue = params.enabled === "1" ? 1 : 0;
    filterConditions.enabled = { [Op.eq]: filterValue };
  }

  if (
    params.request_reason === "1" ||
    params.request_reason === "2" ||
    params.request_reason === "3"
  ) {
    if (params.request_reason === "1") {
      const filterValue = 1;
      filterConditions.request_reason = { [Op.eq]: filterValue };
    }
    if (params.request_reason === "2") {
      const filterValue = 2;
      filterConditions.request_reason = { [Op.eq]: filterValue };
    }
    if (params.request_reason === "3") {
      const filterValue = 3;
      filterConditions.request_reason = { [Op.eq]: filterValue };
    }
  }

  try {
    const employeeRequests = await dbSAPPS.EmployeeRequest.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.User,
          as: "user",
          attributes: ["id", "email", "full_name"],
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["employee_name"],
              include: [
                {
                  model: dbSAPPS.Designation,
                  as: "designation",
                  attributes: ["display_name"],
                },
              ],
            },
          ],
        },
        {
          model: dbSAPPS.Applicant,
          as: "applicants",
          attributes: [
            "id",
            "modified",
            "modified_by",
            "applicant_status",
            "name",
          ],
          include: [
            {
              model: dbSAPPS.User,
              as: "modifier",
              attributes: ["full_name"],
            },
          ],
        },
        {
          model: dbSAPPS.Department,
          as: "department",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "required_designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "replace_employee",
          attributes: ["employee_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "internal_employee",
          attributes: ["employee_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "required_designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "superior_designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "new_territory",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "new_designation",
          attributes: ["id", "display_name"],
          include: [
            {
              model: dbSAPPS.Department,
              as: "department",
              attributes: ["id", "display_name"],
            },
          ],
        },
        {
          model: dbSAPPS.Employee,
          as: "replace_employee",
          attributes: ["id", "employee_name", "employee_number"],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.Department,
              as: "department",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.Territory,
              as: "territories",
              attributes: ["id", "display_name"],
            },
          ],
        },
        {
          model: dbSAPPS.Employee,
          as: "internal_employee",
          attributes: ["employee_name"],
        },
        { model: dbSAPPS.Company, as: "company", attributes: ["display_name"] },

        {
          model: dbSAPPS.Province,
          as: "province",
          attributes: ["display_name"],
        },
        { model: dbSAPPS.City, as: "city", attributes: ["display_name"] },
      ],
    });

    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: employeeRequests.map((item) => item.id),
        },
        doctype_id: 109,
      },
      attributes: [
        "id",
        "doc_id",
        "doctype_id",
        "status",
        "modified_by",
        "modified",
      ],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: [
            "id",
            "workflow_id",
            "designation_id",
            "territory_id",
            "status",
            "modified",
          ],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["id", "employee_name"],
                },
              ],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employees",
                  attributes: ["id", "employee_name"],
                  include: [
                    {
                      model: dbSAPPS.Designation,
                      as: "designation",
                      attributes: ["display_name"],
                    },
                  ],
                },
              ],
            },
          ],
        },
      ],
    });

    let data = employeeRequests.map((item) => {
      const workflow = workflows.find((wf) => wf.doc_id === item.id);
      return {
        ...item.dataValues,
        workflow: workflow ? workflow.dataValues : null,
      };
    });

    if (params.workflowStat) {
      if (params.workflowStat === "Approved") {
        data = data.filter((item) => {
          if (!item.workflow) {
            console.warn(`Item dengan id ${item.id} tidak memiliki workflow.`);
            return false;
          }
          return item.workflow.status === "Approved";
        });
      } else if (params.workflowStat !== "Approved") {
        // Untuk nilai selain "Approved" (dan bukan string kosong)
        data = data.filter((item) => {
          if (!item.workflow) {
            console.warn(`Item dengan id ${item.id} tidak memiliki workflow.`);
            return true;
          }
          return item.workflow.status !== "Approved";
        });
      }
    }

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

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

  // Company filtering/scoping (same behavior as filter)
  if (params?.company_id != null && String(params.company_id).trim() !== "") {
    const companyId = Number(params.company_id);
    if (!Number.isNaN(companyId)) {
      filterConditions.company_id = { [Op.eq]: companyId };
    }
  } else {
    const rawCompaniesId =
      params?.companiesId ??
      params?.["companiesId[]"] ??
      params?.companies_id ??
      params?.["companies_id[]"];

    let companiesId = [];
    if (Array.isArray(rawCompaniesId)) {
      companiesId = rawCompaniesId;
    } else if (typeof rawCompaniesId === "string") {
      companiesId = rawCompaniesId.split(",");
    } else if (typeof rawCompaniesId === "number") {
      companiesId = [rawCompaniesId];
    }

    const parsedCompaniesId = companiesId
      .map((v) => Number(String(v).trim()))
      .filter((v) => !Number.isNaN(v));

    if (parsedCompaniesId.length) {
      filterConditions.company_id = { [Op.in]: parsedCompaniesId };
    }
  }

  if (params?.month && params?.year) {
    // Konversi bulan dan tahun menjadi rentang tanggal
    const startDate = new Date(
      `${params.year}-${params.month}-01T00:00:00.000Z`
    );
    const endDate = new Date(startDate);
    endDate.setMonth(endDate.getMonth() + 1); // Naik ke bulan berikutnya

    filterConditions.creation = {
      [Op.gte]: startDate, // Lebih besar atau sama dengan awal bulan
      [Op.lt]: endDate, // Kurang dari awal bulan berikutnya
    };
  }

  if (params.enabled === "1" || params.enabled === "0") {
    const filterValue = params.enabled === "1" ? 1 : 0;
    filterConditions.enabled = { [Op.eq]: filterValue };
  }

  if (params.request_reason === "3") {
    filterConditions.request_reason = {
      [Op.in]: [3], // hanya ambil yang request_reason-nya 1 atau 2
    };
  } else {
    filterConditions.request_reason = {
      [Op.in]: [1, 2], // hanya ambil yang request_reason-nya 1 atau 2
    };
  }

  try {
    const employeeRequests = await dbSAPPS.EmployeeRequest.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.User,
          as: "user",
          attributes: ["id", "email", "full_name"],
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["employee_name"],
              include: [
                {
                  model: dbSAPPS.Designation,
                  as: "designation",
                  attributes: ["display_name"],
                },
              ],
            },
          ],
        },
        {
          model: dbSAPPS.Applicant,
          as: "applicants",
          attributes: [
            "id",
            "modified",
            "modified_by",
            "applicant_status",
            "name",
          ],
          include: [
            {
              model: dbSAPPS.User,
              as: "modifier",
              attributes: ["full_name"],
            },
          ],
        },
        {
          model: dbSAPPS.Department,
          as: "department",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "required_designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "replace_employee",
          attributes: ["employee_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "internal_employee",
          attributes: ["employee_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "required_designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "superior_designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "new_territory",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "new_designation",
          attributes: ["id", "display_name"],
          include: [
            {
              model: dbSAPPS.Department,
              as: "department",
              attributes: ["id", "display_name"],
            },
          ],
        },
        {
          model: dbSAPPS.Employee,
          as: "replace_employee",
          attributes: ["id", "employee_name", "employee_number"],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.Department,
              as: "department",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.Territory,
              as: "territories",
              attributes: ["id", "display_name"],
            },
          ],
        },
        {
          model: dbSAPPS.Employee,
          as: "internal_employee",
          attributes: ["employee_name"],
        },
        { model: dbSAPPS.Company, as: "company", attributes: ["display_name"] },

        {
          model: dbSAPPS.Province,
          as: "province",
          attributes: ["display_name"],
        },
        { model: dbSAPPS.City, as: "city", attributes: ["display_name"] },
      ],
    });

    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: employeeRequests.map((item) => item.id),
        },
        doctype_id: 109,
      },
      attributes: [
        "id",
        "doc_id",
        "doctype_id",
        "status",
        "modified_by",
        "modified",
      ],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: [
            "id",
            "workflow_id",
            "designation_id",
            "territory_id",
            "status",
            "modified",
          ],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["id", "employee_name"],
                },
              ],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employees",
                  attributes: ["id", "employee_name"],
                  include: [
                    {
                      model: dbSAPPS.Designation,
                      as: "designation",
                      attributes: ["display_name"],
                    },
                  ],
                },
              ],
            },
          ],
        },
      ],
    });

    let data = employeeRequests
      .map((item) => {
        const workflow = workflows.find((wf) => wf.doc_id === item.id);
        return {
          ...item.dataValues,
          workflow: workflow ? workflow.dataValues : null,
        };
      })
      .filter((item) => item.workflow && item.workflow.status === "Approved");
    if (params.workflowStat) {
      if (params.workflowStat === "Approved") {
        data = data.filter((item) => {
          if (!item.workflow) {
            console.warn(`Item dengan id ${item.id} tidak memiliki workflow.`);
            return false;
          }
          return item.workflow.status === "Approved";
        });
      } else if (params.workflowStat !== "Approved") {
        // Untuk nilai selain "Approved" (dan bukan string kosong)
        data = data.filter((item) => {
          if (!item.workflow) {
            console.warn(`Item dengan id ${item.id} tidak memiliki workflow.`);
            return true;
          }
          return item.workflow.status !== "Approved";
        });
      }
    }

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

async function filterByID(params) {
  try {
    const employeeRequest = await dbSAPPS.EmployeeRequest.findAll({
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.User,
          as: "user",
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["employee_name"],
              include: [
                {
                  model: dbSAPPS.Designation,
                  as: "designation",
                  attributes: ["display_name"],
                },
              ],
            },
          ],
        },
        {
          model: dbSAPPS.Applicant,
          as: "applicants",
          attributes: [
            "id",
            "modified",
            "modified_by",
            "applicant_status",
            "name",
          ],
          include: [
            {
              model: dbSAPPS.User,
              as: "modifier",
              attributes: ["full_name"],
            },
          ],
        },
        {
          model: dbSAPPS.Department,
          as: "department",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "required_designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "superior_designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "replace_employee",
          attributes: ["employee_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "internal_employee",
          attributes: ["employee_name"],
        },
        { model: dbSAPPS.Company, as: "company", attributes: ["display_name"] },
        {
          model: dbSAPPS.Province,
          as: "province",
          attributes: ["display_name"],
        },
        { model: dbSAPPS.City, as: "city", attributes: ["display_name"] },
      ],
    });

    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: employeeRequest.map((item) => item.id),
        },
        doctype_id: 109,
      },
      attributes: [
        "id",
        "doc_id",
        "doctype_id",
        "status",
        "modified_by",
        "modified",
      ],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: [
            "id",
            "workflow_id",
            "designation_id",
            "territory_id",
            "status",
            "modified",
          ],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["id", "employee_name"],
                },
              ],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employees",
                  attributes: ["id", "employee_name"],
                  include: [
                    {
                      model: dbSAPPS.Designation,
                      as: "designation",
                      attributes: ["display_name"],
                    },
                  ],
                },
              ],
            },
          ],
        },
      ],
    });

    const data = employeeRequest.map((item) => {
      const workflow = workflows.find((wf) => wf.doc_id === item.id);
      return {
        ...item.dataValues,
        workflow: workflow ? workflow.dataValues : null,
      };
    });

    // Filter data berdasarkan kondisi yang ditentukan
    const filteredData = data.filter((item) => {
      const isOwnerMatch = parseInt(item.owner) === parseInt(params.user_id);
      const hasTerritoryMatch =
        item.workflow?.workflowapprovers?.some((approver) => {
          return (
            parseInt(approver.territory?.id) === parseInt(params.territory_id)
          );
        }) || false;
      const hasDesignationMatch =
        item.workflow?.workflowapprovers?.some((approver) => {
          return (
            parseInt(approver.designation?.id) ===
            parseInt(params.designation_id)
          );
        }) || false;
      return isOwnerMatch || hasTerritoryMatch || hasDesignationMatch;
    });

    // Mengembalikan respons dengan data yang telah difilter
    return {
      employeeRequest: filteredData,
      success: true,
      message: "employeeRequest loaded successfully!",
    };
  } catch (error) {
    // Menangani error dan mengembalikan respons dengan status 500 Internal Server Error
    const errorMessage = error.message || "Failed to load employeeRequest!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function option() {
  try {
    const employeeRequests = await dbSAPPS.EmployeeRequest.findAll({
      attributes: [
        "id",
        "no_fpk",
        "required_designation_id",
        "city_id",
        "province_id",
        "company_id",
        "request_reason"
      ],
      where: {
        enabled: true,
        request_reason: {
          [Op.in]: [1, 2], // hanya ambil yang request_reason-nya 1 atau 2
        },
      },
      order: [["creation", "ASC"]],
      include: [
        {
          model: dbSAPPS.Department,
          as: "department",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "required_designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "replace_employee",
          attributes: ["employee_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "internal_employee",
          attributes: ["employee_name"],
        },
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["display_name"],
        },

        {
          model: dbSAPPS.Province,
          as: "province",
          attributes: ["display_name"],
        },
        { model: dbSAPPS.City, as: "city", attributes: ["display_name"] },
      ],
    });

    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: { [Op.in]: employeeRequests.map((item) => item.id) },
        doctype_id: 109,
      },
      attributes: [
        "id",
        "doc_id",
        "doctype_id",
        "status",
        "modified_by",
        "modified",
      ],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: [
            "id",
            "workflow_id",
            "designation_id",
            "territory_id",
            "status",
            "modified",
          ],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["id", "employee_name"],
                },
              ],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employees",
                  attributes: ["id", "employee_name"],
                },
              ],
            },
          ],
        },
      ],
    });
    // Gabungkan data dan filter berdasarkan status workflow "Approved"
    const data = employeeRequests
      .map((item) => {
        const workflow = workflows.find((wf) => wf.doc_id === item.id);
        return {
          ...item.dataValues,
          workflow: workflow ? workflow.dataValues : null,
        };
      })
      .filter((item) => item.workflow && item.workflow.status === "Approved"); // Filter di sini

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

async function count() {
  try {
    const count = await dbSAPPS.EmployeeRequest.count({
      where: { enabled: true },
    });

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

async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // validasi fpk
    const existingRequest = await dbSAPPS.EmployeeRequest.findOne({
      where: {
        company_id: params.company_id,
        required_designation_id: params.required_designation_id,
        department_id: params.department_id,
        province_id: params.province_id,
        city_id: params.city_id,
        priority: params.priority,
        due_date: params.due_date,
        request_date: params.request_date,
      },
    });

    if (existingRequest)
      return { success: false, message: "Data Request already exists." };

    const existingFPK = await dbSAPPS.EmployeeRequest.findOne({
      where: { no_fpk: params.no_fpk },
    });

    if (existingFPK)
      return { success: false, message: "No FPK already exists." };

    // Generate NO FPK
    // Helper function to convert month to Roman numeral
    const toRoman = (month) => {
      const romanNumerals = [
        "I",
        "II",
        "III",
        "IV",
        "V",
        "VI",
        "VII",
        "VIII",
        "IX",
        "X",
        "XI",
        "XII",
      ];
      return romanNumerals[month - 1];
    };

    // Generate next FPK number
    const generateNextFPK = async () => {
      const now = new Date();
      const currentMonth = now.getMonth() + 1;
      const currentYear = now.getFullYear();
      const monthRoman = toRoman(currentMonth);

      // Find all FPKs in current month/year
      const existingFPKs = await dbSAPPS.EmployeeRequest.findAll({
        where: {
          no_fpk: {
            [Op.like]: `%/FPK/${monthRoman}/${currentYear}`,
          },
        },
        transaction,
      });

      // Extract and find max sequence number
      let maxNumber = 0;
      existingFPKs.forEach((fpk) => {
        const parts = fpk.no_fpk.split("/");
        if (parts.length === 4) {
          const numberPart = parseInt(parts[0], 10);
          if (!isNaN(numberPart)) {
            maxNumber = Math.max(maxNumber, numberPart);
          }
        }
      });

      // Increment and format with leading zeros
      const nextNumber = (maxNumber + 1).toString().padStart(3, "0");
      return `${nextNumber}/FPK/${monthRoman}/${currentYear}`;
    };

    // Generate the new FPK number
    const newFPKNumber = await generateNextFPK();

    if (!params.enabled) params.enabled = true;

    const newParams = {
      ...params,
      no_fpk: newFPKNumber,
    };
    const newEmployeeRequest = await dbSAPPS.EmployeeRequest.create(newParams, {
      transaction,
    });

    if (params.workflowterritories && params.workflowterritories.length !== 0) {
      if (params.workflowterritories) {
        if (
          Array.isArray(params.workflowterritories) &&
          params.workflowterritories.length > 0
        ) {
          const workflowSettings = await dbSAPPS.WorkflowSetting.findAll(
            {
              where: {
                doctype_id: parseInt(params.doctype_id),
                enabled: true,
              },
            },
            { transaction }
          );
          const limitWorkflowTerritories = workflowSettings[0]?.limit || 0;
          const workflowTerritories = params.workflowterritories.slice(
            0,
            limitWorkflowTerritories
          );

          if (workflowTerritories.length === 0) {
            await transaction.rollback();
            return {
              success: false,
              message: "You must have at least one workflow territory.",
            };
          }

          const workflowData = {
            creation: Date.now(),
            modified: Date.now(),
            modified_by: parseInt(params.modified_by) || 1,
            owner: parseInt(params.owner) || 1,
            doctype_id: parseInt(params.doctype_id),
            doc_id: newEmployeeRequest.id,
            status: "Draft",
          };

          const workflow = await dbSAPPS.Workflow.create(workflowData, {
            transaction,
          });
          if (workflow) {
            const workflowApproversData = workflowTerritories.map(
              (workflowTerritory) => ({
                creation: Date.now(),
                modified: Date.now(),
                modified_by: parseInt(params.modified_by) || 1,
                owner: parseInt(params.owner) || 1,
                workflow_id: workflow.id,
                territory_id: parseInt(workflowTerritory.territory_id),
                status: null,
              })
            );
            const approvers = await dbSAPPS.WorkflowApprover.bulkCreate(
              workflowApproversData,
              { transaction }
            );
            if (
              approvers?.length !== workflowTerritories.length ||
              !approvers.every(
                (approver) =>
                  approver.workflow_id === workflow.id &&
                  approver.territory_id !== null
              )
            ) {
              await transaction.rollback();
              return {
                success: false,
                message:
                  "Failed to create workflow approvers. Please try again later.",
              };
            }
          }
        }
      }
    } else {
      if (params.workflowdesignations) {
        if (
          Array.isArray(params.workflowdesignations) &&
          params.workflowdesignations.length > 0
        ) {
          const workflowSettings = await dbSAPPS.WorkflowSetting.findAll(
            {
              where: {
                doctype_id: params.doctype_id,
                type: "Designation",
                enabled: true,
              },
            },
            { transaction }
          );
          const limitWorkflowDesignations = workflowSettings[0]?.limit || 0;
          const workflowDesignations = params.workflowdesignations.slice(
            0,
            limitWorkflowDesignations
          );

          if (workflowDesignations.length === 0) {
            await transaction.rollback();
            return {
              success: false,
              message: "You must have at least one workflow designation.",
            };
          }

          const workflowData = {
            creation: Date.now(),
            modified: Date.now(),
            modified_by: params.modified_by || 1,
            owner: params.owner || 1,
            doctype_id: params.doctype_id,
            doc_id: newEmployeeRequest.id,
            status: "Draft",
          };

          const workflow = await dbSAPPS.Workflow.create(workflowData, {
            transaction,
            lock: transaction.LOCK.UPDATE,
          });
          if (workflow) {
            const workflowApproversData = workflowDesignations.map(
              (workflowDesignation) => ({
                creation: Date.now(),
                modified: Date.now(),
                modified_by: params.modified_by || 1,
                owner: params.owner || 1,
                workflow_id: workflow.id,
                designation_id: workflowDesignation.designation_id,
                status: null,
              })
            );
            const approvers = await dbSAPPS.WorkflowApprover.bulkCreate(
              workflowApproversData,
              { transaction, lock: transaction.LOCK.UPDATE }
            );
            if (
              approvers?.length !== workflowDesignations.length ||
              !approvers.every(
                (approver) =>
                  approver.workflow_id === workflow.id &&
                  approver.designation_id !== null
              )
            ) {
              await transaction.rollback();
              return {
                success: false,
                message:
                  "Failed to create workflow approvers. Please try again later.",
              };
            }
          }
        }
      }
    }

    if (!params.workflowdesignations && !params.workflowterritories) {
      await transaction.rollback();
      return {
        success: false,
        message:
          "You must have at least one workflow designation or workflow territory.",
      };
    }

    await transaction.commit();
    return {
      success: true,
      message: "Your FPK has been successfully created!",
      employeeRequest: newEmployeeRequest,
    };
  } catch (error) {
    const errorMessage =
      error.message || "An error occurred during employee registration.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function update(id, params) {
  try {
    const employeeRequest = await dbSAPPS.EmployeeRequest.findByPk(id);
    if (!employeeRequest)
      return {
        success: false,
        message: "Employee Request not found!",
      };

    // dipastiin dulu deh
    // const existingRequest = await dbSAPPS.EmployeeRequest.findOne({
    //     where: {
    //         company_id: params.company_id,
    //         required_designation_id: params.required_designation_id,
    //         department_id: params.department_id,
    //         province_id: params.province_id,
    //         city_id: params.city_id,
    //         priority: params.priority,
    //         due_date: params.due_date,
    //         request_date: params.request_date,
    //         owner: params.owner
    //     }
    // });

    // if (existingRequest) return { success: false, message: 'Data Request already exists.' };

    await employeeRequest.update(params);
    return {
      success: true,
      message: "Your data FPK has been saved!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to update employee data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function enabled(id, params) {
  try {
    const employeeRequest = await dbSAPPS.EmployeeRequest.findByPk(id);
    if (!employeeRequest)
      return {
        success: false,
        message: "Employee Request not found!",
      };

    const updateData = {};
    updateData.enabled = params.enabled;
    if (params.modified_by) updateData.modified_by = params.modified_by;
    if (params.modified) updateData.modified = params.modified;

    await employeeRequest.update(updateData);

    return {
      success: true,
      message: "Employee Request has been updated!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to update employee request.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function _delete(id) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const employeeRequest = await dbSAPPS.EmployeeRequest.findByPk(id);
    if (!employeeRequest)
      return { success: false, message: "Employee request not found!" };
    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.eq]: employeeRequest.id,
        },
        doctype_id: 109,
      },
      attributes: [
        "id",
        "doc_id",
        "doctype_id",
        "status",
        "modified_by",
        "modified",
      ],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: [
            "id",
            "workflow_id",
            "designation_id",
            "territory_id",
            "status",
            "modified",
          ],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["id", "employee_name"],
                },
              ],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employees",
                  attributes: ["id", "employee_name"],
                },
              ],
            },
          ],
        },
      ],
    });
    if (!workflows)
      return {
        success: false,
        message: "Workflows employee request not found!",
      };
    for (const workflow of workflows) {
      if (workflow) {
        await dbSAPPS.WorkflowApprover.destroy({
          where: { workflow_id: workflow.id },
          transaction,
        });
        await dbSAPPS.Workflow.destroy({
          where: { id: workflow.id },
          transaction,
        });
      }
    }
    await new Promise((resolve) => setTimeout(resolve, 500));
    await employeeRequest.destroy({ transaction });

    await transaction.commit();
    return {
      success: true,
      message: "Employee Request successfully deleted!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to delete employee request.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function createWorkflow(params) {
    const transaction = await dbSAPPS.sequelize.transaction();
    try {
        const existingWorkflow = await dbSAPPS.Workflow.findOne({ where: { doc_id: parseInt(params.doc_id), doctype_id: parseInt(params.doctype_id) }, transaction });
        if (existingWorkflow) {
            await dbSAPPS.WorkflowApprover.destroy({ where: { workflow_id: existingWorkflow.id }, transaction });
            await existingWorkflow.destroy({ transaction });
        }

        if (params.territory_id) {
            const workflowData = {
                creation: Date.now(),
                modified: Date.now(),
                modified_by: parseInt(params.modified_by) || 1,
                owner: parseInt(params.owner) || 1,
                doctype_id: parseInt(params.doctype_id),
                doc_id: parseInt(params.doc_id),
                status: params.workflow_status ? params.workflow_status : 'Draft',
            }

            const workflow = await dbSAPPS.Workflow.create(workflowData, { transaction, lock: transaction.LOCK.UPDATE });

            if (!workflow) {
                return {
                    success: false,
                    message: 'Failed to create workflow. Please try again later.'
                };
            }

            const workflowApproversData = {
                creation: Date.now(),
                modified: Date.now(),
                modified_by: parseInt(params.modified_by) || 1,
                owner: parseInt(params.owner) || 1,
                workflow_id: workflow.id,
                territory_id: parseInt(params.territory_id),
                status: params.approver_status ? params.approver_status : null,
            };

            const approvers = await dbSAPPS.WorkflowApprover.bulkCreate([workflowApproversData], { transaction, lock: transaction.LOCK.UPDATE });

            if (!approvers || approvers.length !== 1 || !approvers.every(approver => approver.workflow_id === workflow.id && approver.territory_id === parseInt(params.territory_id))) {
                await transaction.rollback();
                return {
                    success: false,
                    message: 'Failed to create workflow approver. Please try again later.'
                };
            }

        } else {
            if (params.designation_id) {
                const workflowData = {
                    creation: Date.now(),
                    modified: Date.now(),
                    modified_by: parseInt(params.modified_by) || 1,
                    owner: parseInt(params.owner) || 1,
                    doctype_id: parseInt(params.doctype_id),
                    doc_id: parseInt(params.doc_id),
                    status: params.workflow_status ? params.workflow_status : 'Draft',
                };

                const workflow = await dbSAPPS.Workflow.create(workflowData, { transaction, lock: transaction.LOCK.UPDATE });

                if (!workflow) {
                    return {
                        success: false,
                        message: 'Failed to create workflow. Please try again later.'
                    };
                }

                const workflowApproversData = {
                    creation: Date.now(),
                    modified: Date.now(),
                    modified_by: parseInt(params.modified_by) || 1,
                    owner: parseInt(params.owner) || 1,
                    workflow_id: workflow.id,
                    designation_id: parseInt(params.designation_id),
                    status: params.approver_status ? params.approver_status : null,
                };

                let approvers = await dbSAPPS.WorkflowApprover.bulkCreate([workflowApproversData], { transaction, lock: transaction.LOCK.UPDATE });

                if (!approvers || approvers.length !== 1 || !approvers.every(approver => approver.workflow_id === workflow.id && approver.designation_id === parseInt(params.designation_id))) {
                    await transaction.rollback();
                    return {
                        success: false,
                        message: 'Failed to create workflow approvers. Please try again later.'
                    };
                }

            }
        }

        await transaction.commit();
        return {
            success: true,
            message: 'Workflow has been created!',
        };
    } catch (error) {
        const errorMessage = error.message || 'Failed to create workflow.';
        return {
            success: false,
            message: errorMessage
        };
    }
}
