const dbSAPPS = require("../../helpers/db");
require("dotenv").config();
const { Op, where } = require("sequelize");
const moment = require("moment");
const nodemailer = require("nodemailer");
const emailToGA = require("./emailToGA");
let puppeteer = null;
try {
  puppeteer = require("puppeteer");
} catch (e) {
  console.warn("Puppeteer module not found. PDF generation features will be disabled.");
}

const fs = require("fs");
const path = require("path");

module.exports = {
  generateDocumentNo,
  create,
  getAll,
  filter,
  option,
  count,
  update,
  enabled,
  generatePdfFromTemplate,
  requestGA,
  updateFile,
  delete: _delete,
};

/**
 * Format an array of upCountries by appending a modifier field to each company.
 * The modifier field is the full name of the user who last modified the company.
 * @param {Object[]} upCountries - The array of upCountries to format.
 * @returns {Promise<Object[]>} - The formatted array of upCountries.
 */
const formatWithModifier = async (upCountries) => {
  return Promise.all(
    upCountries.map(async (upCountry) => {
      const modifier = await dbSAPPS.User.findOne({
        where: {
          id: upCountry.modified_by,
        },
        attributes: ["full_name"],
      });

      return {
        ...upCountry,
        modifier: modifier?.full_name,
      };
    }),
  );
};

/**
 * Get all upCountries with their respective companies.
 * @param {Object} params - An object that contains parameters to filter the upCountries.
 * @param {Array} params.companiesId - Array of company IDs to filter by.
 * @returns {Promise<Object>} Resolves with an object that contains the upCountries and a success flag.
 * @example
 * const result = await getAll({ companiesId: [1, 2] });
 * console.log(result);
 */
async function getAll(params) {
  try {
    console.log("PARMA UC ALL : ", params);
    const filterConditions = {};

    // === FILTER UTAMA ===
    if (params.doc_id) {
      filterConditions.id = {
        [Op.eq]: parseInt(params.doc_id),
      };
    } else {
      if (
        params.access_read_all_data === true ||
        params.access_read_all_data === "true" ||
        params.administrator_of_msd === true ||
        params.administrator_of_msd === "true"
      ) {
        filterConditions.territory_id = {
          [Op.not]: null,
        };
      } else {
        if (params.territoryIds && params.territoryIds.length > 0) {
          filterConditions.territory_id = {
            [Op.in]: params.territoryIds.map((item) => parseInt(item)),
          };
        } else {
          if (params.territory_id) {
            filterConditions.territory_id = {
              [Op.eq]: parseInt(params.territory_id),
            };
          }
        }
      }
    }

    // === AMBIL DATA UTAMA UpCountry ===
    let upCountries = await dbSAPPS.UpCountry.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        { model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name"] },
        { model: dbSAPPS.Territory, as: "territory", attributes: ["id", "display_name"] },
        { model: dbSAPPS.Designation, as: "designation", attributes: ["id", "display_name"] },
        { model: dbSAPPS.Employee, as: "employee", attributes: ["id", "employee_name"] },
      ],
    });

    // === LOOP PER UpCountry ===
    upCountries = await Promise.all(
      upCountries.map(async (upCountry) => {
        // workflow
        const workflow = await dbSAPPS.Workflow.findOne({
          where: { doc_id: upCountry.id, doctype_id: 194 },
          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"],
                        },
                      ],
                    },
                  ],
                },
              ],
            },
          ],
        });

        // visit lists
        const visitlistData = await dbSAPPS.UpCountryVisitList.findAll({
          where: { upcountry_id: upCountry.id },
          order: [["id", "ASC"]],
          include: [
            {
              model: dbSAPPS.Client,
              as: "client",
              attributes: ["id", "display_name", "code", "priority", "specialist_id"],
              include: [{ model: dbSAPPS.Specialist, as: "specialist", attributes: ["id", "display_name"] }],
            },
            { model: dbSAPPS.Outlet, as: "outlet", attributes: ["id", "display_name", "code"] },
          ],
        });

        // itinerary / agenda
        const upcountryitinerary = await dbSAPPS.UpCountryItinerary.findAll({
          where: { upcountry_id: upCountry.id },
        });

        // distances
        const estimation_distances = await dbSAPPS.UpCountryDistance.findAll({
          where: { upcountry_id: upCountry.id },
        });

        // accommodation_requests
        const accommodation_requests = await dbSAPPS.UpCountryAccommodation.findAll({
          where: { upcountry_id: upCountry.id },
        });

        const visitlists = visitlistData?.map((visitlist) => {
          return {
            ...visitlist.dataValues,
            items: JSON.parse(visitlist.items),
          };
        });

        return {
          ...upCountry.dataValues,
          company_name: upCountry?.company?.display_name,
          territory_name: upCountry?.territory?.display_name,
          employee_name: upCountry?.employee?.employee_name,
          designation_name: upCountry?.designation?.display_name,
          workflow: workflow ? workflow.dataValues : null,
          visitlists: visitlists,
          estimation_distances: estimation_distances.map((distance) => distance.dataValues),
          itineraries: upcountryitinerary.map((itinerary) => itinerary.dataValues),
          accommodation_requests: accommodation_requests.map((request) => request.dataValues),
        };
      }),
    );

    upCountries = await formatWithModifier(upCountries);

    return {
      success: true,
      message: "Loaded successfully!",
      upCountries,
    };
  } catch (error) {
    console.error("getAll Error:", error);
    return {
      success: false,
      message: error?.message || "Failed to load, please try again!",
    };
  }
}

async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    console.log("PARAMS UC : ", params);
    // validate existing
    const existingDocNo = await dbSAPPS.UpCountry.findOne({
      where: { doc_no: params.doc_no, company_id: params.company_id, territory_id: params.territory_id },
      transaction,
    });
    if (existingDocNo) {
      return {
        success: false,
        message: `"${params.doc_no}" is already taken.`,
      };
    }

    // Validasi required fields untuk UpCountry
    if (!params.modified_by || !params.owner) {
      throw new Error("modified_by dan owner harus diisi");
    }

    const existingVisitOrder = await dbSAPPS.UpCountry.findOne({
      where: { visit_order: params.visit_order, company_id: params.company_id, territory_id: params.territory_id },
      transaction,
    });
    if (existingVisitOrder) {
      return {
        success: false,
        message: `Kunjungan ke- ${params.visit_order} sudah ada. Silakan gunakan nomor urut kunjungan yang lain.`,
      };
    }

    // 1. save UC
    const upCountry = await dbSAPPS.UpCountry.create(
      {
        ...params,
        creation: new Date(), // ✅ Date object
        modified: new Date(), // ✅ Date object
        modified_by: params.modified_by,
        owner: params.owner,
      },
      { transaction },
    );

    // 2. save estimation distances data
    if (params?.estimation_distances && params.estimation_distances.length > 0) {
      if (typeof params.estimation_distances === "string") {
        params.estimation_distances = JSON.parse(params.estimation_distances);
      }

      // Validasi dan persiapkan data untuk bulkCreate
      const estimationData = params.estimation_distances.map((ed) => {
        // Validasi field yang diperlukan
        if (!ed.address_name) {
          throw new Error("Missing required fields in estimation distances.");
        }

        return {
          creation: new Date(), // ✅ Date object
          modified: new Date(), // ✅ Date object
          modified_by: params.modified_by,
          owner: params.owner,
          upcountry_id: upCountry.id,
          enabled: true,
          ...ed,
        };
      });

      // Lakukan bulkCreate untuk estimate distances
      await dbSAPPS.UpCountryDistance.bulkCreate(estimationData, {
        transaction,
        validate: true,
      });
    }

    // 3. save visit lists
    if (params?.visitlists && params.visitlists.length > 0) {
      if (typeof params.visitlists === "string") {
        params.visitlists = JSON.parse(params.visitlists);
      }

      const visitlistData = params.visitlists.map((vl) => {
        // Validasi required fields
        if (!vl.client_id || !vl.client_name) {
          throw new Error("Missing required fields in visit lists.");
        }

        return {
          ...vl,
          creation: new Date(), // ✅ Date object
          modified: new Date(), // ✅ Date object
          modified_by: params.modified_by,
          owner: params.owner,
          upcountry_id: upCountry.id,
          enabled: true,
        };
      });

      // Lakukan bulkCreate untuk visit lists
      await dbSAPPS.UpCountryVisitList.bulkCreate(visitlistData, {
        transaction,
        validate: true,
      });
    }

    // 4. save itinerary/agenda data
    if (params?.itineraries && params.itineraries.length > 0) {
      if (typeof params.itineraries === "string") {
        params.itineraries = JSON.parse(params.itineraries);
      }

      const itinerariesData = params.itineraries.map((it) => {
        // Validasi field yang diperlukan
        if (!it.activity || !it.date || !it.time) {
          throw new Error("Missing required fields in itineraries.");
        }

        // Format date untuk DATEONLY
        let formattedDate = it.date;
        if (it.date && !it.date.match(/^\d{4}-\d{2}-\d{2}$/)) {
          const dateObj = new Date(it.date);
          formattedDate = dateObj.toISOString().split("T")[0];
        }

        // Format time untuk TIME
        let formattedTime = it.time;
        if (it.time && !it.time.match(/^\d{2}:\d{2}:\d{2}$/)) {
          const timeObj = new Date(`1970-01-01T${it.time}`);
          formattedTime = timeObj.toISOString().split("T")[1].split(".")[0];
        }

        return {
          ...it,
          date: formattedDate,
          time: formattedTime,
          creation: new Date(), // ✅ Date object
          modified: new Date(), // ✅ Date object
          modified_by: params.modified_by,
          owner: params.owner,
          upcountry_id: upCountry.id,
          enabled: true,
        };
      });

      // Lakukan bulkCreate untuk Itineraries/agenda
      await dbSAPPS.UpCountryItinerary.bulkCreate(itinerariesData, {
        transaction,
        validate: true,
      });
    }

    // 5.  save accommodation request data
    if (params?.accommodation_requests && params.accommodation_requests.length > 0) {
      if (typeof params.accommodation_requests === "string") {
        params.accommodation_requests = JSON.parse(params.accommodation_requests);
      }

      const accommodationRequestsData = params.accommodation_requests.map((ar) => {
        if (!ar.who_requested || !ar.accommodation_type || !ar.purpose) {
          throw new Error("Missing required fields in accommodation requests.");
        }

        return {
          ...ar,
          // from_date: ar.from_date,
          // to_date: ar.to_date,
          // creation: new Date(),
          // modified: new Date(),
          modified_by: params.modified_by,
          owner: params.owner,
          upcountry_id: upCountry.id,
          enabled: true,
        };
      });

      // Lakukan bulkCreate untuk Accommodation Requests
      await dbSAPPS.UpCountryAccommodation.bulkCreate(accommodationRequestsData, {
        transaction,
        validate: true,
      });
    }

    // save workflow
    if (Array.isArray(params?.workflowTerritories) && params?.workflowTerritories.length > 0) {
      const workflowSettings = await dbSAPPS.WorkflowSetting.findAll(
        {
          where: {
            doctype_id: params.doctype_id,
            type: "Territory",
            enabled: true,
          },
        },
        { transaction },
      );

      const limitWorkflow = workflowSettings[0]?.limit || 0;
      const workflowTerritories = params?.workflowTerritories.slice(0, limitWorkflow);

      if (!workflowTerritories || workflowTerritories.length === 0) {
        await transaction.rollback();
        return {
          success: false,
          message: `Workflow Territories is required. Transaction rolled back.`,
        };
      }

      const workflowData = {
        creation: new Date(), // ✅ Date object
        modified: new Date(), // ✅ Date object
        modified_by: params.modified_by || 1,
        owner: params.owner || 1,
        doctype_id: params.doctype_id,
        doc_id: upCountry.id,
        status: "Draft",
      };

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

      if (workflow) {
        const workflowApproversData = workflowTerritories.map((workflowTerritory) => {
          if (!workflowTerritory.territory_id) {
            throw new Error("Missing territory_id in workflow territories.");
          }

          return {
            creation: new Date(), // ✅ Date object
            modified: new Date(), // ✅ Date object
            modified_by: params.modified_by || 1,
            owner: params.owner || 1,
            workflow_id: workflow.id,
            territory_id: workflowTerritory.territory_id,
            status: null,
          };
        });

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

        if (approvers?.length !== workflowTerritories.length) {
          await transaction.rollback();
          return {
            success: false,
            message: "Failed to create workflow approvers. Please try again later.",
          };
        }
      }
    }

    await transaction.commit();
    return {
      success: true,
      message: "Saved successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    console.error("Create Error Details:", {
      message: error.message,
      stack: error.stack,
      params: params ? Object.keys(params) : "No params",
    });

    const errorMessage = error?.message || "Failed to save, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function update(id, params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // 1️⃣ Cek apakah data UpCountry ada
    const upCountry = await dbSAPPS.UpCountry.findByPk(id, { transaction });
    if (!upCountry) {
      return {
        success: false,
        message: "Data not found.",
      };
    }

    // const existingVisitOrder = await dbSAPPS.UpCountry.findOne({
    //   where: { visit_order: params.visit_order, company_id: params.company_id, territory_id: params.territory_id },
    //   transaction,
    // });
    // if (existingVisitOrder) {
    //   return {
    //     success: false,
    //     message: `Kunjungan ke- ${params.visit_order} sudah ada. Silakan gunakan nomor urut kunjungan yang lain.`,
    //   };
    // }

    if (params.filename && upCountry.filename && params.filename !== upCountry.filename) {
      const oldFilePath = path.resolve(__dirname, "../../../../public/data/images/upcountries/", upCountry.filename);
      try {
        await fs.unlink(oldFilePath);
        console.warn("Deleted old file:", oldFilePath);
      } catch (err) {
        console.warn("Gagal hapus file lama:", err.message);
      }
    }

    // 2️⃣ Validasi field penting
    if (!params.modified_by || !params.owner) {
      throw new Error("modified_by dan owner harus diisi");
    }

    // 3️⃣ Update data utama UpCountry
    await upCountry.update(
      {
        ...params,
        modified: new Date(), // selalu update waktu terakhir
      },
      { transaction },
    );

    // 4️⃣ Handle estimation_distances (hapus lama, simpan baru)
    if (params?.estimation_distances) {
      if (typeof params.estimation_distances === "string") {
        params.estimation_distances = JSON.parse(params.estimation_distances);
      }

      // hapus semua estimation lama
      await dbSAPPS.UpCountryDistance.destroy({
        where: { upcountry_id: id },
        transaction,
      });

      // tambahkan data baru
      if (params.estimation_distances.length > 0) {
        const estimationData = params.estimation_distances.map((ed) => ({
          ...ed,
          creation: new Date(),
          modified: new Date(),
          modified_by: params.modified_by,
          owner: params.owner,
          upcountry_id: id,
          enabled: true,
        }));

        await dbSAPPS.UpCountryDistance.bulkCreate(estimationData, {
          transaction,
          validate: true,
        });
      }
    }

    // 5️⃣ Handle visit lists
    if (params?.visitlists) {
      if (typeof params.visitlists === "string") {
        params.visitlists = JSON.parse(params.visitlists);
      }

      await dbSAPPS.UpCountryVisitList.destroy({
        where: { upcountry_id: id },
        transaction,
      });

      if (params.visitlists.length > 0) {
        const visitlistData = params.visitlists.map((vl) => ({
          ...vl,
          creation: new Date(),
          modified: new Date(),
          modified_by: params.modified_by,
          owner: params.owner,
          upcountry_id: id,
          enabled: true,
        }));

        await dbSAPPS.UpCountryVisitList.bulkCreate(visitlistData, {
          transaction,
          validate: true,
        });
      }
    }

    // 6️⃣ Handle itineraries
    if (params?.itineraries) {
      if (typeof params.itineraries === "string") {
        params.itineraries = JSON.parse(params.itineraries);
      }

      await dbSAPPS.UpCountryItinerary.destroy({
        where: { upcountry_id: id },
        transaction,
      });

      if (params.itineraries.length > 0) {
        const itinerariesData = params.itineraries.map((it) => {
          let formattedDate = it.date;
          if (it.date && !it.date.match(/^\d{4}-\d{2}-\d{2}$/)) {
            const dateObj = new Date(it.date);
            formattedDate = dateObj.toISOString().split("T")[0];
          }

          let formattedTime = it.time;
          if (it.time && !it.time.match(/^\d{2}:\d{2}:\d{2}$/)) {
            const timeObj = new Date(`1970-01-01T${it.time}`);
            formattedTime = timeObj.toISOString().split("T")[1].split(".")[0];
          }

          return {
            ...it,
            date: formattedDate,
            time: formattedTime,
            creation: new Date(),
            modified: new Date(),
            modified_by: params.modified_by,
            owner: params.owner,
            upcountry_id: id,
            enabled: true,
          };
        });

        await dbSAPPS.UpCountryItinerary.bulkCreate(itinerariesData, {
          transaction,
          validate: true,
        });
      }
    }

    // 7️⃣ Handle accommodation requests
    if (params?.accommodation_requests) {
      if (typeof params.accommodation_requests === "string") {
        params.accommodation_requests = JSON.parse(params.accommodation_requests);
      }

      await dbSAPPS.UpCountryAccommodation.destroy({
        where: { upcountry_id: id },
        transaction,
      });

      if (params.accommodation_requests.length > 0) {
        const accommodationRequestsData = params.accommodation_requests.map((ar) => ({
          ...ar,
          creation: new Date(),
          modified: new Date(),
          modified_by: params.modified_by,
          owner: params.owner,
          upcountry_id: id,
          enabled: true,
        }));

        await dbSAPPS.UpCountryAccommodation.bulkCreate(accommodationRequestsData, {
          transaction,
          validate: true,
        });
      }
    }

    // 8️⃣ Workflow update (hapus & insert ulang)
    if (Array.isArray(params.workflowTerritories) && params.workflowTerritories.length > 0) {
      // Hapus workflow & approver lama
      const existingWorkflow = await dbSAPPS.Workflow.findOne({
        where: { doc_id: id, doctype_id: params.doctype_id },
        transaction,
      });

      if (existingWorkflow) {
        await dbSAPPS.WorkflowApprover.destroy({
          where: { workflow_id: existingWorkflow.id },
          transaction,
        });
        await dbSAPPS.Workflow.destroy({
          where: { id: existingWorkflow.id },
          transaction,
        });
      }

      const workflowSettings = await dbSAPPS.WorkflowSetting.findAll(
        {
          where: {
            doctype_id: params.doctype_id,
            type: "Territory",
            enabled: true,
          },
        },
        { transaction },
      );

      const limitWorkflow = workflowSettings[0]?.limit || 0;
      const workflowTerritories = params.workflowTerritories.slice(0, limitWorkflow);

      if (!workflowTerritories || workflowTerritories.length === 0) {
        await transaction.rollback();
        return {
          success: false,
          message: `Workflow Territories is required. Transaction rolled back.`,
        };
      }

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

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

      if (workflow) {
        const workflowApproversData = workflowTerritories.map((workflowTerritory) => ({
          creation: new Date(),
          modified: new Date(),
          modified_by: params.modified_by || 1,
          owner: params.owner || 1,
          workflow_id: workflow.id,
          territory_id: workflowTerritory.territory_id,
          status: null,
        }));

        await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, {
          transaction,
          validate: true,
        });
      }
    }

    await transaction.commit();
    return {
      success: true,
      message: "Updated successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    console.error("Update Error Details:", {
      message: error.message,
      stack: error.stack,
      params: params ? Object.keys(params) : "No params",
    });

    return {
      success: false,
      message: error?.message || "Failed to update, please try again!",
    };
  }
}

/**
 * Get upCountries with their respective settings.
 * @param {Object} params - An object that contains parameters to filter the upCountries.
 * @param {Array} params.companiesId - Array of company IDs to filter by.
 * @returns {Promise<Object>} Resolves with an object that contains the upCountries and a success flag.
 * @example
 * const result = await option({ companiesId: [1, 2] });
 * console.log(result);
 */
async function option(params) {
  try {
    // filter
    const filterConditions = {};

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

    // must be enabled
    filterConditions.enabled = {
      [Op.eq]: true,
    };

    let options = [];
    options = await dbSAPPS.UpCountry.findAll({
      where: filterConditions,
      attributes: ["id", "name", "display_name"],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "name", "display_name"],
        },
      ],
      order: [["name", "ASC"]],
    });

    options = options.map((option) => {
      return {
        ...option.dataValues,
        company_name: option?.company?.display_name,
      };
    });

    return {
      options,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Count active upCountries
 *
 * @returns {Promise<{
 *   count: number,
 *   success: boolean,
 *   message: string
 * }>}
 */
async function count(params) {
  try {
    // filter
    const filterConditions = {};

    // must be enabled
    filterConditions.enabled = {
      [Op.eq]: true,
    };

    const count = await dbSAPPS.UpCountry.count({
      where: filterConditions,
    });

    return {
      count,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Enable or disable a upCountry
 * @param {number} id - the id of the upCountry
 * @param {object} params - the update data
 * @param {boolean} params.enabled - the enabled status of the upCountry
 * @param {number} [params.modified_by] - the modified by of the upCountry
 * @param {Date} [params.modified] - the modified of the upCountry
 * @returns {Promise<object>} - the result of the update
 */
async function enabled(id, params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // validate existing
    const upCountry = await dbSAPPS.UpCountry.findByPk(id);
    if (!upCountry) return { success: false, message: "ID not found!" };

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

    // save
    await upCountry.update(updateData, { transaction });

    await transaction.commit();
    return {
      success: true,
      message: "Saved successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed to save, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Delete a upCountry
 * @param {number} id - the id of the upCountry
 * @returns {Promise<object>} - the result of the delete
 */
async function _delete(id) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // validate existing
    const upCountry = await dbSAPPS.UpCountry.findByPk(id, { transaction });
    if (!upCountry) return { success: false, message: "UC not found!" };

    // delete upcountryaccommodation file
    const accommodations = await dbSAPPS.UpCountryAccommodation.findAll({
      where: { upcountry_id: upCountry.id },
    });

    if (!accommodations || accommodations.length === 0) {
      console.warn("⚠️ Tidak ada file terkait dengan UC:", id);
    }

    // Hapus file di disk (jika ada)
    for (const file of accommodations) {
      if (file.files && fs.existsSync(file.files)) {
        try {
          fs.unlinkSync(path.resolve(file.files));
          console.log("🗑️ File dihapus:", file.files);
        } catch (err) {
          console.error("⚠️ Gagal hapus file:", file.files, err.message);
        }
      }
    }

    // delete filename
    if (upCountry.filename) {
      const filePath = path.resolve(__dirname, "../../../../public/data/images/upcountries/", upCountry.filename);
      try {
        await fs.unlink(filePath);
        console.warn("Deleted file:", filePath);
      } catch (err) {
        console.warn("Gagal hapus file:", err.message);
      }
    }

    // delete workflows
    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.eq]: upCountry.id,
        },
        doctype_id: {
          [Op.eq]: 194,
        },
      },
      attributes: ["id", "doc_id", "doctype_id", "status"],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: ["id", "workflow_id", "designation_id", "territory_id", "status"],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
            },
          ],
        },
      ],
    });
    if (!workflows) return { success: false, message: "Workflows for upCountry 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));

    // delete
    await upCountry.destroy({ transaction });

    await transaction.commit();
    return {
      success: true,
      message: "Deleted successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed to delete, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function generateDocumentNo(params) {
  try {
    const param = params.params;
    // GR/ACEH/06/2025/0001
    const lastDocumentNo = await dbSAPPS.UpCountry.findOne({
      where: {
        doc_no: {
          [Op.like]: `%${param.code}-${param.year}${param.month}%`,
        },
      },
      order: [["doc_no", "DESC"]],
    });

    let documentNo;
    if (lastDocumentNo) {
      const lastNo = parseInt(lastDocumentNo.doc_no.split("-").pop(), 10);
      documentNo = `${param.code}-${param.year}${param.month}-${String(lastNo + 1).padStart(4, "0")}`;
    } else {
      documentNo = `${param.code}-${param.year}${param.month}-0001`;
    }

    return {
      success: true,
      documentNo: documentNo,
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to generate document no.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function generatePdfFromTemplate(data) {
  try {
    if (!puppeteer) {
      throw new Error("Puppeteer is not installed. Cannot generate PDF.");
    }
    const browser = await puppeteer.launch({
      headless: "new",
      args: ["--no-sandbox", "--disable-setuid-sandbox"],
    });

    const page = await browser.newPage();

    // FETCH TRAVEL COST
    const designationId = data ? data?.designation_id : null;
    const rate = await dbSAPPS.TravelCostRate.findOne({
      where: {
        designation_id: designationId,
      },
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "designation",
          attributes: ["id", "display_name"],
        },
      ],
    });

    const travelCostRates = rate
      ? {
          ...rate.dataValues,
          company_name: rate?.company?.display_name || null,
          designation_name: rate?.designation?.display_name || null,
        }
      : null;

    // ====== DATE PROCESSING ======
    const dates = data?.itineraries?.map((item) => new Date(item.date)) || [];
    dates.sort((a, b) => a - b);
    const dayNumbers = dates.map((d) => d.getDate().toString().padStart(2, "0"));
    const monthNames = [
      "Januari",
      "Februari",
      "Maret",
      "April",
      "Mei",
      "Juni",
      "Juli",
      "Agustus",
      "September",
      "Oktober",
      "November",
      "Desember",
    ];
    const lastDate = dates[dates.length - 1];
    const monthName = lastDate ? monthNames[lastDate.getMonth()] : "";
    const yearShort = lastDate ? lastDate.getFullYear().toString().slice(-2) : "";
    const formattedDays =
      dayNumbers.length > 1 ? dayNumbers.slice(0, -1).join(", ") + " dan " + dayNumbers.slice(-1) : dayNumbers[0] || "";
    const tanggal_pelaksanaan = `${formattedDays} ${monthName} ${yearShort}`;
    const lama_bekerja = dates.length || "-";

    // ====== RUTE PERJALANAN ======
    const rute = data?.estimation_distances || [];
    const rutePerjalanan = rute?.map((item) => item.address_name.split(", ")[0]);
    const rute_perjalanan =
      rutePerjalanan.length > 1
        ? rutePerjalanan.slice(0, -1).join(", ") + ", dan " + rutePerjalanan.slice(-1)
        : rutePerjalanan[0] || "";

    // ====== BANGUN DATA TABEL ======
    const tableRows = [];
    if (data?.visitlists?.length > 0) {
      data?.visitlists?.forEach((item) => {
        const productNames = item?.items?.map((it) => it.display_name).join(", ");
        tableRows.push({
          customerId: item?.client?.code || "-",
          customer: item?.client_name,
          specialist: item?.client?.specialist?.display_name || "-",
          priority: item?.client?.priority || "-",
          outlet: item?.outlet_name,
          products: productNames || "-",
        });
      });
    }

    const makan =
      data?.is_event === true
        ? parseInt(travelCostRates?.daily_meal_rate / 2)
        : parseInt(travelCostRates?.daily_meal_rate);
    const uang_saku = parseInt(travelCostRates?.daily_operational_rate);
    const penginapan = parseInt(travelCostRates?.accomodation_rate);
    const totalCost = makan + uang_saku + penginapan;

    const html = `
    <!DOCTYPE html>
<html lang="id">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Surat Tugas Up Country</title>
    <style>
      * {
        margin: 0;
        padding: 0;
        box-sizing: border-box;
      }

      body {
        font-family: Arial, sans-serif;
        background-color: white;
        padding: 10mm; /* Margin 1 cm */
      }

      .container {
        width: 100%;
      }

      .header {
        display: flex;
        align-items: flex-start;
        gap: 20px;
        margin-bottom: 25px;
        padding-bottom: 12px;
        border-bottom: 3px solid #333;
      }

      .logo {
        width: 80px;
        height: 80px;
        background-color: #ddd;
        flex-shrink: 0;
      }

      .company-info h1 {
        color: #008b8b;
        font-size: 18px;
        margin-bottom: 5px;
      }

      .company-info p {
        font-size: 10px;
        line-height: 1.4;
      }

      .document-title {
        text-align: center;
        margin: 25px 0;
      }

      .document-title h2 {
        font-size: 16px;
        letter-spacing: 2px;
        margin-bottom: 3px;
      }

      .document-title p {
        font-size: 12px;
      }

      .info-grid {
        display: grid;
        grid-template-columns: 1fr 1fr;
        gap: 40px;
        margin-bottom: 25px;
      }

      .info-row {
        display: flex;
        font-size: 12px;
        margin-bottom: 6px;
      }

      .info-label {
        min-width: 150px;
        flex-shrink: 0;
      }

      table {
        width: 100%;
        border-collapse: collapse;
        font-size: 10px;
        margin-bottom: 25px;
      }

      table th,
      table td {
        border: 1px solid #000;
        padding: 5px;
      }

      table th {
        background-color: white;
        font-weight: bold;
        text-align: center;
      }

      .text-center {
        text-align: center;
      }

      .text-red {
        color: #dc2626;
      }

      .date-line {
        font-size: 12px;
        margin: 35px 0;
      }

      /* Signature */
      .signature-section {
        display: grid;
        grid-template-columns: repeat(4, 1fr);
        gap: 20px;
      }

      .signature-box {
        text-align: center;
      }

      .signature-box p {
        font-size: 11px;
      }

      .signature-box .title {
        margin-bottom: 60px;
      }

      .signature-box .name {
        font-weight: bold;
      }

      /* khusus Diperiksa (PSN + Finance) */
      .sub-sign {
        display: flex;
        justify-content: center;
        gap: 40px;
        margin-top: 40px;
      }

      .sub-sign div {
        text-align: center;
      }

      @media print {
        body {
          background-color: white;
          padding: 10mm;
        }
      }
    </style>
  </head>
  <body>
    <div class="container">
      <!-- HEADER -->
      <div class="header">
        <div class="logo"></div>
        <div class="company-info">
          <h1>PT. SATYA ABADI PHARMA</h1>
          <p>RUKO BINTARO SEKTOR IX Blok H4, H6, F1, KEL. PONDOK PUCUNG, KEC. PONDOK AREN</p>
          <p>TANGERANG SELATAN – INDONESIA</p>
          <p>Telp. +6221 7451965</p>
        </div>
      </div>

      <!-- TITLE -->
      <div class="document-title">
        <h2>SURAT TUGAS UP COUNTRY</h2>
        <p>${data.doc_no || "-"}</p>
      </div>

      <!-- EMPLOYEE INFO -->
      <div class="info-grid">
        <div>
          <div class="info-row"><span class="info-label">Nama :</span> <span>${data.employee_name || "-"}</span></div>
          <div class="info-row"><span class="info-label">Jabatan :</span> <span>${data.designation_name || "-"}</span></div>
          <div class="info-row"><span class="info-label">Territory :</span> <span>${data.territory_name || "-"}</span></div>
          <div class="info-row"><span class="info-label">Team :</span> <span>${
            data?.workflow?.workflowapprovers
              ? data?.workflow?.workflowapprovers[data?.workflow?.workflowapprovers.length - 1]?.territory?.display_name
              : "-"
          }</span></div>
          <div class="info-row">
            <span class="info-label">Usulan Pelaksanaan :</span> <span>${data?.from_date} - ${data?.to_date}</span>
          </div>
        </div>
        <div>
          <div class="info-row">
            <span class="info-label">Tanggal Pelaksanaan UC :</span> <span>${tanggal_pelaksanaan}</span>
          </div>
          <div class="info-row"><span class="info-label">Lama Bekerja :</span> <span>${lama_bekerja}</span></div>
          <div class="info-row">
            <span class="info-label">Route Perjalanan :</span> <span>${rute_perjalanan}</span>
          </div>
        </div>
      </div>

      <!-- TABLE -->
      <table>
        <thead>
          <tr>
            <th>No.</th>
            <th>Customer ID</th>
            <th>Customer</th>
            <th>Specialist</th>
            <th>Outlet</th>
            <th>Class</th>
            <th>Product</th>
            <th>Transport (Rp)</th>
            <th>Meals</th>
            <th>Uang Saku (Rp)</th>
            <th>Penginapan (Rp)</th>
            <th>Total Biaya (Rp)</th>
          </tr>
        </thead>
        <tbody>
        ${tableRows
          .map((row, i) => {
            return `
           <tr key={i}>
              <td class="text-center">${i + 1}</td>
              <td class="text-center">${row.customerId}</td>
              <td class="text-center">${row.customer}</td>
              <td class="text-center">${row.specialist}</td>
              <td class="text-center">${row.outlet}</td>
              <td class="text-center">${row.priority}</td>
              <td class="text-center">${row.products}</td>
               <td colspan="5"></td>
            </tr>
            
          `;
          })
          .join("")}
          <tr>
              <td colspan="7">
                <strong>Total Bayar</strong>
              </td>
              <td class="text-center">-</td>
              <td class="text-center">${makan?.toLocaleString("id-ID") || "-"}</td>
              <td class="text-center">${uang_saku?.toLocaleString("id-ID") || "-"}</td>
              <td class="text-center">${penginapan?.toLocaleString("id-ID") || "-"}</td>
              <td class="text-center">${totalCost?.toLocaleString("id-ID") || "-"}</td>
            </tr>
        </tbody>
      </table>

      <div class="date-line">………… ${new Date().toLocaleDateString("id-ID", {
        day: "2-digit",
        month: "long",
        year: "numeric",
      })}</div>

      <!-- SIGNATURE -->
      <div class="signature-section">
        <div class="signature-box">
          <p class="title">Mengajukan,</p>
          <p class="name">${data?.employee_name}</p>
          <p class="position">${data?.designation_name || "MR / SPV"}</p>
        </div>

        <div class="signature-box">
          <p class="title">Menyetujui,</p>
          <p class="name">(${
            data?.workflow?.workflowapprovers[0]?.status === "Approved"
              ? `Approved by ${data?.workflow?.workflowapprovers[0]?.territory?.employees[0]?.employee_name}`
              : "Waiting for approval"
          })</p>
          <p class="position">${data?.workflow?.workflowapprovers[0]?.territory?.display_name || "-"}</p>
        </div>

        <div class="signature-box">
          <p class="title">Mengetahui,</p>
          <p class="name">(${
            data?.workflow?.workflowapprovers[1]?.status === "Approved"
              ? `Approved by ${data?.workflow?.workflowapprovers[1]?.territory?.employees[0]?.employee_name}`
              : "Waiting for approval"
          })</p>
          <p class="position">${data?.workflow?.workflowapprovers[1]?.territory?.display_name || "-"}</p>
        </div>

        <div class="signature-box">
          <p class="title">Diperiksa,</p>

          <!-- SUB SIGN -->
          <div class="sub-sign">
            <div>
              <p>(………………………………)</p>
              <p class="position">PSN Department</p>
            </div>

            <div>
              <p>(………………………………)</p>
              <p class="position">Finance/Accounting</p>
            </div>
          </div>
        </div>
      </div>
    </div>
  </body>
</html>
    `;

    await page.setContent(html, {
      waitUntil: "networkidle0",
    });

    const pdfBuffer = await page.pdf({
      format: "A4",
      printBackground: true,
      landscape: true,
      margin: { top: "10mm", right: "10mm", bottom: "10mm", left: "10mm" }, // ✅
    });

    await browser.close();
    return pdfBuffer;
  } catch (err) {
    console.error("Error generate PDF:", err.message);
    throw new Error("Failed to generate PDF");
  }
}

async function requestGA(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  let didCommit = false;
  try {
    // ✅ Ambil detail UpCountry + relasinya
    let upCountry = await dbSAPPS.UpCountry.findOne({
      where: { id: params.id },
      include: [
        { model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name"] },
        { model: dbSAPPS.Territory, as: "territory", attributes: ["id", "display_name"] },
        { model: dbSAPPS.Designation, as: "designation", attributes: ["id", "display_name"] },
        { model: dbSAPPS.Employee, as: "employee", attributes: ["id", "employee_name"] },
      ],
    });

    if (!upCountry) {
      throw new Error("UpCountry document not found");
    }

    // ✅ Query workflow
    const workflow = await dbSAPPS.Workflow.findOne({
      where: { doc_id: upCountry.id, doctype_id: 194 },
      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"],
                    },
                  ],
                },
              ],
            },
          ],
        },
      ],
    });

    // ✅ Query visit list
    const visitlistData = await dbSAPPS.UpCountryVisitList.findAll({
      where: { upcountry_id: upCountry.id },
      order: [["id", "ASC"]],
      include: [
        {
          model: dbSAPPS.Client,
          as: "client",
          attributes: ["id", "display_name", "code", "priority", "specialist_id"],
          include: [{ model: dbSAPPS.Specialist, as: "specialist", attributes: ["id", "display_name"] }],
        },
        { model: dbSAPPS.Outlet, as: "outlet", attributes: ["id", "display_name", "code"] },
      ],
    });

    const visitlists = visitlistData.map((visitlist) => ({
      ...visitlist.dataValues,
      items: JSON.parse(visitlist.items),
    }));

    // ✅ itinerary, distance, accommodation
    const itineraries = await dbSAPPS.UpCountryItinerary.findAll({
      where: { upcountry_id: upCountry.id },
    });

    const estimation_distances = await dbSAPPS.UpCountryDistance.findAll({
      where: { upcountry_id: upCountry.id },
    });

    const accommodation_requests = await dbSAPPS.UpCountryAccommodation.findAll({
      where: { upcountry_id: upCountry.id },
    });

    const travelCost = await dbSAPPS.TravelCostRate.findOne({
      where: {
        designation_id: upCountry?.designation_id,
      },
    });

    // ✅ FINAL object untuk PDF / email
    upCountry = {
      ...upCountry.dataValues,
      company_name: upCountry?.company?.display_name,
      territory_name: upCountry?.territory?.display_name,
      employee_name: upCountry?.employee?.employee_name,
      designation_name: upCountry?.designation?.display_name,
      workflow: workflow ? workflow.dataValues : null,
      visitlists,
      estimation_distances: estimation_distances.map((d) => d.dataValues),
      itineraries: itineraries.map((i) => i.dataValues),
      accommodation_requests: accommodation_requests.map((r) => r.dataValues),
      travelCost: travelCost ? travelCost.dataValues : null,
    };

    // manual setup tranporter
    const transporter = nodemailer.createTransport({
      host: "mail.sapharma.co.id",
      port: "465",
      secure: true,
      auth: {
        user: "noreply-sapps@sapharma.co.id",
        pass: "noreply-sappss@p123**",
      },
      connectionTimeout: 30_000,
      greetingTimeout: 30_000,
      socketTimeout: 30_000,
      tls: {
        rejectUnauthorized: false,
      },
    });

    // 2. Step 2 - Set mail options
    const subject = `UC Request - ${upCountry.doc_no} - ${upCountry?.employee_name} - (${moment(
      upCountry.from_date,
    ).format("yyyy-MM-DD")} to ${moment(upCountry.to_date).format("yyyy-MM-DD")})`;

    // 3. Send email and handle status
    let emailStatus = "sent";
    let errorMessage = null;

    // attachment
    const pdfAttachment = await generatePdfFromTemplate(upCountry);

    // body email
    const html = emailToGA.html(upCountry);

    // ✅ Send Email with attachment
    try {
      await transporter.sendMail({
        from: "noreply-sapps@sapharma.co.id",
        to: params.ga_email,
        subject,
        html,
        attachments: [
          {
            filename: `${upCountry.doc_no}.pdf`,
            content: pdfAttachment,
            contentType: "application/pdf",
          },
        ],
      });
    } catch (err) {
      emailStatus = "failed";
      errorMessage = err?.message || "Failed to send email";
    }

    // 4. Create history email
    await dbSAPPS.HistoryEmail.create(
      {
        // main
        creation: new Date(),
        modified: new Date(),
        modified_by: params.modified_by,
        owner: params.owner,

        // extra
        company_id: params.company_id,
        employee_id: params.employee_id,
        recipient_email: params.ga_email,
        subject: subject,
        body: html,
        status: emailStatus,
        error_message: errorMessage,
        upcountry_id: params.id,
      },
      { transaction },
    );

    await transaction.commit();
    didCommit = true;
    return emailStatus === "sent"
      ? { success: true, message: "Email sent successfully!." }
      : { success: false, message: errorMessage || "Failed to request to ga." };
  } catch (error) {
    if (!didCommit) {
      try {
        await transaction.rollback();
      } catch (_) {
        // best-effort rollback
      }
    }
    const errorMessage = error.message || "Failed to request to ga.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function filter(params) {
  console.log("PARAMS FILTER : ", params);
  const filterConditions = {};

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

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

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

  try {
    let upCountries = [];
    upCountries = await dbSAPPS.UpCountry.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "designation",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "employee",
          attributes: ["id", "employee_name"],
        },
      ],
    });

    // === LOOP PER UpCountry ===
    upCountries = await Promise.all(
      upCountries.map(async (upCountry) => {
        // workflow
        const workflow = await dbSAPPS.Workflow.findOne({
          where: { doc_id: upCountry.id, doctype_id: 194 },
          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"],
                        },
                      ],
                    },
                  ],
                },
              ],
            },
          ],
        });

        // visit lists
        const visitlistData = await dbSAPPS.UpCountryVisitList.findAll({
          where: { upcountry_id: upCountry.id },
          order: [["id", "ASC"]],
          include: [
            {
              model: dbSAPPS.Client,
              as: "client",
              attributes: ["id", "display_name", "code", "priority", "specialist_id"],
              include: [{ model: dbSAPPS.Specialist, as: "specialist", attributes: ["id", "display_name"] }],
            },
            { model: dbSAPPS.Outlet, as: "outlet", attributes: ["id", "display_name", "code"] },
          ],
        });

        // itinerary / agenda
        const upcountryitinerary = await dbSAPPS.UpCountryItinerary.findAll({
          where: { upcountry_id: upCountry.id },
        });

        // distances
        const estimation_distances = await dbSAPPS.UpCountryDistance.findAll({
          where: { upcountry_id: upCountry.id },
        });

        // accommodation_requests
        const accommodation_requests = await dbSAPPS.UpCountryAccommodation.findAll({
          where: { upcountry_id: upCountry.id },
        });

        const visitlists = visitlistData?.map((visitlist) => {
          return {
            ...visitlist.dataValues,
            items: JSON.parse(visitlist.items),
          };
        });

        return {
          ...upCountry.dataValues,
          company_name: upCountry?.company?.display_name,
          territory_name: upCountry?.territory?.display_name,
          employee_name: upCountry?.employee?.employee_name,
          designation_name: upCountry?.designation?.display_name,
          workflow: workflow ? workflow.dataValues : null,
          visitlists: visitlists,
          estimation_distances: estimation_distances.map((distance) => distance.dataValues),
          itineraries: upcountryitinerary.map((itinerary) => itinerary.dataValues),
          accommodation_requests: accommodation_requests.map((request) => request.dataValues),
        };
      }),
    );

    upCountries = await formatWithModifier(upCountries);

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

async function updateFile(id, params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  let isCommitted = false;

  try {
    console.log("PARAM ID FILE : ", id);
    const existingFile = await dbSAPPS.UpCountryAccommodation.findByPk(id, { transaction });

    if (!existingFile) {
      await transaction.rollback();
      return { success: false, message: "File not found!" };
    }

    // Cek duplikat file
    const duplicate = await dbSAPPS.UpCountryAccommodation.findOne({
      where: {
        files: params.files,
        id: { [dbSAPPS.Sequelize.Op.ne]: id },
      },
      transaction,
    });

    if (duplicate) {
      await transaction.rollback();
      return {
        success: false,
        message: `"${params.files}" already exists for this document.`,
      };
    }

    const oldFilePath = existingFile.files;

    // Update record
    await existingFile.update(
      {
        ...params,
        modified: new Date(),
        modified_by: params.owner || existingFile.modified_by,
      },
      { transaction },
    );

    await transaction.commit();
    isCommitted = true;

    // Hapus file lama setelah commit
    if (oldFilePath && fs.existsSync(oldFilePath)) {
      try {
        fs.unlinkSync(path.resolve(oldFilePath));
        console.log("🗑️ File lama dihapus:", oldFilePath);
      } catch (err) {
        console.error("⚠️ Gagal menghapus file lama:", err);
      }
    }

    return {
      success: true,
      message: "File updated successfully!",
      data: existingFile,
    };
  } catch (error) {
    if (!isCommitted) {
      await transaction.rollback(); // hanya rollback jika belum commit
    }
    console.error("❌ Error updating procurement file:", error);
    return {
      success: false,
      message: error.message || "Failed to update file!",
    };
  }
}
