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

module.exports = {
  filter,
  assignOutletsToTerritory,
  getAllTerritoryOutlet,
  getClientOutletByTerritories,
  getByTerritoryIdPeriodId,
  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}`,
    };
  }

  const outletNameTrimmed = params.outlet_name && String(params.outlet_name).trim();
  if (outletNameTrimmed) {
    const matchingRows = await dbSAPPS.TerritoryOutlet.findAll({
      include: [
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
          where: {
            display_name: { [Op.like]: `%${outletNameTrimmed}%` },
          },
          required: true,
        },
      ],
      attributes: ["territory_id"],
      raw: false,
    });
    const territoryIds = [...new Set(matchingRows.map((r) => r.territory_id))];
    if (territoryIds.length === 0) {
      return {
        territoryOutlets: [],
        success: true,
        message: "Territory outlets has been loaded",
      };
    }
    filterConditions.territory_id = { [Op.in]: territoryIds };
  }

  const outletInclude = {
    model: dbSAPPS.Outlet,
    as: "outlet",
  };

  try {
    const territoryOutlets = await dbSAPPS.TerritoryOutlet.findAll({
      include: [
        {
          model: dbSAPPS.Territory,
          as: "territory",
        },
        outletInclude,
      ],
      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_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,
          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((accOut, outlet) => {
        accOut[outlet.outlet_name] = true;
        return accOut;
      }, {});

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

  // update 20260213 ini masih error
  //  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_name ${outlet.outlet_name}`);
  //       }
  //       // 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 getByTerritoryIdPeriodId(params) {
  try {
    // console.log('params territory : ', params);
    const territoryId = params.territory_id;
    const period = await dbSAPPS.Period.findByPk(params.period_id);
    if (!period) {
      return {
        success: false,
        message: "Period not found!",
      };
    }

    const start_date = period.start_date;
    const end_date = period.end_date;

    const data = await dbSAPPS.TerritoryOutlet.findAll({
      where: {
        territory_id: territoryId,
        valid_from: {
          [Op.lte]: start_date,
        },
        [Op.or]: [
          {
            valid_to: {
              [Op.gte]: end_date,
            },
          },
          {
            valid_to: null,
          },
        ],
      },
      include: [
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
          attributes: ["id", "display_name", "code"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name", "code"],
        },
      ],
    });

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