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

module.exports = {
  filter,
  assignOutletsToTerritory,
  getAllTerritoryOutlet,
  getClientOutletByTerritories,
  deleteTerritoryOutlet,
};

async function getOutletsIdsByName(names) {
  const outlets = await dbSAPPS.Outlet.findAll({
    where: {
      display_name: names,
    },
    attributes: ["id"],
  });

  return outlets.map((outlet) => outlet.id);
}

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

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

  try {
    const territoryOutlets = await dbSAPPS.TerritoryOutlet.findAll({
      include: [
        {
          model: dbSAPPS.Territory,
          as: "territory",
        },
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
        },
      ],
      where: filterConditions,
      order: [["updatedAt", "DESC"]],
    });

    const result = territoryOutlets.reduce((acc, territoryOutlet) => {
      const id = territoryOutlet.territory.id;
      const territory_id = territoryOutlet.territory.id;
      const territory_name = territoryOutlet.territory.display_name;
      const territory_code = territoryOutlet.territory.code;
      const outlet_name = territoryOutlet.outlet.display_name;
      const outlet_code = territoryOutlet.outlet.code;

      const createdAt = territoryOutlet.createdAt;
      const updatedAt = territoryOutlet.updatedAt;

      let existingTerritory = acc.find((item) => item.territory_id === territory_id);
      if (!existingTerritory) {
        existingTerritory = {
          id,
          territory_id,
          territory_name,
          territory_code,
          // outlet_name,
          // outlet_code,
          // outlet_id,
          outlets: [],
          outletsObject: {},
          createdAt,
          updatedAt,
        };
        acc.push(existingTerritory);
      }

      existingTerritory.outlets.push({ id: outlet_id, outlet_name, outlet_code });
      existingTerritory.outletsObject = existingTerritory.outlets.reduce((acc, display_name) => {
        acc[display_name] = true;
        return acc;
      }, {});

      return acc;
    }, []);

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

// backup
// async function assignOutletsToTerritory(params) {
//   const transaction = await dbSAPPS.sequelize.transaction();
//   try {
//     const { territoryId, lastUsedTerritoryId, outletData, update } = params;
//     if (!territoryId || !Array.isArray(outletData) || outletData.length === 0) {
//       return {
//         success: false,
//         message: "Invalid input. Please ensure that all required fields are provided in the correct format.",
//       };
//     }

//     const territory = await dbSAPPS.Territory.findByPk(territoryId, { transaction });
//     if (!territory) {
//       await transaction.rollback();
//       return { success: false, message: "Territory not found!" };
//     }

//     const territoryOutlet = await dbSAPPS.TerritoryOutlet.findOne({ where: { territory_id: territoryId } });
//     if (territoryOutlet && update === false) {
//       await transaction.rollback();
//       return {
//         success: false,
//         message: "Territory is already assigned to a outlet, please update the assignment if necessary.",
//       };
//     }

//     if (lastUsedTerritoryId && update === true) {
//       const lastUse = await dbSAPPS.TerritoryOutlet.findOne({ where: { territory_id: lastUsedTerritoryId } });
//       if (lastUse) {
//         await lastUse.destroy({ transaction });
//       }
//     }

//     const outletIds = await getOutletsIdsByName(outletData);

//     await territory.setOutlets(outletIds, { transaction });
//     await transaction.commit();
//     return {
//       success: true,
//       message: "Outlets have been assigned to the territory!",
//     };
//   } catch (error) {
//     await transaction.rollback();
//     const errorMessage = error.message || "Failed to assign outlets to the territory.";
//     return { success: false, message: errorMessage };
//   }
// }

// update
async function assignOutletsToTerritory(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const { territoryId, lastUsedTerritoryId, outletData, update } = params;

    if (!territoryId || !Array.isArray(outletData) || outletData.length === 0) {
      return {
        success: false,
        message: "Invalid input. Please ensure that all required fields are provided in the correct format.",
      };
    }

    const territory = await dbSAPPS.Territory.findByPk(territoryId, { transaction });
    if (!territory) {
      await transaction.rollback();
      return { success: false, message: "Territory not found!" };
    }

    const territoryOutlet = await dbSAPPS.TerritoryOutlet.findOne({
      where: { territory_id: territoryId },
      transaction,
    });

    if (territoryOutlet && update === false) {
      await transaction.rollback();
      return {
        success: false,
        message: "Territory is already assigned to an outlet, please update the assignment if necessary.",
      };
    }

    // kalau ada lastUsedTerritoryId dan update true → hapus relasi lama
    if (lastUsedTerritoryId && update === true) {
      await dbSAPPS.TerritoryOutlet.destroy({
        where: { territory_id: lastUsedTerritoryId },
        transaction,
      });
    }

    // hapus dulu semua relasi territory lama biar clean (kalau update)
    if (update === true) {
      await dbSAPPS.TerritoryOutlet.destroy({
        where: { territory_id: territoryId },
        transaction,
      });
    }

    // Validasi awal: pastikan semua outlet memiliki valid_from
    for (const outlet of outletData) {
      if (!outlet.valid_from) {
        throw new Error(`valid_from must be sent for outlet_id ${outlet.id}`);
      }
      // Validasi format valid_from
      const validFromMoment = moment(outlet.valid_from, "YYYY-MM-DD", true);
      if (!validFromMoment.isValid()) {
        throw new Error(`Invalid valid_from format for outlet_id ${outlet.id}. Expected format: YYYY-MM-DD, got: ${outlet.valid_from}`);
      }
    }

    // cek dan update valid_to di territory lama jika ada perpindahan outlet dengan itemGroup yang sama
    const newTerritoryItemGroups = await dbSAPPS.TerritoryItemGroup.findAll({
      where: { territory_id: territoryId },
      transaction,
    });
    const newItemGroupIds = newTerritoryItemGroups.map((ig) => ig.item_group_id);

    for (const outlet of outletData) {
      // cari semua relasi lama outlet di territory lain
      const existingAssignments = await dbSAPPS.TerritoryOutlet.findAll({
        where: { outlet_id: outlet.id },
        include: [
          {
            model: dbSAPPS.Territory,
            as: "territory",
            include: [
              {
                model: dbSAPPS.TerritoryItemGroup,
                as: "territoryitemgroups",
              },
            ],
          },
        ],
        transaction,
      });

      for (const assign of existingAssignments) {
        // skip kalau territory lama = territory baru (biar nggak tabrakan update sendiri)
        if (assign.territory_id === territoryId) continue;

        // ambil semua itemGroup territory lama
        const oldTerritoryGroups = assign.territory.territoryitemgroups.map((g) => g.item_group_id);

        // cek apakah ada irisan itemGroup antara territory lama & territory baru
        const hasSameGroup = oldTerritoryGroups.some((id) => newItemGroupIds.includes(id));

        if (hasSameGroup) {
          // Parse valid_from dengan format eksplisit untuk menghindari masalah timezone
          // Gunakan moment dengan format 'YYYY-MM-DD' untuk memastikan parsing yang benar
          const validFromMoment = moment(outlet.valid_from, "YYYY-MM-DD", true);
          
          // Subtract 1 day dan format tanpa timezone conversion
          // Clone moment object untuk menghindari mutasi
          const validToDate = moment(validFromMoment).subtract(1, "days").format("YYYY-MM-DD");
          
          await dbSAPPS.TerritoryOutlet.update(
            {
              valid_to: validToDate,
            },
            {
              where: {
                territory_id: assign.territory_id,
                outlet_id: assign.outlet_id,
                valid_to: null, // update hanya active data
              },
              transaction,
            }
          );
        }
      }
    }

    // Validasi dan normalisasi format tanggal sebelum insert
    const normalizedOutletData = outletData.map((outlet) => {
      // Validasi valid_from
      if (!outlet.valid_from) {
        throw new Error(`valid_from must be sent for outlet_id ${outlet.id}`);
      }
      
      // Validasi format valid_from (harus YYYY-MM-DD)
      const validFromMoment = moment(outlet.valid_from, "YYYY-MM-DD", true);
      if (!validFromMoment.isValid()) {
        throw new Error(`Invalid valid_from format for outlet_id ${outlet.id}. Expected format: YYYY-MM-DD, got: ${outlet.valid_from}`);
      }

      
      
      // Normalisasi valid_from ke format YYYY-MM-DD
      const normalizedValidFrom = validFromMoment.format("YYYY-MM-DD");
      
      // Validasi dan normalisasi valid_to jika ada
      let normalizedValidTo = null;
      if (outlet.valid_to) {
        const validToMoment = moment(outlet.valid_to, "YYYY-MM-DD", true);
        if (!validToMoment.isValid()) {
          throw new Error(`Invalid valid_to format for outlet_id ${outlet.id}. Expected format: YYYY-MM-DD, got: ${outlet.valid_to}`);
        }
        normalizedValidTo = validToMoment.format("YYYY-MM-DD");
      }
      
      return {
        territory_id: territoryId,
        outlet_id: outlet.id,
        valid_from: normalizedValidFrom,
        valid_to: normalizedValidTo,
      };
    });

    // insert baru dengan valid_from & valid_to yang sudah dinormalisasi
    await dbSAPPS.TerritoryOutlet.bulkCreate(
      normalizedOutletData,
      {
        updateOnDuplicate: ["valid_from", "valid_to"], // kalau sudah ada, update field ini
        transaction,
      }
    );

    await transaction.commit();
    return {
      success: true,
      message: "Outlets have been assigned to the territory with valid_from and valid_to!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error.message || "Failed to assign outlets to the territory.";
    return { success: false, message: errorMessage };
  }
}

async function getAllTerritoryOutlet() {
  try {
    const territoryOutlets = await dbSAPPS.TerritoryOutlet.findAll({
      include: [
        {
          model: dbSAPPS.Territory,
          as: "territory",
        },
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
        },
      ],
      order: [["updatedAt", "DESC"]],
    });

    const result = territoryOutlets.reduce((acc, territoryOutlet) => {
      const id = territoryOutlet.territory.id;
      const territory_id = territoryOutlet.territory.id;
      const territory_name = territoryOutlet.territory.display_name;
      const territory_code = territoryOutlet.territory.code;
      const outlet_id = territoryOutlet.outlet.id;
      const outlet_name = territoryOutlet.outlet.display_name;
      const outlet_code = territoryOutlet.outlet.code;
      const valid_from = territoryOutlet.valid_from;
      const valid_to = territoryOutlet.valid_to;

      const createdAt = territoryOutlet.createdAt;
      const updatedAt = territoryOutlet.updatedAt;

      let existingTerritory = acc.find((item) => item.territory_id === territory_id);
      if (!existingTerritory) {
        existingTerritory = {
          id,
          territory_id,
          territory_name,
          territory_code,
          // outlet_name,
          // outlet_code,
          // outlet_id,
          outlets: [],
          outletsObject: {},
          createdAt,
          updatedAt,
        };
        acc.push(existingTerritory);
      }

      existingTerritory.outlets.push({ id: outlet_id, outlet_name, outlet_code, valid_from, valid_to });
      existingTerritory.outletsObject = existingTerritory.outlets.reduce((acc, display_name) => {
        acc[display_name] = true;
        return acc;
      }, {});

      return acc;
    }, []);

    return {
      territoryOutlets: result,
      success: true,
      message: "Territory outlets has been loaded",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load outlets!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function getClientOutletByTerritories(params) {
  try {
    const territoryIds = params.params;

    const data = await dbSAPPS.TerritoryOutlet.findAll({
      where: {
        territory_id: {
          [Op.in]: territoryIds,
        },
      },
      include: [
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
          include: [
            {
              model: dbSAPPS.Client,
              as: "clients",
              include: [
                {
                  model: dbSAPPS.ClientType,
                  as: "clienttype",
                  attributes: ["display_name"],
                },
                {
                  model: dbSAPPS.Specialist,
                  as: "specialist",
                },
                {
                  model: dbSAPPS.SubSpecialist,
                  as: "subspecialist",
                },
                {
                  model: dbSAPPS.ClientSubtype,
                  as: "clientsubtype",
                },
              ],
            },
          ],
        },
      ],
    });

    const dataByTerritory = data.reduce((acc, territoryOutlet) => {
      const clientIds = [...new Set(territoryOutlet.outlet.clients.map((client) => client.id))];

      clientIds.forEach((client_id) => {
        let existingClient = acc.find((item) => item.client_id === client_id);
        if (!existingClient) {
          existingClient = {
            client_id,
            client_name: territoryOutlet.outlet.clients.find((client) => client.id === client_id)?.display_name,
            client_priority:
              territoryOutlet.outlet.clients.find((client) => client.id === client_id)?.priority || "N/A",
            client_type:
              territoryOutlet.outlet.clients.find((client) => client.id === client_id).clienttype?.display_name ||
              "N/A",
            specialist:
              territoryOutlet.outlet.clients.find((client) => client.id === client_id).specialist?.display_name ||
              "N/A",
            subspecialist:
              territoryOutlet.outlet.clients.find((client) => client.id === client_id).subspecialist?.display_name ||
              "N/A",
            clientsubtype:
              territoryOutlet.outlet.clients.find((client) => client.id === client_id).clientsubtype?.display_name ||
              "N/A",
            outlets: [],
          };
          acc.push(existingClient);
        }
        if (!existingClient.outlets.find((outlet) => outlet.id === territoryOutlet.outlet.id)) {
          existingClient.outlets.push({
            id: territoryOutlet.outlet.id,
            name: territoryOutlet.outlet.display_name,
          });
        }
      });

      return acc;
    }, []);

    return {
      dataByTerritory,
      success: true,
      message: "Data has been loaded successfully",
    };
  } catch (error) {
    console.error("Error fetching data:", error);
    throw error;
  }
}

async function deleteTerritoryOutlet(id) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const territory = await dbSAPPS.Territory.findByPk(id, { transaction });
    if (!territory) {
      await transaction.rollback();
      return { success: false, message: "Territory not found!" };
    }

    const territoryOutlet = await dbSAPPS.TerritoryOutlet.findAll({
      where: { territory_id: id },
      transaction,
    });

    if (territoryOutlet.length === 0) {
      await transaction.rollback();
      return {
        success: false,
        message: "Territory has no associated outlet, so it was not deleted.",
      };
    }

    await dbSAPPS.TerritoryOutlet.destroy({
      where: { territory_id: id },
      transaction,
    });

    await transaction.commit();
    return {
      success: true,
      message: "Territory and outlets has been deleted!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error.message || "Failed to delete Territory and outlets.";
    return { success: false, message: errorMessage };
  }
}
