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

module.exports = {
  getAll,
  getAllSaldoUpload,
  uploadSaldoAwal,
  filter,
  option,
  count,

  create,
  update,
  enabled,
  delete: _delete,
};

async function getAll(params) {
  try {
    const filterConditions = {};
    if (params.territory_id) {
      filterConditions.territory_id = {
        [Op.eq]: parseInt(params.territory_id),
      };
    }

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

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

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

    const saldoawalclients = await dbSAPPS.SaldoAwalClient.findAll({
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Client,
          as: "client",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Item,
          as: "item",
          attributes: ["id", "display_name"],
        },
      ],
    });

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

async function getAllSaldoUpload() {
  try {
    const saldoAwalList = await dbSAPPS.SaldoAwalClient.findAll({
      where: {
        enabled: true,
        is_imported: 0,
      },
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Client,
          as: "client",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Item,
          as: "item",
          attributes: ["id", "display_name"],
        },
      ],
    });

    if (!saldoAwalList.length) {
      return {
        data: [],
        success: true,
        message: "No saldo awal upload data to process.",
      };
    }

    const results = await Promise.all(
      saldoAwalList.map(async (row) => {
        let error_msg = null;

        try {
          const [client, item] = await Promise.all([
            dbSAPPS.Client.findByPk(row.client_id),
            dbSAPPS.Item.findByPk(row.item_id),
          ]);

          const errors = {};

          // ✅ Validasi client
          if (!client) {
            errors.client_id = "Client not found!";
          }

          // ✅ Validasi item
          if (!item) {
            errors.item_id = "Item not found!";
          }

          // ✅ Validasi balance
          if (row.balance === null || row.balance === undefined) {
            errors.balance = "Balance is required!";
          } else if (isNaN(Number(row.balance))) {
            errors.balance = "Balance must be a number!";
          }

          // ✅ Validasi type
          if (!row.type) {
            errors.type = "Type is required!";
          } else if (!["debit", "credit"].includes(row.type)) {
            errors.type = "Type must be 'debit' or 'credit'!";
          }

          // jika ada error → set object
          if (Object.keys(errors).length > 0) {
            error_msg = errors;
          }

          return {
            ...row.toJSON(),
            error_msg, // ← SELALU ADA
          };
        } catch (err) {
          return {
            ...row.toJSON(),
            error_msg: {
              system: "Failed to validate client or item data.",
            },
          };
        }
      })
    );

    return {
      data: results, // ⬅ kirim semua, frontend yang tentukan
      success: true,
      message: "Data upload has been loaded successfully!",
    };
  } catch (error) {
    return {
      success: false,
      message: error.message || "Failed to load data sales upload!",
    };
  }
}

const headerMapping = Object.fromEntries(
  Object.entries({
    item_id: ["ITEM_ID", "item_id", "item id"],
    item_name: ["item_name", "ITEM NAME", "item name"],
    client_id: ["CLIENT_ID", "client_id", "id_client", "client id", "customer_id", "customer id", "id_customer"],
    client_name: ["CUSTOMER_NAME", "customer_name", "client name", "customer name", "client_name", "customer_name"],
    balance: ["BALANCE", "balance", "SALDO_AWAL"],
    type: ["TYPE", "type"],
  }).flatMap(([key, values]) => values.map((value) => [value.toLowerCase(), key]))
);

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

  try {
    console.log("Received Params:", params);

    const filePath = path.join(process.cwd(), "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(
      (header) => headerMapping[header?.trim()?.toLowerCase()] || header?.trim()?.toLowerCase()
    );

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

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

    // =============================
    // REMOVE DUPLICATE DALAM FILE
    // =============================
    const seen = new Set();
    const uniqueData = data.filter((item) => {
      const key = `${item.client_id}-${item.item_id}-${item.balance}-${item.type}`;
      if (seen.has(key)) return false;
      seen.add(key);
      return true;
    });

    // =============================
    // VALIDATION
    // =============================
    const formattedData = await Promise.all(
      uniqueData.map(async (item) => {
        let error_msg = null;

        const client = item.client_id ? await dbSAPPS.Client.findByPk(item.client_id) : null;

        if (!client) error_msg = "Client not found!";

        const product = item.item_id ? await dbSAPPS.Item.findByPk(item.item_id) : null;

        if (!error_msg && !product) error_msg = "Item not found!";

        if (!error_msg && (item.balance === null || item.balance === undefined)) error_msg = "Balance is required!";

        if (!error_msg && isNaN(Number(item.balance))) error_msg = "Balance must be a number!";

        if (!error_msg && !item.type) error_msg = "Type is required!";

        if (!error_msg && !["debit", "credit"].includes(item.type)) error_msg = "Type must be 'debit' or 'credit'!";

        return {
          ...item,
          client_id: client ? client.id : null,
          item_id: product ? product.id : null,
          balance: Number(item.balance) || 0,
          is_imported: false,
          error_msg,
        };
      })
    );

    // =============================
    // CEK DATA SUDAH ADA DI DB
    // =============================
    const existingData = await dbSAPPS.SaldoAwalClient.findAll({
      where: {
        [Op.or]: formattedData.map((item) => ({
          client_id: item.client_id,
          item_id: item.item_id,
          balance: item.balance,
          type: item.type,
        })),
      },
    });

    const finalData = formattedData.map((item) => {
      const isDuplicate = existingData.some(
        (exist) =>
          Number(exist.client_id) === Number(item.client_id) &&
          Number(exist.item_id) === Number(item.item_id) &&
          Number(exist.balance) === Number(item.balance) &&
          exist.type === item.type
      );

      if (isDuplicate) {
        return {
          ...item,
          error_msg: "Data already exists",
        };
      }

      return item;
    });

    // =============================
    // INSERT SEMUA KECUALI YANG SUDAH ADA
    // =============================
    const insertData = finalData.filter((item) => item.error_msg !== "Data already exists");

    if (insertData.length > 0) {
      await dbSAPPS.SaldoAwalClient.bulkCreate(insertData, {
        transaction,
      });
    }

    await transaction.commit();

    return {
      success: true,
      message: "File processed successfully",
      data: finalData,
    };
  } catch (error) {
    await transaction.rollback();
    return {
      success: false,
      message: error.message || "Failed to process upload",
    };
  }
}

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

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

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

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

  if (params.enabled) {
    filterConditions.enabled = {
      [Op.eq]: params.enabled === "1" ? true : false,
    };
  }
  try {
    const saldoawalclients = await dbSAPPS.SaldoAwalClient.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
      ],
    });

    return {
      success: true,
      saldoawalclients,
      message: "saldoawalclients loaded successfully!",
    };
  } catch (error) {
    console.error("Error retrieving saldoawalclients: ", error);
    const errorMessage = error.message || "Failed to retrieve saldoawalclients. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
async function option() {
  try {
    const saldoawalclients = await dbSAPPS.SaldoAwalClient.findAll({
      attributes: ["id", "display_name", "code"],
      where: { enabled: true },
      order: [["display_name", "ASC"]],
      include: [
        {
          model: dbSAPPS.SaldoAwalClientBranch,
          as: "branches",
          attributes: ["id", "display_name", "code", "email_address1", "email_address2"],
        },
      ],
    });

    return {
      success: true,
      saldoawalclients,
      message: "data loaded successfully!",
    };
  } catch (error) {
    console.error("Error retrieving data: ", error);
    const errorMessage = error.message || "Failed to retrieve data. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
async function count() {
  try {
    const count = await dbSAPPS.SaldoAwalClient.count({
      where: { enabled: true },
    });

    return {
      success: true,
      count,
      message: "data loaded successfully!",
    };
  } catch (error) {
    console.error("Error counting data: ", error);
    return {
      success: false,
      message: "Failed to retrieve data count. Please try again later.",
    };
  }
}
async function create(params) {
  try {
    const existingData = await dbSAPPS.SaldoAwalClient.findOne({
      where: {
        client_id: params.client_id,
        item_id: params.item_id,
        type: params.type,
        balance: params.balance,
      },
    });
    if (existingData)
      return {
        success: false,
        message: `Data Client "${params.client_id}", item "${params.item_id}", type "${params.type}", balance "${params.balance}" is already taken.`,
      };

    if (!params.enabled) params.enabled = true;

    const newsaldoawalclients = await dbSAPPS.SaldoAwalClient.create(params);

    return {
      success: true,
      message: "Data has been successfully added!",
      saldoawalclients: newsaldoawalclients,
    };
  } catch (error) {
    const errorMessage = error.message || "An error occurred during create saldo awal clients processing.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
async function update(id, params) {
  try {
    const saldoawalclients = await dbSAPPS.SaldoAwalClient.findByPk(id);
    if (!saldoawalclients)
      return {
        success: false,
        message: "Data Saldo not found!",
      };

    if (
      params.client_id &&
      params.client_id !== saldoawalclients.client_id &&
      params.item_id &&
      params.item_id !== saldoawalclients.item_id &&
      params.type &&
      params.type !== saldoawalclients.type &&
      params.balance &&
      params.balance !== saldoawalclients.balance
    ) {
      const existingNameNNumber = await dbSAPPS.SaldoAwalClient.findOne({
        where: {
          client_id: params.client_id,
          item_id: params.item_id,
          type: params.type,
          balance: params.balance,
        },
      });
      if (existingNameNNumber)
        return {
          success: false,
          message: `Data Client "${params.client_id}", item "${params.item_id}", type "${params.type}", balance "${params.balance}" is already taken.`,
        };
    }

    await saldoawalclients.update(params);

    return {
      success: true,
      message: "Data has been saved!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to update data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
async function _delete(id) {
  try {
    const saldoawalclients = await dbSAPPS.SaldoAwalClient.findByPk(id);
    if (!saldoawalclients) return { success: false, message: "saldo awal clients not found!" };

    await saldoawalclients.destroy();

    return {
      success: true,
      message: "data successfully deleted!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to delete data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
async function enabled(id, params) {
  try {
    const saldoawalclients = await dbSAPPS.SaldoAwalClient.findByPk(id);
    if (!saldoawalclients)
      return {
        success: false,
        message: "data 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 saldoawalclients.update(updateData);

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