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;
  }
};

/** Normalize code untuk lookup (Excel bisa number/string, DB string) – hindari mismatch "123" vs 123 */
const normCode = (v) => (v == null || v === "") ? "" : String(v).trim();

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 – gunakan normCode agar Excel number/string match dengan DB
      const outlet = distributorOutlets.find(
        (o) =>
          String(o.distributor_id) === String(distributor_id) &&
          normCode(o.code) === normCode(item.cust_code)
      );
      const outlet_id = outlet?.outlet_id ?? null;
      const outlet_not_found = !outlet;

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

      // PRODUCT – gunakan normCode agar Excel number/string match dengan DB
      const product = distributorProducts.find(
        (p) =>
          String(p.distributor_id) === String(distributor_id) &&
          normCode(p.code) === normCode(item.part_num)
      );
      const item_id = product?.item_id ?? null;
      const product_not_found = !product;

      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,
        outlet_not_found,
        product_not_found,
      };
    });

    // ===============================
    // 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) =>
            Number(e.distributor_id) === Number(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 – hanya bila BENAR-BENAR belum ada (jangan buat duplikat kalau sudah ada tapi belum di-map)
    // ===============================
    const unsetOutlet = newPostData.filter((i) => i.outlet_not_found);

    const newOutletMap = {};
    unsetOutlet.forEach((i) => {
      const key = `${i.distributor_id}-${normCode(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: normCode(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 – hanya bila BENAR-BENAR belum ada (jangan buat duplikat kalau sudah ada tapi belum di-map)
    // ===============================
    const unsetProduct = newPostData.filter((i) => i.product_not_found);

    const newProductMap = {};
    unsetProduct.forEach((i) => {
      const key = `${i.distributor_id}-${normCode(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: normCode(i.part_num),
        };
      }
    });

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

    // ===============================
    // INSERT SALES DATA (strip outlet_not_found / product_not_found – hanya untuk logic)
    // ===============================
    if (newPostData.length > 0) {
      const toInsert = newPostData.map(({ outlet_not_found, product_not_found, ...rest }) => rest);
      await dbSAPPS.ImportSalesDistributor.bulkCreate(toInsert, {
        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",
    };
  }
}


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

    if (data.length === 0) {
      return {
        data: [],
        success: true,
        message: "Data sales upload with relations has been loaded!",
      };
    }

    // =====================================================
    // LOAD ALL MASTER DATA ONCE (BATCH LOADING)
    // =====================================================
    const uniqueCompanyIds = [...new Set(data.map((row) => row.company_id).filter(Boolean))];
    const uniqueDistributorIds = [...new Set(data.map((row) => row.distributor_id).filter(Boolean))];
    const uniqueOutletIds = [];
    const uniqueItemIds = [];
    const uniquePeriodIds = [];
    const uniqueTerritoryIds = [];
    const uniqueItemGroupIds = [];

    // Collect all distributor outlet codes and product codes for batch lookup
    const distributorOutletKeys = new Set();
    const distributorProductKeys = new Set();

    data.forEach((row) => {
      if (row.distributor_id && row.cust_code) {
        distributorOutletKeys.add(`${row.distributor_id}|${row.cust_code}`);
      }
      if (row.distributor_id && row.part_num) {
        distributorProductKeys.add(`${row.distributor_id}|${row.part_num}`);
      }
    });

    // Batch load all master data
    const [
      periods,
      distributorOutlets,
      distributorProducts,
      itemsByCompany,
      territoryItemGroups,
      territoryOutlets,
    ] = await Promise.all([
      // Load all periods once
      dbSAPPS.Period.findAll({
        where: { enabled: true },
        attributes: ["id", "start_date", "end_date", "display_name", "year_id"],
      }),
      // Load all distributor outlets that match our data
      dbSAPPS.DistributorOutlet.findAll({
        where: { enabled: true },
        attributes: ["distributor_id", "outlet_id", "code", "display_name"],
      }),
      // Load all distributor products that match our data
      dbSAPPS.DistributorProduct.findAll({
        attributes: ["distributor_id", "item_id", "code", "display_name"],
      }),
      // Load all items by company
      dbSAPPS.Item.findAll({
        where: {
          enabled: true,
          company_id: { [Op.in]: uniqueCompanyIds },
        },
        attributes: ["id", "itemgroup_id", "company_id", "enabled", "display_name", "price"],
      }),
      // Load all territory item groups
      dbSAPPS.TerritoryItemGroup.findAll({
        attributes: ["itemgroup_id", "territory_id"],
      }),
      // Load all territory outlets with territory relation
      dbSAPPS.TerritoryOutlet.findAll({
        attributes: ["territory_id", "outlet_id", "valid_from", "valid_to"],
        include: [
          {
            model: dbSAPPS.Territory,
            as: "territory",
            attributes: ["id", "display_name"],
          },
        ],
      }),
    ]);

    // =====================================================
    // CREATE LOOKUP MAPS FOR FAST ACCESS (pakai normCode agar konsisten dengan upload)
    // =====================================================
    // Map: distributor_id|normCode(code) -> DistributorOutlet
    const outletMap = new Map();
    distributorOutlets.forEach((outlet) => {
      const key = `${outlet.distributor_id}|${normCode(outlet.code)}`;
      outletMap.set(key, outlet);
    });

    // Map: distributor_id|normCode(code) -> DistributorProduct
    const productMap = new Map();
    distributorProducts.forEach((product) => {
      const key = `${product.distributor_id}|${normCode(product.code)}`;
      productMap.set(key, product);
    });

    // Map: company_id -> Item[]
    const itemsByCompanyMap = new Map();
    itemsByCompany.forEach((item) => {
      if (!itemsByCompanyMap.has(item.company_id)) {
        itemsByCompanyMap.set(item.company_id, []);
      }
      itemsByCompanyMap.get(item.company_id).push(item);
    });

    // Map: itemgroup_id -> TerritoryItemGroup[]
    const territoryItemGroupMap = new Map();
    territoryItemGroups.forEach((tig) => {
      if (!territoryItemGroupMap.has(tig.itemgroup_id)) {
        territoryItemGroupMap.set(tig.itemgroup_id, []);
      }
      territoryItemGroupMap.get(tig.itemgroup_id).push(tig);
    });

    // Map: outlet_id -> TerritoryOutlet[] (sorted by valid_from DESC)
    const territoryOutletMap = new Map();
    territoryOutlets.forEach((to) => {
      if (!territoryOutletMap.has(to.outlet_id)) {
        territoryOutletMap.set(to.outlet_id, []);
      }
      territoryOutletMap.get(to.outlet_id).push(to);
    });
    // Sort each array by valid_from DESC
    territoryOutletMap.forEach((arr) => {
      arr.sort((a, b) => {
        const dateA = a.valid_from ? new Date(a.valid_from) : new Date(0);
        const dateB = b.valid_from ? new Date(b.valid_from) : new Date(0);
        return dateB - dateA;
      });
    });

    // =====================================================
    // PROCESS DATA (FIRST LOOP - VALIDATION)
    // =====================================================
    const cekData = [];

    for (const row of data) {
      // Lookup outlet (normCode agar match dengan Excel number/string vs DB)
      const outletKey = `${row.distributor_id}|${normCode(row.cust_code)}`;
      const cekOutlet = outletMap.get(outletKey) || null;

      // Lookup product (normCode agar match dengan Excel number/string vs DB)
      const productKey = `${row.distributor_id}|${normCode(row.part_num)}`;
      const cekProduct = productMap.get(productKey) || null;

      // Get items for this company
      const itemCompany = itemsByCompanyMap.get(row.company_id) || [];

      // Process invoice date
      const 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 = null;
      let dataCekTerritoryItemGroup = null;

      if (cekOutlet && cekOutlet.outlet_id && cekProduct && cekProduct.item_id) {
        const cekItemGroup = itemCompany.find((i) => i.id === cekProduct.item_id);
        const itemGroup = cekItemGroup ? cekItemGroup.itemgroup_id : null;

        if (itemGroup) {
          dataCekTerritoryItemGroup = territoryItemGroupMap.get(itemGroup) || [];
          if (dataCekTerritoryItemGroup.length > 0) {
            const territoryIds = dataCekTerritoryItemGroup.map((item) => item.territory_id);
            const outletTerritoryOutlets = territoryOutletMap.get(cekOutlet.outlet_id) || [];

            // Find matching territory outlet with date validation
            const cekMapping = outletTerritoryOutlets.find((to) => {
              if (!territoryIds.includes(to.territory_id)) return false;
              const validFrom = to.valid_from ? new Date(to.valid_from) : new Date(0);
              const validTo = to.valid_to ? new Date(to.valid_to) : null;
              const invcDate = new Date(invc_date);

              return validFrom <= invcDate && (validTo === null || validTo >= invcDate);
            });

            dataCekMapping = cekMapping || null;
            if (cekMapping) {
              territory = cekMapping.territory_id;
            }
          }
        }
      }

      // Build error message
      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";
      }

      // Collect IDs for batch loading in second loop
      if (cekOutlet && cekOutlet.outlet_id) uniqueOutletIds.push(cekOutlet.outlet_id);
      if (cekProduct && cekProduct.item_id) uniqueItemIds.push(cekProduct.item_id);
      if (period) uniquePeriodIds.push(period);
      if (territory) uniqueTerritoryIds.push(territory);

      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,
      });
    }

    // =====================================================
    // BATCH LOAD ALL RELATED ENTITIES FOR FINAL DATA
    // =====================================================
    const uniqueOutletIdsSet = [...new Set(uniqueOutletIds.filter(Boolean))];
    const uniqueItemIdsSet = [...new Set(uniqueItemIds.filter(Boolean))];
    const uniquePeriodIdsSet = [...new Set(uniquePeriodIds.filter(Boolean))];
    const uniqueTerritoryIdsSet = [...new Set(uniqueTerritoryIds.filter(Boolean))];

    const [distributors, periodsFinal, outlets, items, territories] = await Promise.all([
      dbSAPPS.Distributor.findAll({
        where: {
          id: { [Op.in]: uniqueDistributorIds },
          enabled: true,
        },
        attributes: ["id", "company_id", "display_name", "enabled"],
      }),
      dbSAPPS.Period.findAll({
        where: {
          id: { [Op.in]: uniquePeriodIdsSet },
          enabled: true,
        },
        attributes: ["id", "display_name", "start_date", "end_date"],
      }),
      dbSAPPS.Outlet.findAll({
        where: {
          id: { [Op.in]: uniqueOutletIdsSet },
          enabled: true,
        },
        attributes: ["id", "company_id", "display_name", "enabled"],
      }),
      dbSAPPS.Item.findAll({
        where: {
          id: { [Op.in]: uniqueItemIdsSet },
          enabled: true,
        },
        attributes: ["id", "itemgroup_id", "company_id", "enabled", "display_name", "price"],
      }),
      dbSAPPS.Territory.findAll({
        where: {
          id: { [Op.in]: uniqueTerritoryIdsSet },
          enabled: true,
        },
        attributes: ["id", "display_name", "code"],
      }),
    ]);

    // Create lookup maps for final data
    const distributorMap = new Map(distributors.map((d) => [d.id, d]));
    const periodMap = new Map(periodsFinal.map((p) => [p.id, p]));
    const outletMapFinal = new Map(outlets.map((o) => [o.id, o]));
    const itemMapFinal = new Map(items.map((i) => [i.id, i]));
    const territoryMapFinal = new Map(territories.map((t) => [t.id, t]));

    // =====================================================
    // BUILD FINAL DATA (SECOND LOOP - IN MEMORY)
    // =====================================================
    const finalData = cekData.map((row) => {
      const distributor = row.distributor_id ? distributorMap.get(row.distributor_id) : null;
      const period = row.period_id ? periodMap.get(row.period_id) : null;
      const outlet = row.outlet_id ? outletMapFinal.get(row.outlet_id) : null;
      const item = row.item_id ? itemMapFinal.get(row.item_id) : null;
      const territory = row.territory_id ? territoryMapFinal.get(row.territory_id) : null;

      return {
        ...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,
      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,
    };
  }
}

// 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.",
    };
  }
}
