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

module.exports = {
  getFromMapping,
  getAllFromMapping,
  getAll,
  getByTerritoriId,
  assignMasterList,
  filter,
  storeToMasterLists,
  storeToMasterList,
  deleteMasterLists,
};

// GET FROM TERRITORY OUTLET, OUTLET CLIENT
// async function markExistingMasterList(data) {
//   const existingData = await dbSAPPS.MasterList.findAll({
//     where: {
//       [Op.or]: data.map((d) => ({
//         territory_id: d.territory_id,
//         outlet_id: d.outlet_id,
//         client_id: d.client_id,
//       })),
//     },
//     attributes: ["territory_id", "outlet_id"],
//   });

//   const existingDataSet = new Set(
//     existingData.map((ml) => `${ml.territory_id}-${ml.outlet_id}-${ml.client_id}`)
//   );

//   data.forEach((d) => {
//     d.exists_in_database = !!existingDataSet.has(
//       `${d.territory_id}-${d.outlet_id}-${d.client_id}`
//     );
//   });

//   return data;
// }

// test
async function markExistingMasterList(data) {
  if (!Array.isArray(data) || data.length === 0) {
    return data;
  }

  const outletIds = data.map((d) => d.outlet_id);
  const clientIds = data.map((d) => d.client_id);
  const territoryIds = data.map((d) => d.territory_id);

  const existData = await dbSAPPS.MasterList.findAll({
    attributes: ["outlet_id", "client_id", "territory_id"],
    where: {
      [Op.and]: [
        { outlet_id: { [Op.in]: outletIds } },
        { client_id: { [Op.in]: clientIds } },
        { territory_id: { [Op.in]: territoryIds } },
      ],
    },
  });

  const existDataSet = new Set(existData.map((oc) => `${oc.outlet_id}-${oc.client_id}-${oc.territory_id}`));

  data.forEach((d) => {
    d.exists_in_database = existDataSet.has(`${d.outlet_id}-${d.client_id}-${d.territory_id}`);
  });

  return data;
}

function getAllIds(arr) {
  let ids = [];
  arr.forEach((item) => {
    // Add id from current object
    ids.push(item.id);
    // If object has children, recursive call to get all ids from children
    if (item?.children && item.children?.length > 0) {
      ids = ids.concat(getAllIds(item?.children));
    }
  });
  return ids;
}

async function getFromMapping(params) {
  try {
    const filterConditions = {};

    let territoryVersionChilds = [];
    let territories;
    if (params && params.territory_id) {
      const today = new Date();
      const getTerritoryVersion = await dbSAPPS.TerritoryVersion.findAll({
        attributes: ["id", "display_name", "valid_from", "valid_to", "enabled"],
        where: {
          [Op.and]: [
            { valid_from: { [Op.lte]: today } },
            { [Op.or]: [{ valid_to: { [Op.gte]: today } }, { valid_to: null }] },
          ],
        },
        include: [
          {
            model: dbSAPPS.TerritoryVersionChild,
            as: "territoryversionchilds",
            attributes: ["id", "name", "parent_id", "territory_id"],
          },
        ],
      });

      if (getTerritoryVersion && getTerritoryVersion.length > 0) {
        territoryVersionChilds = getTerritoryVersion.reduce((acc, curr) => {
          return [...acc, ...curr.territoryversionchilds];
        }, []);
      }
      const hierarchyTerritoryVersionChilds = (parent_id = null) => {
        const children = territoryVersionChilds.filter((c) => c.parent_id === parent_id);
        const hierarchy = children.map((child) => ({
          id: child?.territory_id,
          name: child?.name,
          children: hierarchyTerritoryVersionChilds(child?.territory_id),
        }));
        return hierarchy;
      };

      const territoryDatas = [
        {
          id: parseInt(params?.territory_id),
          children: hierarchyTerritoryVersionChilds(parseInt(params?.territory_id)),
        },
      ];
      territories = getAllIds(territoryDatas && territoryDatas.length > 0 ? territoryDatas : []);
      if (territories.length > 0) {
        filterConditions.territory_id = {
          [Op.in]: territories,
        };
      }
    }

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

    const masterLists = data.reduce((acc, data) => {
      const territoryId = data.territory.id;
      const territoryName = data.territory.display_name;

      data.outlet.outletclients.forEach((outletClient) => {
        const outletId = data.outlet.id;
        const outletName = data.outlet.display_name;
        const clientId = outletClient.client.id;
        const clientName = outletClient.client.display_name;
        const clientPriority = outletClient.client.priority;

        acc.push({
          territory_id: territoryId,
          territory_name: territoryName,
          outlet_id: outletId,
          outlet_name: outletName,
          client_id: clientId,
          client_name: clientName,
          client_priority: clientPriority,
        });
      });

      return acc;
    }, []);

    const cek = await markExistingMasterList(masterLists);

    return {
      masterLists: cek,
      success: true,
      message: "Master list by mapping has been loaded",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load data!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function getAllFromMapping() {
  try {
    const data = await dbSAPPS.TerritoryOutlet.findAll({
      include: [
        {
          model: dbSAPPS.Territory,
          as: "territory",
        },
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
          include: [
            {
              model: dbSAPPS.OutletClient,
              as: "outletclients",
              include: [
                {
                  model: dbSAPPS.Client,
                  as: "client",
                },
              ],
            },
          ],
        },
      ],
      order: [["updatedAt", "DESC"]],
    });
    const returnData = data.reduce((acc, data) => {
      const territoryId = data.territory.id;
      const territoryName = data.territory.display_name;
      const outletClients = data.outlet.outletclients;

      let existingTerritory = acc.find((item) => item.id === territoryId);
      if (!existingTerritory) {
        existingTerritory = {
          id: territoryId,
          territory_name: territoryName,
          outletClients: [],
        };
        acc.push(existingTerritory);
      }

      existingTerritory.outletClients.push(...outletClients);
      return acc;
    }, []);

    // const result = outletClients.reduce((acc, outletClient) => {
    //     const id = outletClient.outlet.id;
    //     const outlet_id = outletClient.outlet.id;
    //     const outlet_name = outletClient.outlet.display_name;
    //     const outlet_code = outletClient.outlet.code;
    //     const client_name = outletClient.client.display_name;
    //     const client_code = outletClient.client.code;

    //     const createdAt = outletClient.createdAt;
    //     const updatedAt = outletClient.updatedAt;

    //     let existingOutlet = acc.find(item => item.outlet_id ===     );
    //     if (!existingOutlet) {
    //         existingOutlet = {
    //             id,
    //             outlet_id,
    //             outlet_name,
    //             outlet_code,
    //             // client_name,
    //             // client_code,
    //             clients: [],
    //             clientsObject: {},
    //             createdAt,
    //             updatedAt
    //         };
    //         acc.push(existingOutlet);
    //     }

    //     existingOutlet.clients.push({ id: client_name, client_name, client_code });
    //     existingOutlet.clientsObject = existingOutlet.clients.reduce((acc, display_name) => {
    //         acc[display_name] = true;
    //         return acc;
    //     }, {});

    //     return acc;
    // }, []);

    return {
      // outletClients: result,
      returnData,
      success: true,
      message: "Outlet clients has been loaded",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load data!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

// GET FROM DATABASE MASTER LIST
async function getAll() {
  try {
    const data = await dbSAPPS.MasterList.findAll({
      include: [
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Client,
          as: "client",
          attributes: ["id", "display_name"],
        },
      ],
      order: [["updatedAt", "DESC"]],
    });

    const returnData = data.map((data) => ({
      id: data.id,
      territory_id: data.territory.id,
      territory_name: data.territory.display_name,
      outlet_id: data.outlet.id,
      outlet_name: data.outlet.display_name,
      client_id: data.client.id,
      client_name: data.client.display_name,
    }));
    return {
      masterLists: returnData,
      success: true,
      message: "Master lists have been loaded",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load data!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function getByTerritoriId(territoryId) {
  try {
    const condition = Array.isArray(territoryId) ? { [Op.in]: territoryId } : { [Op.eq]: territoryId };

    const data = await dbSAPPS.MasterList.findAll({
      where: { territory_id: condition },
      include: [
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Client,
          as: "client",
          attributes: [
            "id",
            "display_name",
            "priority",
            "specialist_id",
            "subspecialist_id",
            "clienttype_id",
            "clientsubtype_id",
            "final_balance",
          ],
        },
      ],
      order: [["updatedAt", "DESC"]],
    });

    const returnData = await Promise.all(
      data.map(async (data) => {
        const specialist = await dbSAPPS.Specialist.findByPk(data.client.specialist_id);
        const subspecialist = await dbSAPPS.SubSpecialist.findByPk(data.client.subspecialist_id);
        const clienttype = await dbSAPPS.ClientType.findByPk(data.client.clienttype_id);
        const clientSubtype = await dbSAPPS.ClientSubtype.findByPk(data.client.clientsubtype_id);
        const bankAccounts = await dbSAPPS.BankAccount.findAll({
          where: { client_id: data.client.id, enabled: true },
          include: [{ model: dbSAPPS.Bank, as: "bank", attributes: ["id", "display_name", "code"] }],
        });
        return {
          id: data.id,
          territory_id: data.territory.id,
          territory_name: data.territory.display_name,
          outlet_id: data.outlet.id,
          outlet_name: data.outlet.display_name,
          client_id: data.client.id,
          client_name: data.client.display_name,
          client_priority: data.client.priority,
          specialist_id: specialist ? specialist.id : null,
          specialist_name: specialist ? specialist.display_name : null,
          clienttype_id: clienttype ? clienttype.id : null,
          clienttype_name: clienttype ? clienttype.display_name : null,
          subspecialist_id: subspecialist ? subspecialist.id : null,
          subspecialist_name: subspecialist ? subspecialist.display_name : null,
          clientsubtype_id: clientSubtype ? clientSubtype.id : null,
          clientsubtype_name: clientSubtype ? clientSubtype.display_name : null,
          bankaccounts: bankAccounts?.length > 0 ? bankAccounts : null,
          saldo: data.client.final_balance,
        };
      })
    );

    const uniqueData = returnData.reduce((acc, current) => {
      const existingIndex = acc.findIndex(
        (item) => item.outlet_id === current.outlet_id && item.client_id === current.client_id
      );
      if (existingIndex === -1) {
        acc.push(current);
      }
      return acc;
    }, []);

    const mappingByOutlet = returnData.reduce((acc, data) => {
      let existingClient = acc.find((item) => item.client_id === data.client_id);
      if (!existingClient) {
        existingClient = {
          client_id: data.client_id,
          client_name: data.client_name,
          client_priority: data.client_priority || "N/A",
          client_type: data.clienttype_name || "N/A",
          specialist: data.specialist_name || "N/A",
          subspecialist: data.subspecialist_name || "N/A",
          clientsubtype: data.clientsubtype_name || "N/A",
          outlets: [],
        };
        acc.push(existingClient);
      }
      if (!existingClient.outlets.find((outlet) => outlet.id === data.outlet_id)) {
        existingClient.outlets.push({
          id: data.outlet_id,
          name: data.outlet_name,
        });
      }
      return acc;
    }, []);

    return {
      masterLists: returnData,
      uniqueData,
      mappingByOutlet, // call plan
      success: true,
      message: "Master lists have been loaded",
    };
  } catch (error) {
    return {
      success: false,
      message: error.message || "Failed to load data!",
    };
  }
}

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

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

  try {
    const outletClients = await dbSAPPS.OutletClient.findAll({
      include: [
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
        },
        {
          model: dbSAPPS.Client,
          as: "client",
        },
      ],
      where: filterConditions,
      order: [["updatedAt", "DESC"]],
    });

    const result = outletClients.reduce((acc, outletClient) => {
      const id = outletClient.outlet.id;
      const outlet_id = outletClient.outlet.id;
      const outlet_name = outletClient.outlet.display_name;
      const outlet_code = outletClient.outlet.code;
      const client_name = outletClient.client.display_name;
      const client_code = outletClient.client.code;

      const createdAt = outletClient.createdAt;
      const updatedAt = outletClient.updatedAt;

      let existingOutlet = acc.find((item) => item.outlet_id === outlet_id);
      if (!existingOutlet) {
        existingOutlet = {
          id,
          outlet_id,
          outlet_name,
          outlet_code,
          // client_name,
          // client_code,
          clients: [],
          clientsObject: {},
          createdAt,
          updatedAt,
        };
        acc.push(existingOutlet);
      }

      existingOutlet.clients.push({ id: client_name, client_name, client_code });
      existingOutlet.clientsObject = existingOutlet.clients.reduce((acc, display_name) => {
        acc[display_name] = true;
        return acc;
      }, {});

      return acc;
    }, []);

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

async function assignMasterList(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const { territoryId, lastUsedTerritoryId, outletId, clientId, update, id } = params;
    if (!territoryId || !outletId || !clientId) {
      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 outlet = await dbSAPPS.Outlet.findByPk(outletId, { transaction });
    if (!outlet) {
      await transaction.rollback();
      return { success: false, message: "Outlet not found!" };
    }

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

    // Mode edit/update: update record by id agar tidak membuat record baru
    if (update === true && id) {
      const masterListToUpdate = await dbSAPPS.MasterList.findByPk(id, { transaction });
      if (!masterListToUpdate) {
        await transaction.rollback();
        return { success: false, message: "Master list not found!" };
      }
      await masterListToUpdate.update(
        { territory_id: territoryId, outlet_id: outletId, client_id: clientId },
        { transaction }
      );
      await transaction.commit();
      return {
        success: true,
        message: "Master list have been saved !",
      };
    }

    // fungsi untuk auto create data ke territory ↔ outlet
    // const today = new Date();
    // const validFrom = today.toISOString().slice(0, 10); // hasil: '2025-08-25'
    // const territoryOutlet = await dbSAPPS.TerritoryOutlet.findOne({
    //   where: { territory_id: territoryId, outlet_id: outletId },
    //   transaction,
    // });

    // if (territoryOutlet) {
    //   // kalau sudah ada, update relasi aja, jangan ubah valid_from & valid_to
    //   await territoryOutlet.update(
    //     {
    //       territory_id: territoryId,
    //       outlet_id: outletId,
    //     },
    //     { transaction }
    //   );
    // } else {
    //   // kalau belum ada → set valid_from hari ini, valid_to null
    //   await dbSAPPS.TerritoryOutlet.create(
    //     {
    //       territory_id: territoryId,
    //       outlet_id: outletId,
    //       valid_from: validFrom, // isi hari ini
    //       valid_to: null, // biarkan kosong
    //     },
    //     { transaction }
    //   );
    // }

    // const outletClient = await dbSAPPS.OutletClient.findOne({ where: { outlet_id: outletId, client_id: clientId } });
    // if (outletClient) {
    //   outletClient.outlet_id = outletId;
    //   outletClient.client_id = clientId;
    //   await outletClient.save({ transaction });
    // } else {
    //   await dbSAPPS.OutletClient.create({ outlet_id: outletId, client_id: clientId }, { transaction });
    // }

    const masterList = await dbSAPPS.MasterList.findOne({
      where: { territory_id: territoryId, outlet_id: outletId, client_id: clientId },
      transaction
    });
    if (!masterList) {
      await dbSAPPS.MasterList.create(
        { territory_id: territoryId, outlet_id: outletId, client_id: clientId },
        { transaction }
      );
    } else {
      await masterList.update({ territory_id: territoryId, outlet_id: outletId, client_id: clientId }, { transaction });
    }

    await transaction.commit();
    return {
      success: true,
      message: "Master list have been saved !",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error.message || "Failed to assign master list";
    return { success: false, message: errorMessage };
  }
}

async function deleteMasterLists(id) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const masterList = await dbSAPPS.MasterList.findByPk(id, { transaction });
    if (!masterList) {
      await transaction.rollback();
      return { success: false, message: "Master list not found!" };
    }

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

    const outlet = await dbSAPPS.Outlet.findByPk(masterList.outlet_id, { transaction });
    if (!outlet) {
      await transaction.rollback();
      return { success: false, message: "Outlet not found!" };
    }

    const client = await dbSAPPS.Client.findByPk(masterList.client_id, { transaction });
    if (!client) {
      await transaction.rollback();
      return { success: false, message: "Client not found!" };
    }

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

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

// generate master list
//  banyak data / all
async function storeToMasterLists(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    if (!Array.isArray(params) || params.length === 0) {
      return {
        success: false,
        message: "No parameters provided for import.",
      };
    }

    const existingData = await dbSAPPS.MasterList.findAll({
      where: {
        [Op.or]: params.map((param) => ({
          territory_id: param.territory_id,
          outlet_id: param.outlet_id,
          client_id: param.client_id,
        })),
      },
      transaction,
    });

    if (existingData.length > 0) {
      return {
        success: false,
        message: `Data already exists in MasterList table. ${existingData.length} records found.`,
      };
    }

    const uniqueKeys = new Set();
    const duplicateParams = [];

    for (const param of params) {
      const key = `${param.territory_id}-${param.outlet_id}-${param.client_id}`;
      if (uniqueKeys.has(key)) {
        duplicateParams.push(param);
      } else {
        uniqueKeys.add(key);
      }
    }

    if (duplicateParams.length > 0) {
      return {
        success: false,
        message: `Duplicate records found based on territory_id, outlet_id, and client_id. ${duplicateParams.length} duplicates identified.`,
        duplicates: duplicateParams,
      };
    }

    const newParamsArray = params.map((param) => ({
      territory_id: param.territory_id,
      outlet_id: param.outlet_id,
      client_id: param.client_id,
    }));

    if (newParamsArray.length > 0) {
      const result = await dbSAPPS.MasterList.bulkCreate(newParamsArray, { transaction });
      console.log(`${result.length} new data have been inserted.`);
    }

    await transaction.commit();
    return {
      newdata: newParamsArray,
      success: true,
      message:
        newParamsArray.length > 0 ? `${newParamsArray.length} new data have been imported!` : "No new data to import.",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error.message || "Error during import all data!";
    console.error(errorMessage);
    return {
      success: false,
      message: errorMessage,
    };
  }
}

//  satuan / dua
async function storeToMasterList(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const existingData = await dbSAPPS.MasterList.findOne({
      where: {
        territory_id: params.territory_id,
        outlet_id: params.outlet_id,
        client_id: params.client_id,
      },
      transaction,
    });

    if (existingData) {
      return {
        success: false,
        message: "Data already exists in MasterList table.",
      };
    }

    const newParams = {
      territory_id: params.territory_id,
      outlet_id: params.outlet_id,
      client_id: params.client_id,
    };

    const result = await dbSAPPS.MasterList.create(newParams, { transaction });
    console.log(`new data have been inserted.`);

    await transaction.commit();
    return {
      success: true,
      message: `new data have been inserted!`,
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error.message || "Error during import master list!";
    console.error(errorMessage);
    return {
      success: false,
      message: errorMessage,
    };
  }
}
