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

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

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

  if (params.doc_id) {
    filterConditions.id = params.doc_id;
  }
  try {
    const deliveryTrips = await dbSAPPS.DeliveryTrip.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.DeliveryTripChild,
          as: "deliverytripchilds",
          attributes: [
            "id",
            "deliverytrip_id",
            "delivery_note",
            "customer",
            "address",
            "receiver",
            "start",
            "end",
            "total_time",
            "send_date",
            "receive_date",
            "sign",
          ],
        },
        {
          model: dbSAPPS.DeliveryTripCost,
          as: "deliverytripcosts",
          attributes: ["id", "item_type", "description", "price"],
        },
        {
          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: ["name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "driver_info",
          attributes: ["employee_name"],
        },
        {
          model: dbSAPPS.Vehicle,
          as: "vehicle",
          attributes: ["license_plate", "odometer_value"],
        },
        {
          model: dbSAPPS.DeliveryExpeditionVendor,
          as: "expedition_vendor",
          attributes: ["id", "name"],
          required: false,
        },
      ],
    });

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

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

/**
 * Returns list of Delivery Note names that are already used in Delivery Trips
 * that have been approved by the approver (workflow status = Approved).
 * Only such DNs are excluded from the dropdown; DNs used in Draft/Need Approval
 * trips can still be selected (e.g. for edit before approval).
 * Frontend uses this to exclude them from the Delivery Note dropdown.
 */
async function getUsedDeliveryNoteNames() {
  try {
    const DELIVERY_TRIP_DOCTYPE_ID = 72;
    const approvedWorkflows = await dbSAPPS.Workflow.findAll({
      where: {
        doctype_id: DELIVERY_TRIP_DOCTYPE_ID,
        status: "Approved",
      },
      attributes: ["doc_id"],
      raw: true,
    });
    const approvedTripIds = approvedWorkflows
      .map((w) => w.doc_id)
      .filter((id) => id != null);
    if (approvedTripIds.length === 0) {
      return {
        usedDeliveryNoteNames: [],
        success: true,
        message: "Used delivery note names loaded.",
      };
    }
    const rows = await dbSAPPS.DeliveryTripChild.findAll({
      where: { deliverytrip_id: { [Op.in]: approvedTripIds } },
      attributes: ["delivery_note"],
      raw: true,
    });
    const names = new Set();
    for (const row of rows) {
      let arr = row.delivery_note;
      if (arr == null) continue;
      if (typeof arr === "string") {
        try {
          arr = JSON.parse(arr);
        } catch {
          continue;
        }
      }
      if (!Array.isArray(arr)) continue;
      arr.forEach((name) => {
        if (name != null && String(name).trim()) names.add(String(name).trim());
      });
    }
    return {
      usedDeliveryNoteNames: Array.from(names),
      success: true,
      message: "Used delivery note names loaded.",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load used delivery note names.";
    return {
      usedDeliveryNoteNames: [],
      success: false,
      message: errorMessage,
    };
  }
}

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

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

  if (params.company_id) {
    filterConditions.company_id = { [Op.eq]: Number(params.company_id) };
  } else if (
    params.companiesId &&
    Array.isArray(params.companiesId) &&
    params.companiesId.length > 0
  ) {
    filterConditions.company_id = {
      [Op.in]: params.companiesId.map((id) => Number(id)).filter((id) => !Number.isNaN(id)),
    };
  }

  if (params.delivery_type === "internal" || params.delivery_type === "external") {
    filterConditions.delivery_type = params.delivery_type;
  }

  try {
    const deliveryTrips = await dbSAPPS.DeliveryTrip.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]], // Opsional: Mengurutkan hasil berdasarkan kolom 'modified'
      include: [
        {
          model: dbSAPPS.DeliveryTripChild,
          as: "deliverytripchilds",
          attributes: [
            "id",
            "deliverytrip_id",
            "delivery_note",
            "customer",
            "address",
            "receiver",
            "start",
            "end",
            "total_time",
            "send_date",
            "receive_date",
            "sign",
          ],
        },
        {
          model: dbSAPPS.DeliveryTripCost,
          as: "deliverytripcosts",
          attributes: ["id", "item_type", "description", "price"],
        },
        {
          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: ["name"],
        },
        {
          model: dbSAPPS.Vehicle,
          as: "vehicle",
          attributes: ["id", "license_plate", "odometer_value"],
        },
        {
          model: dbSAPPS.DeliveryExpeditionVendor,
          as: "expedition_vendor",
          attributes: ["id", "name"],
          required: false,
        },
      ],
    });
    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: deliveryTrips.map((item) => item.id),
        },
        doctype_id: 72,
      },
      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 = deliveryTrips.map((item) => {
      const workflow = workflows.find((wf) => wf.doc_id === item.id);
      return {
        ...item.dataValues,
        workflow: workflow ? workflow.dataValues : null,
      };
    });

    const workflowStat = params.workflowStat;
    if (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 (workflowStat === "Need Approval" || workflowStat === "Draft" || workflowStat === "Rejected" || workflowStat === "On Progress") {
      data = data.filter((item) => {
        if (!item.workflow) {
          console.warn(`Item dengan id ${item.id} tidak memiliki workflow.`);
          return true;
        }
        return item.workflow.status === workflowStat;
      });
    }
    // workflowStat kosong, undefined, atau selain di atas: tampilkan semua data

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

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

  if (params.year || params.month) {
    filterConditions.date = {};

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

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

  try {
    const deliveryTrips = await dbSAPPS.DeliveryTrip.findAll({
      where: filterConditions,
      order: [["date", "DESC"]], // Opsional: Mengurutkan hasil berdasarkan kolom 'modified'
      include: [
        {
          model: dbSAPPS.DeliveryTripChild,
          as: "deliverytripchilds",
          attributes: [
            "id",
            "deliverytrip_id",
            "delivery_note",
            "customer",
            "address",
            "receiver",
            "start",
            "end",
            "total_time",
            "send_date",
            "receive_date",
            "sign",
          ],
        },
        {
          model: dbSAPPS.DeliveryTripCost,
          as: "deliverytripcosts",
          attributes: ["id", "item_type", "description", "price"],
        },
        {
          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: ["name"],
        },
        {
          model: dbSAPPS.Vehicle,
          as: "vehicle",
          attributes: ["id", "license_plate", "odometer_value"],
        },
        {
          model: dbSAPPS.DeliveryExpeditionVendor,
          as: "expedition_vendor",
          attributes: ["id", "name"],
          required: false,
        },
      ],
    });

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

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

async function option() {
  try {
    const deliveryTrips = await dbSAPPS.DeliveryTrip.findAll({
      attributes: ["id", "delivery_no"],
      where: { enabled: true },
      order: [["delivery_no", "ASC"]],
      include: [
        {
          model: dbSAPPS.DeliveryTripChild,
          as: "deliverytripchilds",
          attributes: [
            "id",
            "deliverytrip_id",
            "delivery_note",
            "customer",
            "address",
            "receiver",
            "start",
            "end",
            "total_time",
            "send_date",
            "receive_date",
            "sign",
          ],
        },
        {
          model: dbSAPPS.DeliveryTripCost,
          as: "deliverytripcosts",
          attributes: ["id", "item_type", "description", "price"],
        },
        {
          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: ["name"],
        },
        {
          model: dbSAPPS.Vehicle,
          as: "vehicle",
          attributes: ["license_plate", "odometer_value"],
        },
        {
          model: dbSAPPS.DeliveryExpeditionVendor,
          as: "expedition_vendor",
          attributes: ["id", "name"],
          required: false,
        },
      ],
    });

    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: { [Op.in]: deliveryTrips.map((item) => item.id) },
      },
      doctype_id: 72,
      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 = deliveryTrips
      .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,
      deliveryTrips: data,
      message: "Delivery Trips loaded successfully!",
    };
  } catch (error) {
    console.error("Error retrieving Delivery Trips: ", error);
    const errorMessage =
      error.message ||
      "Failed to retrieve Delivery Trips. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

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

async function getLatestDeliveryNumber() {
  try {
    const deliveryTrips = await dbSAPPS.DeliveryTrip.findAll({
      order: [["modified", "DESC"]],
    });

    const currentYear = new Date().getFullYear().toString(); // Mendapatkan tahun saat ini
    let nextDeliveryNumber = "SAP/DT/" + currentYear + "/1"; // Default jika tidak ada delivery trip
    let nextDeliveryID = "DT" + currentYear + "1"; // Default jika tidak ada delivery trip

    if (deliveryTrips && deliveryTrips.length > 0) {
      // Mengambil semua nomor deliveryTrips
      const deliveryTripNumbers = deliveryTrips
        .map((deliverytrip) => {
          const splittedDT = deliverytrip.delivery_no.split("/");

          // Filter untuk tahun sekarang
          if (splittedDT[2] === currentYear) {
            // Mengambil bagian nomor urutan (diasumsikan ada di index ke-3)
            return parseInt(splittedDT[3], 10);
          }
          return null;
        })
        .filter((number) => number !== null);

      // Mencari nilai tertinggi dari nomor urutan
      const highestNoID =
        deliveryTripNumbers.length > 0 ? Math.max(...deliveryTripNumbers) : 0;
      const nextNoID = highestNoID + 1;
      nextDeliveryNumber = `SAP/DT/${currentYear}/${nextNoID.toString()}`;
      nextDeliveryID = `DT${currentYear}${nextNoID.toString()}`;
    }

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

async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // Validasi parameter yang diperlukan
    if (!params.company_id) {
      return {
        success: false,
        message: "Company is required!",
      };
    }

    // Generate NO UDK
    const toRoman = (month) => {
      const romanNumerals = [
        "I",
        "II",
        "III",
        "IV",
        "V",
        "VI",
        "VII",
        "VIII",
        "IX",
        "X",
        "XI",
        "XII",
      ];
      return romanNumerals[month - 1];
    };
    // Tentukan prefix berdasarkan company_id
    let prefix;
    if (params.company_id === 9) {
      prefix = "ASA";
    } else if (params.company_id === 8) {
      prefix = "SAP";
    } else {
      prefix = "ASA";
    }

    // Generate next Number
    const generateNextNumber = async () => {
      const now = new Date();
      const currentYear = now.getFullYear();
      const yearString = currentYear.toString(); // Ambil dua digit terakhir dari tahun

      // Find all UDKs in current month/year, regardless of prefix
      const existingDTs = await dbSAPPS.DeliveryTrip.findAll({
        where: {
          delivery_no: {
            [Op.like]: `SAP/DT/${yearString}/%`, // Menggunakan wildcard di depan untuk mencocokkan semua prefix
          },
        },
        transaction,
      });

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

      // Increment and format with leading zeros
      const nextNumber = (maxNumber + 1).toString();
      return `SAP/DT/${yearString}/${nextNumber}`;
    };

    // Generate the new number
    const newNumber = await generateNextNumber();
    const newParams = {
      ...params,
      delivery_no : newNumber,
    };

    // Cek apakah delivery trip sudah ada
    const existingDeliveryTrip = await dbSAPPS.DeliveryTrip.findOne({
      where: {
        delivery_no: params.delivery_no,
      },
    });

    if (existingDeliveryTrip) {
      return {
        success: false,
        message: `Delivery with "${params.delivery_no}" is already taken.`,
      };
    }

    // Set default value untuk enabled
    if (!params.enabled) params.enabled = true;

    // Buat Delivery Trip baru
    const newDeliveryTrip = await dbSAPPS.DeliveryTrip.create(newParams, {
      transaction,
    });

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

      // Validasi dan persiapkan data untuk bulkCreate
      const deliveryTripsChildData = params.deliverytripchilds.map(
        (deliveryTripChild) => {
          // Validasi field yang diperlukan
          if (
            !deliveryTripChild.delivery_note ||
            !deliveryTripChild.customer ||
            !deliveryTripChild.address
          ) {
            throw new Error("Missing required fields in delivery trip child.");
          }

          return {
            ...deliveryTripChild,
            creation: Date.now(),
            modified: Date.now(),
            modified_by: params.modified_by,
            owner: params.owner,
            deliverytrip_id: newDeliveryTrip.id,
          };
        }
      );

      // Lakukan bulkCreate
      try {
        await dbSAPPS.DeliveryTripChild.bulkCreate(deliveryTripsChildData, {
          transaction,
        });
      } catch (bulkCreateError) {
        await transaction.rollback();
        return {
          success: false,
          message:
            "Failed to create delivery trip children: " +
            bulkCreateError.message,
        };
      }
    }
    // Proses deliverytripcosts jika ada
    if (params.deliverytripcosts) {
      // Pastikan data adalah array
      if (typeof params.deliverytripcosts === "string") {
        params.deliverytripcosts = JSON.parse(params.deliverytripcosts);
      }

      // Validasi dan persiapkan data untuk bulkCreate
      const deliveryTripsCostData = params.deliverytripcosts.map(
        (deliveryTripCost) => {
          // Validasi field yang diperlukan
          if (!deliveryTripCost.item_type) {
            throw new Error("Missing required fields in delivery trip cost.");
          }

          return {
            ...deliveryTripCost,
            creation: Date.now(),
            modified: Date.now(),
            modified_by: params.modified_by,
            owner: params.owner,
            deliverytrip_id: newDeliveryTrip.id,
          };
        }
      );

      // Lakukan bulkCreate
      try {
        await dbSAPPS.DeliveryTripCost.bulkCreate(deliveryTripsCostData, {
          transaction,
        });
      } catch (bulkCreateError) {
        await transaction.rollback();
        return {
          success: false,
          message:
            "Failed to create delivery trip cost: " + bulkCreateError.message,
        };
      }
    }

    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: newDeliveryTrip.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: newDeliveryTrip.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 Delivery Trip has been successfully added!",
      deliveryTrips: newDeliveryTrip,
    };
  } catch (error) {
    // Rollback transaksi jika terjadi kesalahan
    await transaction.rollback();
    const errorMessage =
      error.message || "An error occurred during Delivery trip creation.";
    console.error("Error creating delivery trip:", error); // Log kesalahan untuk debugging
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function update(id, params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // Mencari Delivery Trip berdasarkan ID
    const deliveryTrip = await dbSAPPS.DeliveryTrip.findByPk(id);
    if (!deliveryTrip) {
      return {
        success: false,
        message: "Delivery trip not found!",
      };
    }

    if (params.vehicle_id && params.in_odometer != null && params.in_odometer !== 0) {
      const vehicle = await dbSAPPS.Vehicle.findByPk(params.vehicle_id);
      if (vehicle) {
        await vehicle.update({ odometer_value: params.in_odometer });
      }
    }

    // Validasi jika delivery_no diubah
    if (params.delivery_no && params.delivery_no !== deliveryTrip.delivery_no) {
      const existingDeliveryTripNo = await dbSAPPS.DeliveryTrip.findOne({
        where: {
          delivery_no: params.delivery_no,
        },
      });
      if (existingDeliveryTripNo) {
        return {
          success: false,
          message: `Delivery with "${params.delivery_no}" is already taken. Try again!`,
        };
      }
    }

    // Update Delivery Trip
    await deliveryTrip.update(params, { transaction });

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

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

      // Persiapkan data untuk bulkCreate
      const deliveryTripsChildData = params.deliverytripchilds.map(
        (deliveryTripChild) => {
          // Validasi field yang diperlukan
          if (
            !deliveryTripChild.delivery_note ||
            !deliveryTripChild.customer ||
            !deliveryTripChild.address
          ) {
            throw new Error("Missing required fields in delivery trip child.");
          }

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

      // Lakukan bulkCreate untuk child yang baru
      await dbSAPPS.DeliveryTripChild.bulkCreate(deliveryTripsChildData, {
        transaction,
      });
    }
    // Proses deliverytripcosts jika ada
    if (params.deliverytripcosts) {
      // Pastikan data adalah array
      if (typeof params.deliverytripcosts === "string") {
        params.deliverytripcosts = JSON.parse(params.deliverytripcosts);
      }

      // Hapus cost yang ada sebelum menambahkan yang baru
      await dbSAPPS.DeliveryTripCost.destroy({
        where: { deliverytrip_id: id },
        transaction,
      });

      // Persiapkan data untuk bulkCreate
      const deliveryTripsCostData = params.deliverytripcosts.map(
        (deliveryTripCost) => {
          // Validasi field yang diperlukan
          if (!deliveryTripCost.item_type) {
            throw new Error("Missing required fields in delivery trip cost.");
          }

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

      // Lakukan bulkCreate untuk cost yang baru
      await dbSAPPS.DeliveryTripCost.bulkCreate(deliveryTripsCostData, {
        transaction,
      });
    }


    await transaction.commit();
    return {
      success: true,
      message: "Your data delivery trip has been saved!",
    };
  } catch (error) {
    // Rollback transaksi jika terjadi kesalahan
    await transaction.rollback();
    const errorMessage =
      error.message || "Failed to update delivery trip data.";
    console.error("Error updating delivery trip:", error); // Log kesalahan untuk debugging
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function enabled(id, params) {
  try {
    const deliverytrip = await dbSAPPS.DeliveryTrip.findByPk(id);
    if (!deliverytrip)
      return {
        success: false,
        message: "Delivery Trip 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 deliverytrip.update(updateData);

    return {
      success: true,
      message: "Delivery Trip has been updated!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to Update Delivery Trip.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function _delete(id) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const deliveryTrip = await dbSAPPS.DeliveryTrip.findByPk(id);
    if (!deliveryTrip)
      return { success: false, message: "Delivery Trip not found!" };
    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.eq]: deliveryTrip.id,
        },
        doctype_id: 72,
      },
      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 deliveryTrip.destroy();
    await transaction.commit();

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