const sequelize = require("../config/dbconnection");
const Sequelize = require("sequelize");
const Admin = require("../models/admin");
const { empty, getAgoString } = require("../Helpers/Common");
const fs = require("node:fs");
const util = require("node:util");
const { pipeline } = require("node:stream");
const pump = util.promisify(pipeline);

const StaffMembers = sequelize.define(
  "staff_members",
  {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
    },
    first_name: { type: Sequelize.STRING(100), allowNull: false },
    last_name: {
      type: Sequelize.STRING(100),
      allowNull: true,
      defaultValue: null,
    },
    email: {
      type: Sequelize.STRING(191),
      allowNull: true,
      defaultValue: null
    },
    phone: { type: Sequelize.STRING(20), allowNull: true, defaultValue: null },
    phone_country_code: { type: Sequelize.STRING(5), allowNull: true, defaultValue: null },
    forward_no: { type: Sequelize.STRING(20), allowNull: true, defaultValue: null },
    forward_country_code: { type: Sequelize.STRING(5), allowNull: true, defaultValue: null },
    hourly_rate: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    city: { type: Sequelize.STRING(200), allowNull: true, defaultValue: null },
    state_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    contact_tags: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    workflows: {
      type: Sequelize.STRING(200),
      allowNull: true,
      defaultValue: null,
    },
    permissions: { type: Sequelize.STRING(200), allowNull: false },
    services : {
      type: Sequelize.STRING(200),
      allowNull: true,
      defaultValue: null,
    },
    personal_timezone: {
      type: Sequelize.STRING(20),
      allowNull: true,
      defaultValue: null,
    },
    date_format: {
      type: Sequelize.STRING(15),
      allowNull: true,
      defaultValue: null,
    },
    time_format: {
      type: Sequelize.TINYINT,
      allowNull: false,
      defaultValue: 24,
    },
    internal_note: {
      type: Sequelize.TEXT,
      allowNull: true,
      defaultValue: null,
    },
    about_me: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    onboard_complete: {
      type: Sequelize.TINYINT,
      allowNull: false,
      defaultValue: 0,
    },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    user_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    card_order:{ type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
  },
  { timestamps: false }
);

const RequestTeams = sequelize.define(
  "request_teams",
  {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
    },
    request_id: { type: Sequelize.INTEGER, allowNull: false },
    team_user_id: { type: Sequelize.INTEGER, allowNull: false },
    last_seen: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
  },
  { timestamps: false }
);

const TeamMessages = sequelize.define(
  "team_messages",
  {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
    },
    user_id: { type: Sequelize.INTEGER, allowNull: false },
    to_id: { type: Sequelize.INTEGER, allowNull: false },
    message: { type: Sequelize.TEXT, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    seen_user: { type: Sequelize.CHAR(1), allowNull: false, defaultValue: "N" },
    seen_to: { type: Sequelize.CHAR(1), allowNull: false, defaultValue: "N" },
  },
  { timestamps: false }
);

const getAllTeams = async (
  all = false,
  where = [],
  usersId,
  usersType,
  teamData,
  page,
  rowPerpage,
  search,
  isClient,
  orderBy = 'name',
  order = 'asc',
  isInstallers=false
) => {
  try {
    // console.log('all---',all);
    // console.log('usersId---',usersId);
    // console.log('usersType---',usersType);
    // console.log('teamData---',teamData);
    // console.log('page---',page);
    // console.log('rowPerpage---',rowPerpage);
    // console.log('search---',search);

    const userType = parseInt(usersType);
    const userId = parseInt(usersId);
    let offset = 0;
    if(rowPerpage){
       offset = (page - 1) * rowPerpage;
    }
    let totalCount=0
    let content = ''
    if (!empty(search)) {
      let normalizedSearch = search.trim().toLowerCase();
      content = `and LOWER(REPLACE(TRIM(CONCAT(p.first_name, ' ', p.last_name)), '(', '')) LIKE '%${normalizedSearch.replace(/'/g, "''")}%'`
    }  
    let sql =
      `SELECT DISTINCT p.*,CASE WHEN p.onboard_complete=0 THEN 'N' ELSE 'Y' END AS active, ts.service_id, ` +
      ` u.username,u.company_name,u.company_logo,u.user_type,u.status AS user_status,u.profile_pic,s.short_name AS state,0 AS rating,0 AS no_of_reviews,` +
      ` COALESCE(rp.no_of_requests,0) AS no_of_requests, ur.name AS userRole` +
      ` FROM staff_members p` +
      ` LEFT JOIN users u ON p.user_id=u.id` +
      ` LEFT JOIN states s ON p.state_id=s.id` +
      ` LEFT JOIN user_roles ur ON ur.id=u.user_type` +
      ` LEFT JOIN team_services ts ON ts.team_id=p.id ` +
      ` LEFT JOIN (` +
      ` SELECT COUNT(rt.id) AS no_of_requests,rt.team_user_id FROM request_teams rt ` +
      ` LEFT JOIN requests r ON r.id=rt.request_id ` +
      ` WHERE rt.status=1 and r.status!=0 ` +
      ` GROUP BY rt.team_user_id ` +
      ` ) rp ON p.id=rp.team_user_id `
     ;
    sql += ` WHERE`;

    if(userId){
      sql += ` p.user_id != ${userId} AND`;
    }
    if (userType !== 1 && userType !== 3) {

     if(!empty(teamData)){
      if (teamData?.length > 0) {

        sql += ` p.id != ${teamData[0].id} AND`;
      }
    }
    }

     if (userType === 3 || isClient) {
        sql += ` p.added_by = ${userId} AND `;
      }else{
        sql += ` u.added_by_client =0 AND `;
      }

    // Filter for installers when isInstallers is true
    if (isInstallers) {
      sql += ` (ur.user_permissions LIKE '%260%' OR u.user_type = 19 OR u.user_type = 24) AND `;
    }

    if (all) {
      sql += ` p.status != 2`;
    } else {
      sql += ` p.status = 1`;
    }

    if (!empty(where)) {
      sql += ` AND (`;
      if (!empty(where.service_ids)) {
        sql += ` ts.service_id IN (${where.service_ids.join(",")})`;
      }

      if (!empty(where.state_ids)) {
        sql += ` OR p.state_id IN (${where.state_ids.join(",")})`;
      }
      if (!empty(where.availability_ids)) {
        sql += ` OR p.onboard_complete IN (${where.availability_ids.join(
          ","
        )})`;
      }
      sql += `)`;
    }
    sql += ` ${content}`;

    // Normalize sorting inputs (controllers sometimes pass non-strings like false/null)
    const normalizedOrderBy = (typeof orderBy === 'string' && orderBy.trim())
      ? orderBy.trim().toLowerCase()
      : 'name'
    const normalizedOrderUpper = (() => {
      const o = (typeof order === 'string' && order.trim()) ? order.trim().toUpperCase() : 'ASC'
      return (o === 'ASC' || o === 'DESC') ? o : 'ASC'
    })()

    // Dynamic sorting based on orderBy and order parameters
    if (normalizedOrderBy === 'name') {
      sql += ` ORDER BY LOWER(REPLACE(TRIM(COALESCE(CONCAT(p.first_name, ' ', p.last_name), '')), '(', '')) ${normalizedOrderUpper}`;
    } else if (normalizedOrderBy === 'id') {
      sql += ` ORDER BY p.id ${normalizedOrderUpper}`;
    } else {
      // Default to name sorting if orderBy is not recognized
      sql += ` ORDER BY LOWER(REPLACE(TRIM(COALESCE(CONCAT(p.first_name, ' ', p.last_name), '')), '(', '')) ${normalizedOrderUpper}`;
    }
    if(rowPerpage){
    sql += ` LIMIT ${rowPerpage} OFFSET ${offset} `;
    }
 
    let result = await sequelize.query(sql);

    let data = result[0];
///////////////new total count sec
    let totalCountQuery = `SELECT COUNT(DISTINCT p.id) as totalCount FROM staff_members p
    LEFT JOIN users u ON p.user_id=u.id
    LEFT JOIN user_roles ur ON ur.id=u.user_type
    WHERE p.status != 2`;

    if (userType === 3 || isClient) {
      totalCountQuery += ` AND p.added_by = ${userId}`;
    }

    // Filter for installers when isInstallers is true
    if (isInstallers) {
      totalCountQuery += ` AND (ur.user_permissions LIKE '%260%' OR u.user_type = 19 OR u.user_type = 24)`;
    }

    // ✅ Include content (search condition) properly
    if (!empty(search)) {
      let normalizedSearch = search.trim().toLowerCase();
      totalCountQuery += ` AND LOWER(REPLACE(TRIM(CONCAT(p.first_name, ' ', p.last_name)), '(', '')) LIKE '%${normalizedSearch.replace(/'/g, "''")}%'`;
    }    
    
totalCount = await sequelize.query(totalCountQuery);
/////////////////////////old total count sec
    // totalCount = await sequelize.query(`
    // select count(p.id) as totalCount from staff_members p where p.status !=2 ${content} `)
    if (data) {
      if (data.length > 0) {
        return {data,totalCount:totalCount[0][0].totalCount}
      }
    } else {
      return false;
    }
  } catch (error) {
    console.log("Error------", error);
  }
};

const getTeamMembersForCollaborators = async () => {
  try {
    // Define the base SQL query to fetch team members
    let sql = `
      SELECT DISTINCT p.*, 
             CASE WHEN p.onboard_complete=0 THEN 'N' ELSE 'Y' END AS active, 
             ts.service_id, 
             u.username, u.company_name, u.company_logo, u.user_type, 
             u.status AS user_status, u.profile_pic, s.short_name AS state, 
             0 AS rating, 0 AS no_of_reviews, 
             COALESCE(rp.no_of_requests, 0) AS no_of_requests, 
             ur.name AS userRole
      FROM staff_members p
      LEFT JOIN users u ON p.user_id=u.id
      LEFT JOIN states s ON p.state_id=s.id
      LEFT JOIN user_roles ur ON ur.id=u.user_type
      LEFT JOIN team_services ts ON ts.team_id=p.id
      LEFT JOIN (
        SELECT COUNT(rt.id) AS no_of_requests, rt.team_user_id 
        FROM request_teams rt
        LEFT JOIN requests r ON r.id=rt.request_id
        WHERE rt.status=1 AND r.status!=0
        GROUP BY rt.team_user_id
      ) rp ON p.id=rp.team_user_id
      WHERE p.status = 1 AND u.user_type != 51 AND u.user_type = 12`;


    // Execute the query and get the results
    const result = await sequelize.query(sql);
    const data = result[0];

    // Return the data
    return data?.length > 0 ? data : [];
  } catch (error) {
    console.log('Error in getTeamMembers:', error);
    return[];
  }
};


const getAllSalesRepsMembers = async (
  all = false,
  where = [],
  usersId,
  usersType,
  teamData,
  page,
  rowPerpage,
  search,
  isClient
) => {
  try {
    // console.log('all---',all);
    // console.log('usersId---',usersId);
    // console.log('usersType---',usersType);
    // console.log('teamData---',teamData);
    // console.log('page---',page);
    // console.log('rowPerpage---',rowPerpage);
    // console.log('search---',search);

    const userType = parseInt(usersType);
    const userId = parseInt(usersId);
    let offset = 0;
    if(rowPerpage){
       offset = (page - 1) * rowPerpage;
    }
    let totalCount=0
    let content = ''
    if (!empty(search)) {
      let normalizedSearch = search.trim().toLowerCase();
      content = `and LOWER(REPLACE(TRIM(CONCAT(p.first_name, ' ', p.last_name)), '(', '')) LIKE '%${normalizedSearch.replace(/'/g, "''")}%'`
    }  
    let sql =
      `SELECT DISTINCT p.*,CASE WHEN p.onboard_complete=0 THEN 'N' ELSE 'Y' END AS active, ts.service_id, ` +
      ` u.username,u.company_name,u.company_logo,u.user_type,u.status AS user_status,u.profile_pic,s.short_name AS state,0 AS rating,0 AS no_of_reviews,` +
      ` COALESCE(rp.no_of_requests,0) AS no_of_requests, ur.name AS userRole` +
      ` FROM staff_members p` +
      ` LEFT JOIN users u ON p.user_id=u.id` +
      ` LEFT JOIN states s ON p.state_id=s.id` +
      ` LEFT JOIN user_roles ur ON ur.id=u.user_type` +
      ` LEFT JOIN team_services ts ON ts.team_id=p.id ` +
      ` LEFT JOIN (` +
      ` SELECT COUNT(rt.id) AS no_of_requests,rt.team_user_id FROM request_teams rt ` +
      ` LEFT JOIN requests r ON r.id=rt.request_id ` +
      ` WHERE rt.status=1 and r.status!=0 ` +
      ` GROUP BY rt.team_user_id ` +
      ` ) rp ON p.id=rp.team_user_id `
     ;
    sql += ` WHERE`;
   
    // if(userId){
    //   sql += ` p.user_id != ${userId} AND`;
    // }
    if (userType !== 1 && userType !== 3) {
    
    //  if(!empty(teamData)){
    //   if (teamData?.length > 0) {

    //     sql += ` p.id != ${teamData[0].id} AND`;
    //   }
    // }
    }

     if (userType === 3 || isClient) {
        sql += ` p.added_by = ${userId} AND `;
      }else{
        sql += ` u.added_by_client =0 AND `;
      }

    if (all) {
      sql += ` p.status != 2 AND u.user_type IN (12, 1) AND u.user_type != 51`;
    } else {
      sql += ` p.status = 1 AND u.user_type IN (12, 1) AND u.user_type != 51`;
    }

    if (!empty(where)) {
      sql += ` AND (`;
      if (!empty(where.service_ids)) {
        sql += ` ts.service_id IN (${where.service_ids.join(",")})`;
      }

      if (!empty(where.state_ids)) {
        sql += ` OR p.state_id IN (${where.state_ids.join(",")})`;
      }
      if (!empty(where.availability_ids)) {
        sql += ` OR p.onboard_complete IN (${where.availability_ids.join(
          ","
        )})`;
      }
      sql += `)`;
    }
    sql += ` ${content} ORDER BY p.card_order ASC `;
    if(rowPerpage){
    sql += ` LIMIT ${rowPerpage} OFFSET ${offset} `;
    }
 
    let result = await sequelize.query(sql);

    let data = result[0];
///////////////new total count sec
    let totalCountQuery = `SELECT COUNT(p.id) as totalCount FROM staff_members p LEFT JOIN users u ON p.user_id=u.id WHERE p.status != 2 AND u.user_type IN (12, 1) AND u.user_type != 51`;

    if (userType === 3 || isClient) {
      totalCountQuery += ` AND p.added_by = ${userId}`;
    }

    // ✅ Include content (search condition) properly
    if (!empty(search)) {
      let normalizedSearch = search.trim().toLowerCase();
      totalCountQuery += ` AND LOWER(REPLACE(TRIM(CONCAT(p.first_name, ' ', p.last_name)), '(', '')) LIKE '%${normalizedSearch.replace(/'/g, "''")}%'`;
    }    
    
totalCount = await sequelize.query(totalCountQuery);
/////////////////////////old total count sec
    // totalCount = await sequelize.query(`
    // select count(p.id) as totalCount from staff_members p where p.status !=2 ${content} `)
    let [signwiseAdminData] = await sequelize.query(`
      SELECT users.*, ur.name AS userRole FROM users
      LEFT JOIN user_roles ur ON ur.id = users.user_type
      where users.id = 1`);
    let allData = [...data, ...signwiseAdminData];
    if (allData) {
      if (allData.length > 0) {
        return {data:allData,totalCount:totalCount[0][0].totalCount + signwiseAdminData?.length}
      }
    } else {
      return false;
    }
  } catch (error) {
    console.log("Error------", error);
  }
};

const getAllStates = async (order = "DESC") => {
  const result = await sequelize.query(
    ` SELECT s.*, company_name AS authorized_by, COALESCE(p.no_of_teams,0) AS no_of_teams` +
      ` FROM states s` +
      ` LEFT JOIN users u ON s.added_by=u.id` +
      ` LEFT JOIN (` +
      ` SELECT COUNT(id) AS no_of_teams,state_id FROM staff_members WHERE status=1 GROUP BY state_id` +
      ` ) p ON s.id=p.state_id` +
      ` WHERE s.status = 1` +
      ` ORDER BY s.name ${order}`
  );
  if (result) {
    if (result[0].length > 0) {
      return result[0];
    } else {
      return false;
    }
  } else {
    return false;
  }
};

const getAllServices = async (order = "DESC") => {
  const result = await sequelize.query(
    ` SELECT s.*, company_name as authorized_by,COALESCE(p.no_of_teams,0) AS no_of_teams` +
      ` FROM services s` +
      ` LEFT JOIN users u ON s.added_by=u.id` +
      ` LEFT JOIN (` +
      ` SELECT COUNT(ts.id) AS no_of_teams,ts.service_id FROM team_services ts` +
      ` LEFT JOIN staff_members p ON p.id=ts.team_id WHERE ts.status=1 AND p.status=1 GROUP BY ts.service_id` +
      ` ) p ON s.id=p.service_id` +
      ` WHERE s.status = 1` +
      ` ORDER BY s.id ${order}`
  );

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

const getAllTeamServices = async (teamId) => {
  let result = await sequelize.query(
    `SELECT ps.*,s.name AS service_name FROM team_services ps` +
      ` LEFT JOIN services s ON ps.service_id=s.id` +
      ` WHERE ps.status = 1 AND ps.team_id = ${teamId}` +
      ` ORDER BY ps.id ASC`
  );
  if (result) {
    if (result[0] > 0) {
      // console.log("getAllTeamServices", result[0]);
      return result[0];
    } else {
      return false;
    }
  } else {
    return false;
  }
};

const getAllAvailabilities = async () => {
  const result = await sequelize.query(
    `SELECT p.onboard_complete, COALESCE(COUNT(p.id),0) AS no_of_teams` +
      ` FROM staff_members p` +
      ` WHERE p.status!=2 GROUP BY p.onboard_complete`
  );
  let data = {
    0: {
      id: 0,
      name: "Onboarding not Completed",
      no_of_teams: 0,
    },
    1: {
      id: 1,
      name: "Onboarding Completed",
      no_of_teams: 0,
    },
  };
  if (result) {
    if (result[0].length > 0) {
      // console.log("result[0]---", result[0]);
      for (const row of result[0]) {
        data[row.onboard_complete]["no_of_teams"] = row.no_of_teams;
      }
      // console.log("data--", data);
      return data;
    } else {
      return false;
    }
  } else {
    return false;
  }
};

const getAllWorkflows = async () => {
  const result = await sequelize.query(
    `SELECT ow.* FROM onboard_workflows ow WHERE ow.status = 1 ORDER BY ow.id DESC`
  );
  if (result) {
    if (result[0].length > 0) {
      return result[0];
    } else {
      return false;
    }
  } else {
    return false;
  }
};

const getTeamById = async (teamId) => {
  const result = await sequelize.query(
    `SELECT p.*, CASE WHEN p.onboard_complete=0 THEN 'N' ELSE 'Y' END AS active,u2.username,u2.company_name,u2.admin_password AS pass,` +
      ` u2.user_type,ur.name AS user_role, u2.status AS user_status,u2.profile_pic,u2.company_logo,u2.department_id,u2.worker_sid,s.short_name as state,0 as rating,` +
      ` 0 as no_of_reviews,0 as no_of_requests, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, dp.name AS department_name` +
      ` FROM staff_members p` +
      ` LEFT JOIN users u2 ON p.user_id=u2.id` +
      ` LEFT JOIN users u ON p.added_by=u.id` +
      ` LEFT JOIN user_roles ur ON ur.id=u2.user_type` +
      ` LEFT JOIN states s ON p.state_id=s.id` +
      ` LEFT JOIN departments dp ON u2.department_id=dp.id` +
      ` WHERE p.id=${teamId}` +
      ` AND p.status!=2`
  );
  if (result) {
    let data = result[0];
    return data[0];
  } else {
    return false;
  }
};

const getTeamByUserId = async (userId) => {
  try{
    const result = await sequelize.query(`SELECT p.* FROM staff_members p WHERE p.user_id=${userId}`);
    if (result[0]?.length == 1) {
      return result[0][0];
    } else {
      return false;
    }
  }catch(error){
    console.log('error', error)
    return false;
  }
};

const getAllUserRoles = async (isClientUserRole) => {
  try {
    const result = await sequelize.query(
      `SELECT DISTINCT s.*,CONCAT(u.first_name,' ',u.last_name) AS authorized_by` +
        ` FROM user_roles s` +
        ` LEFT JOIN users u ON s.added_by=u.id` +
        ` WHERE s.status = 1 AND s.isClientUserRole = ${isClientUserRole ? 1 : 0} ` +
        ` ORDER BY s.id DESC`
    );
    if (result) {
      console.log(" getAllUserRoles -- result[0] -- ", result[0].length);
      return result[0];
    } else {
      return false;
    }
  } catch (error) {
    console.log("error :- ", error);
  }
};

const getAllDepartments = async (order = "DESC") => {
  const result = await sequelize.query(
    `SELECT d.* FROM departments d WHERE d.status = 1 ORDER BY d.id ${order}`
  );
  if (result) {
    console.log(" getAllDepartments -- result[0] -- ", result[0].length);
    return result[0];
  } else {
    return false;
  }
};

const updateTeam = async (uid, data) => {
  // console.log("######################", uid, data);
  try {
    const result = await StaffMembers.update(data, {
      where: {
        id: uid,
      },
    });
    // console.log("updateTeam result ---- ", result);
    if (result) {
      return true;
    } else {
      return false;
    }
  } catch (error) {
    console.error("Error updating the team member ", error);
    return false;
  }
};

const doUpload = async (file, filename) => {
  console.log("$$$$$$$$$$$$$$$$$$ filename : ", filename);
  console.log("@@@@@@@@@@@@@@@@@@ file :", file);
  await pump(
    file,
    fs.createWriteStream(`./build/images/team_members/${filename}`)
  );
  return true;
};

const addTeam = async (data) => {
  try {
    let results = await StaffMembers.create(data);
    if (results) {
      // console.log("addTeam result - ", results);
      return results;
    } else {
      return false;
    }
  } catch (error) {
    console.error("Error adding the team member ", error);
    return false;
  }
};

const getTeamByEmail = async (email) => {
  try {
    const result = await StaffMembers.findAll({
      where: { email: email, 
        status: {
        [Sequelize.Op.ne]: 2
      }
    },
    });
    if (result[0]) {
      // console.log("getTeamByEmail result - ", result[0]);
      return result[0];
    } else {
      return false;
    }
  } catch (error) {
    console.log("getTeamByEmail error - ", error);
    return false;
  }
};

const getTeamByPhoneNum = async (num) => {
  try {
    const result = await StaffMembers.findAll({
      where: { phone: num, 
        status: {
        [Sequelize.Op.ne]: 2
      }
    },
    });
    if (result[0]) {
      // console.log("getTeamByEmail result - ", result[0]);
      return result[0];
    } else {
      return false;
    }
  } catch (error) {
    console.log("getTeamByEmail error - ", error);
    return false;
  }
};

// const getTeamByPhoneNumAndPassword = async (num, password,editId) => {
//   try {

//     const query = `
//       SELECT sm.* 
//       FROM staff_members sm
//       LEFT JOIN users u ON sm.user_id = u.id
//       WHERE sm.phone = :num
//       AND u.admin_password = :password
//       AND sm.status != 2
//     `;
    
//     const [results] = await sequelize.query(query, {
//       replacements: { num, password },
//       type: sequelize.QueryTypes.SELECT,
//     });

//     if (results) {
//       return results
//     } else {
//       return false;
//     }
//   } catch (error) {
//     console.log("getTeamByPhoneNum error - ", error);
//     return false;
//   }
// };

const getTeamByPhoneNumAndPassword = async (num, password, editId) => {
  try {
    let query;
    let replacements = { num };

    if (editId && !password) {
      // If editId is provided and password is not
      query = `
      SELECT sm.* 
        FROM staff_members sm
        LEFT JOIN users u ON sm.user_id = u.id
         LEFT JOIN staff_members sm2 ON sm2.id = :editId
         LEFT JOIN users u2 ON sm2.user_id = u2.id
        WHERE sm.phone = :num
        AND sm.id != :editId AND u.admin_password =u2.admin_password 
        AND sm.status != 2;
      `;
      replacements.editId = editId;
    }else if(editId && password){
      query = `
        SELECT sm.* 
        FROM staff_members sm
        LEFT JOIN users u ON sm.user_id = u.id
        WHERE sm.phone = :num
        AND u.admin_password = :password
        AND sm.status != 2
        AND sm.id != :editId
      `;
      replacements.password = password;
      replacements.editId = editId;

    } else {
      // If password is provided
      query = `
        SELECT sm.* 
        FROM staff_members sm
        LEFT JOIN users u ON sm.user_id = u.id
        WHERE sm.phone = :num
        AND u.admin_password = :password
        AND sm.status != 2
      `;
      replacements.password = password;
    }

    const [results] = await sequelize.query(query, {
      replacements,
      type: sequelize.QueryTypes.SELECT,
    });

    if (results) {
      return true;
    } else {
      return false;
    }
  } catch (error) {
    console.log("getTeamByPhoneNumAndPassword error - ", error);
    return false;
  }
};

const deleteTeam = async (id) => {
  const deleted = await StaffMembers.destroy({
    where: {
      id: id,
    },
  });
  return deleted ? true : false;
};

const getAllRequests = async (teamId) => {
  let result = await sequelize.query(
    `SELECT rp.*,r.name,r.type,r.status AS request_status,r.timestamp FROM request_teams rp` +
      ` RIGHT JOIN requests r ON rp.request_id=r.id` +
      ` WHERE r.status!= 0` +
      ` AND rp.status= 1` +
      ` AND rp.team_user_id = ${teamId}` +
      ` ORDER BY rp.id DESC`
  );
  // console.log("getAllRequests -- ", result[0]);
  if (result) {
    if (result[0]) {
      const data = result[0].map((request) => {
        return {
          id: request.id,
          name: request.name,
          added_by: request.added_by,
          last_seen: request.last_seen,
          request_id: request.request_id,
          request_status: request.request_status,
          status: request.status,
          team_user_id: request.team_user_id,
          type: request.type,
          timestamp: getAgoString(new Date(request.timestamp * 1000)),
        };
      });
      // console.log("data ------ ", data);
      return data;
    } else {
      return false;
    }
  } else {
    return false;
  }
};

const batchInsertTeamRequest = async (data) => {
  const result = await RequestTeams.bulkCreate(data);
  // console.log("batchInsertTeamRequest result - ", result);
  if (result) {
    return result;
  } else {
    return false;
  }
};

const getRequests = async (order = "DESC", teamId = 0, exculde = false) => {
  let sql =
    `SELECT g.*,COALESCE(p.no_of_teams,0) as no_of_teams FROM requests g` +
    ` LEFT JOIN (` +
    ` SELECT count(id) AS no_of_teams,request_id FROM request_teams pg WHERE status=1 GROUP BY request_id` +
    ` ) p ON g.id=p.request_id` +
    ` WHERE g.status=2`;

  if (exculde && teamId != 0) {
    sql += ` AND g.id NOT IN (SELECT request_id FROM request_teams WHERE status=1 AND team_user_id=${teamId})`;
  } else if (!exculde && teamId != 0) {
    sql += ` AND g.id IN (SELECT request_id FROM request_teams WHERE status=1 AND team_user_id=${teamId})`;
  }
  sql += ` ORDER BY g.id ${order}`;

  const result = await sequelize.query(sql);
  // console.log("getRequests result-------------", result);
  if (result) {
    return result;
  } else {
    return false;
  }
};

const setPartnerMessage = async (to, userId, message, timestamp) => {
  try {
    let data = {};
    data.to_id = to;
    data.user_id = userId;
    data.message = message;
    data.timestamp = timestamp;
    data.seen_user = "Y";

    const result = await TeamMessages.create(data);
    return result.id;
  } catch (error) {
    console.error(error);
    throw error;
  }
};

const getLastUserMsg = async (userId, lastUser) => {
  try {
    let query = await sequelize.query(
      `SELECT u.id,c.id AS chat_id, CONCAT(u.first_name,' ',u.last_name) AS name , c.to_id ,c.message,c.timestamp` +
        ` FROM team_messages c ` +
        ` INNER JOIN users u ON u.id = c.user_id` +
        ` WHERE c.user_id IN (${userId},${lastUser}) AND c.to_id IN (${userId},${lastUser})` +
        ` ORDER BY timestamp asc`
    );

    if (query.length > 0) {
      const data = query[0].map((row) => {
        return {
          id: row.id,
          chat_id: row.chat_id,
          name: row.name,
          to_id: row.to_id,
          message: row.message,
          timestamp: getAgoString(new Date(row.timestamp * 1000)),
          direction: row.id == userId ? "right" : "left",
        };
      });
      // console.log("last message data ", data);
      return data;
    } else {
      return false;
    }
  } catch (error) {
    console.error(error);
    reply.code(500).send({ error: "Internal Server Error" });
  }
};

const getUserName = async (userId) => {
  try {
    // console.log("userId ==== ", userId);
    const query = await sequelize.query(
      `SELECT CONCAT(first_name,' ',last_name) AS name FROM users WHERE id = ${userId}`
    );
    // console.log("getUserName - ", query[0].length);
    return query[0].length == 1 ? query[0][0].name : null;
  } catch (error) {
    console.error(error);
    throw error;
  }
};

const updateMsg = async (chatId, userId) => {
  try {
    
    const result = await TeamMessages.update(
      { seen_to: "Y" },
      {
        where: {
          id: chatId,
          to_id: userId,
        },
      }
    );
    // console.log("result ------", result);
    if (result) {
      return true;
    } else {
      return false;
    }
  } catch (error) {
    console.error(error);
    throw error;
  }
};

// const getAllChatUsers = async (userId,userType) => {
//   try {
//     const result = await sequelize.query(
//       `SELECT u.* FROM users u` +
//         ` LEFT JOIN staff_members g ON g.user_id=u.id` +
//         ` WHERE (g.status=1 OR u.user_type!=3) AND u.id!=${userId}` +
//         ` ORDER BY u.id DESC`
//     );
//     if (result) {
//       const data = [];
//       if (result[0].length > 0) {
//         for (const row of result[0]) {
//           data.push(row);
//         }
//         return data;
//       } else {
//         return false;
//       }
//     } else {
//       return false;
//     }
//   } catch (error) {
//     console.error(error);
//     throw error;
//   }
// };

const getAllChatUsers = async (userId, userType) => {
  try {
    const result = await sequelize.query(
      `SELECT u.* FROM users u
       LEFT JOIN staff_members g ON g.user_id = u.id
       WHERE (g.status = 1 OR u.user_type != 3)
         AND u.id != ${userId}
         AND (u.added_by_client != 1 OR u.parent_id = ${userId})
       ORDER BY u.id DESC`
    );

    if (result) {
      const data = [];
      if (result[0].length > 0) {
        for (const row of result[0]) {
          data.push(row);
        }
        return data;
      } else {
        return false;
      }
    } else {
      return false;
    }
  } catch (error) {
    console.error(error);
    throw error;
  }
};



const getLastUser = async (userId) => {
  try {
    const result = await TeamMessages.findOne({
      attributes: ["to_id"],
      where: {
        user_id: userId,
      },
      order: [["timestamp", "DESC"]],
      limit: 1,
    });

    if (result) {
      // console.log("getLastUser result ---- ", result.to_id);
      return result.to_id;
    } else {
      return false;
    }
  } catch (error) {
    console.error(error);
    throw error;
  }
};

const getNewMsgCount = async (userId,toId) => {
  try {
    // console.log("%%%%%%%%%%%%%%%%--getNewMsgCount--userId",userId);
    // console.log("%%%%%%%%%%%%%%%%--getNewMsgCount--toId",toId);
    const result = await TeamMessages.findAll({
      attributes: [[sequelize.fn("COUNT", sequelize.literal("*")), "cnt"]],
      where: {
        user_id: userId,
        to_id : toId,
        seen_to: "N",
      },
    });
    const count = result.length > 0 ? result[0].dataValues.cnt : 0;
    // console.log("getNewMsgCount model ", count);
    return count
  } catch (error) {
    console.error(error);
    throw error;
  }
};

const getTotalNewMsgCount = async (userId) => {
  try {
    const result = await TeamMessages.findOne({
      attributes: [[sequelize.fn("COUNT", sequelize.literal("*")), "cnt"]],
      where: {
        to_id: userId,
        seen_to: "N",
      },
    });
    return result ? result.get("cnt") : 0;
  } catch (error) {
    console.error(error);
    throw error;
  }
};
const getAllFieldTemplates=async(id,stageType)=>{
  let query=await sequelize.query(` select  * from team_stages where stage_type=${stageType} and team_id=${id}`)
  let data = []
  if (query[0].length > 0) {
      data = query[0];
  }
  return data;
}

const updateCardOrderStaffMember = async (cards) => {
  if (!cards || !cards.length) {
    return false;
  }
  try {
    const updatePromises = cards.map((card, index) => {
      return StaffMembers.update(
        { card_order: index },
        { where: { id: card.id } }
      );
    });

    await Promise.all(updatePromises);

    return true;
  } catch (error) {
    console.log("Error updating card order:", error);
    throw new Error("Error updating card order");
  }
};

const getAllProjectSalesRepsMembers = async (
  all = false,
  where = [],
  usersId,
  usersType,
  teamData,
  page,
  rowPerpage,
  search,
  isClient
) => {
  try {
    // console.log('all---',all);
    // console.log('usersId---',usersId);
    // console.log('usersType---',usersType);
    // console.log('teamData---',teamData);
    // console.log('page---',page);
    // console.log('rowPerpage---',rowPerpage);
    // console.log('search---',search);

    const userType = parseInt(usersType);
    const userId = parseInt(usersId);
    let offset = 0;
    if(rowPerpage){
       offset = (page - 1) * rowPerpage;
    }
    let totalCount=0
    let content = ''
    if (!empty(search)) {
      let normalizedSearch = search.trim().toLowerCase();
      content = `and LOWER(REPLACE(TRIM(CONCAT(p.first_name, ' ', p.last_name)), '(', '')) LIKE '%${normalizedSearch.replace(/'/g, "''")}%'`
    }  
    let sql =
      `SELECT DISTINCT p.*,CASE WHEN p.onboard_complete=0 THEN 'N' ELSE 'Y' END AS active, ts.service_id, ` +
      ` u.username,u.company_name,u.company_logo,u.user_type,u.status AS user_status,u.profile_pic,s.short_name AS state,0 AS rating,0 AS no_of_reviews,` +
      ` COALESCE(rp.no_of_requests,0) AS no_of_requests, ur.name AS userRole` +
      ` FROM staff_members p` +
      ` LEFT JOIN users u ON p.user_id=u.id` +
      ` LEFT JOIN states s ON p.state_id=s.id` +
      ` LEFT JOIN user_roles ur ON ur.id=u.user_type` +
      ` LEFT JOIN team_services ts ON ts.team_id=p.id ` +
      ` LEFT JOIN (` +
      ` SELECT COUNT(rt.id) AS no_of_requests,rt.team_user_id FROM request_teams rt ` +
      ` LEFT JOIN requests r ON r.id=rt.request_id ` +
      ` WHERE rt.status=1 and r.status!=0 ` +
      ` GROUP BY rt.team_user_id ` +
      ` ) rp ON p.id=rp.team_user_id `
     ;
    sql += ` WHERE`;
   
    // if(userId){
    //   sql += ` p.user_id != ${userId} AND`;
    // }
    if (userType !== 1 && userType !== 3) {
    
    //  if(!empty(teamData)){
    //   if (teamData?.length > 0) {

    //     sql += ` p.id != ${teamData[0].id} AND`;
    //   }
    // }
    }

     if (userType === 3 || isClient) {
        sql += ` p.added_by = ${userId} AND `;
      }else{
        sql += ` u.added_by_client =0 AND `;
      }

    if (all) {
      sql += ` p.status != 2 AND u.user_type IN (12, 1) AND u.user_type != 51`;
    } else {
      sql += ` p.status = 1 AND u.user_type IN (12, 1) AND u.user_type != 51`;
    }

    if (!empty(where)) {
      sql += ` AND (`;
      if (!empty(where.service_ids)) {
        sql += ` ts.service_id IN (${where.service_ids.join(",")})`;
      }

      if (!empty(where.state_ids)) {
        sql += ` OR p.state_id IN (${where.state_ids.join(",")})`;
      }
      if (!empty(where.availability_ids)) {
        sql += ` OR p.onboard_complete IN (${where.availability_ids.join(
          ","
        )})`;
      }
      sql += `)`;
    }
    sql += ` ${content} ORDER BY p.card_order ASC `;
    if(rowPerpage){
    sql += ` LIMIT ${rowPerpage} OFFSET ${offset} `;
    }
 
    let result = await sequelize.query(sql);

    let data = result[0];
///////////////new total count sec
    let totalCountQuery = `SELECT COUNT(p.id) as totalCount FROM staff_members p LEFT JOIN users u ON p.user_id=u.id WHERE p.status != 2 AND u.user_type IN (12, 1) AND u.user_type != 51`;

    if (userType === 3 || isClient) {
      totalCountQuery += ` AND p.added_by = ${userId}`;
    }

    // ✅ Include content (search condition) properly
    if (!empty(search)) {
      let normalizedSearch = search.trim().toLowerCase();
      totalCountQuery += ` AND LOWER(REPLACE(TRIM(CONCAT(p.first_name, ' ', p.last_name)), '(', '')) LIKE '%${normalizedSearch.replace(/'/g, "''")}%'`;
    }    
    
totalCount = await sequelize.query(totalCountQuery);
/////////////////////////old total count sec
    // totalCount = await sequelize.query(`
    // select count(p.id) as totalCount from staff_members p where p.status !=2 ${content} `)
    let [signwiseAdminData] = await sequelize.query(`
      SELECT users.*, ur.name AS userRole FROM users
      LEFT JOIN user_roles ur ON ur.id = users.user_type
      where users.id = 1`);
    let allData = [...data, ...signwiseAdminData];
    if (allData) {
      if (allData.length > 0) {
        return {data:allData,totalCount:totalCount[0][0].totalCount + signwiseAdminData?.length}
      }
    } else {
      return false;
    }
  } catch (error) {
    console.log("Error------", error);
  }
};

const getStaffNamesByIds = async (ids) => {
  try {
    const idArr = Array.isArray(ids) ? ids.map(String) : (typeof ids === 'string' ? ids.split(',').map(s => String(s).trim()).filter(Boolean) : []);
    if (idArr.length === 0) return [];
    const list = await StaffMembers.findAll({ where: { id: idArr }, attributes: ['id', 'first_name', 'last_name'] });
    return list.map(r => ({ id: r.id, name: `${(r.first_name || '').trim()} ${(r.last_name || '').trim()}`.trim() }));
  } catch (err) {
    return [];
  }
};

module.exports = {
  StaffMembers,
  RequestTeams,
  TeamMessages,
  getAllTeams,
  getTeamMembersForCollaborators,
  getStaffNamesByIds,
  getAllStates,
  getAllServices,
  getAllAvailabilities,
  getAllWorkflows,
  getTeamById,
  getTeamByUserId,
  getAllUserRoles,
  getAllDepartments,
  updateTeam,
  doUpload,
  addTeam,
  getTeamByEmail,
  deleteTeam,
  getAllTeamServices,
  getAllRequests,
  batchInsertTeamRequest,
  getRequests,
  setPartnerMessage,
  getLastUserMsg,
  getUserName,
  updateMsg,
  getAllChatUsers,
  getLastUser,
  getNewMsgCount,
  getTotalNewMsgCount,
  getAllFieldTemplates,
  getTeamByPhoneNum,
  updateCardOrderStaffMember,
  getAllSalesRepsMembers,
  getAllProjectSalesRepsMembers,
  getTeamByPhoneNumAndPassword,
};
