const dbSAPPS = require("../helpers/db");
require("dotenv").config();
const { Op } = require("sequelize");
const fs = require("fs");
const path = require("path");
const XLSX = require("xlsx");
const { error } = require("console");
const moment = require("moment");

module.exports = {
  getAllSalesUpload,
  uploadSalesData,
  uploadSalesTarget,
  importSalesData,
  create,
  filter,
  option,
  count,
  update,
  enabled,
  deleteImportSalesData,
};

// Mapping otomatis header Excel ke format database
const headerMappingSales = Object.fromEntries(
  Object.entries({
    no_invc: ["NO_INVC", "invoice_no", "Billing No", "NOMOR_INVOICE"],
    invoice_date: ["Posting Date", "INV_DATE", "inv_date", "I_DATE", "invoice_date", "TANGGAL_INVOICE"],
    cust_name: ["cust_name", "CUSNAME", "CUST_NAME", "Name Ship to", "NAMA_OUTLET"],
    cust_addr: ["Address Ship to", "CUST_ADD", "CUS_ADD", "cust_addr", "ADDRESS"],
    cust_city: ["City Ship to", "City", "CUS_CITY", "city", "CITY"],
    cust_code: ["cust_code", "CUST_CODE", "no_cust", "NO_CUST", "Ship to Party", "KODE_OUTLET"],
    part_num: ["PART_NUM", "PROD_CODE", "prod_code", "Material", "part_code", "ID_PRODUK"],
    part_desc: ["Text Material", "PRODE_NAME", "prode_name", "PART_DESC", "part_name", "NAMA_PRODUK"],
    quantity: ["Quantity", "QTY", "I_QTY", "qty"],
    price: ["Unit Price Penjualan", "PRICE", "price"],
    spec_disc: ["Dis% (ZD04)", "DISC", "SPEC_DISC", "val_disc", "pe_disc", "DISCOUNT_PERSEN_PRINSIPAL"],
    bonus: ["I_BONUS", "pe_value"],
    branch_code: ["Sales Office", "KD_CAB", "kd_cab", "KDBRANCH", "branch"],
    no_rma: ["RMA_NO"],
    ex_invc: ["EX_INVC", "Remark PTD 4", "invoice_no_reff", "NO_INVC_REFF"],
    dist: ["Dist", "DIST"],
  }).flatMap(([key, values]) => values.map((value) => [value.toLowerCase(), key]))
);

const excelToDate = (excelDate) => {
  if (typeof excelDate === "number") {
    // Convert Excel serial number to date (Excel mulai dari 1899-12-30)
    const jsDate = moment.utc("1899-12-30").add(excelDate, "days").format("YYYY-MM-DD");
    return jsDate;
  } else {
    // Handle jika tanggal sudah dalam format string
    const jsDate = moment(excelDate, ["DD-MMM-YY", "DD-MMM-YYYY", "D/M/YYYY", "YYYY-MM-DD"]).format("YYYY-MM-DD");
    return jsDate;
  }
};

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

  try {
    const filePath = path.join(__dirname, "../../../public/data/imports/", params.filename);

    if (!fs.existsSync(filePath)) {
      throw new Error("File not found");
    }

    // ===============================
    // READ EXCEL
    // ===============================
    const workbook = XLSX.readFile(filePath);
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];

    const rawHeaders = XLSX.utils.sheet_to_json(worksheet, { header: 1 })[0];
    const mappedHeaders = rawHeaders.map(
      (h) => headerMappingSales[h?.trim()?.toLowerCase()] || h?.trim()?.toLowerCase()
    );

    const rows = XLSX.utils.sheet_to_json(worksheet, {
      header: mappedHeaders,
      range: 1,
      defval: null,
    });

    const data = rows.map((row) => ({
      ...row,
      ...params,
    }));

    // ===============================
    // REMOVE DUPLICATE ROW (UPLOAD)
    // ===============================
    const seen = new Set();
    const uniqueUploadData = data.filter((item) => {
      const key = [item.no_invc, item.part_num, item.cust_code, item.invoice_date, item.quantity, item.price].join("|");

      if (seen.has(key)) return false;
      seen.add(key);
      return true;
    });

    // ===============================
    // MASTER DATA (LOAD ONCE)
    // ===============================
    const [
      distributors,
      periods,
      distributorOutlets,
      itemsCompany,
      distributorProducts,
      territoryItemGroups,
      territoryOutlets,
    ] = await Promise.all([
      dbSAPPS.Distributor.findAll(),
      dbSAPPS.Period.findAll(),
      dbSAPPS.DistributorOutlet.findAll({ where: { enabled: true } }),
      dbSAPPS.Item.findAll({ where: { enabled: true, company_id: params.company_id } }),
      dbSAPPS.DistributorProduct.findAll(),
      dbSAPPS.TerritoryItemGroup.findAll(),
      dbSAPPS.TerritoryOutlet.findAll(),
    ]);

    // ===============================
    // FORMAT & VALIDATE DATA
    // ===============================
    const formattedData = uniqueUploadData.map((item) => {
      let error_msg = null;

      // DISTRIBUTOR
      const distributor = distributors.find((d) => String(d.code) === String(item.dist));
      const distributor_id = distributor?.id || null;

      if (!distributor_id) error_msg = "Distributor not found";

      // DATE & PERIOD
      let invoice_date = null;
      let period_id = null;

      if (item.invoice_date) {
        const parsedDate = excelToDate(item.invoice_date);
        if (moment(parsedDate, moment.ISO_8601, true).isValid()) {
          invoice_date = moment(parsedDate).toDate();
          const p = periods.find((x) => x.start_date <= invoice_date && x.end_date >= invoice_date);
          period_id = p?.id || null;
        }
      }

      if (!period_id && !error_msg) error_msg = "Period not found";

      // OUTLET
      const outlet = distributorOutlets.find(
        (o) => String(o.distributor_id) === String(distributor_id) && o.code === item.cust_code
      );
      const outlet_id = outlet?.outlet_id || null;

      if (!outlet_id && !error_msg) error_msg = "Outlet not found";

      // PRODUCT
      const product = distributorProducts.find(
        (p) => String(p.distributor_id) === String(distributor_id) && p.code === item.part_num
      );
      const item_id = product?.item_id || null;

      if (!item_id && !error_msg) error_msg = "Product not found";

      // TERRITORY
      let territory_id = null;

      if (outlet_id && item_id) {
        const itemMaster = itemsCompany.find((i) => i.id === item_id);
        const itemGroup = itemMaster?.itemgroup_id;

        if (itemGroup) {
          const territories = territoryItemGroups
            .filter((t) => t.itemgroup_id === itemGroup)
            .map((t) => t.territory_id);

          const mapping = territoryOutlets.find(
            (t) => territories.includes(t.territory_id) && t.outlet_id === outlet_id
          );

          territory_id = mapping?.territory_id || null;
        }
      }

      if (!territory_id && !error_msg) error_msg = "Territory not found";

      return {
        ...item,
        distributor_id,
        distributor_name: distributor?.display_name || null,
        invoice_date,
        period_id,
        outlet_id,
        item_id,
        territory_id,
        error_msg,
      };
    });

    // ===============================
    // CHECK EXISTING IMPORT DATA
    // ===============================
    const existingData = await dbSAPPS.ImportSalesDistributor.findAll({
      where: {
        [Op.or]: formattedData.map((i) => ({
          distributor_id: i.distributor_id,
          company_id: params.company_id,
          no_invc: i.no_invc,
          cust_code: i.cust_code,
          part_num: i.part_num,
          invoice_date: i.invoice_date,
          quantity: i.quantity,
          price: i.price,
          period_id: i.period_id,
        })),
      },
    });

    const newPostData = formattedData.filter(
      (item) =>
        !existingData.some(
          (e) =>
            String(e.distributor_id) === String(item.distributor_id) &&
            e.no_invc === item.no_invc &&
            e.part_num === item.part_num &&
            e.cust_code === item.cust_code &&
            Number(e.quantity) === Number(item.quantity) &&
            Number(e.price) === Number(item.price)
        )
    );

    // ===============================
    // INSERT UNSET OUTLET
    // ===============================
    const unsetOutlet = newPostData.filter((i) => !i.outlet_id);

    const newOutletMap = {};
    unsetOutlet.forEach((i) => {
      const key = `${i.distributor_id}-${i.cust_code}`;
      if (!newOutletMap[key]) {
        newOutletMap[key] = {
          owner: 1,
          enabled: true,
          creation: new Date(),
          modified: new Date(),
          distributor_id: i.distributor_id,
          display_name: i.cust_name,
          code: i.cust_code,
          city: i.cust_city,
          address: i.cust_addr,
        };
      }
    });

    if (Object.values(newOutletMap).length > 0) {
      await dbSAPPS.DistributorOutlet.bulkCreate(Object.values(newOutletMap), { transaction });
    }

    // ===============================
    // INSERT UNSET PRODUCT
    // ===============================
    const unsetProduct = newPostData.filter((i) => !i.item_id);

    const newProductMap = {};
    unsetProduct.forEach((i) => {
      const key = `${i.distributor_id}-${i.part_num}`;
      if (!newProductMap[key]) {
        newProductMap[key] = {
          owner: 1,
          enabled: true,
          creation: new Date(),
          modified: new Date(),
          distributor_id: i.distributor_id,
          display_name: i.part_desc,
          code: i.part_num,
        };
      }
    });

    if (Object.values(newProductMap).length > 0) {
      await dbSAPPS.DistributorProduct.bulkCreate(Object.values(newProductMap), { transaction });
    }

    // ===============================
    // INSERT SALES DATA
    // ===============================
    if (newPostData.length > 0) {
      await dbSAPPS.ImportSalesDistributor.bulkCreate(newPostData, {
        transaction,
      });
    }

    await transaction.commit();

    return {
      success: true,
      message: "Sales data uploaded successfully",
      total_upload: uniqueUploadData.length,
      inserted: newPostData.length,
      data: newPostData,
    };
  } catch (error) {
    await transaction.rollback();
    return {
      success: false,
      message: error.message || "Upload failed",
    };
  }
}

// SEBELUM DI OPTIMASI
// async function getAllSalesUpload() {
//   try {
//     const data = await dbSAPPS.ImportSalesDistributor.findAll({
//       where: { enabled: true, is_imported: 0 },
//       order: [["modified", "DESC"]],
//     });

//     const cekData = [];
//     let invc_date = null;
//     for (const row of data) {
//       const cekOutlet = await dbSAPPS.DistributorOutlet.findOne({
//         where: { distributor_id: row.distributor_id, code: row.cust_code },
//         attributes: ["distributor_id", "outlet_id", "code", "display_name"],
//       });
//       const cekProduct = await dbSAPPS.DistributorProduct.findOne({
//         where: { distributor_id: row.distributor_id, code: row.part_num },
//         attributes: ["distributor_id", "item_id", "code", "display_name"],
//       });
//       // const distributorOutlet = await dbSAPPS.DistributorOutlet.findAll({ where: { enabled: true } });
//       const itemCompany = await dbSAPPS.Item.findAll({
//         where: {
//           enabled: true,
//           company_id: row.company_id,
//         },
//         attributes: ["id", "itemgroup_id", "company_id", "enabled", "display_name", "price"],
//       });
//       const periods = await dbSAPPS.Period.findAll({
//         where: { enabled: true },
//         attributes: ["id", "start_date", "end_date", "display_name", "year_id"],
//       });
//       invc_date = excelToDate(row.invoice_date);
//       // CEK PERIOD
//       let period = row.period_id;
//       if (row.period_id === null) {
//         const filterPeriod = periods?.find(
//           (p) => moment(p.start_date).isSameOrBefore(invc_date) && moment(p.end_date).isSameOrAfter(invc_date)
//         );
//         period = filterPeriod ? filterPeriod.id : null;
//       }

//       // CEK TERRITORY
//       let territory = row.territory_id;
//       let dataCekMapping, dataCekTerritoryItemGroup;

//       if (cekOutlet && cekOutlet.outlet_id && cekProduct && cekProduct.item_id) {
//         const cekItemGroup = itemCompany?.find((i) => i.id === cekProduct.item_id);
//         // console.log('cekItemGroup', cekItemGroup);
//         const itemGroup = cekItemGroup ? cekItemGroup.itemgroup_id : null;
//         // console.log('itemGroup', itemGroup);
//         if (itemGroup) {
//           const cekTerritoryItemGroup = await dbSAPPS.TerritoryItemGroup.findAll({
//             where: { itemgroup_id: itemGroup },
//             attributes: ["itemgroup_id", "territory_id"],
//           });
//           dataCekTerritoryItemGroup = cekTerritoryItemGroup;
//           if (cekTerritoryItemGroup.length > 0) {
//             // cek ke territory oulet dan outlet_id manakah yang sama dan invoice date tidak melebih valid_to
//             const { Op } = require("sequelize");

//             const cekMapping = await dbSAPPS.TerritoryOutlet.findOne({
//               where: {
//                 territory_id: { [Op.in]: cekTerritoryItemGroup.map((item) => item.territory_id) },
//                 outlet_id: cekOutlet.outlet_id,
//                 valid_from: { [Op.lte]: invc_date },
//                 [Op.or]: [{ valid_to: { [Op.gte]: invc_date } }, { valid_to: null }],
//               },
//               attributes: ["territory_id", "outlet_id", "valid_from", "valid_to"],
//               include: [
//                 {
//                   model: dbSAPPS.Territory,
//                   as: "territory",
//                   attributes: ["id", "display_name"],
//                 },
//               ],
//               order: [["valid_from", "DESC"]], // ambil yang paling baru
//             });

//             dataCekMapping = cekMapping;
//             // console.log("cekMapping : ", invc_date);
//             if (cekMapping) {
//               territory = cekMapping.territory_id;
//             }
//           }
//         }
//       }

//       let error_msg = "";
//       if (cekOutlet && !cekOutlet.outlet_id) {
//         error_msg = `Outlet with code ${row.cust_code} not found !`;
//       } else if (cekProduct && !cekProduct.item_id) {
//         error_msg += ` Product with code ${row.part_num} not found !`;
//       } else if (!territory) {
//         error_msg += "Territory not found !";
//       } else if (!period) {
//         error_msg += "Period not found !";
//       } else if (cekOutlet && cekOutlet.outlet_id && cekProduct && cekProduct.item_id && territory && period) {
//         error_msg = "Ready to import";
//       }

//       cekData.push({
//         ...row.dataValues,
//         distributor_id: row.distributor_id,
//         period_id: row.period_id,
//         outlet_id: cekOutlet ? cekOutlet.outlet_id : null,
//         item_id: cekProduct ? cekProduct.item_id : null,
//         territory_id: territory ? territory : null,
//         period_id: period ? period : null,
//         error_msg: error_msg,
//         dataCekMapping,
//         dataCekTerritoryItemGroup,
//       });
//     }

//     const finalData = [];
//     for (const row of cekData) {
//       const distributor = await dbSAPPS.Distributor.findOne({
//         where: { id: row.distributor_id, enabled: true },
//         attributes: ["id", "company_id", "display_name", "enabled"],
//       });
//       const period = await dbSAPPS.Period.findOne({
//         where: { id: row.period_id, enabled: true },
//         attributes: ["id", "display_name", "start_date", "end_date"],
//       });
//       const outlet = await dbSAPPS.Outlet.findOne({
//         where: { id: row.outlet_id, enabled: true },
//         attributes: ["id", "company_id", "display_name", "enabled"],
//       });
//       const item = await dbSAPPS.Item.findOne({
//         where: { id: row.item_id, enabled: true },
//         attributes: ["id", "itemgroup_id", "company_id", "enabled", "display_name", "price"],
//       });
//       const territory = await dbSAPPS.Territory.findOne({
//         where: { id: row.territory_id, enabled: true },
//         attributes: ["id", "display_name", "code"],
//       });

//       finalData.push({
//         ...row,
//         distributor: distributor ? distributor.dataValues : null,
//         period: period ? period.dataValues : null,
//         outlet: outlet ? outlet.dataValues : null,
//         item: item ? item.dataValues : null,
//         territory: territory ? territory.dataValues : null,
//       });
//     }

//     return {
//       data: finalData,
//       // test: data,
//       success: true,
//       message: "Data sales upload with relations has been loaded!",
//     };
//   } catch (error) {
//     const errorMessage = error.message || "Failed to load data sales upload!";
//     return {
//       success: false,
//       message: errorMessage,
//     };
//   }
// }

// SETELAH DI OPTIMASI


// SEBELUM DI OPTIMASI
// async function importSalesData(params) {
//   const transaction = await dbSAPPS.sequelize.transaction();
//   try {
//     // console.log("Received Params:", params);
//     // buat log import data
//     // cek dulu log import data udah ada atau belum kalo belum ada buat baru
//     const [importLog, created] = await dbSAPPS.ImportLog.findOrCreate({
//       where: {
//         doctype_id: params[0].doctype_id,
//         filename: params[0]?.filename,
//       },
//       defaults: {
//         creation: new Date(),
//         modified: new Date(),
//         owner: params[0].owner,
//         enabled: true,
//         doctype_id: params.doctype_id,
//         filename: params[0]?.filename,
//         is_imported: 1,
//         row_imported: params.length,
//         status: "Completed",
//       },
//     });
//     if (!created) {
//       // update import log
//       await dbSAPPS.ImportLog.update(
//         {
//           modified: new Date(),
//           row_imported: params.length,
//           status: "Completed",
//         },
//         {
//           where: {
//             id: importLog.id,
//           },
//           transaction,
//         }
//       );
//     }

//     const existingSalesData = await dbSAPPS.SalesDistributor.findAll({
//       where: {
//         [Op.and]: params.map((item) => ({
//           territory_id: item.territory_id,
//           no_invoice: item.no_invc,
//           transaction_date: item.invoice_date,
//           discount: item.spec_disc,
//           price: item.price,
//           quantity: item.quantity,
//           total: item.price * item.quantity,
//           company_id: item.company_id,
//           distributor_id: item.distributor_id,
//           outlet_id: item.outlet_id,
//           item_id: item.item_id,
//           period_id: item.period_id,
//           territory_id: item.territory_id,
//         })),
//       },
//     });

//     // update existing data dulu jika ada,

//     // ambil data sales distributor yang belum udah ada
//     const cekSalesData = params.filter(
//       (item) =>
//         !existingSalesData.find(
//           (data) =>
//             data.territory_id === item.territory_id &&
//             data.no_invoice === item.no_invc &&
//             data.transaction_date === item.invoice_date &&
//             data.discount === item.spec_disc &&
//             data.price === item.price &&
//             data.quantity === item.quantity &&
//             data.company_id === item.company_id &&
//             data.distributor_id === item.distributor_id &&
//             data.outlet_id === item.outlet_id &&
//             data.item_id === item.item_id &&
//             data.period_id === item.period_id &&
//             data.territory_id === item.territory_id
//         )
//     );

//     const newParam = cekSalesData.map((item) => ({
//       creation: new Date(),
//       modified: new Date(),
//       modified_by: params[0].owner, // atau user login
//       owner: params[0].owner,
//       enabled: true,

//       no_invoice: item.no_invc,
//       transaction_date: item.invoice_date,

//       discount: item.spec_disc ? Number(item.spec_disc) : null,
//       price: Number(item.price),
//       quantity: Number(item.quantity),
//       total: Number(item.price) * Number(item.quantity),

//       company_id: item.company_id,
//       distributor_id: item.distributor_id,
//       outlet_id: item.outlet_id,
//       item_id: item.item_id,
//       period_id: item.period_id,
//       territory_id: item.territory_id,
//     }));

//     await dbSAPPS.ImportSalesDistributor.update(
//       {
//         is_imported: 1,
//       },
//       {
//         where: {
//           id: newParam.map((item) => item.id),
//         },
//         transaction,
//       }
//     );

//     if (newParam?.length > 0) {
//       await dbSAPPS.SalesDistributor.bulkCreate(newParam, { transaction });
//     }

//     await transaction.commit();

//     return {
//       success: true,
//       message: "File sales has been imported",
//       data: newParam,
//     };
//   } catch (error) {
//     await transaction.rollback();
//     const errorMessage = error.message || "An error occurred during imported data.";
//     console.error("IMPORT SALES ERROR:", error.errors || error);
//     return {
//       success: false,
//       message: errorMessage,
//     };
//   }
// }

// FUNGSI IMPORT SALES DISTRIBUTOR YANG SUDAH DI OPTIMIZE

// SESUDAH DI OPTIMASI
async function importSalesData(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  const { Op } = require("sequelize");

  try {
    if (!params || params.length === 0) {
      return { success: false, message: "No sales data to import" };
    }

    const owner = params[0].owner;
    const filename = params[0]?.filename;
    const doctypeId = params[0]?.doctype_id;

    /* =====================================================
     * 1. IMPORT LOG
     * ===================================================== */
    const [importLog] = await dbSAPPS.ImportLog.findOrCreate({
      where: { doctype_id: doctypeId, filename },
      defaults: {
        creation: new Date(),
        modified: new Date(),
        owner,
        enabled: true,
        doctype_id: doctypeId,
        filename,
        is_imported: 1,
        row_imported: 0,
        status: "Processing",
      },
      transaction,
    });

    await dbSAPPS.ImportLog.update(
      { modified: new Date(), status: "Processing" },
      { where: { id: importLog.id }, transaction }
    );

    /* =====================================================
     * 2. LOAD EXISTING SALES (KEY + ID)
     * ===================================================== */
    const existingSales = await dbSAPPS.SalesDistributor.findAll({
      attributes: [
        "id",
        "no_invoice",
        "transaction_date",
        "company_id",
        "distributor_id",
        "outlet_id",
        "item_id",
        "period_id",
        "territory_id",
      ],
      transaction,
      raw: true,
    });

    const existingMap = new Map();
    for (const s of existingSales) {
      const key = `${s.no_invoice}|${s.transaction_date}|${s.company_id}|${s.distributor_id}|${s.outlet_id}|${s.item_id}|${s.period_id}|${s.territory_id}`;
      existingMap.set(key, s.id);
    }

    /* =====================================================
     * 3. BUILD INSERT & UPDATE PAYLOAD
     * ===================================================== */
    const insertPayload = [];
    const updatePayload = [];
    const importIds = [];

    for (const item of params) {
      const key = `${item.no_invc}|${item.invoice_date}|${item.company_id}|${item.distributor_id}|${item.outlet_id}|${item.item_id}|${item.period_id}|${item.territory_id}`;

      const baseData = {
        modified: new Date(),
        modified_by: owner,
        discount: item.spec_disc !== undefined ? Number(item.spec_disc) : null,
        price: Number(item.price),
        quantity: Number(item.quantity),
        total: Number(item.price) * Number(item.quantity),
      };

      if (existingMap.has(key)) {
        // ================= UPDATE =================
        updatePayload.push({
          id: existingMap.get(key),
          ...baseData,
        });
      } else {
        // ================= INSERT =================
        insertPayload.push({
          creation: new Date(),
          modified: new Date(),
          owner,
          modified_by: owner,
          enabled: true,

          no_invoice: item.no_invc,
          transaction_date: item.invoice_date,

          ...baseData,

          company_id: item.company_id,
          distributor_id: item.distributor_id,
          outlet_id: item.outlet_id,
          item_id: item.item_id,
          period_id: item.period_id,
          territory_id: item.territory_id,
        });
      }

      if (item.id) importIds.push(item.id);
    }

    /* =====================================================
     * 4. EXECUTE INSERT
     * ===================================================== */
    if (insertPayload.length) {
      await dbSAPPS.SalesDistributor.bulkCreate(insertPayload, { transaction });
    }

    /* =====================================================
     * 5. EXECUTE UPDATE (BULK SAFE)
     * ===================================================== */
    for (const row of updatePayload) {
      await dbSAPPS.SalesDistributor.update(
        {
          modified: row.modified,
          modified_by: row.modified_by,
          discount: row.discount,
          price: row.price,
          quantity: row.quantity,
          total: row.total,
        },
        {
          where: { id: row.id },
          transaction,
        }
      );
    }

    /* =====================================================
     * 6. UPDATE IMPORT SOURCE STATUS
     * ===================================================== */
    if (importIds.length) {
      await dbSAPPS.ImportSalesDistributor.update(
        { is_imported: 1, modified: new Date() },
        { where: { id: { [Op.in]: importIds } }, transaction }
      );
    }

    /* =====================================================
     * 7. FINALIZE IMPORT LOG
     * ===================================================== */
    await dbSAPPS.ImportLog.update(
      {
        modified: new Date(),
        row_imported: insertPayload.length + updatePayload.length,
        status: "Completed",
      },
      { where: { id: importLog.id }, transaction }
    );

    await transaction.commit();

    return {
      success: true,
      message: "Sales data imported (insert & update)",
      inserted: insertPayload.length,
      updated: updatePayload.length,
    };
  } catch (error) {
    await transaction.rollback();
    console.error("IMPORT SALES ERROR:", error);

    return {
      success: false,
      message: error.message || "An error occurred during import sales data",
    };
  }
}

async function deleteImportSalesData(id) {
  try {
    const importSalesDistributor = await dbSAPPS.ImportSalesDistributor.findByPk(id);
    if (!importSalesDistributor) return { success: false, message: "Data Import Sales not found!" };

    await importSalesDistributor.destroy();
    return {
      success: true,
      message: "Import sales data has been deleted!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to delete import sales data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

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

  if (params.name) {
    filterConditions.name = {
      [Op.like]: `%${params.name}%`,
    };
  }

  if (params.display_name) {
    filterConditions.display_name = {
      [Op.like]: `%${params.display_name}%`,
    };
  }

  if (params.slug_name) {
    filterConditions.slug_name = {
      [Op.like]: `%${params.slug_name}%`,
    };
  }

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

  try {
    const companies = await dbSAPPS.Company.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
    });

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

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

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

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

    const companies = await dbSAPPS.Company.findAll({
      attributes: ["id", "name", "display_name", "slug_name"],
      where: filterConditions,
      order: [["name", "ASC"]],
      include: [
        {
          model: dbSAPPS.ClientType,
          as: "clienttypes",
          attributes: ["id", "display_name", "code"],
          as: "clienttypes",
          include: [
            {
              model: dbSAPPS.Specialist,
              as: "specialists",
              attributes: ["id", "display_name", "code"],
              include: [
                {
                  model: dbSAPPS.SubSpecialist,
                  as: "subspecialists",
                  attributes: ["id", "display_name", "code"],
                },
              ],
            },
            {
              model: dbSAPPS.ClientSubtype,
              as: "clientsubtypes",
              attributes: ["id", "display_name", "code"],
            },
          ],
        },
      ],
    });

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

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

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

async function create(params) {
  try {
    if (!params.name || !params.display_name || !params.slug_name)
      return { success: false, message: "Company name, display name, slug name are required!" };

    const existingName = await dbSAPPS.Company.findOne({ where: { name: params.name } });
    if (existingName) return { success: false, message: `Company name "${params.name}" is already taken.` };

    const existingDisplayName = await dbSAPPS.Company.findOne({ where: { display_name: params.display_name } });
    if (existingDisplayName)
      return { success: false, message: `Display name "${params.display_name}" is already taken.` };

    const existingSlugName = await dbSAPPS.Company.findOne({ where: { slug_name: params.slug_name } });
    if (existingSlugName) return { success: false, message: `Slug name "${params.slug_name}" is already taken.` };

    await dbSAPPS.Company.create(params);
    return {
      success: true,
      message: "Company has been created!",
    };
  } catch (error) {
    const errorMessage = error.message || "An error occurred during creating company.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

    if (params.name && params.name !== company.name) {
      const existingName = await dbSAPPS.Company.findOne({ where: { name: params.name } });
      if (existingName) {
        if (existingName.id != params.id) {
          return {
            success: false,
            message: `Company name "${params.name}" is already taken. Try again!`,
          };
        }
      }
    }

    if (params.display_name && params.display_name !== company.display_name) {
      const existingDisplayName = await dbSAPPS.Company.findOne({ where: { display_name: params.display_name } });
      if (existingDisplayName) {
        if (existingDisplayName.id != params.id) {
          return {
            success: false,
            message: `Display name "${params.display_name}" is already taken. Try again!`,
          };
        }
      }
    }

    if (params.slug_name && params.slug_name !== company.slug_name) {
      const existingSlugName = await dbSAPPS.Company.findOne({ where: { slug_name: params.slug_name } });
      if (existingSlugName) {
        if (existingSlugName.id != params.id) {
          return {
            success: false,
            message: `Slug name "${params.slug_name}" is already taken. Try again!`,
          };
        }
      }
    }

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

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

    // debug
    // console.log('id', id);
    // console.log('params', params);
    // console.log('updateData', updateData)

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

async function uploadSalesTarget(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const { filename, company_id, period_id } = params;

    const filePath = path.join(__dirname, "../../../public/data/imports/", filename);

    if (!fs.existsSync(filePath)) {
      throw new Error("File not found.");
    }

    // Load Excel
    const workbook = XLSX.readFile(filePath);
    const worksheet = workbook.Sheets[workbook.SheetNames[0]];
    const rows = XLSX.utils.sheet_to_json(worksheet, { defval: "" });

    if (rows.length === 0) {
      throw new Error("Excel file is empty.");
    }

    // Ambil kode item dari header
    const headers = Object.keys(rows[0]);
    const itemCodes = headers?.filter((h) => !["NO", "AREA", "TERRITORY"].includes(h.toUpperCase()));

    // Ambil semua item ID
    const itemData = await dbSAPPS.Item.findAll({
      where: {
        code: { [Op.in]: itemCodes },
      },
      attributes: ["id", "code", "display_name"],
    });
    const itemMap = {};
    itemData.forEach((i) => (itemMap[i.code] = i.id));

    const createdTargets = [];

    for (const row of rows) {
      const area = row["AREA"]?.toString().trim();
      if (!area) continue;

      const territory = await dbSAPPS.Territory.findOne({
        where: {
          display_name: area,
          company_id,
        },
        attributes: ["id"],
      });

      if (!territory) {
        throw new Error(`Territory "${area}" not found in master.`);
      }

      // Cek salestarget
      let salesTarget = await dbSAPPS.SalesTarget.findOne({
        where: {
          period_id,
          company_id,
          territory_id: territory.id,
        },
      });

      if (!salesTarget) {
        salesTarget = await dbSAPPS.SalesTarget.create(
          {
            period_id,
            company_id,
            territory_id: territory.id,
            creation: new Date(),
            modified: new Date(),
            modified_by: 1,
            owner: 1,
            enabled: 1,
          },
          { transaction }
        );
      }

      for (const code of itemCodes) {
        const item_id = itemMap[code];
        const quantity = Number(row[code]) || 0;

        if (item_id && quantity > 0) {
          const existingDetail = await dbSAPPS.SalesTargetDetail.findOne({
            where: {
              salestarget_id: salesTarget.id,
              item_id,
            },
            transaction,
          });

          if (existingDetail) {
            // Update existing detail
            await existingDetail.update(
              {
                quantity,
                modified: new Date(),
                modified_by: 1,
              },
              { transaction }
            );
          } else {
            // Create new detail
            await dbSAPPS.SalesTargetDetail.create(
              {
                salestarget_id: salesTarget.id,
                item_id,
                quantity,
                creation: new Date(),
                modified: new Date(),
                modified_by: 1,
                owner: 1,
                enabled: 1,
              },
              { transaction }
            );
          }
        }
      }
      createdTargets.push(salesTarget.id);
    }

    await transaction.commit();
    return {
      success: true,
      message: `Berhasil import ${createdTargets.length} target area.`,
      data: createdTargets,
      headers,
      itemCodes,
      itemData,
    };
  } catch (error) {
    await transaction.rollback();
    return {
      success: false,
      message: error.message || "An error occurred during file processing.",
    };
  }
}
