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

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

module.exports = {
  getAll,
  getFiles,
  getLastDocNo,
  option,
  count,
  create,
  createFile,
  update,
  updateFile,
  enabled,
  delete: _delete,
  deleteFile,
};

const formatWithModifier = async (procurementTrackers) => {
  return Promise.all(
    procurementTrackers.map(async (province) => {
      const modifier = await dbSAPPS.User.findOne({
        where: {
          id: province.modified_by,
        },
        attributes: ["full_name"],
      });

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

async function getFiles(params) {
  try {
    let procurementTrackerFiles = await dbSAPPS.ProcurementTrackerFile.findAll({
      where: { procurementtracker_id: params?.procurementtracker_id },
      order: [["modified", "DESC"]],
    });

    procurementTrackerFiles = procurementTrackerFiles.map((item) => ({
      ...item.dataValues,
    }));

    procurementTrackerFiles = await formatWithModifier(procurementTrackerFiles);
    return {
      success: true,
      procurementTrackerFiles,
      message: "Procurement Trackers Files loaded successfully!",
    };
  } catch (error) {
    return {
      success: false,
      message: error.message || "Failed to load Procurement Trackers Files!",
    };
  }
}

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

//     if (params.access_read_all_data === true || params.access_read_all_data === "true") {
//       filterConditions.enabled = { [Op.not]: null };
//     } else {
//       filterConditions.enabled = { [Op.eq]: true };
//     }

//     if (params?.search) {
//       const search = `%${params.search}%`;

//       filterConditions[Op.or] = [
//         { code: { [Op.like]: search } },
//         { vendor_name: { [Op.like]: search } },
//         { from_nationality: { [Op.like]: search } },
//       ];
//     }

//     let procurementTrackers = await dbSAPPS.ProcurementTracker.findAll({
//       where: filterConditions,
//       // distinct: true,
//       // subQuery: false,
//       order: [
//         ["modified", "DESC"], // order utama untuk parent
//         [{ model: dbSAPPS.ProcurementTrackerPackingList, as: "packinglists" }, "packinglist_date", "ASC"], // ✅ order include
//         [{ model: dbSAPPS.ProcurementTrackerInvoice, as: "invoices" }, "invoice_date", "ASC"], // ✅ order include
//         [{ model: dbSAPPS.ProcurementTrackerAWB, as: "awb_list" }, "awb_date", "DESC"], // ✅ order include
//         [{ model: dbSAPPS.ProcurementTrackerStatus, as: "status_list" }, "status_date", "DESC"], // ✅ order include
//       ],
//       limit: 10,
//       include: [
//         {
//           model: dbSAPPS.ProcurementTrackerPO,
//           as: "po_list",
//           attributes: ["id", "enabled", "procurementtracker_id", "po_no"],
//           required: false,
//           include: [
//             {
//               model: dbSAPPS.ProcurementTrackerItemPO,
//               as: "items",
//               attributes: ["id", "enabled", "procurementtrackerpo_id", "item_name", "item_code", "uom", "quantity"],
//               required: false,
//             },
//           ],
//         },
//         {
//           model: dbSAPPS.ProcurementTrackerStatus,
//           as: "status_list",
//           attributes: ["id", "enabled", "procurementtracker_id", "status_name", "status_date", "note"],
//           required: false,
//         },
//         {
//           model: dbSAPPS.ProcurementTrackerAWB,
//           as: "awb_list",
//           attributes: ["id", "enabled", "procurementtracker_id", "awb_status", "courier", "note", "awb_date"],
//           required: false,
//         },
//         {
//           model: dbSAPPS.ProcurementTrackerFile,
//           as: "files",
//           required: false,
//         },
//         {
//           model: dbSAPPS.ProcurementTrackerPackingList,
//           as: "packinglists",
//           attributes: ["id", "enabled", "procurementtracker_id", "packinglist_date", "packinglist_number"],
//           required: false,
//         },
//         {
//           model: dbSAPPS.ProcurementTrackerInvoice,
//           as: "invoices",
//           attributes: ["id", "enabled", "procurementtracker_id", "no_invoice", "invoice_date"],
//           required: false,
//         },
//       ],
//     });

//     procurementTrackers = procurementTrackers.map((item) => ({
//       ...item.dataValues,
//     }));

//     procurementTrackers = await formatWithModifier(procurementTrackers);

//     return {
//       success: true,
//       procurementTrackers,
//       message: "Procurement Trackers loaded successfully!",
//     };
//   } catch (error) {
//     return {
//       success: false,
//       message: error.message || "Failed to load Procurement Trackers!",
//     };
//   }
// }

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

    if (params.access_read_all_data === true || params.access_read_all_data === "true") {
      filterConditions.enabled = { [Op.not]: null };
    } else {
      filterConditions.enabled = { [Op.eq]: true };
    }

    if (params?.search) {
      const search = `%${params.search}%`;

      filterConditions[Op.or] = [
        { code: { [Op.like]: search } },
        { vendor_name: { [Op.like]: search } },
        { from_nationality: { [Op.like]: search } },
      ];
    }

    let procurementTrackers = await dbSAPPS.ProcurementTracker.findAll({
      where: filterConditions,
      // distinct: true,
      // subQuery: false,
      order: [
        ["modified", "DESC"], // order utama untuk parent
      ],
      limit: 10,
    });

    procurementTrackers = await Promise.all(
      procurementTrackers.map(async (item) => {
        const po_list = await dbSAPPS.ProcurementTrackerPO.findAll({
          where: { procurementtracker_id: item.id },
          attributes: ["id", "enabled", "procurementtracker_id", "po_no"],
          include: [
            {
              model: dbSAPPS.ProcurementTrackerItemPO,
              as: "items",
              attributes: ["id", "enabled", "procurementtrackerpo_id", "item_name", "item_code", "uom", "quantity"],
              required: false,
            },
          ],
        });

        const status_list = await dbSAPPS.ProcurementTrackerStatus.findAll({
          where: { procurementtracker_id: item.id },
          attributes: ["id", "enabled", "procurementtracker_id", "status_name", "status_date", "note"],
          order: [["status_date", "DESC"]],
          required: false,
        });

        const awb_list = await dbSAPPS.ProcurementTrackerAWB.findAll({
          where: { procurementtracker_id: item.id },
          attributes: ["id", "enabled", "procurementtracker_id", "awb_status", "courier", "note", "awb_date"],
          order: [["awb_date", "DESC"]],
          required: false,
        });

        const files = await dbSAPPS.ProcurementTrackerFile.findAll({
          where: { procurementtracker_id: item.id },
        });

        const packinglists = await dbSAPPS.ProcurementTrackerPackingList.findAll({
          where: { procurementtracker_id: item.id },
          attributes: ["id", "enabled", "procurementtracker_id", "packinglist_date", "packinglist_number"],
          order: [["packinglist_date", "ASC"]],
          required: false,
        });

        const invoices = await dbSAPPS.ProcurementTrackerInvoice.findAll({
          where: { procurementtracker_id: item.id },
          attributes: ["id", "enabled", "procurementtracker_id", "no_invoice", "invoice_date"],
          order: [["invoice_date", "ASC"]],
          required: false,
        });

        return {
          ...item.dataValues,
          po_list,
          status_list,
          awb_list,
          files,
          packinglists,
          invoices,
        };
      })
    );

    // procurementTrackers = procurementTrackers.map((item) => ({
    //   ...item.dataValues,
    // }));

    procurementTrackers = await formatWithModifier(procurementTrackers);

    return {
      success: true,
      procurementTrackers,
      message: "Procurement Trackers loaded successfully!",
    };
  } catch (error) {
    return {
      success: false,
      message: error.message || "Failed to load Procurement Trackers!",
    };
  }
}
async function option(params) {
  try {
    const procurementTrackers = await dbSAPPS.ProcurementTracker.findAll({
      attributes: ["id", "display_name", "code"],
      where: { enabled: true },
      order: [["display_name", "ASC"]],
    });

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

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

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

async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();

  try {
    // 2 Validasi duplicate code
    const existingCode = await dbSAPPS.ProcurementTracker.findOne({
      where: { code: params.code },
      transaction,
    });
    if (existingCode) {
      return {
        success: false,
        message: `"${params.code}" is already taken.`,
      };
    }

    // Default value
    if (params.enabled === undefined) params.enabled = true;
    if (!params.creation) params.creation = new Date();
    if (!params.modified) params.modified = new Date();

    // 4️⃣ Simpan data utama ProcurementTracker
    const tracker = await dbSAPPS.ProcurementTracker.create(
      {
        creation: params.creation,
        modified: params.modified,
        modified_by: params.modified_by,
        owner: params.owner,
        enabled: params.enabled,
        code: params.code,
        vendor_name: params.vendor_name,
        awb_no: params.awb_no,
        from_nationality: params.from_nationality,
        estimated_arrival: params.estimated_arrival,
      },
      { transaction }
    );

    // CREATE PO + ITEMS
    if (Array.isArray(params.po_list) && params.po_list.length > 0) {
      for (const po of params.po_list) {
        const newPO = await dbSAPPS.ProcurementTrackerPO.create(
          {
            procurementtracker_id: tracker.id,
            creation: new Date(),
            modified: new Date(),
            modified_by: params.modified_by,
            owner: params.owner,
            enabled: true,
            po_no: po.po_no,
          },
          { transaction }
        );

        // CREATE ITEMS
        if (Array.isArray(po.items) && po.items.length > 0) {
          for (const item of po.items) {
            await dbSAPPS.ProcurementTrackerItemPO.create(
              {
                procurementtrackerpo_id: newPO.id,
                creation: new Date(),
                modified: new Date(),
                modified_by: params.modified_by,
                owner: params.owner,
                enabled: true,
                item_code: item.item_code,
                item_name: item.item_name,
                uom: item.uom,
                quantity: item.input_qty ?? 0,
              },
              { transaction }
            );
          }
        }
      }
    }

    // CREATE AWB
    if (Array.isArray(params.awb_list) && params.awb_list.length > 0) {
      for (const awb of params.awb_list) {
        await dbSAPPS.ProcurementTrackerAWB.create(
          {
            procurementtracker_id: tracker.id,
            creation: new Date(),
            modified: new Date(),
            modified_by: params.modified_by,
            owner: params.owner,
            enabled: true,
            awb_status: awb.awb_status,
            courier: awb.courier,
            note: awb.note,
            awb_date: awb.awb_date ? new Date(awb.awb_date) : null,
          },
          { transaction }
        );
      }
    }

    // CREATE INVOICE
    if (Array.isArray(params.invoices) && params.invoices.length > 0) {
      for (const invoice of params.invoices) {
        await dbSAPPS.ProcurementTrackerInvoice.create(
          {
            procurementtracker_id: tracker.id,
            creation: new Date(),
            modified: new Date(),
            modified_by: params.modified_by,
            owner: params.owner,
            enabled: true,
            no_invoice: invoice.no_invoice,
            invoice_date: invoice.invoice_date ? new Date(invoice.invoice_date) : null,
          },
          { transaction }
        );
      }
    }

    // CREATE PACKING LIST
    if (Array.isArray(params.packinglists) && params.packinglists.length > 0) {
      for (const packinglist of params.packinglists) {
        await dbSAPPS.ProcurementTrackerPackingList.create(
          {
            procurementtracker_id: tracker.id,
            creation: new Date(),
            modified: new Date(),
            modified_by: params.modified_by,
            owner: params.owner,
            enabled: true,
            packinglist_number: packinglist.packinglist_number,
            packinglist_date: packinglist.packinglist_date ? new Date(packinglist.packinglist_date) : null,
          },
          { transaction }
        );
      }
    }

    // CREATE STATUS LIST
    if (Array.isArray(params.status_list) && params.status_list.length > 0) {
      for (const st of params.status_list) {
        await dbSAPPS.ProcurementTrackerStatus.create(
          {
            procurementtracker_id: tracker.id,
            creation: new Date(),
            modified: new Date(),
            modified_by: params.modified_by,
            owner: params.owner,
            enabled: true,
            status_name: st.status_name,
            status_date: st.status_date ? new Date(st.status_date) : null,
            note: st.note,
          },
          { transaction }
        );
      }
    }

    // Commit transaksi
    await transaction.commit();

    return {
      success: true,
      message: "Saved successfully!",
      data: tracker,
    };
  } catch (error) {
    // Rollback kalau error
    await transaction.rollback();
    console.error("Error saving procurement tracker:", error);
    return {
      success: false,
      message: error.message || "Failed to save, please try again!",
    };
  }
}

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

  try {
    // 1. Cari data utama yang akan diupdate
    const tracker = await dbSAPPS.ProcurementTracker.findOne({
      where: { id: id },
      transaction,
    });

    if (!tracker) {
      await transaction.rollback();
      transactionCompleted = true;
      return {
        success: false,
        message: "Procurement tracker not found.",
      };
    }

    // 2. Validasi duplicate code (kecuali untuk data yang sama)
    if (params.code && params.code !== tracker.code) {
      const existingCode = await dbSAPPS.ProcurementTracker.findOne({
        where: { code: params.code },
        transaction,
      });
      if (existingCode) {
        await transaction.rollback();
        transactionCompleted = true;
        return {
          success: false,
          message: `"${params.code}" is already taken.`,
        };
      }
    }

    // 3. Update data utama ProcurementTracker
    const updateData = {
      modified: new Date(),
      modified_by: params.modified_by,
    };

    // Hanya update field yang ada di params
    if (params.owner !== undefined) updateData.owner = params.owner;
    if (params.enabled !== undefined) updateData.enabled = params.enabled;
    if (params.code !== undefined) updateData.code = params.code;
    if (params.vendor_name !== undefined) updateData.vendor_name = params.vendor_name;
    if (params.awb_no !== undefined) updateData.awb_no = params.awb_no;
    if (params.from_nationality !== undefined) updateData.from_nationality = params.from_nationality;
    if (params.estimated_arrival !== undefined) updateData.estimated_arrival = params.estimated_arrival;

    await dbSAPPS.ProcurementTracker.update(updateData, {
      where: { id: id },
      transaction,
    });

    // 4. Handle PO + ITEMS (Delete yang lama, create yang baru)
    if (Array.isArray(params.po_list)) {
      // Hapus semua PO dan items yang terkait
      const existingPOs = await dbSAPPS.ProcurementTrackerPO.findAll({
        where: { procurementtracker_id: id },
        transaction,
      });

      for (const po of existingPOs) {
        await dbSAPPS.ProcurementTrackerItemPO.destroy({
          where: { procurementtrackerpo_id: po.id },
          transaction,
        });
      }

      await dbSAPPS.ProcurementTrackerPO.destroy({
        where: { procurementtracker_id: id },
        transaction,
      });

      // Create PO + ITEMS baru
      if (params.po_list.length > 0) {
        for (const po of params.po_list) {
          const newPO = await dbSAPPS.ProcurementTrackerPO.create(
            {
              procurementtracker_id: id,
              creation: new Date(),
              modified: new Date(),
              modified_by: params.modified_by,
              owner: params.owner,
              enabled: true,
              po_no: po.po_no,
            },
            { transaction }
          );

          // CREATE ITEMS
          if (Array.isArray(po.items) && po.items.length > 0) {
            for (const item of po.items) {
              await dbSAPPS.ProcurementTrackerItemPO.create(
                {
                  procurementtrackerpo_id: newPO.id,
                  creation: new Date(),
                  modified: new Date(),
                  modified_by: params.modified_by,
                  owner: params.owner,
                  enabled: true,
                  item_code: item.item_code,
                  item_name: item.item_name,
                  uom: item.uom,
                  quantity: item.input_qty ?? 0,
                },
                { transaction }
              );
            }
          }
        }
      }
    }

    // 5. Handle AWB (Delete yang lama, create yang baru)
    if (Array.isArray(params.awb_list)) {
      await dbSAPPS.ProcurementTrackerAWB.destroy({
        where: { procurementtracker_id: id },
        transaction,
      });

      if (params.awb_list.length > 0) {
        for (const awb of params.awb_list) {
          await dbSAPPS.ProcurementTrackerAWB.create(
            {
              procurementtracker_id: id,
              creation: new Date(),
              modified: new Date(),
              modified_by: params.modified_by,
              owner: params.owner,
              enabled: true,
              awb_status: awb.awb_status,
              courier: awb.courier,
              note: awb.note,
              awb_date: awb.awb_date ? new Date(awb.awb_date) : null,
            },
            { transaction }
          );
        }
      }
    }

    // handle invoice (delete yang lama, create yang baru)
    if (Array.isArray(params.invoices)) {
      await dbSAPPS.ProcurementTrackerInvoice.destroy({
        where: { procurementtracker_id: id },
        transaction,
      });

      if (params.invoices.length > 0) {
        for (const invoice of params.invoices) {
          await dbSAPPS.ProcurementTrackerInvoice.create(
            {
              procurementtracker_id: id,
              creation: new Date(),
              modified: new Date(),
              modified_by: params.modified_by,
              owner: params.owner,
              enabled: true,
              no_invoice: invoice.no_invoice,
              invoice_date: invoice.invoice_date ? new Date(invoice.invoice_date) : null,
            },
            { transaction }
          );
        }
      }
    }
    // handle packing list (delete yang lama, create yang baru)
    if (Array.isArray(params.packinglists)) {
      await dbSAPPS.ProcurementTrackerPackingList.destroy({
        where: { procurementtracker_id: id },
        transaction,
      });

      if (params.packinglists.length > 0) {
        for (const packinglist of params.packinglists) {
          await dbSAPPS.ProcurementTrackerPackingList.create(
            {
              procurementtracker_id: id,
              creation: new Date(),
              modified: new Date(),
              modified_by: params.modified_by,
              owner: params.owner,
              enabled: true,
              packinglist_number: packinglist.packinglist_number,
              packinglist_date: packinglist.packinglist_date ? new Date(packinglist.packinglist_date) : null,
            },
            { transaction }
          );
        }
      }
    }

    // 6. Handle Status (Delete yang lama, create yang baru)
    if (Array.isArray(params.status_list)) {
      await dbSAPPS.ProcurementTrackerStatus.destroy({
        where: { procurementtracker_id: id },
        transaction,
      });

      if (params.status_list.length > 0) {
        for (const st of params.status_list) {
          await dbSAPPS.ProcurementTrackerStatus.create(
            {
              procurementtracker_id: id,
              creation: new Date(),
              modified: new Date(),
              modified_by: params.modified_by,
              owner: params.owner,
              enabled: true,
              status_name: st.status_name,
              status_date: st.status_date ? new Date(st.status_date) : null,
              note: st.note,
            },
            { transaction }
          );
        }
      }
    }

    // Commit transaksi
    await transaction.commit();
    transactionCompleted = true;

    return {
      success: true,
      message: "Updated successfully!",
    };
  } catch (error) {
    // Rollback hanya jika transaction belum selesai
    if (!transactionCompleted) {
      await transaction.rollback();
    }

    console.error("Error updating procurement tracker:", error);
    return {
      success: false,
      message: error.message || "Failed to update, please try again!",
    };
  }
}

async function createFile(params) {
  const transaction = await dbSAPPS.sequelize.transaction();

  try {
    const existingCode = await dbSAPPS.ProcurementTrackerFile.findOne({
      where: {
        file_name: params.file_name,
        file_type: params.file_type,
        procurementtracker_id: params.procurementtracker_id,
        document_no: params.document_no,
      },
      transaction,
    });

    if (existingCode) {
      await transaction.rollback();
      return { success: false, message: `"${params.file_name}" is already taken.` };
    }

    const data = await dbSAPPS.ProcurementTrackerFile.create(
      {
        ...params,
        mime_type: params.mime_type || "application/pdf",
        enabled: params.enabled ?? true,
        creation: params.creation || new Date(),
        modified: params.modified || new Date(),
        owner: params.owner || null,
        created_by: params.owner || null,
        modified_by: params.owner || null,
      },
      { transaction }
    );

    await transaction.commit();

    return { success: true, message: "File saved successfully!", data };
  } catch (error) {
    await transaction.rollback();
    console.error("Error saving procurement file:", error);
    return { success: false, message: error.message || "Failed to save file, please try again!" };
  }
}

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

  try {
    const existingFile = await dbSAPPS.ProcurementTrackerFile.findByPk(id, { transaction });

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

    // Cek duplikat file
    const duplicate = await dbSAPPS.ProcurementTrackerFile.findOne({
      where: {
        file_name: params.file_name,
        file_type: params.file_type,
        procurementtracker_id: params.procurementtracker_id,
        document_no: params.document_no,
        id: { [dbSAPPS.Sequelize.Op.ne]: id },
      },
      transaction,
    });

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

    const oldFilePath = existingFile.file_path;

    // Update record
    await existingFile.update(
      {
        ...params,
        mime_type: params.mime_type || "application/pdf",
        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!",
    };
  }
}

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

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

async function getLastDocNo() {
  try {
    const prefix = "SHP"; // prefix dokumen
    const currentYear = new Date().getFullYear(); // contoh: 2025

    const lastDoc = await dbSAPPS.ProcurementTracker.findOne({
      order: [["code", "DESC"]],
    });

    // Kalau belum ada data sama sekali
    if (!lastDoc) {
      return `${prefix}-${currentYear}-0001`;
    }

    // Ambil nomor terakhir
    const lastDocNo = lastDoc.code.toString(); // misal: SHP-2025-0042
    const parts = lastDocNo.split("-");
    const lastYear = parseInt(parts[1], 10);
    const lastNumber = parseInt(parts[2], 10);

    // Jika tahun sekarang beda, reset ke 0001
    const newNumber = lastYear === currentYear ? lastNumber + 1 : 1;

    // Format hasil akhir
    const newDocNo = `${prefix}-${currentYear}-${newNumber.toString().padStart(4, "0")}`;

    return newDocNo;
  } catch (error) {
    console.error("Error getting last doc no: ", error);
    // fallback jika gagal
    const prefix = "SHP";
    const currentYear = new Date().getFullYear();
    return `${prefix}-${currentYear}-0001`;
  }
}

async function _delete(id) {
  try {
    // Ambil semua file terkait tracker
    const procurementTrackerFiles = await dbSAPPS.ProcurementTrackerFile.findAll({
      where: { procurementtracker_id: id },
    });

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

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

    // Hapus record file dari DB
    if (procurementTrackerFiles.length > 0) {
      await dbSAPPS.ProcurementTrackerFile.destroy({
        where: { procurementtracker_id: id },
      });
    }

    // Cek record utama
    const procurementTracker = await dbSAPPS.ProcurementTracker.findByPk(id);
    if (!procurementTracker) {
      return { success: false, message: "Procurement Tracker not found!" };
    }

    // Hapus record utama
    await procurementTracker.destroy();

    return {
      success: true,
      message: "Procurement Tracker and related files successfully deleted!",
    };
  } catch (error) {
    console.error("❌ Error deleting procurement tracker:", error);
    return {
      success: false,
      message: error.message || "Failed to delete Procurement Tracker.",
    };
  }
}

async function deleteFile(id) {
  try {
    const procurementTrackerFile = await dbSAPPS.ProcurementTrackerFile.findByPk(id);
    if (!procurementTrackerFile) return { success: false, message: "Procurement Tracker Document not found!" };

    fs.unlinkSync(procurementTrackerFile.file_path);

    await procurementTrackerFile.destroy();

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