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

module.exports = {
  getAll,
  getDuplicateData,
  getSalesByPeriodAndTerritory,
  filter,
  option,
  count,
  create,
  update,
  enabled,
  delete: _delete,
};

async function getAll() {
  try {
    const salesDistributors = await dbSAPPS.SalesDistributor.findAll({
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Item,
          as: "item",
          attributes: ["id", "display_name", "item_cor"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Period,
          as: "period",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Distributor,
          as: "distributor",
          attributes: ["id", "display_name"],
        },
      ],
    });

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

async function getDuplicateData() {
  try {
    // Step 1: Find duplicate combinations using raw query
    const duplicateCombinations = await dbSAPPS.sequelize.query(
      `SELECT no_invoice, item_id, territory_id, period_id, outlet_id, distributor_id, company_id, quantity
       FROM salesdistributors
       GROUP BY no_invoice, item_id, territory_id, period_id, outlet_id, distributor_id, company_id, quantity
       HAVING COUNT(id) > 1`,
      {
        type: dbSAPPS.sequelize.QueryTypes.SELECT,
      }
    );

    if (duplicateCombinations.length === 0) {
      return {
        salesDistributors: [],
        success: true,
        message: "No duplicate Sales Distributor found!",
      };
    }

    // Step 2: Build WHERE conditions to find all records matching duplicate combinations
    const whereConditions = duplicateCombinations.map((combo) => ({
      [Op.and]: [
        { no_invoice: combo.no_invoice },
        { item_id: combo.item_id },
        { territory_id: combo.territory_id },
        { period_id: combo.period_id },
        { outlet_id: combo.outlet_id },
        { distributor_id: combo.distributor_id },
        { company_id: combo.company_id },
        { quantity: combo.quantity },
      ],
    }));

    // Step 3: Get all duplicate records with includes
    const allDuplicateRecords = await dbSAPPS.SalesDistributor.findAll({
      where: {
        [Op.or]: whereConditions,
      },
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Item,
          as: "item",
          attributes: ["id", "display_name", "item_cor"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Period,
          as: "period",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Distributor,
          as: "distributor",
          attributes: ["id", "display_name"],
        },
      ],
    });

    // Step 4: Group duplicate records by their combination key
    // dan sisakan 1 yang tidak ditampilkan (yang paling baru berdasarkan modified)
    const groupedDuplicates = {};
    
    allDuplicateRecords.forEach((record) => {
      // Buat key unik berdasarkan kombinasi duplikat
      const duplicateKey = `${record.no_invoice}_${record.item_id}_${record.territory_id}_${record.period_id}_${record.outlet_id}_${record.distributor_id}_${record.company_id}_${record.quantity}`;
      
      if (!groupedDuplicates[duplicateKey]) {
        groupedDuplicates[duplicateKey] = [];
      }
      groupedDuplicates[duplicateKey].push(record);
    });

    // Step 5: Dari setiap grup, ambil semua kecuali 1 yang paling baru (sisakan yang paling baru)
    // Records sudah di-sort DESC berdasarkan modified, jadi index 0 adalah yang paling baru
    const salesDistributors = [];
    
    Object.values(groupedDuplicates).forEach((group) => {
      // Ambil semua kecuali yang pertama (index 0) yang merupakan yang paling baru
      // Jadi jika ada 3, ambil 2 (index 1 dan 2)
      // Jika ada 2, ambil 1 (index 1)
      if (group.length > 1) {
        salesDistributors.push(...group.slice(1)); // slice(1) mengambil semua kecuali index 0
      }
    });

    return {
      salesDistributors,
      success: true,
      message: "Duplicate Sales Distributor loaded successfully!",
    };
  } catch (error) {
    return {
      success: false,
      message: error.message,
    };
  }
}

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

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

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

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

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

  if (params.enabled) {
    filterConditions.enabled = {
      [Op.eq]: params.enabled === "1" ? true : false,
    };
  }
  try {
    const salesDistributors = await dbSAPPS.SalesDistributor.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Period,
          as: "period",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Distributor,
          as: "distributor",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Item,
          as: "item",
          attributes: ["id", "display_name", "item_cor"],
        },
      ],
    });

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

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

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

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

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

  try {
    const salesDistributors = await dbSAPPS.SalesDistributor.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Period,
          as: "period",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Distributor,
          as: "distributor",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Item,
          as: "item",
          attributes: ["id", "display_name", "item_cor"],
        },
      ],
    });

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

async function option() {
  try {
    const salesDistributors = await dbSAPPS.SalesDistributor.findAll({
      attributes: ["id", "display_name", "code"],
      where: { enabled: true },
      order: [["display_name", "ASC"]],
      include: [
        {
          model: dbSAPPS.City,
          as: "cities",
          attributes: ["id", "display_name", "code"],
          where: { enabled: true },
          required: false,
        },
      ],
    });

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

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

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

async function create(params) {
  try {
    console.log("params", params);
    const existingSales = await dbSAPPS.SalesDistributor.findOne({
      where: {
        no_invoice: params.no_invoice,
        transaction_date: params.transaction_date,
        distributor_id: params.distributor_id,
        company_id: params.company_id,
        territory_id: params.territory_id,
        outlet_id: params.outlet_id,
        period_id: params.period_id,
        item_id: params.item_id,
        price: params.price,
        quantity: params.quantity,
        discount: params.discount,
        total: params.total,
      },
    });

    if (existingSales)
      return {
        success: false,
        message: "Sales data already exists!",
      };

    if (!params.enabled) params.enabled = true;
    const newsalesDistributor = await dbSAPPS.SalesDistributor.create(params);

    return {
      success: true,
      message: "Sales Distributor data has been successfully added!",
      salesDistributor: newsalesDistributor,
    };
  } catch (error) {
    const errorMessage = error.message || "An error occurred during create Sales Distributor processing.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

    const duplicateSalesDistributor = await dbSAPPS.SalesDistributor.findOne({
      where: {
        no_invoice: params.no_invoice,
        transaction_date: params.transaction_date,
        distributor_id: params.distributor_id,
        company_id: params.company_id,
        territory_id: params.territory_id,
        outlet_id: params.outlet_id,
        period_id: params.period_id,
        item_id: params.item_id,
        price: params.price,
        quantity: params.quantity,
        discount: params.discount,
        total: params.total,
      },
    });

    if (duplicateSalesDistributor)
      return {
        success: false,
        message: `Duplicate Sales data found for No Invoice : ${params.no_invoice}`,
      };

    const updateData = {};
    if (params.no_invoice) updateData.no_invoice = params.no_invoice;
    if (params.transaction_date) updateData.transaction_date = params.transaction_date;
    if (params.distributor_id) updateData.distributor_id = params.distributor_id;
    if (params.company_id) updateData.company_id = params.company_id;
    if (params.territory_id) updateData.territory_id = params.territory_id;
    if (params.outlet_id) updateData.outlet_id = params.outlet_id;
    if (params.period_id) updateData.period_id = params.period_id;
    if (params.item_id) updateData.item_id = params.item_id;
    if (params.price) updateData.price = params.price;
    if (params.quantity) updateData.quantity = params.quantity;
    if (params.discount) updateData.discount = params.discount;
    if (params.total) updateData.total = params.total;
    if (params.enabled) updateData.enabled = params.enabled;
    if (params.modified_by) updateData.modified_by = params.modified_by;
    if (params.modified) updateData.modified = params.modified;

    await salesDistributor.update(updateData);

    return {
      success: true,
      message: "Sales Distributor data has been successfully updated!",
      salesDistributor: salesDistributor,
    };
  } catch (error) {
    const errorMessage = error.message || "An error occurred during update Sales Distributor processing.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

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

async function _delete(id) {
  try {
    const salesDistributor = await dbSAPPS.SalesDistributor.findByPk(id);
    if (!salesDistributor) return { success: false, message: "Sales Distributor not found!" };

    await salesDistributor.destroy();

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