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

module.exports = {
  getAll,
  filter,
  filterByYear,
  option,
  count,
  create,
  update,
  enabled,
  delete: _delete,
};

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

  if (params.doc_id) {
    filterConditions.id = params.doc_id;
  }

  if (params.userID) {
    filterConditions.owner = params.userID;
  }

  if (params.year || params.month) {
    filterConditions.barp_date = {};
    filterConditions.barp_date[Op.and] = [];

    if (params.year) {
      filterConditions.barp_date[Op.and].push(
        Sequelize.where(
          Sequelize.fn("YEAR", Sequelize.col("barp_date")),
          params.year
        )
      );
    }

    if (params.month) {
      filterConditions.barp_date[Op.and].push(
        Sequelize.where(
          Sequelize.fn("MONTH", Sequelize.col("barp_date")),
          params.month
        )
      );
    }

    // Kalau `Op.and` kosong, hapus dari kondisi
    if (filterConditions.barp_date[Op.and].length === 0) {
      delete filterConditions.barp_date[Op.and];
    }
  }

  try {
    const productReturns = await dbSAPPS.ProductReturn.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.ProductReturnChild,
          as: "productreturnchilds",
          attributes: [
            "id",
            "product_name",
            "item_code",
            "quantity",
            "batch_no",
            "expiry_date",
            "return_reason",
            "distributor_note",
            "company_note",
            "status",
            "product_note",
          ],
        },
        {
          model: dbSAPPS.ProductReturnFU,
          as: "productreturnfus",
          attributes: ["id", "date", "description"],
        },
        {
          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.Company,
          as: "company",
          attributes: ["id", "display_name", "name"],
        },
      ],
    });

    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: productReturns.map((item) => item.id),
        },
        doctype_id: 150,
      },
      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 = productReturns.map((item) => {
      const workflow = workflows.find((wf) => wf.doc_id === item.id);
      return {
        ...item.dataValues,
        workflow: workflow ? workflow.dataValues : null,
      };
    });

    return {
      productReturns: data,
      success: true,
      message: "product returns loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error || "Failed to load product returns!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

  if (params.company_id) {
    filterConditions.company_id = {
      [Op.eq]: params.company_id,
    };
  }

  if (params.enabled) {
    filterConditions.enabled = {
      [Op.eq]: params.enabled === "1" ? true : false,
    };
  }

  try {
    const productReturns = await dbSAPPS.ProductReturn.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.ProductReturnChild,
          as: "productreturnchilds",
          attributes: [
            "id",
            "product_name",
            "item_code",
            "quantity",
            "batch_no",
            "expiry_date",
            "return_reason",
            "distributor_note",
            "company_note",
            "status",
            "product_note",
          ],
        },
        {
          model: dbSAPPS.ProductReturnFU,
          as: "productreturnfus",
          attributes: ["id", "date", "description"],
        },
        {
          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.Company,
          as: "company",
          attributes: ["id", "display_name", "name"],
        },
      ],
    });

    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: productReturns.map((item) => item.id),
        },
        doctype_id: 150,
      },
      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 = productReturns.map((item) => {
      const workflow = workflows.find((wf) => wf.doc_id === item.id);
      return {
        ...item.dataValues,
        workflow: workflow ? workflow.dataValues : null,
      };
    });

    return {
      success: true,
      productReturns: data,
      message: "product returns loaded successfully!",
    };
  } catch (error) {
    const errorMessage =
      error || "Failed to retrieve product returns. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function filterByYear(params) {
  let filterConditions = {};

  if (params.year || params.month) {
    filterConditions.barp_date = {};
    filterConditions.barp_date[Op.and] = [];

    if (params.year) {
      filterConditions.barp_date[Op.and].push(
        Sequelize.where(
          Sequelize.fn("YEAR", Sequelize.col("barp_date")),
          params.year
        )
      );
    }

    if (params.month) {
      filterConditions.barp_date[Op.and].push(
        Sequelize.where(
          Sequelize.fn("MONTH", Sequelize.col("barp_date")),
          params.month
        )
      );
    }

    // Kalau `Op.and` kosong, hapus dari kondisi
    if (filterConditions.barp_date[Op.and].length === 0) {
      delete filterConditions.barp_date[Op.and];
    }
  }

  try {
    const productReturns = await dbSAPPS.ProductReturn.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.ProductReturnChild,
          as: "productreturnchilds",
          attributes: [
            "id",
            "product_name",
            "item_code",
            "quantity",
            "batch_no",
            "expiry_date",
            "return_reason",
            "distributor_note",
            "company_note",
            "status",
            "product_note",
          ],
        },
        {
          model: dbSAPPS.ProductReturnFU,
          as: "productreturnfus",
          attributes: ["id", "date", "description"],
        },
        {
          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.Company,
          as: "company",
          attributes: ["id", "display_name", "name"],
        },
      ],
    });

    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: productReturns.map((item) => item.id),
        },
        doctype_id: 150,
      },
      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 = productReturns.map((item) => {
      const workflow = workflows.find((wf) => wf.doc_id === item.id);
      return {
        ...item.dataValues,
        workflow: workflow ? workflow.dataValues : null,
      };
    });


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

async function option() {
  try {
    const productReturns = await dbSAPPS.ProductReturn.findAll({
      attributes: ["id", "barp_company", "barp_distributor"],
      where: { enabled: true },
      order: [["id", "ASC"]],
      include: [
        {
          model: dbSAPPS.ProductReturnChild,
          as: "productreturnchilds",
          attributes: [
            "id",
            "product_name",
            "item_code",
            "quantity",
            "batch_no",
            "expiry_date",
            "return_reason",
            "distributor_note",
            "company_note",
            "status",
            "product_note",
          ],
        },
        {
          model: dbSAPPS.ProductReturnFU,
          as: "productreturnfus",
          attributes: ["id", "date", "description"],
        },
        {
          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.Company,
          as: "company",
          attributes: ["id", "display_name", "name"],
        },
      ],
    });



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

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

    return {
      count,
      success: true,
      message: "product return loaded successfully!",
    };
  } catch (error) {
    const errorMessage =
      error || "Failed to counting product return. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function create(params) {

  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // Buat Product Return baru
    const newProductReturn = await dbSAPPS.ProductReturn.create(params, {
      transaction,
    });

    // Proses productreturnchilds jika ada
    if (params.productreturnchilds) {
      // Pastikan data adalah array
      if (typeof params.productreturnchilds === "string") {
        params.productreturnchilds = JSON.parse(params.productreturnchilds);
      }

      // Validasi dan persiapkan data untuk bulkCreate
      const productReturnsChildData = params.productreturnchilds.map(
        (productReturnChild) => {
          // Validasi field yang diperlukan
          if (!productReturnChild.product_name) {
            throw new Error("Missing required fields in product return child.");
          }

          return {
            ...productReturnChild,
            creation: Date.now(),
            modified: Date.now(),
            modified_by: params.modified_by,
            owner: params.owner,
            productreturn_id: newProductReturn.id,
          };
        }
      );

      // Lakukan bulkCreate
      try {
        await dbSAPPS.ProductReturnChild.bulkCreate(productReturnsChildData, {
          transaction,
        });
      } catch (bulkCreateError) {
        await transaction.rollback();
        return {
          success: false,
          message: "Failed to create data child: " + bulkCreateError.message,
        };
      }
    }

    if (params.productreturnfus) {
      // Pastikan data adalah array
      if (typeof params.productreturnfus === "string") {
        params.productreturnfus = JSON.parse(params.productreturnfus);
      }

      // Validasi dan persiapkan data untuk bulkCreate
      const productReturnsFUData = params.productreturnfus.map(
        (productReturnFU) => {
          // Validasi field yang diperlukan
          if (!productReturnFU.description) {
            throw new Error("Missing required fields in nc child.");
          }

          return {
            ...productReturnFU,
            creation: Date.now(),
            modified: Date.now(),
            modified_by: params.modified_by,
            owner: params.owner,
            productreturn_id: newProductReturn.id,
          };
        }
      );

      // Lakukan bulkCreate
      try {
        await dbSAPPS.ProductReturnFU.bulkCreate(productReturnsFUData, {
          transaction,
        });
      } catch (bulkCreateError) {
        await transaction.rollback();
        return {
          success: false,
          message: "Failed to create fu data: " + bulkCreateError.message,
        };
      }
    }

    if (params.workflowterritories && params.workflowterritories.length !== 0) {
          if (params.workflowterritories) {
            if (typeof params.workflowterritories === "string") {
              params.workflowterritories = JSON.parse(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: newProductReturn.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 (typeof params.workflowdesignations === "string") {
              params.workflowdesignations = JSON.parse(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: newProductReturn.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.",
                  };
                }
              }
            }
          }
        }

    // Commit transaksi jika semua berhasil
    await transaction.commit();
    return {
      success: true,
      message: "Data has been successfully adeded!",
      productReturns: newProductReturn,
    };
  } catch (error) {
    const errorMessage = error || "An error occurred during data creation.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

    await productReturn.update(params);

    if (params.productreturnchilds) {
      // Pastikan data adalah array
      if (typeof params.productreturnchilds === "string") {
        params.productreturnchilds = JSON.parse(params.productreturnchilds);
      }

      // Hapus child yang ada sebelum menambahkan yang baru
      await dbSAPPS.ProductReturnChild.destroy({
        where: { productreturn_id: id },
        transaction,
      });

      // Persiapkan data untuk bulkCreate
      const productReturnsChildData = params.productreturnchilds.map(
        (child) => {
          // Validasi field yang diperlukan
          if (!child.product_name) {
            throw new Error("Missing required fields in data child.");
          }

          return {
            ...child,
            creation: Date.now(),
            modified: Date.now(),
            modified_by: params.modified_by,
            owner: params.owner,
            productreturn_id: id, // Mengaitkan dengan ID Delivery Trip yang diupdate
          };
        }
      );

      // Lakukan bulkCreate untuk child yang baru
      await dbSAPPS.ProductReturnChild.bulkCreate(productReturnsChildData, {
        transaction,
      });
    }

    if (params.productreturnfus) {
      // Pastikan data adalah array
      if (typeof params.productreturnfus === "string") {
        params.productreturnfus = JSON.parse(params.productreturnfus);
      }

      // Hapus child yang ada sebelum menambahkan yang baru
      await dbSAPPS.ProductReturnFU.destroy({
        where: { productreturn_id: id },
        transaction,
      });

      // Persiapkan data untuk bulkCreate
      const productReturnsFUData = params.productreturnfus.map((child) => {
        // Validasi field yang diperlukan
        if (!child.description) {
          throw new Error("Missing required fields in data child.");
        }

        return {
          ...child,
          creation: Date.now(),
          modified: Date.now(),
          modified_by: params.modified_by,
          owner: params.owner,
          productreturn_id: id,
        };
      });

      // Lakukan bulkCreate untuk child yang baru
      await dbSAPPS.ProductReturnFU.bulkCreate(productReturnsFUData, {
        transaction,
      });
    }

    // Commit transaksi jika semua berhasil
    await transaction.commit();
    return {
      success: true,
      message: "Your data has been saved!",
    };
  } catch (error) {
    const errorMessage = error || "Failed to update product return data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function enabled(id, params) {
  try {
    const productReturn = await dbSAPPS.ProductReturn.findByPk(id);
    if (!productReturn)
      return {
        success: false,
        message: "Data 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 productReturn.update(updateData);
    return {
      success: true,
      message: "Data has been updated!",
    };
  } catch (error) {
    const errorMessage = error || "Failed to update Data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function _delete(id) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const productReturn = await dbSAPPS.ProductReturn.findByPk(id);
    if (!productReturn) return { success: false, message: "product return not found!" };
    const workflows = await dbSAPPS.Workflow.findAll({
          where: {
            doc_id: {
              [Op.eq]: productReturn.id,
            },
            doctype_id: 150,
          },
          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 productReturn.destroy();
    await transaction.commit();
    return {
      success: true,
      message: "Product return successfully deleted!",
    };
  } catch (error) {
    const errorMessage = error || "Failed to delete product return.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
