const sequelize = require("../config/dbconnection");
const { empty } = require("../Helpers/Common");
const Community = require("../models/community");
const Admin = require("../models/admin");
const client=require("../models/client")
const Client = require("../models/client");
const Teams = require("../models/teams");
const moment = require("moment");

const addCommunity = async (request, reply) => {
  try {
    let currentDate = Math.round(moment().valueOf() / 1000);
    let communityData = {};
    communityData.name = request.body.values.name;
    const formattedDescription = (request.body.values.description || '').replace(/\r?\n/g, '\n');
    communityData.description = formattedDescription;
    communityData.description_lines = formattedDescription ? formattedDescription.split('\n') : [];
    // communityData.type = request.body.values.type;
    communityData.type = request.body.values.type || null;
    communityData.phone = request.body.values.phone || null;
    communityData.email = request.body.values.email || null;
    // PO details will be stored separately, poNumber will contain comma-separated PO detail IDs
    communityData.poNumber = null; // Will be updated after creating PO details
    // if(request.body.values.showAddress==true){
    communityData.address = request.body.values.address || null;
    communityData.city = request.body.values.city || null;
    communityData.state = request.body.values.state || null;
    communityData.zip = request.body.values.zip || null;
    // }
    communityData.thumbnail = request.body.values.avatarUrl || null;
    communityData.clients=request.body.values.clientData;
    communityData.added_by = request.body.userId;
    communityData.status = 1;
    communityData.timestamp = currentDate;
    communityData.tax_category = request.body.values.taxCategory || null;
    communityData.show_address = request.body.values.showAddress || false;
    const salesRepNames = request.body.values.salesRepNames?.map(rep => rep.id)?.join(',') || null;
    communityData.sales_rep_names = salesRepNames;


    console.log(" : ",communityData);

    let communityNameExist = await Community.isCommunityNameExist(communityData.name,communityData.type);

    if (communityNameExist != true) {
      try {
        let result = await Community.addCommunity(communityData);
        if (result) {
          // Handle PO details
          let poDetailIds = [];
          if (request.body.values.poDetails && Array.isArray(request.body.values.poDetails) && request.body.values.poDetails.length > 0) {
            for (const poDetail of request.body.values.poDetails) {
              if (poDetail.po_number && poDetail.po_number.trim()) {
                // Check if PO number already exists for this community
                const poExists = await Community.isPoNumberExistForCommunity(poDetail.po_number.trim(), result.id);
                if (!poExists) {
                  const poData = {
                    community_id: result.id,
                    po_number: poDetail.po_number.trim(),
                    po_name: poDetail.po_name ? poDetail.po_name.trim() : null,
                    status: 1,
                    timestamp: currentDate
                  };
                  const poResult = await Community.addPoDetails(poData);
                  if (poResult) {
                    poDetailIds.push(poResult.id);
                  }
                } else {
                  // If PO already exists, get its ID and include it
                  const existingPoDetails = await Community.getPoDetailsByCommunityId(result.id);
                  const existingPo = existingPoDetails.find(po => po.po_number === poDetail.po_number.trim());
                  if (existingPo) {
                    poDetailIds.push(existingPo.id);
                  }
                }
              }
            }
          }

          // Update community with PO detail IDs
          if (poDetailIds.length > 0) {
            await Community.updateCommunity(result.id, { poNumber: poDetailIds.join(',') });
          } else {
            // Clear poNumber if no PO details
            await Community.updateCommunity(result.id, { poNumber: null });
          }

          reply.send({ status: 1, message: `Project ${communityData.name} added successfully` });

          // Notification
          let notificationMsg = `New Project "${communityData.name}" has been added.`;
          let notificationUrl = "community";

          // Alert to user

          const incrementResult_User = await Admin.incrementField( communityData.added_by, "noti_count", 1 );
          let notificationData_user = {
            user_id: communityData.added_by,
            url: notificationUrl,
            timestamp: currentDate,
            message: notificationMsg,
            status: 1,
            from_id: communityData.added_by,
          };
          const addNotificationResult_user = await Admin.addNotification( notificationData_user );

          // Alert to all admins

          let adminIds = await Admin.getAllAdminIds();
          let notificationData = {};
          for (const adminId of adminIds) {
            if (adminId != communityData.added_by) {
              const incrementResult = await Admin.incrementField( adminId,"noti_count",1 );
              notificationData = {
                user_id: adminId,
                url: notificationUrl,
                timestamp: currentDate,
                message: notificationMsg,
                status: 1,
                from_id: communityData.added_by,
              };
              const addNotificationResult = await Admin.addNotification( notificationData );
            }
          }
        } else {
            console.log("Failed to add the community ");
            reply.send({ status: 0, message: "Failed to add the Project" });
        }
      } catch (error) {
        console.log("Error adding community : ", error);
        reply.send({ status: 0, message: "Failed to add the Project" });
      }
    } else {
      console.log("The Project already exist!");
      reply.send({ status: 2, message: `Project name ${communityData.name} already exists!` });
    }
  } catch (error) {
    console.log("Server error", error);
    reply.status(500).send({ message: "Server error" });
  }
};

const getAllCommunities = async (request, reply) => {
  try {
    const {sort,filterClient, filterSalesRep, clientId, userType, userId} =request.body
    let results = await Community.getAllCommunities(true,sort,filterClient, filterSalesRep, clientId, userType);
        let clients = await client.getAllClientsDataFilters();
    
    console.log("getAllCommunities..length...", results.length);
    if (!empty(results)) {
      reply.send({ status: 1, results,clients, message: "Data fetched successfully" });
    } else {
      reply.send({ status: 0, results, message: "Error fetching data" });
    }
  } catch (error) {
    console.log("Server error",error);
    reply.status(500).send("Server error");
  }
};

const getCommunityById = async (request, reply) => {
  try {
    let { editId } = request.body;
    let communityData = await Community.getCommunityById(editId);
    let communityType = await Community.getAllCommunityTypes()
    let clients=await client.getAllClients(true,userType=1)
    clients=clients.data
    // console.log("communityData************************", communityData);
    if (communityData) {
      reply.send({ status: 1, result: communityData,clients,communityType: communityType });
    } else {
      reply.send({ status: 0, message: "Project not found" });
    }
  } catch (error) {
    console.log(error);
    reply.status(500).send("Server error");
  }
};

const updateCommunity = async (request, reply) => {
  try {
    let currentDate = Math.round(moment().valueOf() / 1000);
    let communityData = {};
    let id = request.body.editId;
    communityData.name = request.body.values.name;
    const formattedDescription = (request.body.values.description || '').replace(/\r?\n/g, '\n');
    communityData.description = formattedDescription;
    communityData.description_lines = formattedDescription ? formattedDescription.split('\n') : [];
    // communityData.type = request.body.values.type;
    communityData.type = null;
    communityData.phone = request.body.values.phone || null;
    communityData.email = request.body.values.email || null;
    // PO details will be handled separately, poNumber will contain comma-separated PO detail IDs
    communityData.poNumber = null; // Will be updated after handling PO details
    communityData.lat = null;
    communityData.lng = null;
    // if(request.body.values.showAddress){
    communityData.address = request.body.values.address || null;
    communityData.city = request.body.values.city || null;
    communityData.state = request.body.values.state || null;
    communityData.zip = request.body.values.zip || null;
    // }
    communityData.thumbnail = request.body.values.avatarUrl || null;
    communityData.clients=request.body.values.clientData;
    communityData.tax_category = request.body?.values?.taxCategory || null;
    communityData.show_address = request.body.values.showAddress || false;
    const salesRepNames = request.body.values.salesRepNames?.map(rep => rep.id)?.join(',') || null;
    console.log("salesRepNames",request.body.values.salesRepNames);
    communityData.sales_rep_names = salesRepNames;


    // communityData.status = 1;

    let communityNameExist = await Community.isCommunityNameExist(communityData.name,communityData.type,id);
    // console.log("isCommunityNameExist : ", isCommunityNameExist);

    if (communityNameExist != true) {
      try {
        let updated = await Community.updateCommunity(id, communityData);
        if (updated) {
          // Handle PO details
          let poDetailIds = [];

          // Get existing PO details for this community
          const existingPoDetails = await Community.getPoDetailsByCommunityId(id);

          // Handle PO details updates
          if (request.body.values.poDetails && Array.isArray(request.body.values.poDetails) && request.body.values.poDetails.length > 0) {
            for (const poDetail of request.body.values.poDetails) {
              if (poDetail.id) {
                // Update existing PO detail
                const existingPo = existingPoDetails.find(po => po.id === poDetail.id);
                if (existingPo) {
                  if (poDetail.po_number && poDetail.po_number.trim()) {
                    // Check if PO number conflicts with other PO details for this community
                    const poExists = await Community.isPoNumberExistForCommunity(poDetail.po_number.trim(), id, poDetail.id);
                    if (!poExists) {
                      const updateResult = await Community.updatePoDetails(poDetail.id, {
                        po_number: poDetail.po_number.trim(),
                        po_name: poDetail.po_name ? poDetail.po_name.trim() : null
                      });
                      if (updateResult) {
                        poDetailIds.push(poDetail.id);
                      }
                    } else {
                      // Keep the existing ID if update failed due to conflict
                      poDetailIds.push(poDetail.id);
                    }
                  } else {
                    // Mark for deletion if PO number is empty
                    await Community.deletePoDetails(poDetail.id);
                  }
                }
              } else {
                // Add new PO detail
                if (poDetail.po_number && poDetail.po_number.trim()) {
                  const poExists = await Community.isPoNumberExistForCommunity(poDetail.po_number.trim(), id);
                  if (!poExists) {
                    const poData = {
                      community_id: id,
                      po_number: poDetail.po_number.trim(),
                      po_name: poDetail.po_name ? poDetail.po_name.trim() : null,
                      status: 1,
                      timestamp: currentDate
                    };
                    const poResult = await Community.addPoDetails(poData);
                    if (poResult) {
                      poDetailIds.push(poResult.id);
                    }
                  } else {
                    // If PO already exists, get its ID and include it
                    const existingPo = existingPoDetails.find(po => po.po_number === poDetail.po_number.trim());
                    if (existingPo) {
                      poDetailIds.push(existingPo.id);
                    }
                  }
                }
              }
            }
          }

          // Mark remaining existing PO details for deletion (those not in the updated list)
          const updatedPoIds = request.body.values.poDetails?.map(po => po.id).filter(id => id) || [];
          for (const existingPo of existingPoDetails) {
            if (!updatedPoIds.includes(existingPo.id)) {
              await Community.deletePoDetails(existingPo.id);
            } else if (!poDetailIds.includes(existingPo.id)) {
              // Include existing PO details that weren't processed above
              poDetailIds.push(existingPo.id);
            }
          }

          // Update community with PO detail IDs
          if (poDetailIds.length > 0) {
            await Community.updateCommunity(id, { poNumber: poDetailIds.join(',') });
          } else {
            // Clear poNumber if no PO details
            await Community.updateCommunity(id, { poNumber: null });
          }

          reply.send({ status: 1, message: "Project has been successfully updated!", });

          // Notification
          let notificationMsg = `The Project "${communityData.name}" has been updated.`;
          let notificationUrl = "community";

          // Alert to user of updation

          const incrementResult_User = await Admin.incrementField( communityData.added_by, "noti_count", 1 );

          let notificationData_user = {
            user_id: communityData.added_by,
            url: notificationUrl,
            timestamp: currentDate,
            message: notificationMsg,
            status: 1,
            from_id: communityData.added_by,
          };
          
          const addNotificationResult_user = await Admin.addNotification( notificationData_user );

          // Alert to all admins of updation

          let adminIds = await Admin.getAllAdminIds();
          let notificationData = {};
          for (const adminId of adminIds) {
            if (adminId != communityData.added_by) {
              const incrementResult = await Admin.incrementField( adminId, "noti_count", 1 );
              notificationData = {
                user_id: adminId,
                url: notificationUrl,
                timestamp: currentDate,
                message: notificationMsg,
                status: 1,
                from_id: communityData.added_by,
              };
              const addNotificationResult = await Admin.addNotification( notificationData );
            }
          }
        } else {
            console.log("Failed to update the Project ");
          reply.send({ status: 0, message: "Failed to update the community" });
        }
      } catch (error) {
        console.log("Error updating the Project : ", error);
        reply.send({ status: 3, message: "Error updating the community" });
      }
    } else {
      console.log("The Project already exist!");
      reply.send({ status: 2, message: "The Project already exist!" });
    }
  } catch (error) {
    console.log(error);
    reply.send({ status: 500, message: "Server error" });
  }
};

const saveProjectOrder = async (req, res) => {
  try {
    const { reorderedItems } = req.body;
    console.log("updatedCardsupdatedCardsupdatedCards",reorderedItems)
    await Community.updateCardOrderProject(reorderedItems);
    res.status(200).send({ status: 1, message: 'Order saved' });
  } catch (error) {
    console.log('saveReq error : ',error)
    res.status(500).send({ status: 500, message: 'Error saving order', error:error });
  }
}

const deleteCommunity = async (request,reply) => {
    try{
        let {deleteId,status,userId} = request.body;
        let currentDate = Math.round(moment().valueOf() / 1000);
        let data = {};
        let template = {};
        data['status']=status;
        console.log("deleteId,status,userId",deleteId,status,userId);
        let notificationMsg;
        if(deleteId){
            let deleted= await Community.updateCommunity(deleteId,data);
            template= await Community.getCommunityById(deleteId);
            if(data['status']===2)
            {
                notificationMsg=`The Project "${template?.name}" has been deleted.`;
            }
            else if(data['status']===0)
            {
                notificationMsg=`The Project "${template?.name}" has been made inactive.`;
            }
            else
            {
                notificationMsg=`The Project "${template?.name}" has been made active.`;
            }
            if(deleted){
                // Notification
                let notificationUrl="Project";
                // Alert to user
                const incrementResult_User = await Admin.incrementField(userId,"noti_count",1);
                let notificationData_user = {
                    user_id: userId,
                    url: notificationUrl,
                    timestamp: currentDate,
                    message: notificationMsg,
                    status: 1,
                    from_id: userId,
                };
                const addNotificationResult_user = await Admin.addNotification(notificationData_user);
                
                // Alert to all admins
                
                let adminIds = await Admin.getAllAdminIds();
                let notificationData = {};
                for (const adminId of adminIds) {
                    if (adminId != userId) {
                        const incrementResult = await Admin.incrementField(adminId,"noti_count",1);
                        notificationData = {
                            user_id: adminId,
                            url: notificationUrl,
                            timestamp: currentDate,
                            message: notificationMsg,
                            status: 1,
                            from_id: userId,
                        };
                        const addNotificationResult = await Admin.addNotification(notificationData);
                    }
                }
                reply.send({ status: 1, message: notificationMsg });
            } else {
                console.log("Failed to delete community. ");
                reply.send({ status: 0, message: "Failed to delete community." });
            }
        }
    }
    catch(error){
        console.log("delete community error : ",error)
    }
}

const addCommunityType = async (request, reply) => {
  try {
    let currentDate = Math.round(moment().valueOf() / 1000);
    let communityData = {};
    communityData.name = request.body.values.name;
    communityData.added_by = request.body.userId;
    communityData.status = 1;
    communityData.timestamp = currentDate;

    console.log("Community Type data : ",communityData);

    let communityTypeNameExist = await Community.isCommunityTypeNameExist(communityData.name);

    if (communityTypeNameExist != true) {
      try {
        let result = await Community.addCommunityType(communityData);
        if (result) {
          reply.send({ status: 1, message: `Project Type ${communityData.name} added successfully` });

          // Notification
          let notificationMsg = `New Project type "${communityData.name}" has been added.`;
          let notificationUrl = "community";

          // Alert to user

          const incrementResult_User = await Admin.incrementField( communityData.added_by, "noti_count", 1 );
          let notificationData_user = {
            user_id: communityData.added_by,
            url: notificationUrl,
            timestamp: currentDate,
            message: notificationMsg,
            status: 1,
            from_id: communityData.added_by,
          };
          const addNotificationResult_user = await Admin.addNotification( notificationData_user );

          // Alert to all admins

          let adminIds = await Admin.getAllAdminIds();
          let notificationData = {};
          for (const adminId of adminIds) {
            if (adminId != communityData.added_by) {
              const incrementResult = await Admin.incrementField( adminId,"noti_count",1 );
              notificationData = {
                user_id: adminId,
                url: notificationUrl,
                timestamp: currentDate,
                message: notificationMsg,
                status: 1,
                from_id: communityData.added_by,
              };
              const addNotificationResult = await Admin.addNotification( notificationData );
            }
          }
        } else {
            console.log("Failed to add the community type");
            reply.send({ status: 0, message: "Failed to add the Project type" });
        }
      } catch (error) {
        console.log("Error adding community type : ", error);
        reply.send({ status: 0, message: "Failed to add the community type" });
      }
    } else {
      console.log("The Project type already exist!");
      reply.send({ status: 2, message: `Project type name ${communityData.name} already exists!` });
    }
  } catch (error) {
    console.log("Server error", error);
    reply.status(500).send({ message: "Server error" });
  }
};

const getAllCommunityTypes = async (request, reply) => {
  try {
    let results = await Community.getAllCommunityTypes(true);
    let clients=await client.getAllClients(true,userType=1)
    clients=clients.data
    console.log("getAllCommunityTypes..length...", results.length);
    if (!empty(results)) {
      reply.send({ status: 1, results,clients, message: "Data fetched successfully" });
    } else {
      reply.send({ status: 0, results,clients, message: "Error fetching data" });
    }
  } catch (error) {
    console.log("Server error",error);
    reply.status(500).send("Server error");
  }
};

const getCommunityTypeById = async (request, reply) => {
  try {
    let { editId } = request.body;
    let communityData = await Community.getCommunityTypeById(editId);
    // console.log("communityData************************", communityData);
    if (communityData) {
      reply.send({ status: 1, result: communityData });
    } else {
      reply.send({ status: 0, message: "Community not found" });
    }
  } catch (error) {
    console.log(error);
    reply.status(500).send("Server error");
  }
};

const updateCommunityType = async (request, reply) => {
  try {
    let currentDate = Math.round(moment().valueOf() / 1000);
    let communityData = {};
    let id = request.body.editId;
    communityData.name = request.body.values.name;
    // communityData.status = 1;

    let communityTypeNameExist = await Community.isCommunityTypeNameExist(communityData.name,id);
    // console.log("isCommunityNameExist : ", isCommunityNameExist);

    if (communityTypeNameExist != true) {
      try {
        let updated = await Community.updateCommunityType(id, communityData);
        if (updated) {
          reply.send({ status: 1, message: "Project type has been successfully updated!", });

          // Notification
          let notificationMsg = `The Project type "${communityData.name}" has been updated.`;
          let notificationUrl = "community";

          // Alert to user of updation

          const incrementResult_User = await Admin.incrementField( communityData.added_by, "noti_count", 1 );

          let notificationData_user = {
            user_id: communityData.added_by,
            url: notificationUrl,
            timestamp: currentDate,
            message: notificationMsg,
            status: 1,
            from_id: communityData.added_by,
          };
          
          const addNotificationResult_user = await Admin.addNotification( notificationData_user );

          // Alert to all admins of updation

          let adminIds = await Admin.getAllAdminIds();
          let notificationData = {};
          for (const adminId of adminIds) {
            if (adminId != communityData.added_by) {
              const incrementResult = await Admin.incrementField( adminId, "noti_count", 1 );
              notificationData = {
                user_id: adminId,
                url: notificationUrl,
                timestamp: currentDate,
                message: notificationMsg,
                status: 1,
                from_id: communityData.added_by,
              };
              const addNotificationResult = await Admin.addNotification( notificationData );
            }
          }
        } else {
            console.log("Failed to update the community type ");
          reply.send({ status: 0, message: "Failed to update the community type" });
        }
      } catch (error) {
        console.log("Error updating the community : ", error);
        reply.send({ status: 3, message: "Error updating the community" });
      }
    } else {
      console.log("The Project type already exist!");
      reply.send({ status: 2, message: "The Project type already exist!" });
    }
  } catch (error) {
    console.log(error);
    reply.send({ status: 500, message: "Server error" });
  }
};

const deleteCommunityType = async (request,reply) => {
    try{
        let {deleteId,status,userId} = request.body;
        let currentDate = Math.round(moment().valueOf() / 1000);
        let data = {};
        let template = {};
        data['status']=status;
        console.log("deleteId,status,userId",deleteId,status,userId);
        let notificationMsg;
        if(deleteId){
            let deleted= await Community.updateCommunityType(deleteId,data);
            template= await Community.getCommunityTypeById(deleteId);
            if(data['status']===2)
            {
                notificationMsg=`The Project type "${template?.name}" has been deleted.`;
            }
            else if(data['status']===0)
            {
                notificationMsg=`The Project type "${template?.name}" has been made inactive.`;
            }
            else
            {
                notificationMsg=`The Project type "${template?.name}" has been made active.`;
            }
            if(deleted){
                // Notification
                let notificationUrl="community";
                // Alert to user
                const incrementResult_User = await Admin.incrementField(userId,"noti_count",1);
                let notificationData_user = {
                    user_id: userId,
                    url: notificationUrl,
                    timestamp: currentDate,
                    message: notificationMsg,
                    status: 1,
                    from_id: userId,
                };
                const addNotificationResult_user = await Admin.addNotification(notificationData_user);
                
                // Alert to all admins
                
                let adminIds = await Admin.getAllAdminIds();
                let notificationData = {};
                for (const adminId of adminIds) {
                    if (adminId != userId) {
                        const incrementResult = await Admin.incrementField(adminId,"noti_count",1);
                        notificationData = {
                            user_id: adminId,
                            url: notificationUrl,
                            timestamp: currentDate,
                            message: notificationMsg,
                            status: 1,
                            from_id: userId,
                        };
                        const addNotificationResult = await Admin.addNotification(notificationData);
                    }
                }
                reply.send({ status: 1, message: notificationMsg });
            } else {
                console.log("Failed to delete community type");
                reply.send({ status: 0, message: "Failed to delete community type" });
            }
        }
    }
    catch(error){
        console.log("delete community type error : ",error)
    }
}

const uploadCommunities = async (request, reply) => {
  try {
    const { data = [], userId } = request.body;
    const successProjects = [];
    const failedProjects = [];
    const updatedProjects = [];
    const notificationTasks = []; // To store notification sending tasks

    const norm = (v) => (typeof v === "string" ? v.trim() : v);

    let inserted = 0;
    const currentDate = Math.round(moment().valueOf() / 1000);

    for (let i = 0; i < data.length; i++) {
      try {
        const row = data[i] || {};
        const name = norm(row.name);
        const description = norm(row.description);
        // const typeName = norm(row.type);
        const phone = norm(row.phone);
        const email = norm(row.email);
        const poNumber = norm(row.poNumber);
        const address = norm(row.address);
        const city = norm(row.city);
        const state = norm(row.state);
        const zip = norm(row.zip);
        const clients = norm(row.client);
        const showAddress = 1;
        const salesRepNames = norm(row.salesRepNames);
// !typeName ||
        if (!name ||  !clients || !salesRepNames || !address || !city || !state || !zip) {
          failedProjects.push({ row: i + 1, reason: "Name, Type, Clients, Sales Rep Names, Address, City, State, and Zip are required" });
          continue;
        }

        // Look up Community Type
        // const communityType = await Community.getCommunityTypeByName(typeName);
        // if (!communityType) {
        //   failedProjects.push({ row: i + 1, reason: `Community Type "${typeName}" not found` });
        //   continue;
        // }

        // Look up Sales Rep Names
        let parsedSalesRepIds = null;
        if (salesRepNames) {
          const salesRepNamesArray = salesRepNames.split(',').map(name => norm(name)).filter(Boolean);
          const foundSalesRepsWithNames = await Promise.all(salesRepNamesArray.map(async name => ({ name, id: await Client.getSalesRepByNameProject(name) })));

          const validSalesRepIds = foundSalesRepsWithNames.filter(item => item.id).map(item => item.id);
          if (validSalesRepIds.length !== salesRepNamesArray.length) {
            const missingSalesReps = foundSalesRepsWithNames.filter(item => !item.id).map(item => item.name);
            failedProjects.push({ row: i + 1, reason: `Sales Reps not found: ${missingSalesReps.join(', ')}` });
            continue;
          }
          parsedSalesRepIds = validSalesRepIds.join(',');
        }

        // Check if community with the same name and type already exists
        // , typeName
        const communityExists = await Community.isCommunityNameAndTypeExist(name);
        if (communityExists) {
          // If exists, compare all fields and update any that are missing or mismatched.
          // , typeName
          const existingCommunity = await Community.getCommunityByNameAndType(name);

          if (existingCommunity) {
            const updateData = {};

            const normStr = (v) =>
              v === null || v === undefined ? "" : String(v).trim();

            const shouldUpdateString = (existingVal, incomingVal) => {
              if (incomingVal === null || incomingVal === undefined) return false;
              return normStr(existingVal) !== normStr(incomingVal);
            };

            if (shouldUpdateString(existingCommunity.description, description)) updateData.description = description;
            if (shouldUpdateString(existingCommunity.phone, phone)) updateData.phone = phone;
            if (shouldUpdateString(existingCommunity.email, email)) updateData.email = email;
            if (shouldUpdateString(existingCommunity.address, address)) updateData.address = address;
            if (shouldUpdateString(existingCommunity.city, city)) updateData.city = city;
            if (shouldUpdateString(existingCommunity.state, state)) updateData.state = state;
            if (shouldUpdateString(existingCommunity.zip, zip)) updateData.zip = zip;
            if (shouldUpdateString(existingCommunity.poNumber, poNumber)) updateData.poNumber = poNumber;

            // show_address: update when different
            if (
              showAddress !== undefined &&
              showAddress !== null &&
              existingCommunity.show_address !== showAddress
            ) {
              updateData.show_address = showAddress;
            }

            // sales_rep_names: update when different
            if (shouldUpdateString(existingCommunity.sales_rep_names, parsedSalesRepIds)) {
              updateData.sales_rep_names = parsedSalesRepIds;
            }

            // clients: update when different
            if (shouldUpdateString(existingCommunity.clients, client_id)) {
              updateData.clients = client_id;
            }

            if (Object.keys(updateData).length > 0) {
              const updated = await Community.updateCommunity(existingCommunity.id, updateData);
              if (updated) {
                inserted++;
                const updatedFields = Object.keys(updateData);
                updatedProjects.push({ name, updatedFields, row: i + 1 });
              } else {
                failedProjects.push({ row: i + 1, reason: `Failed to update project "${name}"` });
              }
            } else {
              // All relevant fields are already matching; treat as existing project
              failedProjects.push({ row: i + 1, reason: "Project already exist" });
            }
          } else {
            // Safety fallback; should not normally happen
            failedProjects.push({ row: i + 1, reason: "Project already exist" });
          }

          continue;
        }

        // Check if poNumber already exists (if provided)
        if (poNumber) {
          const poNumberExists = await Community.isPoNumberExist(poNumber);
          if (poNumberExists) {
            failedProjects.push({ row: i + 1, reason: `PO Number "${poNumber}" already exists` });
            continue;
          }
        }

        // Handle Clients
        let client_id = null;
        if (clients) {
          let foundClient = await Client.getPartnerByName(clients);
          console.log("foundClient========",foundClient);
          if (foundClient) {
            client_id = foundClient.id;
          } else {
            failedProjects.push({ row: i + 1, reason: `Client "${clients}" not found` });
            continue;
          }
        }

        console.log("client_id=======",client_id);

        // Prepare community data
        const communityData = {
          name,
          description,
          // type: typeName,
          phone,
          email,
          poNumber,
          address,
          city,
          state,
          zip,
          thumbnail: null, // No thumbnail for mass upload
          clients: client_id,
          added_by: userId,
          status: 1, // Active status
          timestamp: currentDate,
          tax_category: null, // Assuming no tax category for mass upload
          show_address: showAddress,
          sales_rep_names: parsedSalesRepIds,
        };

        // Insert community data into the database
        let result = await Community.addCommunity(communityData);
        if (result) {
          inserted++;
          successProjects.push(name);

          // Prepare notification tasks
          const notificationMsg = `New Project "${name}" has been added.`;
          const notificationUrl = "community";
          notificationTasks.push({
            type: 'user',
            userId: userId,
            message: notificationMsg,
            url: notificationUrl,
            fromId: userId,
          });

          // Get all admin IDs and prepare admin notifications
          const adminIds = await Admin.getAllAdminIds();
          for (const adminId of adminIds) {
            if (adminId !== userId) {
              notificationTasks.push({
                type: 'admin',
                userId: adminId,
                message: notificationMsg,
                url: notificationUrl,
                fromId: userId,
              });
            }
          }
        } else {
          failedProjects.push({ row: i + 1, reason: `Failed to add project "${name}" to database` });
        }

      } catch (rowErr) {
        console.error("[ROW ERROR]", rowErr?.message || rowErr);
        failedProjects.push({ row: i + 1, reason: `Error processing row: ${rowErr?.message || "unknown"}` });
        continue;
      }
    }

    // Send notification tasks asynchronously after processing all data
    if (notificationTasks.length > 0) {
      for (const task of notificationTasks) {
        if (task.type === 'user') {
          await Admin.incrementField(task.userId, "noti_count", 1);
          await Admin.addNotification({
            user_id: task.userId,
            url: task.url,
            timestamp: currentDate,
            message: task.message,
            status: 1,
            from_id: task.fromId,
          });
        } else if (task.type === 'admin') {
          await Admin.incrementField(task.userId, "noti_count", 1);
          await Admin.addNotification({
            user_id: task.userId,
            url: task.url,
            timestamp: currentDate,
            message: task.message,
            status: 1,
            from_id: task.fromId,
          });
        }
      }
    }

    reply.send({
      status: inserted > 0 ? 1 : 0,
      message:
        inserted > 0
          ? `${inserted} projects uploaded successfully, ${failedProjects.length} failed`
          : "Failed to upload projects.",
      successProjects,
      failedProjects,
      updatedProjects,
    });

  } catch (error) {
    console.error("###############Error in uploadCommunities:", error);
    reply.send({
      status: 0,
      message: "Failed to upload projects",
      error: error.message,
      failedProjects: [],
    });
  }
};

module.exports = { addCommunity, getAllCommunities, getCommunityById,updateCommunity, deleteCommunity,
  addCommunityType, getAllCommunityTypes, getCommunityTypeById, updateCommunityType, deleteCommunityType,
  saveProjectOrder, uploadCommunities };
