const sequelize = require("../config/dbconnection");
const Sequelize = require("sequelize");
const { UserNotification } = require("./UserNotification");
const { StaffMembers } = require("./teams");
const Client = require('../models/client')
const { TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN } = require("../constants/constant")
const client = require("twilio")(TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN);

const Users = sequelize.define(
  "users",
  {
    // `id` int NOT NULL AUTO_INCREMENT,
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
    },
    // `username` varchar(191) NOT NULL,
    username: { type: Sequelize.STRING(191), allowNull: false },
    // `password` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
    password: {
      type: Sequelize.STRING(100),
      allowNull: false,
      charset: "utf8mb3",
      collate: "utf8mb3_general_ci",
    },
    // `first_name` varchar(100) DEFAULT NULL,
    first_name: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    // `last_name` varchar(100) DEFAULT '',
    last_name: { type: Sequelize.STRING, allowNull: true, defaultValue: "" },
    // `company_name` varchar(160) NOT NULL,
    company_name: { type: Sequelize.STRING(160), allowNull: false },
    // `email` varchar(191) NOT NULL,
    email: { type: Sequelize.STRING(191), allowNull: true, defaultValue:null, unique: true },
    // `profile_pic` varchar(100) DEFAULT 'avatar.jpg',
    profile_pic: {
      type: Sequelize.STRING,
      allowNull: true,
      defaultValue: "avatar.jpg",
    },
    // `company_logo` varchar(200) CHARACTER SET utf32 COLLATE utf32_general_ci NOT NULL DEFAULT 'default_logo.png',
    company_logo: {
      type: Sequelize.STRING(200),
      charset: "utf32",
      collate: "utf32_general_ci",
      allowNull: false,
      defaultValue: "default_logo.png",
    },
    // `user_type` int NOT NULL,.
    user_type: { type: Sequelize.INTEGER, allowNull: false },
    // `personal_timezone` varchar(20) CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL,
    personal_timezone: {
      type: Sequelize.STRING(20),
      charset: "utf32",
      collate: "utf32_general_ci",
      allowNull: true,
      defaultValue: null,
    },
    // `date_format` varchar(15) CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL,
    date_format: {
      type: Sequelize.STRING(15),
      charset: "utf32",
      collate: "utf32_general_ci",
      allowNull: true,
      defaultValue: null,
    },
    // `time_format` tinyint NOT NULL DEFAULT '24',
    time_format: {
      type: Sequelize.TINYINT,
      allowNull: false,
      defaultValue: 24,
    },
    // `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at: {
      type: Sequelize.DATE,
      allowNull: false,
      defaultValue: Sequelize.NOW,
    },
    // `terms_and_policy` tinyint NOT NULL DEFAULT '0',
    terms_and_policy: {
      type: Sequelize.TINYINT,
      allowNull: false,
      defaultValue: 0,
    },
    // `reset_code` varchar(50) DEFAULT NULL,
    reset_code: {
      type: Sequelize.STRING(50),
      allowNull: true,
      defaultValue: null,
    },
    // `reset_status` tinyint NOT NULL DEFAULT '0',
    reset_status: {
      type: Sequelize.TINYINT,
      allowNull: false,
      defaultValue: 0,
    },
    // `email_notification` int NOT NULL DEFAULT '1',
    email_notification: {
      type: Sequelize.INTEGER,
      allowNull: false,
      defaultValue: 1,
    },
    // `noti_count` int NOT NULL DEFAULT '0',
    noti_count: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
    // `worker_sid` text,
    worker_sid: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    // `department_id` int DEFAULT '2',
    department_id: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue: 2,
    },
    // `available_for_call` tinyint NOT NULL DEFAULT '0',
    available_for_call: {
      type: Sequelize.TINYINT,
      allowNull: false,
      defaultValue: 0,
    },
    // `active` char(1) NOT NULL DEFAULT 'N',
    active: { type: Sequelize.CHAR(1), allowNull: false, defaultValue: "N" },
    activated_by_admin:{ type: Sequelize.INTEGER, allowNull:false, defaultValue: 0 },
    admin_password: { type: Sequelize.STRING, allowNull: true, defaultValue: "" },
    // `status` int NOT NULL DEFAULT '1',
    job_title:{type:Sequelize.TEXT,allowNull:true,defaultValue:null},
    status: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
    last_activity: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    chat_status: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
    chat_position_x:{ type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    chat_position_y:{ type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    added_by_client:{ type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
    parent_id:{ type: Sequelize.INTEGER, allowNull: true, defaultValue:null },
    parent_user_type:{ type: Sequelize.INTEGER, allowNull: true, defaultValue:null },
    alert_status: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
    smsEnabled: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
    emailEnabled: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
    online: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
    simple_view_enabled: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
    client_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    vendor_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    community_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    admin_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    project_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    community_design_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    community_install_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    community_job_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    community_official_request_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    design_order_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    design_order_job_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    installation_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    official_request_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    pickup_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    proposal_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    quotation_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    shipping_view_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    view_project_type: { type: Sequelize.STRING(20), allowNull: true, defaultValue: 'one' },
    remit_to_address: { type: Sequelize.STRING(255), allowNull: true, defaultValue: null },
    remit_to_city: { type: Sequelize.STRING(255), allowNull: true, defaultValue: null },
    remit_to_state_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    remit_to_zip_code: { type: Sequelize.STRING(255), allowNull: true, defaultValue: null },
    remit_to_country: { type: Sequelize.STRING(10), allowNull: true, defaultValue: null },
    user_pin_unique: { type: Sequelize.STRING(500), allowNull: true, defaultValue: null },
  },
  { tableName:'users', timestamps: false }
);

// This function is used to increment notification count of the user in the database.
const incrementField = async (userId, field, amount) => {
  console.log("incrementField-------------------------", userId, field, amount);
  try {
    const result = await Users.update(
      { [field]: sequelize.literal(`${field} + ${amount}`) },
      { where: { id: userId } }
    );
    console.log("Increment result:", result);
    // result is an array with the number of affected rows
    if (result[0]) {
      // console.log(`${result[0]} row(s) updated.`);
      return true;
    } else {
      console.warn(`No rows were updated for userId: ${userId}`);
      return false;
    }
  } catch (error) {
    console.error("Error incrementing field:", error.message);
    console.error("Error updating field:", error);
  }
};

// This function is used to add notification for the logged in user and admin.
const addNotification = async (data) => {
  delete data["email"];
  delete data["email_notification"];
  delete data["username"];
  const result = await UserNotification.create(data);
  if (result) {
    //  console.log("add_Notification_result", result);
    return true;
  } else {
    return false;
  }
};

const updateNotification= async(data,userId)=>{
  console.log("Hifiiiiiiiiiiiii",data);
  let result=await UserNotification.update(data,{
    where:{user_id:userId}})
    if(result){
      return true
    }
    else{
      return false
    }
}

// This function is used to get all admin ID's.
const getAllAdminIds = async () => {
  try {
    const result = await Users.findAll({
      attributes: ["id"],
      where: {
        status: 1,
        user_type: 1,
      },
      order: [["id", "DESC"]],
    });
    if (result.length >= 1) {
      let data = result.map((row) => row.id);
      // console.log("data--------", data);
      return data;
    } else {
      return false;
    }
  } catch (error) {
    console.log(error);
  }
};

const getAllUserIds = async () => {
  try {
    const result = await Users.findAll({
      attributes: ["id"],
      where: {
        status: 1,
        user_type: { [Sequelize.Op.ne]: 3 },
      },
      order: [["id", "DESC"]],
    });

    return result.length >= 1 ? result.map((row) => row.id) : [];
  } catch (error) {
    console.error("Error fetching all user IDs:", error);
    return [];
  }
};
//fetch admin ids and project manager
const getAllAdminUserIds = async () => {
  try {
    const result = await Users.findAll({
      attributes: ["id"],
      where: {
        status: 1,
        user_type: 1,
      },
      order: [["id", "DESC"]],
    });

    return result.length >= 1 ? result.map((row) => row.id) : [];
  } catch (error) {
    console.error("Error fetching all user IDs:", error);
    return [];
  }
};
const { Op } = require("sequelize");

const getAllAdminAndManagerUserIds = async () => {
  try {
    const result = await Users.findAll({
      attributes: ["id"],
      where: {
        status: 1,
        user_type: {
          [Op.in]: [1, 8], // Include user_type 1 and 8
        },
      },
      order: [["id", "DESC"]],
    });

    return result.length >= 1 ? result.map((row) => row.id) : [];
  } catch (error) {
    console.error("Error fetching all user IDs:", error);
    return [];
  }
};

const getAllClientUserIds = async () => {
  try {
    const result = await sequelize.query(
      `SELECT DISTINCT p.user_id FROM partners p 
       LEFT JOIN users u ON p.user_id = u.id 
       WHERE p.status = 1 AND p.isVendor = 0 AND p.user_id IS NOT NULL AND u.status = 1`,
      { type: sequelize.QueryTypes.SELECT }
    );
    return result.map(row => row.user_id).filter(Boolean);
  } catch (error) {
    console.error("Error fetching client user IDs:", error);
    return [];
  }
};


const getTeamByUserId = async (userId) => {
  const result = await sequelize.query(
    `SELECT * from staff_members where user_id=${userId}`
  );
  if (result[0].length == 1) {
    let data = result[0];
    return data;
  } else {
    return false;
  }
};

const updateUser = async (userId, data) => {
  try {
    console.log("Update worker_sid ##############",data,userId);
    const result = await Users.update(data, {
      where: {
        id: userId,
      },  
    });
    if (result) {
      //  console.log("updateUser result ---- ", result);
      return true;
    } else {
      return false;
    }
  } catch (error) {
    console.log("error updateUser - ", error);
  }
};

const updateStaffsOfAClient = async (clientUserId, data) => {
  try {
    const result = await Users.update(data, {
      where: {
        parent_id: clientUserId,
      },  
    });
    if (result) {
      return true;
    } else {
      return false;
    }
  } catch (error) {
    console.log("error updateStaffsOfAClient - ", error);
    return false;
  }
};


const getUserByEmail = async (email) => {
  try {
    const user = await Users.findAll({
      where: { email: email },
    });
    if (user[0]) {
      // console.log("getUserByEmail admin result - ", user[0]);
      return user[0];
    } else {
      return false;
    }
  } catch (error) {
    console.log("Error getUserByEmail admin - ", error);
    return false;
  }
};

const addUser = async (data) => {
  try {
    const result = await Users.create(data);
     console.log("addUser result - ", result.dataValues.id);
    return result.dataValues.id;
  } catch (error) {
    console.log("Error adding user - ", error);
    return false;
  }
};

const deleteUser = async (id) => {
  try {
    let data = {};
    data["status"] = 2;
    const deleted = await Users.destroy({
      where: {
        id: id,
      },
    });
    console.log("deleted adminmodel", deleted);
    if (deleted) {
      const updated = await StaffMembers.destroy({
        where: {
        user_id: id,
      },
    });
    if (updated) {
      return true;
    } else {
      return false;
    }
  }
  } catch (error) {
    console.log("Delete user error : ", error);
  }
};

const getAllAdminEmails = async () => {
  const result = await Users.findAll({
    attributes: ["email"],
    where: {
      status: 1,
      user_type: 1,
    },
    order: [["id", "DESC"]],
  });
  if (result.length >= 1) {
    const data = result.map((item) => item.email);
    // console.log("getAllAdminEmails -- result[0] -----", data);
    return data;
  } else {
    return false;
  }
};

const isPrivileged = async (permissions) => {
  try {
    const result = await sequelize.query(
      `SELECT up.id AS privilege_ids, up.name AS privileges FROM user_privileges up WHERE up.id=${permissions}`
    );
    if (result) {
      return result[0];
    } else {
      return false;
    }
  } catch (error) {
    console.log("isPrivileged error : ", error);
    return false;
  }
};

// BACKUP: Original getAllNotifications function without pagination
// const getAllNotifications = async (userId) => {
//   let query = await sequelize.query(
//     `select  un.*,u.username as user_name,concat(coalesce(u.first_name,u.company_name),' ',u.last_name) as user_real_name,u.profile_pic as profile_pic,u.user_type FROM user_notifications un` +
//       ` LEFT JOIN users u on u.id=un.from_id ` +
//       ` where un.user_id=${userId} and un.status=1` +
//       ` ORDER BY un.id DESC`
//   );
//   let data = [];
//   if (query[0].length > 0) {
//     data = query[0];
//   }
//   return data;
// };

// NEW: getAllNotifications with pagination support
const getAllNotifications = async (userId, page = 1, limit = 6) => {
  const offset = (page - 1) * limit;
  let query = await sequelize.query(
    `select  un.*,u.username as user_name,concat(coalesce(u.first_name,u.company_name),' ',u.last_name) as user_real_name,u.profile_pic as profile_pic,u.user_type FROM user_notifications un` +
      ` LEFT JOIN users u on u.id=un.from_id ` +
      ` where un.user_id=${userId} and un.status=1` +
      ` ORDER BY un.id DESC LIMIT ${limit} OFFSET ${offset}`
  );
  let data = [];
  if (query[0].length > 0) {
    data = query[0];
  }
  return data;
};

// NEW: Function to get total count of notifications for pagination
const getTotalNotificationsCount = async (userId) => {
  let query = await sequelize.query(
    `SELECT COUNT(*) as total FROM user_notifications WHERE user_id=${userId} and status=1`
  );
  if (query[0].length === 1) {
    return query[0][0].total;
  } else {
    return 0;
  }
};

const getAllNotificationsCount = async (userId) => {
  let query = await sequelize.query(
    `select noti_count from users where id=${userId} and status!=0`
  );
  if (query[0].length === 1) {
    return query[0][0];
  } else {
    return false;
  }
};

const getUserById = async (userid) => {
  let user = {};
  const result = await sequelize.query(
    `SELECT u.*,ur.name AS user_role,d.name AS department_name FROM users u ` +
      ` LEFT JOIN user_roles ur ON ur.id = u.user_type` +
      ` LEFT JOIN departments d ON d.id=u.department_id` +
      ` WHERE u.id = ${userid}`
  );

  // console.log("getUserById result", result[0]);
  if (result[0].length == 1) {
    user = result[0];
    // unset($user['password']);
    return user;
  } else {
    return false;
  }
};
const getUserByIdForteam = async (userid) => {
  let user = {};
  const result = await sequelize.query(
    `SELECT u.*,u.id as user_id,ur.name AS user_role,d.name AS department_name FROM users u ` +
      ` LEFT JOIN user_roles ur ON ur.id = u.user_type` +
      ` LEFT JOIN departments d ON d.id=u.department_id` +
      ` WHERE u.id = ${userid}`
  );

  // console.log("getUserById result", result[0]);
  if (result[0].length == 1) {
    user = result[0];
    // unset($user['password']);
    return user;
  } else {
    return false;
  }
};

const getAllPaymentMethods = async () => {
  const result = await sequelize.query(
    `SELECT * FROM payment_methods WHERE status=1 ORDER BY id DESC`
  );

  if (result[0].length > 0) {
    return result[0];
  }
};

const getPartnerByUserId = async (userId) => {
  const result = await sequelize.query(
    `SELECT p.*,u.first_name,u.last_name,pt.name as partner_type_name,p.email,s.name as state_name` +
      ` FROM partners p` +
      ` LEFT JOIN users u ON u.id=p.user_id` +
      ` LEFT JOIN states s ON s.id=p.state_id` +
      ` LEFT JOIN partner_types pt ON pt.id=p.partner_type` +
      ` WHERE user_id = ${userId}`
  );
  let data = {};
  if (result[0].length === 1) {
    data = result[0][0];
    return data;
  } else {
    return false;
  }
};

const getPartnersUserIdById = async (partnerId) => {
  const result = await sequelize.query(
    `SELECT user_id FROM partners WHERE id = :partnerId`,
    {
      replacements: { partnerId },
      type: sequelize.QueryTypes.SELECT
    }
  );

  if (result.length === 1) {
    return result[0].user_id;
  } else {
    return false;
  }
};

const getAllStates = async () => {
  const result=await sequelize.query(`SELECT s.*, company_name AS authorized_by FROM states s`+
    ` LEFT JOIN users u ON s.added_by=u.id`+
    ` WHERE s.status=1`+
    ` ORDER BY s.name ASC`);

  if(result[0]){
    if(result[0].length > 0){
      return result[0]			
    }
  }
}

const getAllCountries = async () => {
  const result=await sequelize.query (`SELECT * FROM countries WHERE status=1 ORDER BY name ASC`);
  if(result[0]){
    if(result[0].length > 0){
      return result[0]		
    }
  }
}

const getAllIndustries = async () => {
  const result=await sequelize.query(`SELECT i.*, company_name AS added_by FROM industries i`+
    ` LEFT JOIN users u ON i.added_by=u.id`+
    ` WHERE i.status=1`);
  if(result[0]){			
    if(result[0].length > 0){
      return result[0];
    }
  }
}

const isEmailExist = async (emailId,userId=0) => {
  let whereCondition = '';
  if(userId){
    whereCondition = ` AND u.id != ${userId}`;
  }
  let result = await sequelize.query(`SELECT u.email FROM users u WHERE u.email = "${emailId}" ${whereCondition}`);

  if(userId){
    whereCondition = ` AND p.user_id!= ${userId}`;
  }
  let result1 = await sequelize.query(`SELECT p.email FROM partners p WHERE p.email = "${emailId}" ${whereCondition}`);

  if(userId){
    whereCondition = ` AND t.user_id!= ${userId}`;
  }
  let result2 = await sequelize.query(`SELECT t.email FROM staff_members t WHERE t.email = "${emailId}" ${whereCondition}`);
  
  if(userId){
    whereCondition = ` AND d.user_id!= ${userId}`;
  }
  let result4 = await sequelize.query(`SELECT d.email FROM driver d WHERE d.email = "${emailId}" ${whereCondition}`);

  let result3 = await sequelize.query(`SELECT c.email_id FROM contacts c WHERE c.email_id = "${emailId}"`);

  if(result||result1||result2||result3||result4){
    if(result[0].length >= 1||result1[0].length >= 1||result2[0].length >= 1||result3[0].length >= 1||result4[0].length >= 1){
      return true;
    }else{
      return false;
    }
  }else{
    return false;
  }
}

const getLeadPhones = async () => {
  let result = await sequelize.query(`SELECT l.id, l.phone, l.phone2 FROM leads l WHERE l.status=1 ORDER BY l.updated_time ASC`);
  let leads={};
  if(result){			
    if(result[0].length > 0){
      for (const row of result[0]){
        leads['+' + row['phone']] = row['id'];
        if(row['phone2']){
          leads['+' + row['phone2']] = row['id'];
        }
      }
    }
  }
 return leads;
}

const fetchCompleteTwilioCalls = async () => {
  try{
    let leads = await getLeadPhones();
    const calls = await client.calls.list({ limit: 500 });
    
    const callsArray = calls.map((value) => {
      let leadId = '';

      if (leads[value.from]) {
        leadId = leads[value.from];
      } else if (leads[value.to]) {
        leadId = leads[value.to];
      }

      return {
        account_sid: value.accountSid,
        annotation: value.annotation,
        answered_by: value.answeredBy,
        api_version: value.apiVersion,
        caller_name: value.callerName,
        date_created: value.dateCreated,
        date_updated: value.dateUpdated,
        direction: value.direction,
        duration: value.duration,
        end_time: value.endTime,
        forwarded_from: value.forwardedFrom,
        from: value.from,
        from_formatted: value.fromFormatted,
        group_sid: value.groupSid,
        parent_call_sid: value.parentCallSid,
        phone_number_sid: value.phoneNumberSid,
        price: value.price,
        price_unit: value.priceUnit,
        sid: value.sid,
        start_time: value.startTime,
        status: value.status,
        subresource_uris: value.subresourceUris,
        to: value.to,
        to_formatted: value.toFormatted,
        uri: value.uri,
        lead_id: leadId
      };
    });
    return callsArray;
  }
  catch(error){
    console.log("fetchCompleteTwilioCalls error",error)
  }
}

const fetchTwilioCallsByFiterCriteria = async (filterCriteria) => {
  try{
    let leads = await getLeadPhones();
    let calls = await client.calls.list({startTimeAfter:filterCriteria['startTimeAfter'],
                                         startTimeBefore:filterCriteria['startTimeBefore'],
                                         from:filterCriteria['from'],
                                         to:filterCriteria['to'],
                                         limit: 500,});
    let callsArray = calls.map(value => {
      let leadId = ''; 
      if (leads[value.from]) {
        leadId = leads[value.from];
      } else if (leads[value.to]) {
        leadId = leads[value.to];
      }
      return {
        account_sid: value.accountSid,
        annotation: value.annotation,
        answered_by: value.answeredBy,
        api_version:value.apiVersion,
        caller_name:value.callerName,
        date_created:value.dateCreated,
        date_updated:value.dateUpdated,
        direction:value.direction,
        duration:value.duration,
        end_time:value.endTime,
        forwarded_from:value.forwardedFrom,
        from:value.from,
        from_formatted:value.fromFormatted,
        group_sid:value.groupSid,
        parent_call_sid:value.parentCallSid,
        phone_number_sid:value.phoneNumberSid,
        price:value.price,
        price_unit:value.priceUnit,
        sid:value.sid,
        start_time:value.startTime,
        status:value.status,
        subresource_uris:value.subresourceUris,
        to:value.to,
        to_formatted:value.toFormatted,
        uri:value.uri,
        lead_id: leadId,
      };
    });
  return callsArray;
  
  }
  catch(error){
    console.log(error)
  }
}

const fetchCompleteTwilioMessages = async () => {
  try {
    const leads = await getLeadPhones();
    const messages = await client.messages.list({ limit: 500 });
    const messagesArray = [];
    for (const value of messages) {
      let leadId = '';
      if (leads[value.from]) {
        leadId = leads[value.from];
      } else if (leads[value.to]) {
        leadId = leads[value.to];
      }
      messagesArray.push({
        account_sid: value.accountSid,
        api_version: value.apiVersion,
        body: value.body,
        date_created: new Date(value.dateCreated).toISOString(),
        date_sent: new Date(value.dateSent).toISOString(),
        date_updated: new Date(value.dateUpdated).toISOString(),
        direction: value.direction,
        error_code: value.errorCode,
        error_message: value.errorMessage,
        from: value.from,
        num_media: value.numMedia,
        num_segments: value.numSegments,
        price: value.price,
        price_unit: value.priceUnit,
        message_sid: value.sid,
        status: value.status,
        to: value.to,
        uri: value.uri,
        lead_id: leadId,
      });
    }
    return messagesArray;
  } catch (error) {
    console.error('Error fetching Twilio messages:', error);
    throw error; 
  }
};

const fetchTwilioMessagesByFilter = async (filterCriteria) => {
  try {
    const leads = await getLeadPhones();
    const messages = await client.messages.list({dateSentAfter:filterCriteria['dateSentAfter'],
                                                dateSentBefore:filterCriteria['dateSentBefore'],
                                                from:filterCriteria['from'],
                                                to:filterCriteria['to'],
                                                limit: 500,});
    let messagesArray = messages.map(value => {
      let leadId = '';
      if (leads[value.from]) {
        leadId = leads[value.from];
      } else if (leads[value.to]) {
        leadId = leads[value.to];
      }
      return {
        account_sid: value.accountSid,
        api_version: value.apiVersion,
        body: value.body,
        date_created: value.dateCreated,
        date_sent: value.dateSent,
        date_updated: value.dateUpdated,
        direction: value.direction,
        error_code: value.errorCode,
        error_message: value.errorMessage,
        from: value.from,
        num_media: value.numMedia,
        num_segments: value.numSegments,
        price: value.price,
        price_unit: value.priceUnit,
        message_sid: value.sid,
        status: value.status,
        to: value.to,
        uri: value.uri,
        lead_id: leadId,
      }
    });
    return messagesArray;
  } catch (error) {
    console.error('Error fetching Twilio messages by filter:', error);
  }
};

const  getAllWorkflowStages=async(workflowIds,join,joinCondition,select=false)=>{
  let selected=""
  if(select){
  selected=select
  }
  console.log("Nush23444444444",selected);
let query=await sequelize.query(`select ow.name as workflow_name,ows.id, ows.name, ows.stage_type, ows.flow_type as type_of_stage, ows.internal_stage, ows.position_order, ows.message as exit_message, ows.partner_type, ows.group_ids, ows.relationship_managers  ,${selected} `
+` from onboard_workflows ow`
+` left join onboard_workflow_stages ows on ows.workflow_id=ow.id`
+` left join ${join} on ${joinCondition}`
+` where ow.id in (${workflowIds}) and ows.status=1 and ows.stage_type!=7 and ow.status=1`
+` order by ows.position_order desc, ps.status ASC`
)

console.log("Nush23444444444",query);
if(query[0].length>0){
  return query[0]
}
else{
  return false;
}
}

const isOnboardComplete=async(id,table="partners")=>{
  try {
    let result = await sequelize.query(`select * from ${table}
      where onboard_complete = 1 and id = ${id}    
    `)
    console.log("############",result);
    if(result && result[0].length >0){
      return true
    }else{
      return false
    }
  } catch (error) {
    console.log(error)
  }
}

const updatePartner = async(id,data)=>{
  try {
    const result = await Client.update(data, {
      where: {
        id: id,
      },
    });
    if(result){
      return true
    }else{
      return false
    }
  } catch (error) {
    console.log(error)
  }
}

const getAllRelationshipManagerdata=async(id)=>{
  try {
    let result = await sequelize.query(`
    SELECT u.email, u.first_name, u.last_name
FROM partner_relationship_managers r
LEFT JOIN staff_members t ON r.team_id = t.id
LEFT JOIN users u ON t.user_id = u.id
WHERE u.status = 1 AND r.partner_id = ${id}
ORDER BY u.id DESC;
    `)
    let data = [];

    if (result.length >= 1) {
        result.forEach((row) => {
            if (row.email) {
                data.push(row);
            }
        });
    }else{
      return false
    }
  } catch (error) {
    console.log(error)
  }
}

const getAllRelationManagerId =async(id)=>{
  try {
    let result = await sequelize.query(`
    SELECT u.id
FROM partner_relationship_managers r
LEFT JOIN staff_members t ON r.team_id = t.id
LEFT JOIN users u ON t.user_id = u.id
WHERE u.status = 1 AND r.partner_id = ${id}
ORDER BY u.id DESC;
`)
if(result[0].length>0){
  const data = result[0].map((row)=>row.id)
  return data;
}else{
  return false
}
  } catch (error) {
    console.log(error)
  }
}

const scanCrmPhoneNumber = async (phoneNumber) => {
  let existenceResult;
  const result = await sequelize.query (`SELECT `+
    `(CASE `+
        `WHEN (NOT EXISTS (SELECT phone FROM partners WHERE phone = '${phoneNumber}')) `+
        `THEN `+
            `(CASE `+
                `WHEN (NOT EXISTS (SELECT phone FROM contacts WHERE phone = '${phoneNumber}')) `+
                `THEN `+
                    `(CASE `+
                        `WHEN (EXISTS (SELECT phone FROM leads WHERE phone = '${phoneNumber}')) `+
                        `THEN (SELECT CONCAT(id, ',', 1, ',', name, ',', '') AS 'result' FROM leads WHERE phone = '${phoneNumber}' ORDER BY id DESC LIMIT 1) `+
                    `END) `+
                `ELSE (SELECT CONCAT(id, ',', 2, ',', name, ',', '') AS 'result' FROM contacts WHERE phone = '${phoneNumber}') `+
            `END) `+
        `ELSE (SELECT CONCAT(p.id, ',', 3, ',', p.name, ',', COALESCE(u.profile_pic, '')) AS 'result' FROM partners p LEFT JOIN users u ON u.id = p.user_id WHERE p.phone = '${phoneNumber}') `+
    `END) 'result'`);
  if(result[0][0].result){
    console.log("scanCrmPhoneNumber result : ",result[0][0].result);
    existenceResult = result[0][0].result;
  }
  else{
    existenceResult = false;
  }
  return existenceResult;
}

const fetchNotificationCount=async(userId)=>{
  let query=await sequelize.query(`select noti_cnt as count from users where id=${userId} `)
  if(query[0].length>0){ 
    return query[0][0].count
  } 
  
}

const findUserByEmail =async(email)=>{
  let result = await sequelize.query(
    `SELECT * FROM driver WHERE email = :email AND status = 1`,
    {
      replacements: { email },
      type: sequelize.QueryTypes.SELECT, 
    }
  );
  if (result.length > 0) {
    return result;
  } else {
    return false;
  }
}

const findUserByPhoneNumber =async(phone)=>{
  let result = await sequelize.query(
    `SELECT * FROM driver WHERE phone = :phone AND status = 1`,
    {
      replacements: { phone }, // Use replacements to prevent SQL injection
      type: sequelize.QueryTypes.SELECT,
    }
  );
  if (result.length > 0) {
    return result;
  } else {
    return false;
  }
}

const chatPositionByUser = async (userId) => {
  try {
    const result = await sequelize.query(
      `SELECT chat_position_x, chat_position_y FROM users WHERE id = :userId`,
      {
        replacements: { userId },
        type: sequelize.QueryTypes.SELECT,
      }
    );

    return result.length > 0 ? result[0] : null;
  } catch (error) {
    console.error('Error fetching chat position:', error);
    return null;
  }
};

const updateChatPositionByUserId = async (userId, positionX, positionY) => {
  try {
    const result = await sequelize.query(
      `UPDATE users 
       SET chat_position_x = :positionX, 
           chat_position_y = :positionY 
       WHERE id = :userId`,
      {
        replacements: {
          userId,
          positionX,
          positionY
        },
        type: sequelize.QueryTypes.UPDATE,
      }
    );

    return true;
  } catch (error) {
    console.error('Error updating chat position:', error);
    return false;
  }
};
const getPhoneNumber = async (user) => {
  try {
    let phoneNumberQuery = '';
    let replacements = { userId: user.id };

    if (user.user_type === 3) {
      phoneNumberQuery = `SELECT phone FROM partners WHERE user_id = :userId`;
    } else {
      phoneNumberQuery = `SELECT phone FROM staff_members WHERE user_id = :userId`;
    }

    const result = await sequelize.query(phoneNumberQuery, {
      replacements,
      type: sequelize.QueryTypes.SELECT
    });
    if (result && result.length > 0) {
      return result[0].phone;
    } else {
      return false;
    }

  } catch (error) {
    console.error('getPhoneNumber error:', error);
    return false;
  }
};

const getCountryCodeNumber = async (user) => {
  try {
    let phoneNumberQuery = '';
    let replacements = { userId: user.id };

    if (user.user_type === 3) {
      phoneNumberQuery = `SELECT phone_country_code FROM partners WHERE user_id = :userId`;
    } else {
      phoneNumberQuery = `SELECT phone_country_code FROM staff_members WHERE user_id = :userId`;
    }

    const result = await sequelize.query(phoneNumberQuery, {
      replacements,
      type: sequelize.QueryTypes.SELECT
    });
    if (result && result.length > 0) {
      return result[0].phone_country_code;
    } else {
      return false;
    }

  } catch (error) {
    console.error('getPhoneNumber error:', error);
    return false;
  }
};
const getNotificationSettingsByUserId = async (userId) => {
  const result = await sequelize.query(
    `SELECT smsEnabled, emailEnabled FROM users WHERE id = :userId`,
    {
      replacements: { userId },
      type: sequelize.QueryTypes.SELECT,
    }
  );

  return result.length ? result[0] : null;
};

const getUserNotificationAndPermissionSettings = async (userId) => {
  const query = await sequelize.query(
    `
    SELECT 
      u.id AS user_id,
      u.smsEnabled,
      u.emailEnabled,
      u.email,
      
      -- Full name from users table (fallbacks to '' if last_name is null)
      COALESCE(
        CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')),
        NULL
      ) AS name,sm.phone_country_code,

      -- Phone based on user_type (from partners if user_type = 3, from staff_members otherwise, null for admin)
      CASE 
        WHEN u.user_type = 3 THEN p.phone
        WHEN u.user_type != 3 THEN sm.phone
        ELSE NULL
      END AS phone,

      -- Permission flags with email and SMS enabled values
      CASE WHEN FIND_IN_SET(22, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_new_estimate,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 22 AND status != 0 LIMIT 1), 0) AS pcb_new_estimate_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 22 AND status != 0 LIMIT 1), 0) AS pcb_new_estimate_sms,
      
      CASE WHEN FIND_IN_SET(18, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_new_sales_order,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 18 AND status != 0 LIMIT 1), 0) AS pcb_new_sales_order_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 18 AND status != 0 LIMIT 1), 0) AS pcb_new_sales_order_sms,
      
      CASE WHEN FIND_IN_SET(20, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_new_jobs,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 20 AND status != 0 LIMIT 1), 0) AS pcb_new_jobs_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 20 AND status != 0 LIMIT 1), 0) AS pcb_new_jobs_sms,
      
      CASE WHEN FIND_IN_SET(21, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_new_installs,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 21 AND status != 0 LIMIT 1), 0) AS pcb_new_installs_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 21 AND status != 0 LIMIT 1), 0) AS pcb_new_installs_sms,
      
      CASE WHEN FIND_IN_SET(19, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_final_invoice,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 19 AND status != 0 LIMIT 1), 0) AS pcb_final_invoice_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 19 AND status != 0 LIMIT 1), 0) AS pcb_final_invoice_sms,
      
      CASE WHEN FIND_IN_SET(14, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_new_design_jobs,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 14 AND status != 0 LIMIT 1), 0) AS pcb_new_design_jobs_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 14 AND status != 0 LIMIT 1), 0) AS pcb_new_design_jobs_sms,
      
      CASE WHEN FIND_IN_SET(23, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_completed_design_jobs,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 23 AND status != 0 LIMIT 1), 0) AS pcb_completed_design_jobs_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 23 AND status != 0 LIMIT 1), 0) AS pcb_completed_design_jobs_sms,

      CASE WHEN FIND_IN_SET(17, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS view_design_permission,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 17 AND status != 0 LIMIT 1), 0) AS view_design_permission_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 17 AND status != 0 LIMIT 1), 0) AS view_design_permission_sms,
      
      -- New PCB privileges with names and email/SMS enabled values
      CASE WHEN FIND_IN_SET(15, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_approved_designs,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 15 AND status != 0 LIMIT 1), 0) AS pcb_approved_designs_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 15 AND status != 0 LIMIT 1), 0) AS pcb_approved_designs_sms,
      
      CASE WHEN FIND_IN_SET(16, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_rejected_designs,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 16 AND status != 0 LIMIT 1), 0) AS pcb_rejected_designs_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 16 AND status != 0 LIMIT 1), 0) AS pcb_rejected_designs_sms,
      
      CASE WHEN FIND_IN_SET(1, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_overview,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 1 AND status != 0 LIMIT 1), 0) AS pcb_overview_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 1 AND status != 0 LIMIT 1), 0) AS pcb_overview_sms,
      
      CASE WHEN FIND_IN_SET(2, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_status_report,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 2 AND status != 0 LIMIT 1), 0) AS pcb_status_report_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 2 AND status != 0 LIMIT 1), 0) AS pcb_status_report_sms,
      
      CASE WHEN FIND_IN_SET(3, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_financial_analysis,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 3 AND status != 0 LIMIT 1), 0) AS pcb_financial_analysis_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 3 AND status != 0 LIMIT 1), 0) AS pcb_financial_analysis_sms,
      
      CASE WHEN FIND_IN_SET(4, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_upcoming_deadlines,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 4 AND status != 0 LIMIT 1), 0) AS pcb_upcoming_deadlines_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 4 AND status != 0 LIMIT 1), 0) AS pcb_upcoming_deadlines_sms,
      
      CASE WHEN FIND_IN_SET(5, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_utilization,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 5 AND status != 0 LIMIT 1), 0) AS pcb_utilization_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 5 AND status != 0 LIMIT 1), 0) AS pcb_utilization_sms,
      
      CASE WHEN FIND_IN_SET(6, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_clients,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 6 AND status != 0 LIMIT 1), 0) AS pcb_clients_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 6 AND status != 0 LIMIT 1), 0) AS pcb_clients_sms,
      
      CASE WHEN FIND_IN_SET(7, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_projects,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 7 AND status != 0 LIMIT 1), 0) AS pcb_projects_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 7 AND status != 0 LIMIT 1), 0) AS pcb_projects_sms,
      
      CASE WHEN FIND_IN_SET(8, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_design_order,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 8 AND status != 0 LIMIT 1), 0) AS pcb_design_order_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 8 AND status != 0 LIMIT 1), 0) AS pcb_design_order_sms,
      
      CASE WHEN FIND_IN_SET(9, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_sales_order,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 9 AND status != 0 LIMIT 1), 0) AS pcb_sales_order_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 9 AND status != 0 LIMIT 1), 0) AS pcb_sales_order_sms,
      
      CASE WHEN FIND_IN_SET(10, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_installs,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 10 AND status != 0 LIMIT 1), 0) AS pcb_installs_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 10 AND status != 0 LIMIT 1), 0) AS pcb_installs_sms,
      
      CASE WHEN FIND_IN_SET(11, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_installation_calendar,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 11 AND status != 0 LIMIT 1), 0) AS pcb_installation_calendar_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 11 AND status != 0 LIMIT 1), 0) AS pcb_installation_calendar_sms,
      
      CASE WHEN FIND_IN_SET(12, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_design_calendar,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 12 AND status != 0 LIMIT 1), 0) AS pcb_design_calendar_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 12 AND status != 0 LIMIT 1), 0) AS pcb_design_calendar_sms,
      
      CASE WHEN FIND_IN_SET(13, u.pcb_item_order) > 0 THEN 1 ELSE 0 END AS pcb_upload_receipt,
      COALESCE((SELECT pcb_email_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 13 AND status != 0 LIMIT 1), 0) AS pcb_upload_receipt_email,
      COALESCE((SELECT pcb_sms_enabled FROM user_pcb_preferences WHERE user_id = u.id AND pcb_item_id = 13 AND status != 0 LIMIT 1), 0) AS pcb_upload_receipt_sms

    FROM users u
    LEFT JOIN staff_members sm ON sm.user_id = u.id
    LEFT JOIN partners p ON p.user_id = u.id
    LEFT JOIN user_roles ur ON ur.id = u.user_type

    WHERE u.id = :userId
    LIMIT 1
    `,
    {
      replacements: { userId },
      type: sequelize.QueryTypes.SELECT,
    }
  );

  return query.length ? query[0] : null;
};

const getUserNameById = async (userId) => {
  try {
    // Fetch the user based on ID
    const user = await sequelize.query(
      `SELECT username, first_name, last_name FROM users WHERE id = :userId`,
      {
        replacements: { userId },
        type: sequelize.QueryTypes.SELECT,
      }
    );

    // Check if a user is found and return their full name
    if (user.length > 0) {
      const { first_name, last_name } = user[0];
      return `${first_name} ${last_name}`;
    } else {
      return 'Unknown User'; // If user not found
    }
  } catch (error) {
    console.error('Error fetching user name:', error);
    return 'Unknown User';
  }
};

const updateSimpleViewStatusByUserId = async (userId, simpleViewEnabled) => {
  try {
    const result = await Users.update(
      { simple_view_enabled: simpleViewEnabled },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating simple view status:", error);
    return false;
  }
};

const getSimpleViewStatusByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['simple_view_enabled'],
      where: { id: userId }
    });
    return result ? result.simple_view_enabled : 0;
  } catch (error) {
    console.error("Error fetching simple view status:", error);
    return 0;
  }
};

const getUserRoleByName = async (name) => {
  try {
    console.log("Looking for user role:", name); // Log the role name to debug
    const result = await sequelize.query(
      `SELECT id FROM user_roles WHERE LOWER(TRIM(name)) = LOWER(TRIM(:name)) AND status = 1`,
      {
        replacements: { name },
        type: sequelize.QueryTypes.SELECT,
      }
    );
    console.log("Role lookup result:", result); // Log the result
    return result.length > 0 ? result[0].id : null;
  } catch (error) {
    console.error("Error getting user role by name:", error);
    return null;
  }
};

const updateClientViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { client_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating client view type:", error);
    return false;
  }
};

const getClientViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['client_view_type'],
      where: { id: userId }
    });
    return result ? result.client_view_type : 'one';
  } catch (error) {
    console.error("Error fetching client view type:", error);
    return 'one';
  }
};

const updateVendorViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { vendor_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating vendor view type:", error);
    return false;
  }
};

const getVendorViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['vendor_view_type'],
      where: { id: userId }
    });
    return result ? result.vendor_view_type : 'one';
  } catch (error) {
    console.error("Error fetching vendor view type:", error);
    return 'one';
  }
};

const updateCommunityViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { community_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating community view type:", error);
    return false;
  }
};

const getCommunityViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['community_view_type'],
      where: { id: userId }
    });
    return result ? result.community_view_type : 'one';
  } catch (error) {
    console.error("Error fetching community view type:", error);
    return 'one';
  }
};

const updateAdminViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { admin_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating admin view type:", error);
    return false;
  }
};

const getAdminViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['admin_view_type'],
      where: { id: userId }
    });
    return result ? result.admin_view_type : 'one';
  } catch (error) {
    console.error("Error fetching admin view type:", error);
    return 'one';
  }
};

const updateProjectViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { project_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating project view type:", error);
    return false;
  }
};

const getProjectViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['project_view_type'],
      where: { id: userId }
    });
    return result ? result.project_view_type : 'one';
  } catch (error) {
    console.error("Error fetching project view type:", error);
    return 'one';
  }
};

const updateCommunityDesignViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { community_design_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating community design view type:", error);
    return false;
  }
};

const getCommunityDesignViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['community_design_view_type'],
      where: { id: userId }
    });
    return result ? result.community_design_view_type : 'one';
  } catch (error) {
    console.error("Error fetching community design view type:", error);
    return 'one';
  }
};

const updateCommunityInstallViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { community_install_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating community install view type:", error);
    return false;
  }
};

const getCommunityInstallViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['community_install_view_type'],
      where: { id: userId }
    });
    return result ? result.community_install_view_type : 'one';
  } catch (error) {
    console.error("Error fetching community install view type:", error);
    return 'one';
  }
};

const updateCommunityJobViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { community_job_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating community job view type:", error);
    return false;
  }
};

const getCommunityJobViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['community_job_view_type'],
      where: { id: userId }
    });
    return result ? result.community_job_view_type : 'one';
  } catch (error) {
    console.error("Error fetching community job view type:", error);
    return 'one';
  }
};

const updateCommunityOfficialRequestViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { community_official_request_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating community official request view type:", error);
    return false;
  }
};

const getCommunityOfficialRequestViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['community_official_request_view_type'],
      where: { id: userId }
    });
    return result ? result.community_official_request_view_type : 'one';
  } catch (error) {
    console.error("Error fetching community official request view type:", error);
    return 'one';
  }
};

const updateDesignOrderViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { design_order_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating design order view type:", error);
    return false;
  }
};

const getDesignOrderViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['design_order_view_type'],
      where: { id: userId }
    });
    return result ? result.design_order_view_type : 'one';
  } catch (error) {
    console.error("Error fetching design order view type:", error);
    return 'one';
  }
};

const updateDesignOrderJobViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { design_order_job_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating design order job view type:", error);
    return false;
  }
};

const getDesignOrderJobViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['design_order_job_view_type'],
      where: { id: userId }
    });
    return result ? result.design_order_job_view_type : 'one';
  } catch (error) {
    console.error("Error fetching design order job view type:", error);
    return 'one';
  }
};

const updateInstallationViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { installation_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating installation view type:", error);
    return false;
  }
};

const getInstallationViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['installation_view_type'],
      where: { id: userId }
    });
    return result ? result.installation_view_type : 'one';
  } catch (error) {
    console.error("Error fetching installation view type:", error);
    return 'one';
  }
};

const updateOfficialRequestViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { official_request_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating official request view type:", error);
    return false;
  }
};

const getOfficialRequestViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['official_request_view_type'],
      where: { id: userId }
    });
    return result ? result.official_request_view_type : 'one';
  } catch (error) {
    console.error("Error fetching official request view type:", error);
    return 'one';
  }
};

const updatePickupViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { pickup_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating pickup view type:", error);
    return false;
  }
};

const getPickupViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['pickup_view_type'],
      where: { id: userId }
    });
    return result ? result.pickup_view_type : 'one';
  } catch (error) {
    console.error("Error fetching pickup view type:", error);
    return 'one';
  }
};

const updateProposalViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { proposal_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating proposal view type:", error);
    return false;
  }
};

const getProposalViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['proposal_view_type'],
      where: { id: userId }
    });
    return result ? result.proposal_view_type : 'one';
  } catch (error) {
    console.error("Error fetching proposal view type:", error);
    return 'one';
  }
};

const updateQuotationViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { quotation_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating quotation view type:", error);
    return false;
  }
};

const getQuotationViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['quotation_view_type'],
      where: { id: userId }
    });
    return result ? result.quotation_view_type : 'one';
  } catch (error) {
    console.error("Error fetching quotation view type:", error);
    return 'one';
  }
};

const updateShippingViewTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { shipping_view_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating shipping view type:", error);
    return false;
  }
};

const getShippingViewTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['shipping_view_type'],
      where: { id: userId }
    });
    return result ? result.shipping_view_type : 'one';
  } catch (error) {
    console.error("Error fetching shipping view type:", error);
    return 'one';
  }
};

const updateViewProjectTypeByUserId = async (userId, viewType) => {
  try {
    const result = await Users.update(
      { view_project_type: viewType },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating view project type:", error);
    return false;
  }
};

const getViewProjectTypeByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['view_project_type'],
      where: { id: userId }
    });
    return result ? result.view_project_type : 'one';
  } catch (error) {
    console.error("Error fetching view project type:", error);
    return 'one';
  }
};

const getUserPinByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      attributes: ['user_pin_unique'],
      where: { id: userId }
    });
    return result ? result.user_pin_unique : null;
  } catch (error) {
    console.error("Error fetching user PIN:", error);
    return null;
  }
};

const updateUserPinByUserId = async (userId, newPin) => {
  try {
    const result = await Users.update(
      { user_pin_unique: newPin },
      { where: { id: userId } }
    );
    return result[0] > 0;
  } catch (error) {
    console.error("Error updating user PIN:", error);
    return false;
  }
};

const isTeamMemberByUserId = async (userId) => {
  try {
    const result = await sequelize.query(
      `SELECT * FROM staff_members WHERE user_id = :userId`,
      { replacements: { userId } }
    );
    return result ? result[0] : false;
  } catch (error) {
    console.error("Error checking if user is a team member:", error);
    return false;
  }
};

module.exports = {
  Users,
  incrementField,
  addNotification,
  getAllAdminIds,
  getTeamByUserId,
  updateUser,
  getUserByEmail,
  addUser,
  deleteUser,
  getAllAdminEmails,
  isPrivileged,
  getAllNotifications,
  getAllNotificationsCount,
  getTotalNotificationsCount,
  getUserById,
  getUserByIdForteam,
  getAllPaymentMethods,
  getPartnerByUserId,
  getAllStates,
  getAllCountries,
  getAllIndustries,
  isEmailExist,
  getLeadPhones,
  fetchCompleteTwilioCalls,
  fetchTwilioCallsByFiterCriteria,
  fetchCompleteTwilioMessages,
  fetchTwilioMessagesByFilter,
  getAllWorkflowStages,
  isOnboardComplete,
  updatePartner,
  getAllRelationshipManagerdata,
  getAllRelationManagerId,
  scanCrmPhoneNumber,
  fetchNotificationCount,
  updateNotification,
  findUserByEmail,
  findUserByPhoneNumber,
  getAllUserIds,
  getAllAdminUserIds,
  chatPositionByUser,
  updateChatPositionByUserId,
  getPartnersUserIdById,
  getPhoneNumber,
  getNotificationSettingsByUserId,
  getUserNotificationAndPermissionSettings,
  getCountryCodeNumber,
  getAllAdminAndManagerUserIds,
  getAllClientUserIds,
  updateStaffsOfAClient,
  getUserNameById,
  updateSimpleViewStatusByUserId,
  getSimpleViewStatusByUserId,
  getUserRoleByName,
  updateClientViewTypeByUserId,
  getClientViewTypeByUserId,
  updateVendorViewTypeByUserId,
  getVendorViewTypeByUserId,
  updateCommunityViewTypeByUserId,
  getCommunityViewTypeByUserId,
  updateAdminViewTypeByUserId,
  getAdminViewTypeByUserId,
  updateProjectViewTypeByUserId,
  getProjectViewTypeByUserId,
  updateCommunityDesignViewTypeByUserId,
  getCommunityDesignViewTypeByUserId,
  updateCommunityInstallViewTypeByUserId,
  getCommunityInstallViewTypeByUserId,
  updateCommunityJobViewTypeByUserId,
  getCommunityJobViewTypeByUserId,
  updateCommunityOfficialRequestViewTypeByUserId,
  getCommunityOfficialRequestViewTypeByUserId,
  updateDesignOrderViewTypeByUserId,
  getDesignOrderViewTypeByUserId,
  updateDesignOrderJobViewTypeByUserId,
  getDesignOrderJobViewTypeByUserId,
  updateInstallationViewTypeByUserId,
  getInstallationViewTypeByUserId,
  updateOfficialRequestViewTypeByUserId,
  getOfficialRequestViewTypeByUserId,
  updatePickupViewTypeByUserId,
  getPickupViewTypeByUserId,
  updateProposalViewTypeByUserId,
  getProposalViewTypeByUserId,
  updateQuotationViewTypeByUserId,
  getQuotationViewTypeByUserId,
  updateShippingViewTypeByUserId,
  getShippingViewTypeByUserId,
  updateViewProjectTypeByUserId,
  getViewProjectTypeByUserId,
  getUserPinByUserId,
  updateUserPinByUserId,
  isTeamMemberByUserId,
};
