const { empty } = require("../Helpers/Common");
const sequelize = require("../config/dbconnection");
const Sequelize = require("sequelize");
const JobCost=require('./jobCost')
const Recipe=require('./recipe')
const Request=require('./request')
const Projects=require('./Projects')
const Products=require('./product')
let includeLabourInstallService = true;
let laborInstallWhereCondition = includeLabourInstallService ? '' : ' rss.labour_cost_service_id != 10 AND'
let makeAsTaskCondition = true ? ` rss.makeInstallCard = 1 ` : ` (rqs.project_category = 11 OR (rqs.project_category != 11 AND rss.makeAsTask = 1)) `
// let makeAsTaskCondition = true ? ` rss.makeAsTask = 1 ` : ` (rqs.project_category = 11 OR (rqs.project_category != 11 AND rss.makeAsTask = 1)) `
const UserRole = sequelize.define(
  "user_roles",
  {
    id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.STRING(50), allowNull: false },
    user_permissions: { type: Sequelize.TEXT },
    staff_permission: {
      type: Sequelize.TINYINT,
      allowNull: false,
      defaultValue: 0,
    },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    added_by: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
    is_added_by_client: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    isClientUserRole: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
  },
  {
    timestamps: false,
  }
);

const Services = sequelize.define(
  "services",
  {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
    },
    name: { type: Sequelize.STRING(120), allowNull: false, charset: "utf32" },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    department_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
  },
  { timestamps: false }
);

// === Model ===
const UserPcbPreference = sequelize.define('user_pcb_preferences', {
  id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
  user_id: { type: Sequelize.INTEGER, allowNull: false },
  pcb_item_id: { type: Sequelize.INTEGER, allowNull: false },
  pcb_email_enabled: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
  pcb_sms_enabled: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
  timestamp: { type: Sequelize.INTEGER, allowNull: false },
  updated_on: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
  status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
}, { timestamps: false, tableName: 'user_pcb_preferences' });

const PartnerTypes = sequelize.define('partner_types',
{
 id:{type:Sequelize.INTEGER,allowNull: false,autoIncrement: true,primaryKey: true,},
 name: { type: Sequelize.STRING(120), allowNull: false, charset: "utf32" },
 description: { type: Sequelize.TEXT, allowNull: false, defaultValue:null },
 bg_color:{type:Sequelize.CHAR(8),allowNull:false,defaultValue:null},
 added_by: { type: Sequelize.INTEGER, allowNull: false },
 timestamp: { type: Sequelize.INTEGER, allowNull: false },
 status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
},{timestamps:false})

const CompanyProfile = sequelize.define('company_profile',{
  id:{type:Sequelize.INTEGER,allowNull: false,autoIncrement: true,primaryKey: true,},
  name: { type: Sequelize.TEXT, allowNull: true, defaultValue:null },
  company_logo:{ type: Sequelize.TEXT, allowNull: true, defaultValue:null },
  timestamp: { type: Sequelize.INTEGER, allowNull: false },
  address: { type: Sequelize.TEXT, allowNull: true, defaultValue:null },
  city: { type: Sequelize.TEXT, allowNull: true, defaultValue:null },
  state: { type: Sequelize.TEXT, allowNull: true, defaultValue:null },
  zipcode: { type: Sequelize.TEXT, allowNull: true, defaultValue:null },
  lng: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
  lat: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
  phone: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
  phone_country_code: { type: Sequelize.STRING(5), allowNull: true, defaultValue: null },
  company_fax_number: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
  company_mail: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
  company_website: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
  install_address_email: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
},{timestamps:false,tableName:'company_profile'})

const PcbItems = sequelize.define('pcb_items',{
  id:{type:Sequelize.INTEGER,allowNull: false,autoIncrement: true,primaryKey: true,},
  name: { type: Sequelize.STRING, allowNull: false },
},{timestamps:false,tableName:'pcb_items'})

const parseDecimalFields = (order) => {
  order.cost = order.cost ? parseFloat(order.cost) : null;
  order.budget = order.budget ? parseFloat(order.budget) : null;
  order.expense = order.expense ? parseFloat(order.expense) : null;
  return order;
}

const getCompanyProfileDetails = async () => {
  try {
    let result = await sequelize.query(`SELECT * FROM company_profile`);
    return result[0];
  } catch (error) {
    console.error('Error fetching company profile details:', error);
    throw error; 
  }
};

// const getAllPermissions = async () => {
//   const result = await sequelize.query(
//     `SELECT DISTINCT s.*,m.name AS module_name FROM user_privileges s` +
//       ` LEFT JOIN modules m ON m.id=s.module_id` +
//       ` WHERE s.status=1` +
//       ` ORDER BY m.id ASC`
//   );
//   let data = {};
//   if (result) {
//     if (result[0].length > 0) {
//       for (const row of result[0]) {
//         if (!data[row.module_name]) {
//           data[row.module_name] = [];
//         }
//         data[row.module_name].push(row);
//       }
//     }
//   }
//   return data;
// };


const getAllPermissions = async (userId, userType) => {
  let query = `
    SELECT DISTINCT s.*, m.name AS module_name
    FROM user_privileges s
    LEFT JOIN modules m ON m.id = s.module_id
    WHERE s.status = 1
  `;

  // Add extra condition if userType is not 3
  if (userType !== 3) {
    query += ` AND s.for_client_only != 1`;
  }

  query += ` ORDER BY m.id ASC`;

  const result = await sequelize.query(query);

  let data = {};
  if (result && result[0].length > 0) {
    for (const row of result[0]) {
      if (!data[row.module_name]) {
        data[row.module_name] = [];
      }
      data[row.module_name].push(row);
    }
  }

  return data;
};



const getAllServices = async (all = false) => {
  let sql =
    `SELECT DISTINCT s.*,CONCAT(u.first_name,' ',u.last_name) AS authorized_by, d.name AS department_name FROM services s` +
    ` LEFT JOIN users u ON s.added_by=u.id` +
    ` LEFT JOIN departments d ON s.department_id=d.id`;
  // ` ORDER BY s.id DESC`
  if (all) {
    sql += ` WHERE s.status!= 2`;
  } else {
    sql += ` WHERE s.status = 1`;
  }
  sql += ` ORDER BY s.id DESC`;
  const result = await sequelize.query(sql);
  if (result) {
    if (result[0].length > 0) {
      return result[0];
    }
  }
};

const getAllUserRoles = async (all = false,userId,userType,isClientUserRoles) => {
  let sql =
    `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`;
  if (all) {
    sql += ` WHERE s.status!= 2`;
    sql += ` AND s.id!=1`;
    // sql += ` AND s.id!=3`;
  } else {
    sql += ` WHERE s.status = 1`;
    // sql += ` AND s.id!= 3`;
  }
  if(userType == 3){
    sql += ` AND s.added_by = ${userId}`;
  }else{
     sql += ` AND u.user_type !=3`
  }
  if(isClientUserRoles){
     sql += ` AND s.isClientUserRole  = 1`
  }else{
     sql += ` AND s.isClientUserRole  = 0`
  }
  sql += ` ORDER BY s.id DESC`;

  const result = await sequelize.query(sql);
  if (result) {
    if (result[0].length > 0) {
      return result[0];
    }
  }
};

const getAllPartnerTypes = async (all = false) => {
  let sql =
    `SELECT DISTINCT s.*,concat(u.first_name,' ',u.last_name) AS authorized_by FROM partner_types s` +
    ` LEFT JOIN users u ON s.added_by=u.id`;
  if (all) {
    sql += ` WHERE s.status!= 2`;
    sql += ` AND s.id!= 1`;
    sql += ` AND s.id!= 3`;
  } else {
    sql += ` WHERE s.status= 1`;
    sql += ` AND s.id!= 3`;
  }
  sql += ` ORDER BY s.id DESC`;

  const result = await sequelize.query(sql);

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

const getSkillsById = async (table, field, id) => {
  const result = await sequelize.query(
    `SELECT service_id FROM ${table}` +
      ` WHERE status = 1` +
      ` AND ${field} = ${id}` +
      ` ORDER BY id DESC`
  );

  let data = {};
  if (result) {
    if (result[0].length > 0) {
      for (const row of result[0]) {
        data.push = row["service_id"];
      }
    }
  }
  return data;
};

const isUserRoleExist = async (name, userRoleId = 0) => {
  let sql = `SELECT s.name FROM user_roles s WHERE s.name LIKE '${name}' AND s.status!=2`;
  if (userRoleId) {
    sql += `AND s.id != ${userRoleId}`;
  }
  const result = await sequelize.query(sql);
  if (result) {
    if (result[0].length >= 1) {
      return true;
    } else {
      return false;
    }
  } else {
    return false;
  }
};

const getUserRoleById = async (userRoleId) => {
  console.log("@@@@@@@@@@@@@@@@@@@@@@@@@@@", userRoleId);
  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.id=${userRoleId}` +
      ` ORDER BY s.id DESC`
  );
  if (result) {
    if (result[0].length === 1) {
      return result[0][0];
    } else {
      return false;
    }
  } else {
    return false;
  }
};

const addUserRole = async (data) => {
  let results = await UserRole.create(data);
  return results;
};

const updateUserRole = async (roleId, data) => {
  const updatedRows = await UserRole.update(data, {
    where: {
      id: roleId,
    },
    returning: true,
  });
  if (updatedRows) {
      return true;
    } else {
      console.log("updatedRows -else-- ", updatedRows[1]);
      return false;
    }
};

const isServiceExist = async (name, serviceId = 0) => {
  let sql = `SELECT s.name FROM services s WHERE s.name LIKE '${name}' AND s.status!=2`;
  if (serviceId) {
    sql += ` AND s.id!=${serviceId}`;
  }
  const result = await sequelize.query(sql);
  if (result) {
    if (result[0].length >= 1) {
      return true;
    } else {
      return false;
    }
  } else {
    return false;
  }
};

const addService = async (data) => {
  let results = await Services.create(data);
  return results;
};

const getServiceById = async (serviceId) => {
  let result = await sequelize.query(
    `SELECT DISTINCT s.*,concat(u.first_name,' ',u.last_name) AS authorized_by, d.name AS department_name` +
      ` FROM services s` +
      ` LEFT JOIN users u ON s.added_by=u.id` +
      ` LEFT JOIN departments d ON s.department_id=d.id` +
      ` WHERE s.id = ${serviceId}` +
      ` ORDER BY s.id DESC`
  );
  if (result) {
    if (result[0].length == 1) {
      return result[0];
    } else {
      return false;
    }
  } else {
    return false;
  }
};

const updateService = async (id, data) => {
  try {
    const result = await Services.update(data, {
      where: {
        id: id,
      },
    });
    if (result) {
      return true;
    } else {
      return false;
    }
  } catch (error) {
    console.log("updateService error", error);
  }
};
const ispartnerTypeExist=async(name,id=0)=>{
  try {
    let query = `select s.name from partner_types as s where s.name like '${name}' and s.status!=2`
     if(!empty(id)){
      query += ` and s.id!=${id}`;
     }
     const result = await sequelize.query(query);
     if (result) {
       if (result[0].length >= 1) {
         return true;
       } else {
         return false;
       }
     } else {
       return false;
     }
  } catch (error) {
    console.log(error)
  }
}

const addpartnerTypes = async(data)=>{
  try {
    let result = await PartnerTypes.create(data)
    return result.id
  } catch (error) {
    console.log(error)
  }
}
const editpartnerType = async(id,data)=>{
  try {
    const result = await PartnerTypes.update(data, {
      where: {
        id: id,
      },
    });
    if (result) {
      return true;
    } else {
      return false;
    }
  } catch (error) {
    console.log(error)
  }
}
const getPartnerTypeById=async(id)=>{
  try {
    let result = await sequelize.query(`
    select distinct s.*,concat(u.first_name,' ',u.last_name) as authorized_by from partner_types as s left join
    users u on s.added_by = u.id where s.id = ${id} order by s.id desc
    `)
    
    if (result) {
      if (result[0].length == 1) {
        return result[0];
      } else {
        return false;
      }
    } else {
      return false;
    }
  } catch (error) {
    console.log(error)
  }
}
const getOnboardingCount = async () => {
  try {
    const [partnersCount] = await sequelize.query(`
      SELECT COUNT(*) AS partners_count 
      FROM partners 
      WHERE onboard_complete = 1;
    `);

    const [staffMembersCount] = await sequelize.query(`
      SELECT COUNT(*) AS staff_members_count 
      FROM staff_members 
      WHERE onboard_complete = 1;
    `);

    const [requestCount] = await sequelize.query(`
      SELECT COUNT(*) AS request_count 
      FROM requests 
      WHERE onboard_complete = 1 and status != 0;
    `);

    const totalCount = partnersCount[0].partners_count + staffMembersCount[0].staff_members_count + requestCount[0].request_count;

    return totalCount;
  } catch (error) {
    console.error('Error fetching onboarding count:', error);
    throw error;
  }
};
const getCRMDetailsCount = async()=>{
  const [contactsCount] = await sequelize.query(`
  SELECT COUNT(*) AS count 
  FROM contacts 
  WHERE status !=2;
  `);
  const [leadsCount] = await sequelize.query(`
  SELECT COUNT(*) AS count 
  FROM leads 
  WHERE status =1;
  `);
  const [opportunityCount] = await sequelize.query(`
  SELECT COUNT(*) AS count 
  FROM opportunities 
  WHERE status !=2;
  `);
return {contactsCount:contactsCount[0].count,
  leadsCount:leadsCount[0].count,
  opportunityCount:opportunityCount[0].count}
}
const trafficwiseCount=async()=>{
  const [deliveredCount] = await sequelize.query(`
  SELECT COUNT(*) AS count 
  FROM installations 
  WHERE status =3;
  `);
  const [completedCount] = await sequelize.query(`
  SELECT COUNT(*) AS count 
  FROM installations 
  WHERE status =4;
  `);
  const [AssignCount] = await sequelize.query(`
  SELECT COUNT(*) AS count 
  FROM installations 
  WHERE status =6;
  `);
  return {deliveredCount:deliveredCount[0].count,
    completedCount:completedCount[0].count,
    AssignCount:AssignCount[0].count
  }
}
const getClientCount=async()=>{
    let result = await sequelize.query(`select count(id) as total_Clients, COUNT(CASE WHEN onboard_complete = 1 THEN 1 END) as onboard_complete, COUNT(CASE WHEN onboard_complete = 0 THEN 1 END) as onboard_not_complete from partners where status != 2 AND isVendor=0`)
 
      if (result[0].length == 1) {
        return result[0][0];
      } else {
        return false;
      }
    
    
}
const getRequestCount=async()=>{
  let result = await sequelize.query(`select count(id) as total_request, COUNT(CASE WHEN status = 4 THEN 1 END) as accepted, COUNT(CASE WHEN status = 6 THEN 1 END) as rejected from requests `)
 
  if (result[0].length == 1) {
    return result[0][0];
  } else {
    return false;
  }
}
const projectCount =async(userId, userType)=>{
  let whereCondition = '';
  if(userType == 1 || userType == 8){
    whereCondition = ` `;
  }else if(userType == 3){
    whereCondition = ` where pr.user_id=${userId} `;
  }else{
    whereCondition = ` where (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
  }

  let userTypeCondition = '';
  if(userType == 1 || userType == 8){
    userTypeCondition = ''
  } else if(userType == 3){
    userTypeCondition = ` left join request_partners rp on rp.request_id=p.request_id left join partners pr on pr.id=rp.partner_id `
  }else{
    userTypeCondition = ` LEFT JOIN staff_members s1 ON s1.id=p.project_lead  
      LEFT JOIN users u1 ON s1.user_id=u1.id 
      LEFT JOIN staff_members s2 ON s2.id=p.default_assignee 
      LEFT JOIN users u2 ON s2.user_id=u2.id
      LEFT JOIN users u ON p.added_by=u.id 
      LEFT JOIN (select pm.project_id, COALESCE(u.id,u2.id) as user_id from project_members pm left join partners p on p.id=pm.partner_id left join users u on p.user_id=u.id left join staff_members s on s.id=pm.staff_id left join users u2 on s.user_id=u2.id) pmm ON pmm.project_id=p.id and pmm.user_id=${userId} `
  }

  let result = await sequelize.query(`select count(p.id) as total_project, count(case when p.status = 3 then 1 end) as post_production, count(case when (p.status = 1 or p.status=0) then 1 end) as pre_production, `
  +` count(case when (p.status=2 or p.status =4 or p.status=5 or p.status=6 or p.status=7) then 1 end) as production from projects p
    ${userTypeCondition} ${whereCondition}
  `)
 if (result[0].length == 1) {
    return result[0][0];
  } else {
    return false;
  }
}
const installationCount=async(userId, userType)=>{
  let whereCondition = '';
  if(userType == 1 || userType == 8){
    whereCondition = ` `;
  }else if(userType == 3){
    whereCondition = ` where pr.user_id=${userId} `;
  }else{
    whereCondition = ` where (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
  }

  let userTypeCondition = '';
  if(userType == 1 || userType == 8){
    userTypeCondition = ''
  } else if(userType == 3){
    userTypeCondition = ` left join request_partners rp on rp.request_id=p.request_id left join partners pr on pr.id=rp.partner_id `
  }else{
    userTypeCondition = ` LEFT JOIN staff_members s1 ON s1.id=p.project_lead  
      LEFT JOIN users u1 ON s1.user_id=u1.id 
      LEFT JOIN staff_members s2 ON s2.id=p.default_assignee 
      LEFT JOIN users u2 ON s2.user_id=u2.id
      LEFT JOIN users u ON p.added_by=u.id 
      LEFT JOIN (select pm.project_id, COALESCE(u.id,u2.id) as user_id from project_members pm left join partners p on p.id=pm.partner_id left join users u on p.user_id=u.id left join staff_members s on s.id=pm.staff_id left join users u2 on s.user_id=u2.id) pmm ON pmm.project_id=p.id and pmm.user_id=${userId} `
  }

  let result = await sequelize.query(`select count(i.id) as total_installation from installations i
    left join projects p on p.id = i.project_id
    ${userTypeCondition} ${whereCondition} ` )
 if (result[0].length == 1) {
    return result[0];
  } else {
    return false;
  }
}

const installationCountDashboard = async (userId, userType) => {
  let whereCondition = '';
  if (userType == 1 || userType == 8) {
      whereCondition = `WHERE  req.installation_acceptance IS NULL`; // Admins fetch all installations
  } else if (userType == 3) {
      whereCondition = ` WHERE pr.user_id = ${userId} `;
  } else {
      whereCondition = ` WHERE (u1.id = ${userId} OR u2.id = ${userId} OR pmm.user_id = ${userId}) `;
  }

  let userTypeCondition = '';
  if (userType == 1 || userType == 8) {
      userTypeCondition = ``;
  } else if (userType == 3) {
      userTypeCondition = `
          LEFT JOIN request_partners rp ON rp.request_id = p.request_id
          LEFT JOIN partners pr ON pr.id = rp.partner_id
      `;
  } else {
      userTypeCondition = `
          LEFT JOIN staff_members s1 ON s1.id = p.project_lead  
          LEFT JOIN users u1 ON s1.user_id = u1.id 
          LEFT JOIN staff_members s2 ON s2.id = p.default_assignee 
          LEFT JOIN users u2 ON s2.user_id = u2.id
          LEFT JOIN users u ON p.added_by = u.id 
          LEFT JOIN (
              SELECT pm.project_id, COALESCE(u.id, u2.id) AS user_id 
              FROM project_members pm 
              LEFT JOIN partners p ON p.id = pm.partner_id 
              LEFT JOIN users u ON p.user_id = u.id 
              LEFT JOIN staff_members s ON s.id = pm.staff_id 
              LEFT JOIN users u2 ON s.user_id = u2.id
          ) pmm ON pmm.project_id = p.id AND pmm.user_id = ${userId}
      `;
  }

  let finalWhereCondition = whereCondition ? `${whereCondition} AND  i.status !=0 AND req.installation_acceptance IS NULL AND complete_status =1` : ` WHERE req.installation_acceptance IS NULL AND i.status !=0 AND complete_status =1`;

  let result = await sequelize.query(`
      SELECT i.*
      FROM installations i
      LEFT JOIN projects p ON p.id = i.project_id
      LEFT JOIN requests req ON req.id = p.request_id
      ${userTypeCondition} 
      ${finalWhereCondition}
  `);

  return result[0]?.length;
};

// Optimized version for EcommerceYearlySales - returns only essential fields
const getAllProjectsOptimized = async (all = false, selectedPhase, userType, userId, where = []) => {
  try {
      let whereCondition;
      let query = ''
      if (all) {
        if(userType == 1 || userType == 8){
          whereCondition = ` `;
        }else if(userType == 3){
          whereCondition = ` where pr.user_id=${userId} `;
        }else{
          whereCondition = ` WHERE (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
        }
      } else {
        if(!empty(selectedPhase)){
          if(selectedPhase==="3"){
            console.log("#####Entered to post pro");
            whereCondition = ` where  p.status = 3 and (
                  CASE  
                      WHEN inst_counts.installation_count >= cop.product_count  
                      THEN 1  
                      ELSE 0  
                  END
              ) != 1 `;
              if(userType == 1 || userType == 8){
                whereCondition += ` `;
              }else if(userType == 3){
                whereCondition += ` and pr.user_id=${userId} `;
              }else{
                whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
              }
          }
          else if(selectedPhase==="2"){
            console.log("#####Entered to production");
            whereCondition = ` where  p.status !=1 and p.status!=3 and p.status!=0`;
            if(userType == 1 || userType == 8){
              whereCondition += ` `;
            }else if(userType == 3){
              whereCondition += ` and pr.user_id=${userId} `;
            }else{
              whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
            }
          }else if(selectedPhase==="5"){
            console.log("#####Entered to completed");
            whereCondition = ` WHERE p.status = 3 
              AND (
                  CASE  
                      WHEN inst_counts.installation_count >= cop.product_count  
                      THEN 1  
                      ELSE 0  
                  END
              ) = 1 `
              if(userType == 1 || userType == 8){
                whereCondition += ` `;
              }else if(userType == 3){
                whereCondition += ` and pr.user_id=${userId} `;
              }else{
                whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
              }
          }
          else{
            console.log("#####Entered to pre");
            whereCondition = ` where  p.status = 1 or p.status=0`;
          }
        }
        else{
          whereCondition = ` where  p.status = 3`;
          if(userType == 1 || userType == 8){
            whereCondition += ` `;
          }else if(userType == 3){
            whereCondition += ` and pr.user_id=${userId} `;
          }else{
            whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
          }
        }
      }
      let userTypeCondition = '';
      if(userType == 1 || userType == 8){
        userTypeCondition = ''
      } else if(userType == 3){
        userTypeCondition = ` left join request_partners rp on rp.request_id=p.request_id left join partners pr on pr.id=rp.partner_id `
      }else{
        userTypeCondition = ` LEFT JOIN staff_members s1 ON s1.id=p.project_lead  
          LEFT JOIN users u1 ON s1.user_id=u1.id 
          LEFT JOIN staff_members s2 ON s2.id=p.default_assignee 
          LEFT JOIN users u2 ON s2.user_id=u2.id
          LEFT JOIN (select pm.project_id, COALESCE(u.id,u2.id) as user_id from project_members pm left join partners p on p.id=pm.partner_id left join users u on p.user_id=u.id left join staff_members s on s.id=pm.staff_id left join users u2 on s.user_id=u2.id) pmm ON pmm.project_id=p.id and pmm.user_id=${userId} `
      }

      // Simplified query - only select essential fields for EcommerceYearlySales
      query = await sequelize.query(
        ` SELECT p.id, p.name, p.request_id, p.installation_date AS due_date, r.installation_acceptance, c_ord.customer_name, com.name AS community_name, r.doc_number `
        + ` FROM projects p `
        + ` ${userTypeCondition} LEFT JOIN users u ON p.added_by=u.id `
        + ` LEFT JOIN requests r ON p.request_id = r.id `
        + ` LEFT JOIN (
              SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity, 
               SUM(
                    CASE
                      WHEN cop.is_single_card = 1 THEN 1
                      ELSE cop.location_quantity
                    END
                  ) AS product_count
              FROM customer_orders co
              JOIN customer_order_products cop ON co.id = cop.order_id
              JOIN requests req ON co.request_id = req.id
              WHERE cop.material = 0
              GROUP BY co.request_id
            ) cop ON r.id = cop.request_id `
        + ` LEFT JOIN customer_orders c_ord ON c_ord.request_id = r.id `
        + ` LEFT JOIN community com ON com.id = p.community AND FIND_IN_SET(c_ord.customer_id, com.clients) > 0 AND com.status = 1 `
        + ` LEFT JOIN (SELECT request_id, COUNT(*) AS installation_count FROM installations WHERE status = 4 GROUP BY request_id) inst_counts ON inst_counts.request_id = r.id `
        + ` ${whereCondition} ORDER BY p.id DESC`
      );
      
      let data;
      if (query.length > 0) {
        data = query[0];
        return data?.map(record => parseDecimalFields(record));
      } else {
        return [];
      }
  } catch (error) {
      console.log("Server error", error);
      throw new Error("Server error");
  }
}

// Optimized version with pagination for EcommerceYearlySales - returns only essential fields with pagination
const getAllProjectsOptimizedWithPagination = async (all = false, selectedPhase, userType, userId, page = 0, limit = 20, where = []) => {
  try {
      let whereCondition;
      let query = ''
      if (all) {
        if(userType == 1 || userType == 8){
          whereCondition = ` `;
        }else if(userType == 3){
          whereCondition = ` where pr.user_id=${userId} `;
        }else{
          whereCondition = ` WHERE (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
        }
      } else {
        if(!empty(selectedPhase)){
          if(selectedPhase==="3"){
            console.log("#####Entered to post pro");
            whereCondition = ` where  p.status = 3 and (
                  CASE  
                      WHEN inst_counts.installation_count >= cop.product_count  
                      THEN 1  
                      ELSE 0  
                  END
              ) != 1 `;
              if(userType == 1 || userType == 8){
                whereCondition += ` `;
              }else if(userType == 3){
                whereCondition += ` and pr.user_id=${userId} `;
              }else{
                whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
              }
          }
          else if(selectedPhase==="2"){
            console.log("#####Entered to production");
            whereCondition = ` where  p.status !=1 and p.status!=3 and p.status!=0`;
            if(userType == 1 || userType == 8){
              whereCondition += ` `;
            }else if(userType == 3){
              whereCondition += ` and pr.user_id=${userId} `;
            }else{
              whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
            }
          }else if(selectedPhase==="5"){
            console.log("#####Entered to completed");
            whereCondition = ` WHERE p.status = 3 
              AND (
                  CASE  
                      WHEN inst_counts.installation_count >= cop.product_count  
                      THEN 1  
                      ELSE 0  
                  END
              ) = 1 `
              if(userType == 1 || userType == 8){
                whereCondition += ` `;
              }else if(userType == 3){
                whereCondition += ` and pr.user_id=${userId} `;
              }else{
                whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
              }
          }
          else{
            console.log("#####Entered to pre");
            whereCondition = ` where  p.status = 1 or p.status=0`;
          }
        }
        else{
          whereCondition = ` where  p.status = 3`;
          if(userType == 1 || userType == 8){
            whereCondition += ` `;
          }else if(userType == 3){
            whereCondition += ` and pr.user_id=${userId} `;
          }else{
            whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
          }
        }
      }
      let userTypeCondition = '';
      if(userType == 1 || userType == 8){
        userTypeCondition = ''
      } else if(userType == 3){
        userTypeCondition = ` left join request_partners rp on rp.request_id=p.request_id left join partners pr on pr.id=rp.partner_id `
      }else{
        userTypeCondition = ` LEFT JOIN staff_members s1 ON s1.id=p.project_lead  
          LEFT JOIN users u1 ON s1.user_id=u1.id 
          LEFT JOIN staff_members s2 ON s2.id=p.default_assignee 
          LEFT JOIN users u2 ON s2.user_id=u2.id
          LEFT JOIN (select pm.project_id, COALESCE(u.id,u2.id) as user_id from project_members pm left join partners p on p.id=pm.partner_id left join users u on p.user_id=u.id left join staff_members s on s.id=pm.staff_id left join users u2 on s.user_id=u2.id) pmm ON pmm.project_id=p.id and pmm.user_id=${userId} `
      }

      // Add installation_acceptance filter to whereCondition
      let finalWhereCondition = whereCondition;
      if (finalWhereCondition.trim() === '') {
        finalWhereCondition = ` WHERE r.installation_acceptance != 1 OR r.installation_acceptance IS NULL`;
      } else {
        finalWhereCondition += ` AND (r.installation_acceptance != 1 OR r.installation_acceptance IS NULL)`;
      }

      // Get total count first
      let countQuery = await sequelize.query(
        ` SELECT COUNT(p.id) as totalCount `
        + ` FROM projects p `
        + ` ${userTypeCondition} LEFT JOIN users u ON p.added_by=u.id `
        + `  JOIN requests r ON p.request_id = r.id `
        + ` LEFT JOIN (
              SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity, 
               SUM(
                    CASE
                      WHEN cop.is_single_card = 1 THEN 1
                      ELSE cop.location_quantity
                    END
                  ) AS product_count
              FROM customer_orders co
              JOIN customer_order_products cop ON co.id = cop.order_id
              JOIN requests req ON co.request_id = req.id
              WHERE cop.material = 0
              GROUP BY co.request_id
            ) cop ON r.id = cop.request_id `
        + ` LEFT JOIN customer_orders c_ord ON c_ord.request_id = r.id `
        + ` LEFT JOIN community com ON com.id = p.community AND FIND_IN_SET(c_ord.customer_id, com.clients) > 0 AND com.status = 1 `
        + ` LEFT JOIN (SELECT request_id, COUNT(*) AS installation_count FROM installations WHERE status = 4 GROUP BY request_id) inst_counts ON inst_counts.request_id = r.id `
        + ` ${finalWhereCondition}`
      );
      
      let totalCount = countQuery[0][0].totalCount;

      // Get paginated data - only select essential fields for EcommerceYearlySales
      query = await sequelize.query(
        ` SELECT p.id, p.name, p.request_id, p.installation_date AS due_date, r.installation_acceptance, c_ord.customer_name, com.name AS community_name, r.doc_number `
        + ` FROM projects p `
        + ` ${userTypeCondition} LEFT JOIN users u ON p.added_by=u.id `
        + `  JOIN requests r ON p.request_id = r.id `
        + ` LEFT JOIN (
              SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity, 
               SUM(
                    CASE
                      WHEN cop.is_single_card = 1 THEN 1
                      ELSE cop.location_quantity
                    END
                  ) AS product_count
              FROM customer_orders co
              JOIN customer_order_products cop ON co.id = cop.order_id
              JOIN requests req ON co.request_id = req.id
              WHERE cop.material = 0
              GROUP BY co.request_id
            ) cop ON r.id = cop.request_id `
        + ` LEFT JOIN customer_orders c_ord ON c_ord.request_id = r.id `
        + ` LEFT JOIN community com ON com.id = p.community AND FIND_IN_SET(c_ord.customer_id, com.clients) > 0 AND com.status = 1 `
        + ` LEFT JOIN (SELECT request_id, COUNT(*) AS installation_count FROM installations WHERE status = 4 GROUP BY request_id) inst_counts ON inst_counts.request_id = r.id `
        + ` ${finalWhereCondition} ORDER BY p.id DESC LIMIT ${limit} OFFSET ${page * limit}`
      );
      
      let data;
      if (query.length > 0) {
        data = query[0];
        return {
          data: data?.map(record => parseDecimalFields(record)),
          totalCount: totalCount
        };
      } else {
        return {
          data: [],
          totalCount: 0
        };
      }
  } catch (error) {
      console.log("Server error", error);
      throw new Error("Server error");
  }
}

  const getAllProjects = async (all = false, selectedPhase,userType, userId, where = []) => {
  try {
      let whereCondition;
      let query = ''
      if (all) {
        if(userType == 1 || userType == 8){
          whereCondition = ` `;
        }else if(userType == 3){
          whereCondition = ` where pr.user_id=${userId} `;
        }else{
          whereCondition = ` WHERE (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
        }
      } else {
        if(!empty(selectedPhase)){
          if(selectedPhase==="3"){
            console.log("#####Entered to post pro");
            whereCondition = ` where  p.status = 3 and (
                  CASE  
                      WHEN inst_counts.installation_count >= cop.product_count  
                      THEN 1  
                      ELSE 0  
                  END
              ) != 1 `;
              if(userType == 1 || userType == 8){
                whereCondition += ` `;
              }else if(userType == 3){
                whereCondition += ` and pr.user_id=${userId} `;
              }else{
                whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
              }
          }
          else if(selectedPhase==="2"){
            console.log("#####Entered to production");
            whereCondition = ` where  p.status !=1 and p.status!=3 and p.status!=0`;
            if(userType == 1 || userType == 8){
              whereCondition += ` `;
            }else if(userType == 3){
              whereCondition += ` and pr.user_id=${userId} `;
            }else{
              whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
            }
          }else if(selectedPhase==="5"){
            console.log("#####Entered to completed");
            whereCondition = ` WHERE p.status = 3 
              AND (
                  CASE  
                      WHEN inst_counts.installation_count >= cop.product_count  
                      THEN 1  
                      ELSE 0  
                  END
              ) = 1 `
              if(userType == 1 || userType == 8){
                whereCondition += ` `;
              }else if(userType == 3){
                whereCondition += ` and pr.user_id=${userId} `;
              }else{
                whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
              }
          }
          else{
            console.log("#####Entered to pre");
            whereCondition = ` where  p.status = 1 or p.status=0`;
          }
        }
        else{
          whereCondition = ` where  p.status = 3`;
          if(userType == 1 || userType == 8){
            whereCondition += ` `;
          }else if(userType == 3){
            whereCondition += ` and pr.user_id=${userId} `;
          }else{
            whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
          }
        }
      }
      let userTypeCondition = '';
      if(userType == 1 || userType == 8){
        userTypeCondition = ''
      } else if(userType == 3){
        userTypeCondition = ` left join request_partners rp on rp.request_id=p.request_id left join partners pr on pr.id=rp.partner_id `
      }else{
        userTypeCondition = ` LEFT JOIN staff_members s1 ON s1.id=p.project_lead  
          LEFT JOIN users u1 ON s1.user_id=u1.id 
          LEFT JOIN staff_members s2 ON s2.id=p.default_assignee 
          LEFT JOIN users u2 ON s2.user_id=u2.id
          LEFT JOIN (select pm.project_id, COALESCE(u.id,u2.id) as user_id from project_members pm left join partners p on p.id=pm.partner_id left join users u on p.user_id=u.id left join staff_members s on s.id=pm.staff_id left join users u2 on s.user_id=u2.id) pmm ON pmm.project_id=p.id and pmm.user_id=${userId} `
      }

      query = await sequelize.query(
        ` SELECT p.*, concat(u.first_name, ' ', u.last_name) as authorized_by,inst_counts.installation_count,total_inst_counts.total_installation_count, coalesce(pm.count, 0) as member_count, coalesce(pt.count, 0) as task_count,r.installation_acceptance, c_ord.customer_name, com.name AS community_name, r.doc_number, p.installation_date AS due_date, `
      + ` CASE 
              WHEN r.project_category = 12 THEN 
              CASE WHEN inst_counts.installation_count >= COALESCE(cop.product_count, 0) + COALESCE(rss.service_count, 0) THEN 1 ELSE 0 END
              WHEN r.project_category = 11 THEN 
              CASE WHEN inst_counts.installation_count >= COALESCE(rss.service_count, 0) THEN 1 ELSE 0 END 
              WHEN r.project_category = 14 THEN 
              CASE WHEN inst_counts.installation_count >= COALESCE(cop.product_count, 0) + COALESCE(rss.service_count, 0) THEN 1 ELSE 0 END 
              ELSE 
              CASE WHEN inst_counts.installation_count >= COALESCE(cop.product_count, 0) THEN 1 ELSE 0 END 
          END AS oldallInstallationCompleted, `
        + `CASE 
            WHEN 
              (COALESCE(inst_counts.installation_count, 0) >= COALESCE(total_inst_counts.total_installation_count, 0))
              AND (COALESCE(total_inst_counts.total_installation_count, 0) > 0)
            THEN 1
            ELSE 0
          END AS allInstallationCompleted,`
        + ` CASE 
            WHEN inst_counts.installation_count >= COALESCE(cop.product_count,0) OR inst_counts.installation_count >= COALESCE(rss.service_count,0) 
            THEN 1 
            ELSE 0 
          END AS veryoldallInstallationCompleted `
        + ` FROM projects p `
        + ` ${userTypeCondition} LEFT JOIN users u ON p.added_by=u.id `
        + ` LEFT JOIN requests r ON p.request_id = r.id `
        + ` LEFT JOIN (
              SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity, 
               SUM(
                    CASE
                      WHEN cop.is_single_card = 1 THEN 1
                      ELSE cop.location_quantity
                    END
                  ) AS product_count
              FROM customer_orders co
              JOIN customer_order_products cop ON co.id = cop.order_id
              JOIN requests req ON co.request_id = req.id
              WHERE cop.material = 0
              GROUP BY co.request_id
            ) cop ON r.id = cop.request_id `
        + ` LEFT JOIN (SELECT co.request_id, SUM(rss.qty) AS location_quantity, COUNT(rss.qty) AS service_count FROM customer_orders co 
        JOIN  requests rqs ON co.request_id = rqs.id
        JOIN request_services rss ON co.request_id = rss.request_id 
        JOIN  labour_cost_services lcs ON rss.labour_cost_service_id = lcs.id
        WHERE ${laborInstallWhereCondition} ${makeAsTaskCondition} GROUP BY co.request_id) rss ON r.id = rss.request_id`
        + ` LEFT JOIN customer_orders c_ord ON c_ord.request_id = r.id `
        + ` LEFT JOIN community com ON com.id = p.community AND FIND_IN_SET(c_ord.customer_id, com.clients) > 0 AND com.status = 1 `
        + ` LEFT JOIN (select count(id) as count, project_id from project_members group by project_id) pm ON pm.project_id=p.id `
        + ` LEFT JOIN (SELECT request_id, COUNT(*) AS installation_count FROM installations WHERE status = 4 GROUP BY request_id) inst_counts ON inst_counts.request_id = r.id `
        + ` LEFT JOIN (SELECT request_id, COUNT(*) AS total_installation_count FROM installations GROUP BY request_id) total_inst_counts ON total_inst_counts.request_id = r.id `
        + ` LEFT JOIN (select count(ID) as count, project_id from project_tasks where status!=0 group by project_id) pt ON pt.project_id=p.id ${whereCondition} ORDER BY p.id DESC`
      );
      let data;
      if (query.length > 0) {
        data = query[0];
        
        // const results = await Promise.all(data.map(async (item) => {
        //   // Get all the data needed for the correct calculation
        //   let recipe = 1;
        //   let selectedMaterials = await JobCost.newFetchAllJobCostByProjectId(item.id, userId);
        //   let materials = await JobCost.fetchAllJobCostByProjectId(item.id, userId, 0);
          
        //   // Ensure selectedMaterials is an array
        //   if (!Array.isArray(selectedMaterials)) {
        //     console.log(`Warning: selectedMaterials is not an array for project ${item.id}:`, selectedMaterials);
        //     selectedMaterials = [];
        //   }
          
        //   // Ensure materials is an array
        //   if (!Array.isArray(materials)) {
        //     console.log(`Warning: materials is not an array for project ${item.id}:`, materials);
        //     materials = [];
        //   }
          
        //   let stockProducts = await JobCost.fetchAllProductsStock(item.id, userId, recipe);
        //   let allPayOut = await JobCost.fetchAllPayOutByProjectId(item.id, userId);
        //   let allServices = await Recipe.fetchAllServicesByRequest(item.request_id);
          
        //   // Ensure other arrays are arrays
        //   if (!Array.isArray(stockProducts)) {
        //     console.log(`Warning: stockProducts is not an array for project ${item.id}:`, stockProducts);
        //     stockProducts = [];
        //   }
        //   if (!Array.isArray(allPayOut)) {
        //     console.log(`Warning: allPayOut is not an array for project ${item.id}:`, allPayOut);
        //     allPayOut = [];
        //   }
        //   if (!Array.isArray(allServices)) {
        //     console.log(`Warning: allServices is not an array for project ${item.id}:`, allServices);
        //     allServices = [];
        //   }
          
        //   // Get project details
        //   let project = await Projects.getProjectById(item.id);
          
        //   // Calculate material payouts (from recipe vs not from recipe)
        //   let materialPayOut = 0;
        //   let materialPayOut1 = 0;
          
        //   if (materials?.length > 0) {
        //     // Debug materials structure
        //     console.log(`  - materials sample:`, materials[0]);
            
        //     materialPayOut = materials
        //       ?.filter(curr => curr.notFromRecipe != true)
        //       .reduce((acc, curr) => acc + parseFloat(curr.actual_total || 0), 0);

        //     materialPayOut1 = materials
        //       ?.filter(curr => curr.notFromRecipe == true)
        //       .reduce((acc, curr) => acc + parseFloat(curr.total || 0), 0);
        //   }
          
        //   // Calculate materialTotal and materialTotal1 (matching CompareJobCost.js logic)
        //   // materialTotal: combines selectedMaterials and materials, uses unitCost * quantity
        //   let materialTotal = 0;
        //   let materialTotal1 = 0;
          
        //   // Combine selectedMaterials (selectedProjects) and materials (selectedProjects1)
        //   let combinedMaterials = [...selectedMaterials, ...materials];
        //   materialTotal = combinedMaterials.reduce((sum, proj) => {
        //     const unitCost = Number(proj.jobcost_actual_unit_cost || proj.unit_cost || 0);
        //     const quantity = Number(proj.jobcost_actual_quantity || 0);
        //     return sum + (unitCost * quantity);
        //   }, 0);
          
        //   // materialTotal1: uses only selectedMaterials with taken === 1, uses jobcost_total
        //   materialTotal1 = selectedMaterials?.reduce((acc, project) => {
        //     if (project?.taken === 1) {
        //       return acc + (parseFloat(project?.jobcost_total) || 0);
        //     }
        //     return acc;
        //   }, 0) || 0;

        //   // Calculate stock costs
        //   let totalStockCost = 0;
        //   let totalStockCost1 = 0;
          
        //   if (stockProducts?.length > 0) {
        //     totalStockCost = stockProducts.reduce((sum, item) => {
        //       const cost = parseFloat(item.location_quantity || 0) * parseFloat(item.product_retail_price || 0);
        //       return sum + cost;
        //     }, 0);

        //     totalStockCost1 = stockProducts.reduce((sum, item) => {
        //       if (item?.taken !== 1) return sum;
        //       const cost = parseFloat(item.jobcost_total_cost || 0);
        //       return sum + cost;
        //     }, 0);
        //   }

        //   // Calculate selected projects cost (grandTotalCost - expected)
        //   let selectedProjectsCost = 0;
        //   if (selectedMaterials?.length > 0) {
        //     selectedProjectsCost = selectedMaterials.reduce((acc, proj) => {
        //       const totalCost = proj.jobcost_total
        //         ? Number(proj.jobcost_total)
        //         : (Number(proj.jobcost_actual_unit_cost || proj.unit_cost || 0) * Number(proj.jobcost_actual_quantity || 0));
        //       return acc + totalCost;
        //     }, 0);
        //   }

        //   // Calculate services cost (matching CompareJobCost.js simple logic)
        //   let servicesCost = 0;
        //   if (allServices?.length > 0) {
        //     servicesCost = allServices.reduce((sum, service) => {
        //       const quantity = parseFloat(service.qty || 0);
        //       const unitCost = parseFloat(service.originalCost || service.unit_cost || 0);
        //       return sum + (quantity * unitCost);
        //     }, 0);
        //   }



        //   // Calculate actual expense (grandTotalCost1)
        //   let selectedProjectsCost1 = selectedMaterials?.filter(i => i.taken == 1)?.reduce(
        //     (acc, proj) => acc + proj.jobcost_total, 0
        //   );

        //   // Calculate actual service costs (matching CompareJobCost.js logic exactly)
        //   // additionalLaborCost1: services with taken === 1, recipe_id != null, labour_cost_service_id !== 10
        //   let additionalLaborCost1 = allServices.reduce((sum, service) => {
        //     if (service?.taken !== 1 || service.recipe_id != null || service.labour_cost_service_id === 10) return sum;
            
        //     const expected = parseFloat((service.originalCost * service.qty) || 0);
        //     const actual = parseFloat(service.jobcost_total_cost || 0);
        //     const nonExcessAmount = actual > expected ? expected : actual;
            
        //     return sum + nonExcessAmount;
        //   }, 0);
          
        //   // productFormulaCost1: services with recipe_id and labour_cost_service_id !== 10 and taken == 1 (actual)
        //   let productFormulaCost1 = 0;
        //   let filteredServicesTaken = allServices?.filter(s => s?.recipe_id && s?.labour_cost_service_id !== 10 && s?.taken == 1);
        //   if (filteredServicesTaken?.length > 0) {
        //     const expected = filteredServicesTaken.reduce((sum, s) => sum + Number(s.originalCost || 0) * Number(s.qty || 0), 0);
        //     const actual = filteredServicesTaken.reduce((sum, s) => sum + parseFloat(s.jobcost_total_cost || 0), 0);
        //     productFormulaCost1 = Math.min(expected, actual);
        //   }
          
        //   // installLaborCost1: services with labour_cost_service_id === 10 and taken == 1
        //   let installLaborCost1 = 0;
        //   let installServicesTaken = allServices?.filter(s => s?.labour_cost_service_id === 10 && s?.taken == 1);
        //   if (installServicesTaken?.length > 0) {
        //     installLaborCost1 = installServicesTaken.reduce((sum, s) => {
        //       const expected = Number(s.originalCost || 0) * Number(s.qty || 0);
        //       const actual = parseFloat(s.jobcost_total_cost || 0);
        //       const cost = expected > actual ? actual : expected;
        //       return sum + cost;
        //     }, 0);
        //   }
          
        //   // Total services cost for actual
        //   let servicesCost1 = additionalLaborCost1 + productFormulaCost1 + installLaborCost1;

        //   // Calculate payout amounts
        //   let totalHourlyPayoutAmount = allPayOut?.filter(payout => payout.is_auto_pay === 1 && !payout.service_id)?.reduce((acc, curr) => acc + curr.amount, 0);
        //   let totalManualPayoutAmount = allPayOut?.filter(payout => payout.is_auto_pay !== 1 && !payout.service_id)?.reduce((acc, curr) => acc + curr.amount, 0);
          
        //   // Calculate excessLaborCost1 (matching CompareJobCost.js logic exactly)
        //   let labourExpected = 0;
        //   let labourActual = 0;
        //   let otherExcess = 0;
        //   let productionExpected = 0;
        //   let productionActual = 0;
          
        //   allServices.forEach(s => {
        //     const expected = s.taken == 1 ? (parseFloat(s.originalCost * s.qty) || 0) : 0;
        //     const actual = s.taken == 1 ? (parseFloat(s.jobcost_total_cost) || 0) : 0;
            
        //     if (+s.labour_cost_service_id === 10) {
        //       // install labour
        //       labourExpected += expected;
        //       labourActual += actual;
        //     } else if (s.recipe_id && s.labour_cost_service_id !== 10 && s.taken == 1) {
        //       // production labour
        //       productionExpected += expected;
        //       productionActual += actual;
        //     } else {
        //       // Per-row excess calculation for others
        //       if (actual > expected) {
        //         otherExcess += actual - expected;
        //       }
        //     }
        //   });
          
        //   const labourExcess = labourActual > labourExpected ? labourActual - labourExpected : 0;
        //   const productionExcess = productionActual > productionExpected ? productionActual - productionExpected : 0;
          
        //   let excessLaborCost1 = +(
        //     labourExcess +
        //     productionExcess +
        //     otherExcess +
        //     (totalHourlyPayoutAmount || 0) +
        //     (totalManualPayoutAmount || 0)
        //   ).toFixed(2);

        //   // Calculate grandTotalCost (expected budget) - matching CompareJobCost.js simple logic
        //   let grandTotalCost = materialPayOut + selectedProjectsCost + servicesCost + totalStockCost;

        //   // Calculate grandTotalCost1 (actual expense) - matching CompareJobCost.js logic
        //   // Material Cost = materialPayOut1 + materialTotal1 + totalStockCost1
        //   let actualMaterialCost = materialPayOut1 + materialTotal1 + totalStockCost1;
        //   let grandTotalCost1 = actualMaterialCost + servicesCost1 + Math.max(0, excessLaborCost1);

        //   // Set the correct values - ensure they are floats
        //   item.budget = parseFloat(grandTotalCost.toFixed(2));
        //   item.expense = parseFloat(grandTotalCost1.toFixed(2));
          
        //   // Debug logging
        //   console.log(`Project ${item.id}: budget=${grandTotalCost}, expense=${grandTotalCost1}`);
        //   console.log(`  - budget components: materialPayOut=${materialPayOut} + selectedProjectsCost=${selectedProjectsCost} + servicesCost=${servicesCost} + totalStockCost=${totalStockCost}`);
        //   console.log(`  - materialPayOut calculation: materials=${materials?.length}, filtered=${materials?.filter(curr => curr.notFromRecipe != true)?.length}`);
        //   console.log(`  - selectedProjectsCost calculation: selectedMaterials=${selectedMaterials?.length}`);
        //   console.log(`  - servicesCost calculation: allServices=${allServices?.length}`);
        //   console.log(`  - totalStockCost calculation: stockProducts=${stockProducts?.length}`);
        //   console.log(`  - actualMaterialCost: ${actualMaterialCost} (materialPayOut1: ${materialPayOut1} + materialTotal1: ${materialTotal1} + totalStockCost1: ${totalStockCost1})`);
        //   console.log(`  - servicesCost1: ${servicesCost1} (additionalLaborCost1: ${additionalLaborCost1} + productFormulaCost1: ${productFormulaCost1} + installLaborCost1: ${installLaborCost1})`);
        //   console.log(`  - excessLaborCost1: ${excessLaborCost1} (labourExcess: ${labourExcess} + productionExcess: ${productionExcess} + otherExcess: ${otherExcess} + payouts: ${(totalHourlyPayoutAmount || 0) + (totalManualPayoutAmount || 0)})`);
        //   console.log(`  - selectedMaterials count: ${selectedMaterials?.length}, materials count: ${materials?.length}`);
        //   console.log(`  - combinedMaterials count: ${combinedMaterials?.length}`);
          
        //   return item;
        // }));
        // return results?.map(record => parseDecimalFields(record));

        return data?.map(record => parseDecimalFields(record));
      } else {
        return false;
      }
  } catch (error) {
      console.log("Server error",error);
      throw new Error("Server error");
  }
}
// const getAllProjectsforUpcoming = async (all = false,page, userType, userId, where = []) => {
//   try {

//       let whereCondition;
//       let query = ''
//       if (all) {
//         if(userType == 1 || userType == 8){
//           whereCondition = ` WHERE r.installation_acceptance !=1 OR r.installation_acceptance IS NULL`;
//         }else if(userType == 3){
//           whereCondition = ` where (r.installation_acceptance !=1 OR r.installation_acceptance IS NULL) AND pr.user_id=${userId} `;
//         }else{
//           whereCondition = ` where (r.installation_acceptance !=1 OR r.installation_acceptance IS NULL) AND (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
//         }
//       } else {
//           whereCondition = ` where  p.status = 3`;
//           if(userType == 1 || userType == 8){
//             whereCondition += ` `;
//           }else if(userType == 3){
//             whereCondition += ` and pr.user_id=${userId} `;
//           }else{
//             whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
//           }
//       }
//       if (!empty(where)) {

//       }

//       let userTypeCondition = '';
//       if(userType == 1 || userType == 8){
//         userTypeCondition = ''
//       } else if(userType == 3){
//         userTypeCondition = ` left join request_partners rp on rp.request_id=p.request_id left join partners pr on pr.id=rp.partner_id `
//       }else{
//         userTypeCondition = ` LEFT JOIN staff_members s1 ON s1.id=p.project_lead  
//           LEFT JOIN users u1 ON s1.user_id=u1.id 
//           LEFT JOIN staff_members s2 ON s2.id=p.default_assignee 
//           LEFT JOIN users u2 ON s2.user_id=u2.id
//           LEFT JOIN (select pm.project_id, COALESCE(u.id,u2.id) as user_id from project_members pm left join partners p on p.id=pm.partner_id left join users u on p.user_id=u.id left join staff_members s on s.id=pm.staff_id left join users u2 on s.user_id=u2.id) pmm ON pmm.project_id=p.id and pmm.user_id=${userId} `
//       }
      
//           // query = await sequelize.query(
//           //     ` SELECT p.*, concat(u.first_name, ' ', u.last_name) as authorized_by, coalesce(pm.count, 0) as member_count, coalesce(pt.count, 0) as task_count FROM projects p `
//           //     + ` LEFT JOIN users u ON p.added_by=u.id  `
//           //     + ` LEFT JOIN (select count(id) as count, project_id from project_members group by project_id) pm ON pm.project_id=p.id `
//           //     + ` LEFT JOIN (select count(ID) as count, project_id from project_tasks where status!=0 group by project_id) pt ON pt.project_id=p.id ${whereCondition} ORDER BY p.id DESC`
//           // );
//           query = await sequelize.query(
//               ` SELECT p.*, concat(u.first_name, ' ', u.last_name) as authorized_by, coalesce(pm.count, 0) as member_count, coalesce(pt.count, 0) as task_count FROM projects p `
//               +` ${userTypeCondition} LEFT JOIN users u ON p.added_by=u.id  `
//               +` LEFT JOIN requests r ON p.request_id = r.id `
//               +` LEFT JOIN (select count(id) as count, project_id from project_members group by project_id) pm ON pm.project_id=p.id `
//               +` LEFT JOIN (select count(ID) as count, project_id from project_tasks where status!=0 group by project_id) pt ON pt.project_id=p.id `
//               +` ${whereCondition} ORDER BY p.id DESC `
//               +` LIMIT ${10} OFFSET ${page * 10}`
//           );
//       let data;
//       if (query.length > 0) {
//           data = query[0];
//               const results = await Promise.all(data.map(async (item) => {
//                  let selectedMaterials = await JobCost.fetchAllJobCostProjectId(item.id, userId);
//                  let price = selectedMaterials.reduce((total, currentItem) => total + currentItem.total, 0);
                
//                   let payout=await JobCost.fetchAllPayOutByProjectId(item.id, userId);
//                   let payoutPrice = payout.reduce((total, currentItem) => total + currentItem.amount, 0);
//                   let expense=price+payoutPrice
//                   item.expense=expense
//                   item.budget=item.cost ? parseFloat(item.cost) : null
//                   return item;
//               }));
           
//               let totalCountQuery = await sequelize.query(
//                 `SELECT COUNT(p.id) as totalCount FROM projects p`
//                 +` ${userTypeCondition} LEFT JOIN users u ON p.added_by=u.id  `
//                 +` LEFT JOIN requests r ON p.request_id = r.id `
//                 +` LEFT JOIN (select count(id) as count, project_id from project_members group by project_id) pm ON pm.project_id=p.id `
//                 +` LEFT JOIN (select count(ID) as count, project_id from project_tasks where status!=0 group by project_id) pt ON pt.project_id=p.id `
//                 +` ${whereCondition}`
//               );
//               let totalCount = totalCountQuery[0][0].totalCount;
//               return { results:results?.map(record => parseDecimalFields(record)), totalCount };
          
//       } else {
//           return false;
//       }
//   } catch (error) {
//       console.log(error);
//       throw new Error("Server error");
//   }
// }
const getAllProjectsforUpcoming = async (all = false,page, userType, userId, where = []) => {
  try {

      let whereCondition;
      let query = ''
      if (all) {
        if(userType == 1 || userType == 8){
          whereCondition = 
            `WHERE 
            (
              (inst_counts.installation_count IS NULL OR inst_counts.installation_count  < COALESCE(cop.product_count, 0))
                AND
                (inst_counts.installation_count IS NULL OR inst_counts.installation_count < COALESCE(rss.service_count, 0))
            )`
        }else if(userType == 3){
          whereCondition = ` where (
              (inst_counts.installation_count IS NULL OR inst_counts.installation_count  < COALESCE(cop.product_count, 0))
                AND
                (inst_counts.installation_count IS NULL OR inst_counts.installation_count < COALESCE(rss.service_count, 0))
            ) AND pr.user_id=${userId} `;
        }else{
          whereCondition = ` where (
              (inst_counts.installation_count IS NULL OR inst_counts.installation_count  < COALESCE(cop.product_count, 0))
                AND
                (inst_counts.installation_count IS NULL OR inst_counts.installation_count < COALESCE(rss.service_count, 0))
            ) AND (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
        }
      } else {
          whereCondition = ` where  p.status = 3`;
          if(userType == 1 || userType == 8){
            whereCondition += ` `;
          }else if(userType == 3){
            whereCondition += ` and pr.user_id=${userId} `;
          }else{
            whereCondition += ` and (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
          }
      }
      if (!empty(where)) {

      }

      let userTypeCondition = '';
      if(userType == 1 || userType == 8){
        userTypeCondition = ''
      } else if(userType == 3){
        userTypeCondition = ` left join request_partners rp on rp.request_id=p.request_id left join partners pr on pr.id=rp.partner_id `
      }else{
        userTypeCondition = ` LEFT JOIN staff_members s1 ON s1.id=p.project_lead  
          LEFT JOIN users u1 ON s1.user_id=u1.id 
          LEFT JOIN staff_members s2 ON s2.id=p.default_assignee 
          LEFT JOIN users u2 ON s2.user_id=u2.id
          LEFT JOIN (select pm.project_id, COALESCE(u.id,u2.id) as user_id from project_members pm left join partners p on p.id=pm.partner_id left join users u on p.user_id=u.id left join staff_members s on s.id=pm.staff_id left join users u2 on s.user_id=u2.id) pmm ON pmm.project_id=p.id and pmm.user_id=${userId} `
      }
      
          // query = await sequelize.query(
          //     ` SELECT p.*, concat(u.first_name, ' ', u.last_name) as authorized_by, coalesce(pm.count, 0) as member_count, coalesce(pt.count, 0) as task_count FROM projects p `
          //     + ` LEFT JOIN users u ON p.added_by=u.id  `
          //     + ` LEFT JOIN (select count(id) as count, project_id from project_members group by project_id) pm ON pm.project_id=p.id `
          //     + ` LEFT JOIN (select count(ID) as count, project_id from project_tasks where status!=0 group by project_id) pt ON pt.project_id=p.id ${whereCondition} ORDER BY p.id DESC`
          // );
          query = await sequelize.query(
              ` SELECT p.*, COALESCE(inst_counts.installation_count, 0),total_inst_counts.total_installation_count, cop.product_count, rss.service_count,  concat(u.first_name, ' ', u.last_name) as authorized_by, coalesce(pm.count, 0) as member_count, coalesce(pt.count, 0) as task_count FROM projects p `
              +` ${userTypeCondition} LEFT JOIN users u ON p.added_by=u.id  `
              +` LEFT JOIN requests r ON p.request_id = r.id `
              +` LEFT JOIN (select count(id) as count, project_id from project_members group by project_id) pm ON pm.project_id=p.id `
              +` LEFT JOIN (select count(ID) as count, project_id from project_tasks where status!=0 group by project_id) pt ON pt.project_id=p.id `
              + ` LEFT JOIN (
                SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity, 
                 SUM(
                    CASE
                      WHEN cop.is_single_card = 1 THEN 1
                      ELSE cop.location_quantity
                    END
                  ) AS product_count
                FROM customer_orders co
                JOIN customer_order_products cop ON co.id = cop.order_id
                JOIN requests req ON co.request_id = req.id
                WHERE cop.material = 0
                GROUP BY co.request_id
              ) cop ON r.id = cop.request_id `
            + ` LEFT JOIN (
                  SELECT co.request_id, SUM(rss.qty) AS location_quantity, COUNT(rss.qty) AS service_count
                  FROM customer_orders co
                  JOIN  requests rqs ON co.request_id = rqs.id
                  JOIN  request_services rss ON co.request_id = rss.request_id
                  JOIN  labour_cost_services lcs ON rss.labour_cost_service_id = lcs.id
                  WHERE ${laborInstallWhereCondition} ${makeAsTaskCondition}
                  GROUP BY co.request_id
                ) rss ON r.id = rss.request_id `
              + ` LEFT JOIN (SELECT request_id, COUNT(*) AS installation_count FROM installations WHERE status = 4 GROUP BY request_id) inst_counts ON inst_counts.request_id = r.id `
              + ` LEFT JOIN (SELECT request_id, COUNT(*) AS total_installation_count FROM installations GROUP BY request_id) total_inst_counts ON total_inst_counts.request_id = r.id `
              +` ${whereCondition} GROUP BY p.id ORDER BY p.id DESC  `
              +` LIMIT ${10} OFFSET ${page * 10}`
          );
      let data;
      if (query.length > 0) {
          data = query[0];
              const results = await Promise.all(data.map(async (item) => {
                 let selectedMaterials = await JobCost.fetchAllJobCostProjectId(item.id, userId);
                 let price = selectedMaterials.reduce((total, currentItem) => total + currentItem.total, 0);
                
                  let payout=await JobCost.fetchAllPayOutByProjectId(item.id, userId);
                  let payoutPrice = payout.reduce((total, currentItem) => total + currentItem.amount, 0);
                  let expense=price+payoutPrice
                  item.expense=expense
                  item.budget=item.cost ? parseFloat(item.cost) : null
                  return item;
              }));
           
              let totalCountQuery = await sequelize.query(
                `SELECT COUNT(p.id) as totalCount FROM projects p`
                +` ${userTypeCondition} LEFT JOIN users u ON p.added_by=u.id  `
                +` LEFT JOIN requests r ON p.request_id = r.id `
                +` LEFT JOIN (select count(id) as count, project_id from project_members group by project_id) pm ON pm.project_id=p.id `
                +` LEFT JOIN (select count(ID) as count, project_id from project_tasks where status!=0 group by project_id) pt ON pt.project_id=p.id `
                + ` LEFT JOIN (
                  SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity, 
                   SUM(
                    CASE
                      WHEN cop.is_single_card = 1 THEN 1
                      ELSE cop.location_quantity
                    END
                  ) AS product_count
                  FROM customer_orders co
                  JOIN customer_order_products cop ON co.id = cop.order_id
                  JOIN requests req ON co.request_id = req.id
                  WHERE cop.material = 0
                  GROUP BY co.request_id
                ) cop ON r.id = cop.request_id `
              + ` LEFT JOIN (
                    SELECT co.request_id, SUM(rss.qty) AS location_quantity, COUNT(rss.qty) AS service_count
                    FROM customer_orders co
                    JOIN  requests rqs ON co.request_id = rqs.id
                    JOIN  request_services rss ON co.request_id = rss.request_id
                    JOIN  labour_cost_services lcs ON rss.labour_cost_service_id = lcs.id
                    WHERE ${laborInstallWhereCondition} ${makeAsTaskCondition}
                    GROUP BY co.request_id
                  ) rss ON r.id = rss.request_id `
                + ` LEFT JOIN (SELECT request_id, COUNT(*) AS installation_count FROM installations WHERE status = 4 GROUP BY request_id) inst_counts ON inst_counts.request_id = r.id `
                + ` LEFT JOIN (SELECT request_id, COUNT(*) AS total_installation_count FROM installations GROUP BY request_id) total_inst_counts ON total_inst_counts.request_id = r.id `
                +` ${whereCondition}`
              );
              let totalCount = totalCountQuery[0][0].totalCount;
              return { results:results?.map(record => parseDecimalFields(record)), totalCount };
          
      } else {
          return false;
      }
  } catch (error) {
      console.log(error);
      throw new Error("Server error");
  }
}
const updateCompanyprofile= async(data,id)=>{
  let result = CompanyProfile.update(data,{
    where :{
      id:id
    },returning:true
  })
  if(result){
    return true
  }else{
    return false
  }
}
const updateCompanyLatLng = async (lat, lng, id) => {
  try {
    let result = await CompanyProfile.update(
      { lat, lng },
      {
        where: { id: id },
        returning: true,
      }
    );

    return result[0] > 0;
  } catch (error) {
    console.error("Error updating lat/lng:", error);
    return false;
  }
};

const getAllRanks = async () => {
  let result = await sequelize.query(
      `SELECT r.*, company_name as authorized_by,coalesce(p.no_of_partners,0) as no_of_partners  FROM ranks r`
      + ` LEFT JOIN users u ON r.added_by = u.id`
      + ` LEFT JOIN (SELECT count(id) as no_of_partners,rank_id from partners where status=1 group by rank_id) p on r.id=p.rank_id `
  )
  let data = []
  if (result[0].length > 0) {
      data = result[0];
  }
  return data;
}
const updateRank=async(data,id)=>{
  let result = await sequelize.query( 
  `update ranks set status = ${data.status} where id = ${id}`
  )
  if(result){
    return true
  }else{
    return false
  }
}
const getPCBPermission = async()=>{
  let result = await sequelize.query(`
   select pcb.* from pcb_permissions pcb
  `)
  return result[0][0]
}
const updatePCBPermission = async(data,userId)=>{
  try {
    let result = await sequelize.query( 
      `UPDATE pcb_permissions 
      SET projects = :projects, 
          clients = :clients, 
          calendar = :calendar,
          onboarding = :onboarding,
          official_request = :official_request,
          trafficwise = :trafficwise,
          call_center = :call_center,
          crm = :crm
      WHERE added_by = :userId`,
     {
       replacements: {
         projects: data.projects,
         clients: data.clients,
         calendar: data.calendar,
         onboarding: data.onboarding,
         official_request: data.official_request,
         trafficwise: data.trafficwise,
         call_center: data.call_center,
         crm: data.crm,
         userId: userId
       }
     }
      )
      if(result){
        return true
      }else{
        return false
      }
  } catch (error) {
    console.log(error)
  }
}
const getNavigation = async () => {
  try {
    const [result, metadata] = await sequelize.query(`
    SELECT n.* FROM navigation n order by n.position_order
    `);

    if (result.length > 0) {
      return result ;
    } else {
      return [];
    }
  } catch (error) {
    console.error('Error fetching navigation:', error);
    throw error;
  }
};

const fetchclocksettings = async(id=false) =>{
  try{
    let whereCondition = ''
    if(id){
      whereCondition = ` WHERE c.id = ${id} `
    }
    const result = await sequelize.query(`SELECT c.*,CONCAT(u.first_name,' ',u.last_name) AS updated_by_name FROM clock_settings c`
    +` LEFT JOIN users u ON u.id=c.updated_by ${whereCondition}`)
    if(result[0]?.length > 0){
      return result[0]
    }else{
      return []
    }
  }catch(error){
    console.log('Error fetching',error);
  }
}

const editclocksettings = async(id,time,userId,timestamp) =>{
  try{
    const result = await sequelize.query(`UPDATE clock_settings SET time=${time},updated_by=${userId},updated_at=${timestamp} WHERE id=${id}`)
    console.log('Update result----',result);
    if(result){
      return true
    }else{
      return false
    }
  }catch(error){
    console.log('Error fetching',error);
    return false
  }
}

const getAllPcbItems = async (userId) => {
  try {
    const items = await PcbItems.findAll();
    return items;
  } catch (error) {
    console.error("Error fetching PCB Items:", error);
    throw error;
  }
};

const getUsersPcbItems = async (userId) => {
  try {
    const query = `
      SELECT pcb.id
      FROM users u
      LEFT JOIN pcb_items pcb 
      ON FIND_IN_SET(pcb.id, u.pcb_item_order) > 0
      WHERE u.id = :userId
    `;

    const items = await sequelize.query(query, {
      replacements: { userId },
      type: sequelize.QueryTypes.SELECT,
    });

    return items;
  } catch (error) {
    console.error("Error fetching PCB Items:", error);
    throw error;
  }
};

const updateUsersPcbItems = async (userId, selectedPcbIds) => {
  try {
    // Validate input
    if (!Array.isArray(selectedPcbIds)) {
      throw new Error('selectedPcbIds must be an array');
    }

    // Extract just the IDs if objects are passed, or use as-is if already IDs
    const pcbIds = selectedPcbIds.map(item => 
      typeof item === 'object' && item.pcb_item_id ? item.pcb_item_id : item
    ).filter(id => id != null && id !== undefined); // Filter out any null/undefined values
    
    // Handle case where no items are enabled (all items are disabled)
    let pcbItemOrder = '';
    if (pcbIds.length > 0) {
      pcbItemOrder = pcbIds.join(',');
    }

    // Log the actual data being stored for debugging
    console.log('Model: Input selectedPcbIds:', selectedPcbIds);
    console.log('Model: Extracted pcbIds:', pcbIds);
    console.log('Model: Final pcbItemOrder:', pcbItemOrder || '(empty - no items enabled)');

    const query = `
      UPDATE users
      SET pcb_item_order = :pcbItemOrder
      WHERE id = :userId
    `;

    await sequelize.query(query, {
      replacements: { pcbItemOrder, userId },
      type: sequelize.QueryTypes.UPDATE,
    });

    return { success: true, message: "PCB items updated successfully." };
  } catch (error) {
    console.error("Error updating PCB Items:", error);
    throw error;
  }
};

// === Update User PCB Preferences for Multiple Items ===
const updateUserPcbPreferencesForItems = async (userId, selectedItems, preferences) => {
  try {
    const now = Math.round(Date.now() / 1000);
    
    // First, deactivate all existing preferences for this user
    await sequelize.query(`
      UPDATE user_pcb_preferences 
      SET status = 0, updated_on = :now 
      WHERE user_id = :userId
    `, {
      replacements: { userId, now },
      type: sequelize.QueryTypes.UPDATE,
    });

    // Then insert/update preferences for each selected item
    for (const itemId of selectedItems) {
      const itemPreferences = preferences[itemId] || {};
      
      // Check if preference already exists
      const existingPreference = await sequelize.query(`
        SELECT id FROM user_pcb_preferences 
        WHERE user_id = :userId AND pcb_item_id = :itemId
        LIMIT 1
      `, {
        replacements: { userId, itemId },
        type: sequelize.QueryTypes.SELECT,
      });

      if (existingPreference && existingPreference.length > 0) {
        // Update existing preference
        await sequelize.query(`
          UPDATE user_pcb_preferences 
          SET pcb_email_enabled = :emailEnabled, 
              pcb_sms_enabled = :smsEnabled, 
              status = 1, 
              updated_on = :now
          WHERE id = :preferenceId
        `, {
          replacements: { 
            emailEnabled: itemPreferences.email_enabled ? 1 : 0,
            smsEnabled: itemPreferences.sms_enabled ? 1 : 0,
            now,
            preferenceId: existingPreference[0].id
          },
          type: sequelize.QueryTypes.UPDATE,
        });
      } else {
        // Insert new preference
        await sequelize.query(`
          INSERT INTO user_pcb_preferences 
          (user_id, pcb_item_id, pcb_email_enabled, pcb_sms_enabled, timestamp, status)
          VALUES (:userId, :itemId, :emailEnabled, :smsEnabled, :now, 1)
        `, {
          replacements: { 
            userId, 
            itemId, 
            emailEnabled: itemPreferences.email_enabled ? 1 : 0,
            smsEnabled: itemPreferences.sms_enabled ? 1 : 0,
            now
          },
          type: sequelize.QueryTypes.INSERT,
        });
      }
    }

    return true;
  } catch (error) {
    console.error("Error updating PCB preferences:", error);
    throw error;
  }
};

// === Update User PCB Preference by Item ID ===
const updateUserPcbPreferenceByItemId = async (userId, pcbItemId, data) => {
  try {
    const now = Math.round(Date.now() / 1000);
    
    // Check if preference already exists
    const existingPreference = await sequelize.query(`
      SELECT id FROM user_pcb_preferences 
      WHERE user_id = :userId AND pcb_item_id = :pcbItemId
      LIMIT 1
    `, {
      replacements: { userId, pcbItemId },
      type: sequelize.QueryTypes.SELECT,
    });

    if (existingPreference && existingPreference.length > 0) {
      // Update existing preference
      const result = await sequelize.query(`
        UPDATE user_pcb_preferences 
        SET pcb_email_enabled = :emailEnabled, 
            pcb_sms_enabled = :smsEnabled, 
            updated_on = :now
        WHERE id = :preferenceId
      `, {
        replacements: { 
          emailEnabled: data.pcb_email_enabled,
          smsEnabled: data.pcb_sms_enabled,
          now,
          preferenceId: existingPreference[0].id
        },
        type: sequelize.QueryTypes.UPDATE,
      });
      return result[1] > 0; // Return true if rows were affected
    } else {
      // Insert new preference
      const result = await sequelize.query(`
        INSERT INTO user_pcb_preferences 
        (user_id, pcb_item_id, pcb_email_enabled, pcb_sms_enabled, timestamp, status)
        VALUES (:userId, :pcbItemId, :emailEnabled, :smsEnabled, :now, 1)
      `, {
        replacements: { 
          userId, 
          pcbItemId, 
          emailEnabled: data.pcb_email_enabled,
          smsEnabled: data.pcb_sms_enabled,
          now
        },
        type: sequelize.QueryTypes.INSERT,
      });
      return result[0] > 0; // Return true if insert was successful
    }
  } catch (error) {
    console.error("Error updating PCB preference by item ID:", error);
    throw error;
  }
};

const getUserPcbItemsInOrder = async (userId) => {
  try {
    const query = `
      SELECT u.pcb_item_order as id
      FROM users u
      WHERE u.id = :userId
    `;

    const items = await sequelize.query(query, {
      replacements: { userId },
      type: sequelize.QueryTypes.SELECT,
    });

    return items[0]
  } catch (error) {
    console.error("Error fetching PCB Items:", error);
    throw error;
  }
};


// === Add User PCB Preference ===
const addUserPcbPreference = async (data) => {
  let results = await UserPcbPreference.create(data);
  return results;
};

// === Update User PCB Preference ===
const updateUserPcbPreference = async (id, data) => {
  const updatedRows = await UserPcbPreference.update(data, {
    where: {
      id: id,
    },
    returning: true,
  });
  if (updatedRows) {
    return true;
  } else {
    console.log("updatedRows -else-- ", updatedRows[1]);
    return false;
  }
};

// === Get (by user, only active where status != 0) ===
const getUserPcbPreferencesByUser = async (userId) => {
  const sql = `
    SELECT upp.*, pcb.name AS pcb_item_name
    FROM user_pcb_preferences upp
    LEFT JOIN pcb_items pcb ON pcb.id = upp.pcb_item_id
    WHERE upp.user_id = :userId
      AND upp.status != 0
    ORDER BY upp.id DESC
  `;

  const rows = await sequelize.query(sql, {
    replacements: { userId },
    type: sequelize.QueryTypes.SELECT,
  });

  return rows;
};

// === Get single by id (only active where status != 0) ===
const getUserPcbPreferenceById = async (id) => {
  const [rows] = await sequelize.query(
    `SELECT upp.*, pcb.name AS pcb_item_name
     FROM user_pcb_preferences upp
     LEFT JOIN pcb_items pcb ON pcb.id = upp.pcb_item_id
     WHERE upp.id = :id
       AND upp.status != 0
     LIMIT 1`,
    { replacements: { id } }
  );

  return rows?.[0] || null;
};

module.exports = {
  UserRole,
  Services,
  getAllPermissions,
  getAllServices,
  getAllUserRoles,
  getAllPartnerTypes,
  getSkillsById,
  isUserRoleExist,
  addUserRole,
  updateUserRole,
  getUserRoleById,
  isServiceExist,
  addService,
  getServiceById,
  updateService,
  ispartnerTypeExist,
  addpartnerTypes,
  editpartnerType,
  getPartnerTypeById,
  getClientCount,
  getRequestCount,
  projectCount,
  installationCount,
  getAllProjects,
  getAllProjectsOptimized,
  getAllProjectsOptimizedWithPagination,
  updateCompanyprofile,
  getCompanyProfileDetails,
  getAllRanks,
  updateRank,
  getNavigation,
  getAllProjectsforUpcoming,
  updatePCBPermission,
  getPCBPermission,
  getOnboardingCount,
  getCRMDetailsCount,
  trafficwiseCount,
  fetchclocksettings,
  editclocksettings,
  installationCountDashboard,
  updateCompanyLatLng,
  getAllPcbItems,
  getUsersPcbItems,
  updateUsersPcbItems,
  updateUserPcbPreferencesForItems,
  updateUserPcbPreferenceByItemId,
  getUserPcbItemsInOrder,
  addUserPcbPreference,
  getUserPcbPreferencesByUser,
  getUserPcbPreferenceById,
  updateUserPcbPreference,
};
