const { empty } = require("../Helpers/Common")
const sequelize = require("../config/dbconnection")
const Sequelize = require('sequelize')
const moment = require('moment')

const Installation=sequelize.define('installations', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    address: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    name: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    city: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    state: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    zipcode: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    location_quantity: { type: Sequelize.INTEGER, allowNull: false, defaultValue:1 },
    lng: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    lat: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    project_id : { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    labour_cost_service_id : { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    request_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    product_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue:null },
    product_index: { type: Sequelize.INTEGER, allowNull: false, defaultValue:0 },
    location_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue:null },
    installation_temp_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    source: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
    installation_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    installation_start_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    installation_end_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    due_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    team_id: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    order_information: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    added_by: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    timestamp: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    status: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    complete_status: { type: Sequelize.INTEGER, allowNull: true, defaultValue: 1 },
    cancel_reason: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    route_id:{type: Sequelize.INTEGER, allowNull: true, defaultValue: null},
    map_service:{type: Sequelize.INTEGER, allowNull: false, defaultValue: 0},
    driver_id:{type: Sequelize.TEXT, allowNull: true, defaultValue: null},
    driver_is_installer:{type: Sequelize.INTEGER, allowNull: false, defaultValue: 1},
    card_order:{type: Sequelize.INTEGER, allowNull: false, defaultValue: 1},
    reassign_reason:{type: Sequelize.TEXT, allowNull: true, defaultValue: null},
    pending_review:{type: Sequelize.TINYINT, allowNull: true, defaultValue: 0},
    review_reject_reason:{type: Sequelize.TEXT, allowNull: true, defaultValue: null},
    hold_reason:{type: Sequelize.TEXT, allowNull: true, defaultValue: null},
    vehicle_id:{type: Sequelize.INTEGER, allowNull: true, defaultValue: null},
},{ tableName:'installations' ,timestamps: false})

const InstallationNotes=sequelize.define('installation_notes', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    note: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    installation_id: { type: Sequelize.INTEGER, allowNull: false },
    source: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
    added_by: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    timestamp: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    status: { type: Sequelize.INTEGER, allowNull: true, defaultValue: 1 },
},{ tableName:'installation_notes', timestamps: false})

const InstallationTemplateField=sequelize.define('installation_temp_fields',{
  id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  installation_id: { type: Sequelize.INTEGER, allowNull: false },
  template_id :{ type: Sequelize.INTEGER, allowNull: false },
  field_id:{ type: Sequelize.INTEGER, allowNull: false },
  value:{type:Sequelize.TEXT,allowNull:true},
  timestamp: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
  status: { type: Sequelize.INTEGER, allowNull: true},
},{timestamps:false,tableName:'installation_temp_fields'})

const InstallationTabCount=sequelize.define('installation_tab_count',{
  id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  user_id: {type: Sequelize.INTEGER, allowNull: false},
  staged_count: {type: Sequelize.INTEGER, allowNull: false, defaultValue: 0},
  installing_count: {type: Sequelize.INTEGER, allowNull: false ,defaultValue: 0},
  returned_count: {type: Sequelize.INTEGER, allowNull: false,defaultValue: 0},
  completed_count: {type: Sequelize.INTEGER, allowNull: false,defaultValue: 0},
  staged_count_ids: {type: Sequelize.TEXT, allowNull: true,defaultValue: null},
  installing_count_ids: {type: Sequelize.TEXT, allowNull: true,defaultValue: null},
  returned_ids: {type: Sequelize.TEXT, allowNull: true,defaultValue: null},
  completed_count_ids: {type: Sequelize.TEXT, allowNull: true,defaultValue: null},
  status: { type: Sequelize.TINYINT, defaultValue: 1 },
  timestamp: { type: Sequelize.INTEGER, allowNull: true },
},{timestamps:false ,tableName: 'installation_tab_count'})

const PickupTabCount=sequelize.define('pickup_tab_count',{
  id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  user_id: {type: Sequelize.INTEGER, allowNull: false},
  staged_count: {type: Sequelize.INTEGER, allowNull: false, defaultValue: 0},
  completed_count: {type: Sequelize.INTEGER, allowNull: false,defaultValue: 0},
  staged_count_ids: {type: Sequelize.TEXT, allowNull: true,defaultValue: null},
  completed_count_ids: {type: Sequelize.TEXT, allowNull: true,defaultValue: null},
  status: { type: Sequelize.TINYINT, defaultValue: 1 },
  timestamp: { type: Sequelize.INTEGER, allowNull: true },
},{timestamps:false ,tableName: 'pickup_tab_count'})

const ShippingTabCount=sequelize.define('shipping_tab_count',{
  id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  user_id: {type: Sequelize.INTEGER, allowNull: false},
  staged_count: {type: Sequelize.INTEGER, allowNull: false, defaultValue: 0},
  returned_count: {type: Sequelize.INTEGER, allowNull: false,defaultValue: 0},
  completed_count: {type: Sequelize.INTEGER, allowNull: false,defaultValue: 0},
  staged_count_ids: {type: Sequelize.TEXT, allowNull: true,defaultValue: null},
  completed_count_ids: {type: Sequelize.TEXT, allowNull: true,defaultValue: null},
  returned_ids: {type: Sequelize.TEXT, allowNull: true,defaultValue: null},
  status: { type: Sequelize.TINYINT, defaultValue: 1 },
  timestamp: { type: Sequelize.INTEGER, allowNull: true },
},{timestamps:false ,tableName: 'shipping_tab_count'})

const parseDecimalFields = (order) => {
  order.total = order.total ? parseFloat(order.total) : null;
  order.invoice_total = order.invoice_total ? parseFloat(order.invoice_total) : null;
  order.budget = order.budget ? parseFloat(order.budget) : null;
  order.sub_total = order.sub_total ? parseFloat(order.sub_total) : null;
  order.tax = order.tax ? parseFloat(order.tax) : null;
  order.extra_discount = order.extra_discount ? parseFloat(order.extra_discount) : null;
  order.cost = order.cost ? parseFloat(order.cost) : null;
  return order;
}

const parseDecimalFieldsForOrderProducts = (orderProduct) => {
  orderProduct.product_price = orderProduct.product_price ? parseFloat(orderProduct.product_price) : null;
  orderProduct.product_retail_price = orderProduct.product_retail_price ? parseFloat(orderProduct.product_retail_price) : null;
  orderProduct.product_markup = orderProduct.product_markup ? parseFloat(orderProduct.product_markup) : null;
  orderProduct.product_qty = orderProduct.product_qty ? parseFloat(orderProduct.product_qty) : null;
  orderProduct.product_sub_total = orderProduct.product_sub_total ? parseFloat(orderProduct.product_sub_total) : null;
  orderProduct.product_tax = orderProduct.product_tax ? parseFloat(orderProduct.product_tax) : null;
  orderProduct.product_total = orderProduct.product_total ? parseFloat(orderProduct.product_total) : null;
  orderProduct.discount = orderProduct.discount ? parseFloat(orderProduct.discount) : null;
  return orderProduct;
}

const getAllProjects = async ( userType, userId) => {
    try {
        let whereCondition = ` p.status =3`;
        let query = ''
        query = await sequelize.query(`SELECT p.*, c.customer_name, c.customer_phone, c.customer_address, c.sub_total, c.total, c.id AS order_id `
          +  ` FROM projects p `
          +  ` LEFT JOIN users u ON p.added_by = u.id `
          +  ` LEFT JOIN requests r ON r.id = p.request_id `
          +  ` LEFT JOIN customer_orders c ON c.request_id = r.id `
          +  ` LEFT JOIN customer_order_products cop ON cop.order_id = c.id `
          +  ` WHERE ${whereCondition} AND ( `
          +  `     SELECT COUNT(*) `
          +  `     FROM installations i `
          +  `     WHERE i.request_id = c.request_id AND (i.product_id = cop.product_id OR (i.product_id IS NULL AND cop.product_id IS NULL)) AND i.product_index = cop.product_index `
          +  ` ) < ( `
          +  `     SELECT COUNT(*) `
          +  `     FROM request_locations rl `
          +  `     WHERE rl.order_id = cop.id `
          +  ` ) `
          +  ` GROUP BY p.id, c.customer_name, c.customer_phone, c.customer_address, c.sub_total, c.total, c.id `
          +  ` 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(error);
        throw new Error("Server error");
    }
}

const getAllServiceProjects = async ( userType, userId) => {
  try {
      let whereCondition = ` p.status =3`;
      let query = ''
      query = await sequelize.query(`SELECT p.*, c.customer_name, c.customer_phone, c.customer_address, c.sub_total, c.total, c.id AS order_id, `
        + `  (SELECT COUNT(*) FROM installations i WHERE i.request_id = r.id AND (i.labour_cost_service_id = cop.labour_cost_service_id OR (i.labour_cost_service_id IS NULL AND cop.labour_cost_service_id IS NULL)) AND i.product_index = cop.product_index) AS installation_count,
    (SELECT COUNT(*) FROM request_service_locations rl WHERE rl.request_id = r.id) AS service_location_count `
        +  ` FROM projects p `
        +  ` LEFT JOIN users u ON p.added_by = u.id `
        +  ` LEFT JOIN requests r ON r.id = p.request_id `
        +  ` LEFT JOIN customer_orders c ON c.request_id = r.id `
        +  ` LEFT JOIN request_services cop ON cop.request_id = c.id `
        +  ` WHERE ${whereCondition} AND ( `
        +  `     SELECT COUNT(*) `
        +  `     FROM installations i `
        +  `     WHERE i.request_id = c.request_id `
        +  ` ) < ( `
        +  `     SELECT COUNT(*) `
        +  `     FROM request_service_locations rl `
        +  `     WHERE rl.request_id = r.id `
        +  ` ) `
        +  ` GROUP BY p.id, c.customer_name, c.customer_phone, c.customer_address, c.sub_total, c.total, c.id `
        +  ` ORDER BY p.id DESC `);

        // WHERE i.request_id = c.request_id AND ((i.labour_cost_service_id = cop.labour_cost_service_id) OR (i.labour_cost_service_id IS NULL AND cop.labour_cost_service_id IS NULL)) AND i.product_index = cop.product_index
      let data=[];
      if (query.length > 0) {
          data = query[0];
          return data.map(record => parseDecimalFields(record));
      } else {
          return [];
      }
  } catch (error) {
      console.log(error);
      throw new Error("Server error");
  }
}

const getAllServicesForCalendar = async (hasPermission, userType, userId, fromDate = null, toDate = null) => {
  try {
    let whereCondition = '';
    let whereCondition2 = '';
    
    // Add user permission conditions similar to Project.getAllProjects
    if (userType !== 1 && userType !== 8 && userType !== 0) {
      if (userType === 3) {
        whereCondition = `LEFT JOIN request_partners rp ON rp.request_id = r.id 
                         LEFT JOIN partners pr ON pr.id = rp.partner_id`;
        whereCondition2 = `pr.user_id = ${userId}`;
      } else {
        whereCondition = `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 u3 ON p.added_by = u3.id 
                         LEFT JOIN (select pm.project_id, COALESCE(u4.id,u5.id) as user_id from project_members pm 
                         left join partners p on p.id=pm.partner_id 
                         left join users u4 on p.user_id=u4.id 
                         left join staff_members s on s.id=pm.staff_id 
                         left join users u5 on s.user_id=u5.id) pmm ON pmm.project_id=p.id and pmm.user_id=${userId}`;
        whereCondition2 = `(u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} OR p.added_by = ${userId})`;
      }
    }

    // Add date filtering
    let dateCondition = '';
    if (fromDate && toDate) {
      const fromTimestamp = moment(fromDate).startOf('day').unix();
      const toTimestamp = moment(toDate).endOf('day').unix();
      dateCondition = ` AND p.installation_date BETWEEN ${fromTimestamp} AND ${toTimestamp}`;
    }

    let query = await sequelize.query(`SELECT DISTINCT
        p.id as project_id,
        p.name,r.project_category,
        p.start_date,
        p.due_date,
        p.installation_date,
        p.installation_start_date,
        p.installation_end_date,
        p.status,
        p.community,
        com.name AS community_name,
        c.customer_name,
        c.customer_phone,
        c.customer_address,
        c.id as order_id,
        r.id as request_id,rs.makeInstallCard,
        r.doc_number,lcs.id AS labour_cost_service_id,
        GROUP_CONCAT(DISTINCT lcs.name SEPARATOR ', ') AS service_name,
        CONCAT(COALESCE(c.customer_address, ''), ', ', COALESCE(c.customer_phone, ''), ', ', COALESCE(com.name, ''), ', ', COALESCE(c.customer_name, '')) AS description
      FROM projects p
      LEFT JOIN requests r ON r.id = p.request_id
      LEFT JOIN customer_orders c ON c.request_id = r.id
      LEFT JOIN community com ON com.id = p.community
      LEFT JOIN request_services rs ON rs.request_id = r.id AND rs.recipe_id IS NULL
      LEFT JOIN labour_cost_services lcs ON lcs.id = rs.labour_cost_service_id
      ${whereCondition}
      WHERE p.status != 3
      AND r.installation_acceptance IS NULL AND rs.makeInstallCard = 1
      ${whereCondition2 ? ' AND ' + whereCondition2 : ''}
      ${dateCondition}
      GROUP BY p.id, c.id
      ORDER BY p.id DESC`);

    let data = [];
    if (query[0]?.length > 0) {
      data = query[0];
      // console.log('🔍 getAllServicesForCalendar - Sample service data:', data[0]);
      return data //.map(record => parseDecimalFields(record));
    } else {
      console.log('🔍 getAllServicesForCalendar - No services found');
      return [];
    }
  } catch (error) {
    console.log(error);
    throw new Error("Server error");
  }
}

const getAllInstallServicesForCalendar = async (hasPermission, userType, userId, fromDate = null, toDate = null) => {
  try {
    let whereCondition = '';
    let whereCondition2 = '';
    
    // Add user permission conditions similar to Project.getAllProjects
    if (userType !== 1 && userType !== 8 && userType !== 0) {
      if (userType === 3) {
        whereCondition = `LEFT JOIN request_partners rp ON rp.request_id = r.id 
                         LEFT JOIN partners pr ON pr.id = rp.partner_id`;
        whereCondition2 = `pr.user_id = ${userId}`;
      } else {
        whereCondition = `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 u3 ON p.added_by = u3.id 
                         LEFT JOIN (select pm.project_id, COALESCE(u4.id,u5.id) as user_id from project_members pm 
                         left join partners p on p.id=pm.partner_id 
                         left join users u4 on p.user_id=u4.id 
                         left join staff_members s on s.id=pm.staff_id 
                         left join users u5 on s.user_id=u5.id) pmm ON pmm.project_id=p.id and pmm.user_id=${userId}`;
        whereCondition2 = `(u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} OR p.added_by = ${userId})`;
      }
    }

    // Add date filtering
    let dateCondition = '';
    if (fromDate && toDate) {
      const fromTimestamp = moment(fromDate).startOf('day').unix();
      const toTimestamp = moment(toDate).endOf('day').unix();
      dateCondition = ` AND p.installation_date BETWEEN ${fromTimestamp} AND ${toTimestamp}`;
    }

    let query = await sequelize.query(`SELECT DISTINCT
        p.id as project_id,
        p.name,r.project_category,
        p.start_date,
        p.due_date,
        p.installation_date,
        p.installation_start_date,
        p.installation_end_date,
        p.status,
        p.community,
        com.name AS community_name,
        c.customer_name,
        c.customer_phone,
        c.customer_address,
        c.id as order_id,
        r.id as request_id,rs.makeInstallCard,
        r.doc_number,lcs.id AS labour_cost_service_id,
        GROUP_CONCAT(DISTINCT lcs.name SEPARATOR ', ') AS service_name,
        CONCAT(COALESCE(c.customer_address, ''), ', ', COALESCE(c.customer_phone, ''), ', ', COALESCE(com.name, ''), ', ', COALESCE(c.customer_name, '')) AS description
      FROM projects p
      LEFT JOIN requests r ON r.id = p.request_id
      LEFT JOIN customer_orders c ON c.request_id = r.id
      LEFT JOIN community com ON com.id = p.community
      LEFT JOIN request_services rs ON rs.request_id = r.id AND rs.recipe_id IS NULL
      LEFT JOIN labour_cost_services lcs ON lcs.id = rs.labour_cost_service_id
      ${whereCondition}
      WHERE p.status != 3
      AND r.installation_acceptance IS NULL AND rs.makeInstallCard = 1
      ${whereCondition2 ? ' AND ' + whereCondition2 : ''}
      ${dateCondition}
      GROUP BY p.id, c.id
      ORDER BY p.id DESC`);

    let data = [];
    if (query[0]?.length > 0) {
      data = query[0];
      // console.log('🔍 getAllInstallServicesForCalendar - Sample service data:', data[0]);
      return data //.map(record => parseDecimalFields(record));
    } else {
      console.log('🔍 getAllInstallServicesForCalendar - No services found');
      return [];
    }
  } catch (error) {
    console.log(error);
    throw new Error("Server error");
  }
}

const getProducts = async (orderId) => {
    try {
        let whereCondition = ` p.order_id =${orderId}`;
        let query = ''
            // LEFT JOIN customer_orders c ON p.order_id = c.id
            // WHERE i.request_id = c.request_id
            // WHERE WHERE i.product_id = p.product_id
        query = await sequelize.query(
            `SELECT p.*
            FROM customer_order_products p
            LEFT JOIN customer_orders c ON p.order_id = c.id
            WHERE ${whereCondition} AND (
                SELECT COUNT(*)
                FROM installations i
                WHERE i.request_id = c.request_id AND (i.product_id = p.product_id OR (i.product_id IS NULL AND p.product_id IS NULL)) AND i.product_index = p.product_index
            ) < (
                SELECT COUNT(*)
                FROM request_locations rl
                WHERE rl.order_id = p.id
            )
            ORDER BY p.id DESC`
        );
        let data=[];
        if (query.length > 0) {
            data = query[0];
            return data?.map(record => parseDecimalFieldsForOrderProducts(record));
        } else {
            return [];
        }
    } catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

const getProductsBuildOrder = async (orderId) => {
  try {
    let whereCondition = ` p.order_id =${orderId}`;
    let query = await sequelize.query(
      `SELECT p.*, 
              prod.inhouse, 
              prod.in_stock_no,prod.meterial,prod.actual_retail_price,prod.quickbookId
       FROM customer_order_products p
       LEFT JOIN customer_orders c ON p.order_id = c.id
       LEFT JOIN products prod ON prod.id = p.product_id
       WHERE ${whereCondition} AND (
         SELECT COUNT(*)
         FROM installations i
         WHERE i.request_id = c.request_id 
           AND (i.product_id = p.product_id OR (i.product_id IS NULL AND p.product_id IS NULL)) 
           AND i.product_index = p.product_index
       ) < (
         SELECT COUNT(*)
         FROM request_locations rl
         WHERE rl.order_id = p.id
       )
       ORDER BY p.id DESC`
    );

    let data = [];
    if (query.length > 0) {
      data = query[0];
      return data?.map(record => parseDecimalFieldsForOrderProducts(record));
    } else {
      return [];
    }
  } catch (error) {
    console.log(error);
    throw new Error("Server error");
  }
}

const getProductsNoChargeOrder = async (orderId) => {
  try {
    let whereCondition = ` p.order_id =${orderId}`;
    let query = await sequelize.query(
      `SELECT p.*, 
              prod.inhouse, 
              prod.in_stock_no,
              prod.meterial,
              prod.actual_retail_price,
              prod.isTrafficCatalog
       FROM customer_order_products p
       LEFT JOIN customer_orders c ON p.order_id = c.id
       LEFT JOIN products prod ON prod.id = p.product_id
       WHERE ${whereCondition} AND (
         SELECT COUNT(*)
         FROM installations i
         WHERE i.request_id = c.request_id 
           AND (i.product_id = p.product_id OR (i.product_id IS NULL AND p.product_id IS NULL)) 
           AND i.product_index = p.product_index
       ) < (
         SELECT COUNT(*)
         FROM request_locations rl
         WHERE rl.order_id = p.id
       )
       ORDER BY p.id DESC`
    );

    let data = [];
    if (query.length > 0) {
      data = query[0];
      return data?.map(record => parseDecimalFieldsForOrderProducts(record));
    } else {
      return [];
    }
  } catch (error) {
    console.log(error);
    throw new Error("Server error");
  }
}

// const getServices = async (orderId) => {
//   try {
//       // Define the where condition for the specific order
//       const whereCondition = `p.request_id = ${orderId}`;
//       console.log(`Where Condition: ${whereCondition}`);

//       // Construct the SQL query
//       const query = await sequelize.query(
//           `SELECT p.*, c.customer_name, c.customer_phone, c.customer_address, c.sub_total, c.total, c.id AS order_id,
//                   pr.name AS product_name
//            FROM request_services p
//            LEFT JOIN customer_orders c ON p.request_id = c.request_id
//            LEFT JOIN requests r ON r.id = p.request_id
//            LEFT JOIN projects pjt ON pjt.request_id = p.request_id
//            LEFT JOIN labour_cost_services pr ON pr.id = p.labour_cost_service_id
//            WHERE ${whereCondition}
//              AND (
//                  SELECT COUNT(*)
//                  FROM installations i
//                  WHERE i.request_id = r.id
//                    AND (i.labour_cost_service_id = p.labour_cost_service_id OR (i.labour_cost_service_id IS NULL AND p.labour_cost_service_id IS NULL))
//                    AND i.product_index = p.product_index
//              ) < (
//                  SELECT COUNT(*)
//                  FROM request_service_locations rl
//                  WHERE rl.request_id = r.id
//              )
//            ORDER BY p.id DESC;`
//       );

//       // Check if the query returned results and log the output
//       if (query && query[0] && query[0].length > 0) {
//           console.log(`Query Result Length: ${query[0].length}`);
//           console.log(`Raw Query Results: `, query[0]);

//           // Parse the data and log parsed output
//           const parsedData = query[0].map(record => parseDecimalFieldsForServices(record));
//           console.log(`Parsed Data: `, parsedData);

//           return parsedData;
//       } else {
//           console.log(`No data found for orderId: ${orderId}`);
//           return [];
//       }
//   } catch (error) {
//       // Log detailed error message
//       console.error(`Error while executing getServices for orderId ${orderId}: `, error);
//       throw new Error("Server error occurred while fetching services.");
//   }
// };

const getServices = async (orderId) => {
  try {
      // Define the where condition for the specific order
      const whereCondition = `p.request_id = ${orderId}`;
      console.log(`Where Condition: ${whereCondition}`);

      // Construct the SQL query
      const query = await sequelize.query(
          `SELECT p.*, c.customer_name, c.customer_phone, c.customer_address, c.sub_total, c.total, c.id AS order_id,
                  pr.name AS product_name, pr.makeAsTask AS makeAsTask_lcs, pr.makeInstallCard AS makeInstallCard_lcs
            FROM request_services p
            LEFT JOIN customer_orders c ON p.request_id = c.request_id
            LEFT JOIN requests r ON r.id = p.request_id
            LEFT JOIN projects pjt ON pjt.request_id = p.request_id
            LEFT JOIN labour_cost_services pr ON pr.id = p.labour_cost_service_id
            WHERE ${whereCondition}
              AND NOT EXISTS (
                  SELECT 1
                  FROM installations i
                  WHERE i.request_id = r.id
                    AND i.labour_cost_service_id = p.labour_cost_service_id
                    AND i.product_index = p.product_index
              )
            ORDER BY p.id DESC`
      );

      // Check if the query returned results and log the output
      if (query && query[0] && query[0].length > 0) {
          // console.log(`Query Result Length: ${query[0].length}`);
          // console.log(`Raw Query Results: `, query[0]);

          // Parse the data and log parsed output
          const parsedData = query[0].map(record => parseDecimalFieldsForServices(record));
          console.log(`Parsed Data: `, parsedData);

          return parsedData;
      } else {
          console.log(`No data found for orderId: ${orderId}`);
          return [];
      }
  } catch (error) {
      // Log detailed error message
      console.error(`Error while executing getServices for orderId ${orderId}: `, error);
      throw new Error("Server error occurred while fetching services.");
  }
};





const parseDecimalFieldsForServices = (record) => {
  // Parse or format decimal fields, assuming 'sub_total' and 'total' are decimal fields
  if (record.sub_total) {
      record.sub_total = parseFloat(record.sub_total);
  }
  if (record.total) {
      record.total = parseFloat(record.total);
  }
  // You can add more fields to be processed here as necessary
  return record;
}


const getLocations = async (orderId, productId) => {
    try {
        // let whereCondition = ` p.order_id =${orderId} AND p.product_id=${productId}`;
        let whereCondition = `p.order_id = ${orderId}`;
        if (productId === null) {
            whereCondition += ` AND p.product_id IS NULL`;  // Handle null case
        } else {
            whereCondition += ` AND p.product_id = ${productId}`;
        }

        let query = ''
        query = await sequelize.query(
            ` SELECT p.*, cop.location_quantity,cop.product_index FROM request_locations p `
            +` left join customer_order_products cop on cop.id=p.order_id `
            +` where ${whereCondition} AND NOT EXISTS (
                SELECT 1
                FROM installations i
                WHERE i.location_id = p.id
                AND i.product_id IS NOT NULL
            ) ORDER BY p.id DESC`
        );
        let data=[];
        if (query.length > 0) {
            data = query[0];
            return data;
        } else {
            return [];
        }
    } catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

const getServiceLocations = async (serviceOrderId, serviceId) => {
  try {
      // let whereCondition = ` p.order_id =${orderId} AND p.product_id=${productId}`;
      let whereCondition = `p.request_id = ${serviceId}`;
      if (serviceOrderId === null) {
          whereCondition += ` AND p.service_id IS NULL`;  // Handle null case
      } else {
          whereCondition += ` AND p.service_id = ${serviceOrderId}`;
      }

      let query = ''
      query = await sequelize.query(
          ` SELECT p.*, cop.qty As location_quantity,cop.product_index FROM request_service_locations p `
          +` left join  request_services cop on cop.id=p.service_id `
          +` where ${whereCondition} AND NOT EXISTS (
              SELECT 1
              FROM installations i
              WHERE i.location_id = p.id
              AND i.labour_cost_service_id IS NOT NULL
          ) ORDER BY p.id DESC`
      );
      let data=[];
      if (query.length > 0) {
          data = query[0];
          return data;
      } else {
          return [];
      }
  } catch (error) {
      console.log(error);
      throw new Error("Server error");
  }
}


const getTotalPaidAmountByRequestId = async (requestId) => {
    try {
        query =  await sequelize.query(` SELECT SUM(amount) AS total_amount_paid FROM request_payments WHERE request_id=${requestId}`)
        console.log('queryqueryqueryqueryqueryqueryqueryqueryqueryqueryqueryquery',query);
        let data=[];
        if (query.length > 0) {
            data = query[0][0];
            return data;
        } else {
            return false;
        }
    } catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

const updateInstallationStatus=async(data,instId)=>{
  console.log("datatatatatat",data,instId)
  let result = await Installation.update(data, {
      where: { id: instId }
    });
  if(result){
    return true;
  }
  else{
    return false;
  }
}

const updateInstallationHoldStatus = async (instId, data) => {
  try {
    const result = await Installation.update(data, {
      where: { id: instId },
      returning: true,
    });

    const affectedRows = Array.isArray(result) ? Number(result[0] || 0) : Number(result || 0);
    if (affectedRows > 0) return true;

    // MySQL/Sequelize can return 0 when values are already same.
    // Treat it as success if row exists and is already in requested status.
    const currentRow = await Installation.findOne({
      where: { id: instId },
      raw: true,
    });
    if (!currentRow) return false;

    if (Object.prototype.hasOwnProperty.call(data, "status")) {
      return Number(currentRow.status) === Number(data.status);
    }

    return true;
  } catch (error) {
    console.log("updateInstallationHoldStatus error:", error);
    return false;
  }
}

const updateDueDate = async (locationId, dueDate) => {
  const [updated] = await Installation.update(
    { due_date: dueDate },
    { where: { location_id: locationId } }
  );
  return updated > 0; // true only if a row was updated
};

const updateInstallationAddress = async (data, instId, locationId)  =>{
  try{
    let result = await Installation.update(data, {
        where: { id: instId, location_id: locationId, }
      });
    if(result){
      return true;
    }
    else{
      return false;
    }
  }catch(error){
    console.log("updateInstallationAddress error : ",error);
  }
}

const updateInstallationDate=async(projectId,locationId,data)=>{
    let result = await Installation.update(data, {
        where: {
            // project_id: projectId,
            location_id: locationId,
        },
            returning: true,
        });
        if(result[1]>0){
            return true;
        }
        else{
            return false;
        }
    }

  const addInstallationTabCount = async (data) => {
    try {
      const [newOrderCount, created] = await InstallationTabCount.findOrCreate({
        where: { user_id: data.user_id },
        defaults: { 
          staged_count: 0,
          installing_count: 0,
          returned_count: 0,
          completed_count: 0,
          staged_count_ids: null,
          installing_count_ids: null,
          returned_ids: null,
          completed_count_ids: null,
          timestamp: data.timestamp
        }
      });
  
      return newOrderCount.id;
    } catch (error) {
      console.error('Failed to add installation tab count:', error);
      throw error;
    }
  };

  const updateInstallationTabCount = async (userId, data) => {
    try{
      let result = await InstallationTabCount.update(data, {
        where: { user_id: userId }
      })
      if (result[0] > 0) {
        return true;
      } else {
        return false;
      }
    }catch(error){
      console.log('Error : ',error);
      return error
    }
  }

  const getInstallationTabCountByUserId = async (userId) => {
    try {
      const orderCount = await InstallationTabCount.findOne({
        where: { user_id: userId },
        attributes: ['id']
      });
  
      if (orderCount) {
        return orderCount;
      } else {
        return "No record found";
      }
    } catch (error) {
      console.error('Error retrieving installation count:', error);
      return error;
    }
  }

  const getInstallationTabCountByUserData = async (userId) => {
    try {
      const orderCount = await InstallationTabCount.findOne({
        where: { user_id: userId },
      });
  
      if (orderCount) {
        return orderCount;
      } else {
        return "No record found";
      }
    } catch (error) {
      console.error('Error retrieving installation count:', error);
      return error;
    }
  }

  const installationTabIncrementField = async (userId, field, amount, fieldIds, installationId, increase = true) => {
    try {
      const userCount = await InstallationTabCount.findOne({ where: { user_id: userId } });
  
      if (!userCount) {
        console.warn(`No record found for userId: ${userId}`);
        return false;
      }
  
      let currentIds = userCount[fieldIds] ? userCount[fieldIds].split(',') : [];
      if (!increase) {
        if (!currentIds.includes(installationId.toString())) {
          console.warn(`Installation ID not found in ${fieldIds}, cannot reduce count.`);
          return false;
        }
      }
      if (increase) {
        if (!currentIds.includes(installationId.toString())) {
          currentIds.push(installationId.toString());
          amount = 1;
        } else {
          console.warn(`Installation ID ${installationId} already exists in ${fieldIds}, count remains the same.`);
          amount = 0;
        }
      } else {
  
        currentIds = currentIds.filter(id => id !== installationId.toString());
      }
      const fieldUpdate = sequelize.literal(`GREATEST(${field} + (${amount}), 0)`);
      
      const result = await InstallationTabCount.update(
        {
          [field]: fieldUpdate, //sequelize.literal(`${field} + ${amount}`),
          [fieldIds]: currentIds.length > 0 ? currentIds.join(',') : null
        },
        { where: { user_id: userId } }
      );
  
      if (result[0]) {
        return true;
      } else {
        console.warn(`No rows were updated for userId: ${userId}`);
        return false;
      }
    } catch (error) {
      console.error("Error updating count and IDs:", error.message);
      console.error(error);
    }
  };
  
  const getInstallationTabCountByUser = async (userId) => {
    let res = await InstallationTabCount.findOne({
      where: { user_id: userId, status: 1 }
    })
    return res;
  }
  const getInstallCountByUser = async (userId) => {
    let res = await InstallationTabCount.findOne({
      where: { user_id: userId, status: 1 }
    })
    return res;
  }

  const addPickupTabCount = async (data) => {
    try {
      const [newOrderCount, created] = await PickupTabCount.findOrCreate({
        where: { user_id: data.user_id },
        defaults: { 
          staged_count: 0,
          completed_count: 0,
          staged_count_ids: null,
          completed_count_ids: null,
          timestamp: data.timestamp
        }
      });
  
      return newOrderCount.id;
    } catch (error) {
      console.error('Failed to add Pickup tab count:', error);
      throw error;
    }
  };

  const updatePickupTabCount = async (userId, data) => {
    try{
      let result = await PickupTabCount.update(data, {
        where: { user_id: userId }
      })
      if (result[0] > 0) {
        return true;
      } else {
        return false;
      }
    }catch(error){
      console.log('Error : ',error);
      return error
    }
  }

  const getPickupTabCountByUserId = async (userId) => {
    try {
      const orderCount = await PickupTabCount.findOne({
        where: { user_id: userId },
        attributes: ['id']
      });
  
      if (orderCount) {
        return orderCount;
      } else {
        return "No record found";
      }
    } catch (error) {
      console.error('Error retrieving Pickup count:', error);
      return error;
    }
  }

  const getPickupTabCountByUserData = async (userId) => {
    try {
      const orderCount = await PickupTabCount.findOne({
        where: { user_id: userId },
      });
  
      if (orderCount) {
        return orderCount;
      } else {
        return "No record found";
      }
    } catch (error) {
      console.error('Error retrieving pickup count:', error);
      return error;
    }
  }

  const pickupTabIncrementField = async (userId, field, amount, fieldIds, installationId, increase = true) => {
    try {
      const userCount = await PickupTabCount.findOne({ where: { user_id: userId } });
  
      if (!userCount) {
        console.warn(`No record found for userId: ${userId}`);
        return false;
      }
  
      let currentIds = userCount[fieldIds] ? userCount[fieldIds].split(',') : [];
      if (!increase) {
        if (!currentIds.includes(installationId.toString())) {
          console.warn(`Pickup ID not found in ${fieldIds}, cannot reduce count.`);
          return false;
        }
      }
      if (increase) {
        if (!currentIds.includes(installationId.toString())) {
          currentIds.push(installationId.toString());
          amount = 1;
        } else {
          console.warn(`Pickup ID ${installationId} already exists in ${fieldIds}, count remains the same.`);
          amount = 0;
        }
      } else {
  
        currentIds = currentIds.filter(id => id !== installationId.toString());
      }
      const fieldUpdate = sequelize.literal(`GREATEST(${field} + (${amount}), 0)`);
      
      const result = await PickupTabCount.update(
        {
          [field]: fieldUpdate, //sequelize.literal(`${field} + ${amount}`),
          [fieldIds]: currentIds.length > 0 ? currentIds.join(',') : null
        },
        { where: { user_id: userId } }
      );
  
      if (result[0]) {
        return true;
      } else {
        console.warn(`No rows were updated for userId: ${userId}`);
        return false;
      }
    } catch (error) {
      console.error("Error updating count and IDs:", error.message);
      console.error(error);
    }
  };
  
  const getPickupTabCountByUser = async (userId) => {
    let res = await PickupTabCount.findOne({
      where: { user_id: userId, status: 1 }
    })
    return res;
  }

  const addShippingTabCount = async (data) => {
    try {
      const [newOrderCount, created] = await ShippingTabCount.findOrCreate({
        where: { user_id: data.user_id },
        defaults: { 
          staged_count: 0,
          completed_count: 0,
          returned_count: 0,
          staged_count_ids: null,
          completed_count_ids: null,
          returned_count_ids: null,
          timestamp: data.timestamp
        }
      });
  
      return newOrderCount.id;
    } catch (error) {
      console.error('Failed to add Pickup tab count:', error);
      throw error;
    }
  };

  const updateShippingTabCount = async (userId, data) => {
    try{
      let result = await ShippingTabCount.update(data, {
        where: { user_id: userId }
      })
      if (result[0] > 0) {
        return true;
      } else {
        return false;
      }
    }catch(error){
      console.log('Error : ',error);
      return error
    }
  }

  const getShippingTabCountByUserId = async (userId) => {
    try {
      const orderCount = await ShippingTabCount.findOne({
        where: { user_id: userId },
        attributes: ['id']
      });
  
      if (orderCount) {
        return orderCount;
      } else {
        return "No record found";
      }
    } catch (error) {
      console.error('Error retrieving Shipping count:', error);
      return error;
    }
  }

  const getShippingTabCountByUserData = async (userId) => {
    try {
      const orderCount = await ShippingTabCount.findOne({
        where: { user_id: userId },
      });
  
      if (orderCount) {
        return orderCount;
      } else {
        return "No record found";
      }
    } catch (error) {
      console.error('Error retrieving Shipping count:', error);
      return error;
    }
  }

  const shippingTabIncrementField = async (userId, field, amount, fieldIds, installationId, increase = true) => {
    try {
      const userCount = await ShippingTabCount.findOne({ where: { user_id: userId } });
  
      if (!userCount) {
        console.warn(`No record found for userId: ${userId}`);
        return false;
      }
  
      let currentIds = userCount[fieldIds] ? userCount[fieldIds].split(',') : [];
      if (!increase) {
        if (!currentIds.includes(installationId.toString())) {
          console.warn(`Shipping ID not found in ${fieldIds}, cannot reduce count.`);
          return false;
        }
      }
      if (increase) {
        if (!currentIds.includes(installationId.toString())) {
          currentIds.push(installationId.toString());
          amount = 1;
        } else {
          console.warn(`Pickup ID ${installationId} already exists in ${fieldIds}, count remains the same.`);
          amount = 0;
        }
      } else {
  
        currentIds = currentIds.filter(id => id !== installationId.toString());
      }
      const fieldUpdate = sequelize.literal(`GREATEST(${field} + (${amount}), 0)`);
      
      const result = await ShippingTabCount.update(
        {
          [field]: fieldUpdate, //sequelize.literal(`${field} + ${amount}`),
          [fieldIds]: currentIds.length > 0 ? currentIds.join(',') : null
        },
        { where: { user_id: userId } }
      );
  
      if (result[0]) {
        return true;
      } else {
        console.warn(`No rows were updated for userId: ${userId}`);
        return false;
      }
    } catch (error) {
      console.error("Error updating count and IDs:", error.message);
      console.error(error);
    }
  };
  
  const getShippingTabCountByUser = async (userId) => {
    let res = await ShippingTabCount.findOne({
      where: { user_id: userId, status: 1 }
    })
    return res;
  }
  

const getAllCustomerOrders = async () => {
    try {
        let query = await sequelize.query(
            ` SELECT p.*,p.request_id,c.customer_name,c.customer_phone,c.customer_address,c.total,c.sub_total FROM projects p `
            +` LEFT JOIN users u ON p.added_by=u.id  `
            +` LEFT JOIN requests r on r.id=p.request_id`
            +` LEFT JOIN customer_orders c on c.request_id=r.id`
            +` 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(error);
        throw new Error("Server error");
    }
}

const addInstallationAppointment=async(data)=>{
    try {
        let results = await Installation.create(data)
        if (results) {
          console.log('results----------',results);
          return results;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.error("Error adding the projects ", error);
        return false;
    }
}


const fetchAllApointments = async (userId = 0, userType = 0, hasPermission = false, page, rowsPerPage,sort,fromDate,toDate, tab, project_id,dueData,search='',complete_status,filterClient, filterProjectType) => {
  try {
      const offset = (rowsPerPage && page) ? (page - 1) * rowsPerPage : null;
      const limit = rowsPerPage ? ` LIMIT ${rowsPerPage} OFFSET ${offset}` : '';
      let createdAt = '';
      if (dueData) {
        createdAt = `ORDER BY I.installation_date ASC`;
      } else if (sort === 'new') {
        createdAt = `ORDER BY I.id DESC `;
      } else if (sort === 'old') {
        createdAt = `ORDER BY I.id ASC `;
      } else if (sort === 'custom_order') {
        createdAt = `ORDER BY I.card_order ASC`;
      } else {
        createdAt = `ORDER BY I.timestamp DESC`;
      }

      const fromTimestamp = fromDate ? moment(fromDate).startOf('day').unix() : null;
      const toTimestamp = toDate ? moment(toDate).endOf('day').unix() : null;

      let dateCondition = '';
      if (fromTimestamp && toTimestamp) {
        dateCondition = ` I.installation_date BETWEEN ${fromTimestamp} AND ${toTimestamp}`;
      } else if (fromTimestamp) {
        dateCondition = ` I.installation_date >= ${fromTimestamp}`;
      } else if (toTimestamp) {
        dateCondition = ` I.installation_date <= ${toTimestamp}`;
      }
    //   else if (dueTimestamp) {
    //     dateCondition = ` I.installation_date <= ${dueTimestamp}`;
    // }

      const isAdminUser = userType == 1 || userType == 8;
      let whereCondition = '';
      if (userType !== 1 && userType !== 8 && userType !== 24 && userType !== 0 && hasPermission) {
        whereCondition = ` (EXISTS (
            SELECT 1
            FROM project_members pm
            JOIN staff_members sm ON sm.id = pm.staff_id
            WHERE pm.project_id = I.project_id AND sm.user_id = ${userId}
        ) OR (ura.id = ${userId} AND ura.user_type <> 3) OR FIND_IN_SET(${userId}, I.team_id)) `;
      }
      let whereCondition2 = '';
      if (isAdminUser) {
        whereCondition2 = '';
      } else if (userType == 3) {
        whereCondition = '';
        whereCondition2 = ` pr.user_id=${userId} `;
      } else if (userType == 24) {
        whereCondition = '';
        whereCondition2 = ` dr.user_id=${userId} `;
      } else {
        whereCondition2 = ` (FIND_IN_SET(${userId}, I.team_id) OR u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} OR (ura.id = ${userId} AND ura.user_type <> 3)) `
      }
    
      let userTypeCondition = '';
      if (isAdminUser) {
        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 if (userType == 24) {
        userTypeCondition = ``;
      } 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 u3 ON p.added_by=u3.id 
          LEFT JOIN (select pm.project_id, COALESCE(u4.id,u5.id) as user_id from project_members pm 
          left join partners p on p.id=pm.partner_id 
          left join users u4 on p.user_id=u4.id 
          left join staff_members s on s.id=pm.staff_id 
          left join users u5 on s.user_id=u5.id) pmm ON pmm.project_id=p.id and pmm.user_id=${userId} `
      }

      let tabCondition = '';
      if (tab) {
          if (tab === 'Awaiting') {
            tabCondition = ` I.status = 1`;
          } else if (tab === 'In-transit') {
            tabCondition = `(I.status = 2 OR (I.status = 4 AND COALESCE(temp.published, 0) != 1) OR COALESCE(I.pending_review, 0) = 2)`;
          } else if (tab === 'Delivered') {
            tabCondition = ` I.status = 3`;
          } else if (tab === 'Review') {
            tabCondition = ` I.status = 4 AND COALESCE(temp.published, 0) = 1 AND COALESCE(I.pending_review, 0) = 1`;
          } else if (tab === 'Completed') {
            tabCondition = ` I.status = 4 AND COALESCE(temp.published, 0) = 1 AND COALESCE(I.pending_review, 0) = 0`;
          } else if (tab === 'Failed') {
            tabCondition = ` I.status = 5`;
          } else if (tab === 'Assigned') {
            tabCondition = ` I.status = 6`;
          } else if (tab === 'Hold') {
            tabCondition = ` I.status = 7`;
          } else {
            tabCondition = ` I.status != 0`;
          }
      }

      let projectCondition = '';
      if (project_id) {
        projectCondition = ` I.project_id = ${project_id}`;
      }
      let searchCondition = '';
      if (search && search.trim() !== '') {
        const normalizedSearch = search
          .toLowerCase()
          .trim()
          .replace(/["'/\-]/g, '');

          searchCondition = `(
            LOWER(COALESCE(I.name, '')) LIKE '%${normalizedSearch}%' OR
            LOWER(COALESCE(r.description, '')) LIKE '%${normalizedSearch}%' OR
            LOWER(COALESCE(c.customer_name, '')) LIKE '%${normalizedSearch}%' OR
            LOWER(COALESCE(com.name, '')) LIKE '%${normalizedSearch}%' OR
            LOWER(COALESCE(ptp.name, '')) LIKE '%${normalizedSearch}%' OR
            LOWER(COALESCE(pc.name, '')) LIKE '%${normalizedSearch}%' OR
            LOWER(COALESCE(r.doc_number, '')) LIKE '%${normalizedSearch}%'
          )`;          
      }
      // if (search) {
      //   search = search.toLowerCase();
      //   // const escapedSearch = search.replace(/'/g, "''").trim();
      // //   const normalizedSearch = escapedSearch.toLowerCase().replace(/["'/\-]/g, '').replace(/\s+/g, '');
      //   // searchCondition = ` (I.name LIKE '%${search}%' OR r.description LIKE '%${search}%') `;
      //   searchCondition = ` (LOWER(r.description) LIKE '%${search.toLowerCase()}%') `;
      // }
      // if (search) {
      //   const escapedSearch = search.replace(/'/g, "''").trim();
      //   const normalizedSearch = escapedSearch.toLowerCase().replace(/["'/\-]/g, '').replace(/\s+/g, '');
      
      //   // Constructing the match condition
      //   searchCondition = `AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(I.name), '"', ''), '/', ''), '-', ''), ' ', ''), '''', '') LIKE '%${normalizedSearch}%' 
      //             OR REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(r.description), '"', ''), '/', ''), '-', ''), ' ', ''), '''', '') LIKE '%${normalizedSearch}%')`;
      // }
      const completeStatusCondition = complete_status
        ? ` I.complete_status = ${complete_status} `
        : ` I.complete_status = 1`;

      let clientCondition = '';
      if (filterClient && filterClient.length > 0) {
          clientCondition = ` c.customer_id IN (${filterClient.join(',')})`;
      }
      let projectTypeCondition = '';
      if (filterProjectType === 1) {
        projectTypeCondition = `r.project_type = 1`;
      } else if (filterProjectType === 2) {
        projectTypeCondition = `r.project_type = 2`;
      } else if (filterProjectType === 'NULL') {
        projectTypeCondition = `r.project_type IS NULL`;
      } else {
        projectTypeCondition = `(r.project_type IS NULL OR r.project_type IS NOT NULL)`;
      }

      const finalCondition = [
        completeStatusCondition,
        whereCondition,
        whereCondition2,
        dateCondition,
        tabCondition,
        projectCondition,
        searchCondition,
        clientCondition,
        projectTypeCondition,
      ].filter(Boolean);

      const combinedWhere = finalCondition.length > 0
        ? `WHERE ${finalCondition.join(' AND ')} AND r.installation_acceptance IS NULL`
        : `WHERE r.installation_acceptance IS NULL`;

      let query = await sequelize.query(`SELECT c.*,temp.published, I.*,ptp.name AS job_class_name,pc.name AS job_category_name, p.cover_image, p.start_date,p.name,r.doc_number,lcs.avatarUrl AS service_image,r.is_split_order,r.for_parent_facility,r.origin_facility_id,cop_specific.product_name as build_product_name,cop_specific.product_notes AS product_notes,r.min_payment_status,
          CASE 
            WHEN I.product_id IS NOT NULL THEN prod.name
            WHEN I.labour_cost_service_id IS NOT NULL THEN NULL
            ELSE GROUP_CONCAT(DISTINCT cop.product_name SEPARATOR ', ')
          END AS product_name,
          CASE 
            WHEN pc.id = 12 AND I.labour_cost_service_id = 10 THEN NULL
            WHEN I.labour_cost_service_id IS NOT NULL THEN lcs.name
            ELSE NULL
          END AS service_name,
          GROUP_CONCAT(DISTINCT CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) SEPARATOR ', ') AS implementation_manager, r.project_type,
          u.profile_pic AS manager_profile_photo, dr.name AS driver_name, com.name AS community_name,
          ra.attachmentUrl, 
          ra.approved, rl.order_id as customer_order_product_id,
          COALESCE(pf.has_project_files, 0) AS has_project_files,part.thumbnail AS customer_logo
          FROM installations I
          LEFT JOIN users u ON FIND_IN_SET(u.id, I.team_id)
          LEFT JOIN projects p ON p.id = I.project_id
          LEFT JOIN project_categories pc ON pc.id=p.project_category
          LEFT JOIN project_types ptp ON ptp.id=p.project_type
          LEFT JOIN products prod ON I.product_id = prod.id
          ${userTypeCondition}
          JOIN requests r ON r.id = I.request_id
          left join users ura on r.added_by=ura.id
          LEFT JOIN labour_cost_services lcs ON lcs.id = I.labour_cost_service_id
          LEFT JOIN customer_orders c ON c.request_id = r.id
          LEFT JOIN partners part ON c.customer_id = part.id
          LEFT JOIN customer_order_products cop ON cop.order_id = c.id
          LEFT JOIN customer_order_products cop_specific ON cop_specific.order_id = c.id AND (I.product_id IS NULL OR cop_specific.product_id = I.product_id)
          LEFT JOIN (
              SELECT request_id,
                     COALESCE(MAX(CASE WHEN approved = 1 THEN name END), MAX(name)) AS attachmentUrl,
                     COALESCE(MAX(CASE WHEN approved = 1 THEN 1 END), MAX(approved)) AS approved
              FROM request_attachments
              WHERE file_type LIKE 'image/%' OR file_type = 'img'
              GROUP BY request_id
          ) ra ON ra.request_id = I.request_id
          LEFT JOIN request_locations rl ON rl.id = I.location_id
          LEFT JOIN community com ON com.id=p.community and FIND_IN_SET(c.customer_id, com.clients) > 0 AND com.status = 1
          LEFT JOIN driver dr ON dr.id = I.driver_id
          LEFT JOIN (
              SELECT DISTINCT project_id, 1 AS has_project_files
              FROM project_files
          ) pf ON pf.project_id = I.project_id
          LEFT JOIN (
              SELECT installation_id, 
                      MAX(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS published
              FROM installation_temp_fields
              GROUP BY installation_id
              ) temp ON temp.installation_id = I.id
          ${combinedWhere}
          GROUP BY I.id, I.request_id, prod.id, lcs.id
          ${createdAt} ${limit}`);

      let formattedResult = [];

      if (query[0]?.length > 0) {
          const resultMap = new Map();
          query[0].forEach(item => {
              const requestId = item.request_id;
              const attachment = { attachmentUrl: item.attachmentUrl || null, approved: item.approved || null };
              if (!resultMap.has(requestId)) {
                  resultMap.set(requestId, { ...item, attachments: [attachment] });
              } else {
                  resultMap.set(item.id, { ...item, attachments: [attachment] });
              }
              if (attachment.approved === 1) {
                  resultMap.get(requestId).attachments.push(attachment);
              }
          });
          formattedResult = Array.from(resultMap.values());
      }
      return formattedResult.map(record => parseDecimalFields(record));
  } catch (error) {
      console.error("Error fetching appointments:", error);
      return [];
  }
}
// const fetchAllApointments = async (userId = 0, userType = 0, hasPermission = false, page, rowsPerPage,sort,fromDate,toDate, tab, project_id,dueData,search='',complete_status,filterClient, filterProjectType) => {
//   try {
//       let offset = null;
//       if (rowsPerPage && page) {
//           offset = (page - 1) * rowsPerPage;
//       }
//       let limit = '';
//       if (rowsPerPage) {
//           limit = ` LIMIT ${rowsPerPage} OFFSET ${offset}`;
//       }
//       let createdAt = ''
//       if (dueData) {
//         createdAt=`ORDER BY I.installation_date ASC`
//     }else if(sort==='new'){
//          createdAt = `ORDER BY I.id DESC `
//       }else if(sort==='old'){
//           createdAt = `ORDER BY I.id ASC `
//       }else if(sort === 'custom_order'){
//         createdAt=`ORDER BY I.card_order ASC`
//       }else{
//          createdAt=`ORDER BY I.timestamp DESC`
//       }

//       const fromTimestamp = fromDate ? moment(fromDate).startOf('day').unix() : null;
//       const toTimestamp = toDate ? moment(toDate).endOf('day').unix() : null;
//       const dueTimestamp = dueData ? moment().endOf('day').unix() : null;

//       let dateCondition = '';
//       if (fromTimestamp && toTimestamp) {
//         dateCondition = ` I.installation_date BETWEEN ${fromTimestamp} AND ${toTimestamp}`;
//       } else if (fromTimestamp) {
//         dateCondition = ` I.installation_date >= ${fromTimestamp}`;
//       } else if (toTimestamp) {
//         dateCondition = ` I.installation_date <= ${toTimestamp}`;
//       }
//     //   else if (dueTimestamp) {
//     //     dateCondition = ` I.installation_date <= ${dueTimestamp}`;
//     // }

//       let whereCondition = '';
//       if (userType !== 1 && userType !== 8 && userType !== 24 && userType !== 0 && hasPermission) {
//         whereCondition = ` (EXISTS (
//             SELECT 1
//             FROM project_members pm
//             JOIN staff_members sm ON sm.id = pm.staff_id
//             WHERE pm.project_id = I.project_id AND sm.user_id = ${userId}
//         ) OR (ura.id = ${userId} AND ura.user_type <> 3) OR FIND_IN_SET(${userId}, I.team_id)) `;
//       }
//       let whereCondition2 = '';
//       if(userType == 1 || userType == 8){
//         whereCondition2 = '';
//       }else if(userType == 3){
//         whereCondition = ''
//         whereCondition2 = ` pr.user_id=${userId} `;
//       }else if(userType == 24){
//         whereCondition = ''
//         whereCondition2 = ` dr.user_id=${userId} `;
//       }else{
//         whereCondition2 = ` (FIND_IN_SET(${userId}, I.team_id) OR u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} OR (ura.id = ${userId} AND ura.user_type <> 3)) `
//       }
    
//       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 if(userType == 24){
//         userTypeCondition = ``
//       }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 u3 ON p.added_by=u3.id 
//           LEFT JOIN (select pm.project_id, COALESCE(u4.id,u5.id) as user_id from project_members pm 
//           left join partners p on p.id=pm.partner_id 
//           left join users u4 on p.user_id=u4.id 
//           left join staff_members s on s.id=pm.staff_id 
//           left join users u5 on s.user_id=u5.id) pmm ON pmm.project_id=p.id and pmm.user_id=${userId} `
//       }

//       let tabCondition = '';
//       if (tab) {
//           // if (tab === 'Ongoing') {
//           //     tabCondition = ` I.status != 4`;
//           // } else if (tab === 'Completed') {
//           //     tabCondition = ` I.status = 4`;
//           // }
//           if (tab === 'Awaiting') {
//             tabCondition = ` I.status = 1`;
//           }
//           else if (tab === 'In-transit') {
//             // tabCondition = ` I.status = 2 OR (I.status = 4 AND COALESCE(temp.published, 0) != 1)`;
//             tabCondition = `(I.status = 2 OR (I.status = 4 AND COALESCE(temp.published, 0) != 1) OR COALESCE(I.pending_review, 0) = 2)`;
//             // tabCondition = `i.status = 2`
//           }else if (tab === 'Delivered') {
//             tabCondition = ` I.status = 3`;
//           }else if (tab === 'Review') {
//             tabCondition = ` I.status = 4 AND COALESCE(temp.published, 0) = 1 AND COALESCE(I.pending_review, 0) = 1`;
//           }else if (tab === 'Completed') {
//             tabCondition = ` I.status = 4 AND COALESCE(temp.published, 0) = 1 AND COALESCE(I.pending_review, 0) = 0`;
//             // tabCondition= `I.status = 4`
//           }else if (tab === 'Failed') {
//             tabCondition = ` I.status = 5`;
//           }else if (tab === 'Assigned') {
//             tabCondition = ` I.status = 6`;
//           }else if (tab === 'Hold') {
//             tabCondition = ` I.status = 7`;
//           }else{
//             tabCondition = ` I.status != 0`;
//           }
//       }

//       let projectCondition = '';
//       if (project_id) {
//         projectCondition = ` I.project_id = ${project_id}`;
//       }
//       let searchCondition = '';
//       if (search) {
//         const normalizedSearch = search
//           .toLowerCase()
//           .trim()
//           .replace(/["'/\-]/g, '');

//           searchCondition = `(
//             LOWER(COALESCE(I.name, '')) LIKE '%${normalizedSearch}%' OR
//             LOWER(COALESCE(r.description, '')) LIKE '%${normalizedSearch}%' OR
//             LOWER(COALESCE(c.customer_name, '')) LIKE '%${normalizedSearch}%' OR
//             LOWER(COALESCE(com.name, '')) LIKE '%${normalizedSearch}%' OR
//             LOWER(COALESCE(ptp.name, '')) LIKE '%${normalizedSearch}%' OR
//             LOWER(COALESCE(pc.name, '')) LIKE '%${normalizedSearch}%' OR
//             LOWER(COALESCE(r.doc_number, '')) LIKE '%${normalizedSearch}%'
//           )`;          
//       }
//       // if (search) {
//       //   search = search.toLowerCase();
//       //   // const escapedSearch = search.replace(/'/g, "''").trim();
//       // //   const normalizedSearch = escapedSearch.toLowerCase().replace(/["'/\-]/g, '').replace(/\s+/g, '');
//       //   // searchCondition = ` (I.name LIKE '%${search}%' OR r.description LIKE '%${search}%') `;
//       //   searchCondition = ` (LOWER(r.description) LIKE '%${search.toLowerCase()}%') `;
//       // }
//       // if (search) {
//       //   const escapedSearch = search.replace(/'/g, "''").trim();
//       //   const normalizedSearch = escapedSearch.toLowerCase().replace(/["'/\-]/g, '').replace(/\s+/g, '');
      
//       //   // Constructing the match condition
//       //   searchCondition = `AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(I.name), '"', ''), '/', ''), '-', ''), ' ', ''), '''', '') LIKE '%${normalizedSearch}%' 
//       //             OR REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(r.description), '"', ''), '/', ''), '-', ''), ' ', ''), '''', '') LIKE '%${normalizedSearch}%')`;
//       // }
//       let completeStatusCondition = '';
//       if (complete_status) {
//         completeStatusCondition = ` I.complete_status = ${complete_status} `;
//       }else{
//         completeStatusCondition = ` I.complete_status = 1`;
//       }
//       // Combine conditions carefully
//       let finalCondition = [];
//       finalCondition.push(completeStatusCondition);
//       if (whereCondition) {
//           finalCondition.push(whereCondition);
//       }
//       if (whereCondition2) {
//           finalCondition.push(whereCondition2);
//       }
//       if (dateCondition) {
//           finalCondition.push(dateCondition);
//       }
//       if (tabCondition) {
//           finalCondition.push(tabCondition);
//       }
//       if (projectCondition) {
//         finalCondition.push(projectCondition);
//       }
//       if (searchCondition) {
//         finalCondition.push(searchCondition);
//       }
//       let clientCondition = '';
//       if (filterClient && filterClient.length > 0) {
//           clientCondition = ` c.customer_id IN (${filterClient.join(',')})`;
//       }
//       if (clientCondition) {
//           finalCondition.push(clientCondition);
//       }
//       if (filterProjectType === 1) {
//         finalCondition.push(`r.project_type = 1`);
//       } else if (filterProjectType === 2) {
//         finalCondition.push(`r.project_type = 2`);
//       } else if (filterProjectType === 'NULL') {
//         finalCondition.push(`r.project_type IS NULL`);
//       } else {
//         finalCondition.push(`(r.project_type IS NULL OR r.project_type IS NOT NULL)`);
//       }
//       // const combinedWhere = finalCondition.length > 0 ? `WHERE ${finalCondition.join(' AND ')}` : '';
//       const combinedWhere = finalCondition.length > 0
//   ? `WHERE ${finalCondition.join(' AND ')} AND r.installation_acceptance IS NULL`
//   : `WHERE r.installation_acceptance IS NULL`;
//       //multi installer
//       let query = await sequelize.query(`SELECT c.*,temp.published, I.*,ptp.name AS job_class_name,pc.name AS job_category_name, p.cover_image, p.start_date,p.name,r.doc_number,lcs.avatarUrl AS service_image,r.is_split_order,r.for_parent_facility,r.origin_facility_id,cop_specific.product_name as build_product_name,cop_specific.product_notes AS product_notes,r.min_payment_status,
//           CASE 
//             WHEN I.product_id IS NOT NULL THEN prod.name
//             WHEN I.labour_cost_service_id IS NOT NULL THEN NULL
//             ELSE GROUP_CONCAT(DISTINCT cop.product_name SEPARATOR ', ')
//           END AS product_name,
//           CASE 
//             WHEN pc.id = 12 AND I.labour_cost_service_id = 10 THEN NULL
//             WHEN I.labour_cost_service_id IS NOT NULL THEN lcs.name
//             ELSE NULL
//           END AS service_name,
//           GROUP_CONCAT(DISTINCT CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) SEPARATOR ', ') AS implementation_manager, r.project_type,
//           u.profile_pic AS manager_profile_photo, dr.name AS driver_name, com.name AS community_name,
//           COALESCE(MAX(CASE WHEN ra.approved = 1 THEN ra.name END), MAX(ra.name)) AS attachmentUrl, 
//           COALESCE(MAX(CASE WHEN ra.approved = 1 THEN 1 END), MAX(ra.approved)) AS approved ,rl.order_id as customer_order_product_id,
//           CASE WHEN pf.id IS NOT NULL THEN 1 ELSE 0 END AS has_project_files,part.thumbnail AS customer_logo
//           FROM installations I
//          LEFT JOIN staff_members sm ON FIND_IN_SET(sm.id, I.team_id)         
//           LEFT JOIN project_members pm ON pm.project_id = I.project_id
//           LEFT JOIN users u ON FIND_IN_SET(u.id, I.team_id)
//           LEFT JOIN projects p ON p.id = I.project_id
//           LEFT JOIN project_categories pc ON pc.id=p.project_category
//           LEFT JOIN project_types ptp ON ptp.id=p.project_type
//           LEFT JOIN products prod ON I.product_id = prod.id
//           ${userTypeCondition}
//           JOIN requests r ON r.id = I.request_id
//           left join users ura on r.added_by=ura.id
//           LEFT JOIN labour_cost_services lcs ON lcs.id = I.labour_cost_service_id
//           LEFT JOIN customer_orders c ON c.request_id = r.id
//           LEFT JOIN partners part ON c.customer_id = part.id
//           LEFT JOIN customer_order_products cop ON cop.order_id = c.id
//           LEFT JOIN customer_order_products cop_specific ON cop_specific.order_id = c.id AND (I.product_id IS NULL OR cop_specific.product_id = I.product_id)
//           LEFT JOIN request_attachments ra ON ra.request_id = I.request_id AND (ra.file_type LIKE 'image/%' OR ra.file_type = 'img')
//           LEFT JOIN request_locations rl ON rl.id = I.location_id
//           LEFT JOIN community com ON com.id=p.community and FIND_IN_SET(c.customer_id, com.clients) > 0 AND com.status = 1
//           LEFT JOIN driver dr ON dr.id = I.driver_id
//           LEFT JOIN project_files pf ON pf.project_id = I.project_id
//           LEFT JOIN (
//               SELECT installation_id, 
//                       MAX(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS published
//               FROM installation_temp_fields
//               GROUP BY installation_id
//               ) temp ON temp.installation_id = I.id
//           ${combinedWhere}
//           GROUP BY I.id, I.request_id, prod.id, lcs.id
//           ${createdAt} ${limit}`);

//       let formattedResult = [];

//       if (query[0]?.length > 0) {
//           console.log('🔍 Sample installation data with product_name:', query[0][0]?.product_name);
//           const resultMap = new Map();
//           query[0].forEach(item => {
//               const requestId = item.request_id;
//               const attachment = { attachmentUrl: item.attachmentUrl || null, approved: item.approved || null };
//               if (!resultMap.has(requestId)) {
//                   resultMap.set(requestId, { ...item, attachments: [attachment] });
//               } else {
//                   resultMap.set(item.id, { ...item, attachments: [attachment] });
//               }
//               if (attachment.approved === 1) {
//                   resultMap.get(requestId).attachments.push(attachment);
//               }
//           });
//           formattedResult = Array.from(resultMap.values());
//       }
//       return formattedResult.map(record => parseDecimalFields(record));
//   } catch (error) {
//       console.error("Error fetching appointments:", error);
//       return [];
//   }
// }

const fetchAllApointmentsOptimizedForCalendar = async (
  userId = 0,
  userType = 0,
  hasPermission = false,
  page,
  rowsPerPage,
  sort,
  fromDate,
  toDate,
  tab,
  project_id,
  dueData,
  search = '',
  complete_status,
  filterClient,
  filterProjectType
) => {
  try {
    const pageNum = Number(page) || 0;
    const pageSize = Number(rowsPerPage) || 0;

    let limit = '';
    if (pageSize > 0) {
      const offset = Math.max(pageNum - 1, 0) * pageSize;
      limit = ` LIMIT ${pageSize} OFFSET ${offset}`;
    }

    let createdAt = '';
    if (dueData) {
      createdAt = ` ORDER BY I.installation_date ASC `;
    } else if (sort === 'new') {
      createdAt = ` ORDER BY I.id DESC `;
    } else if (sort === 'old') {
      createdAt = ` ORDER BY I.id ASC `;
    } else if (sort === 'custom_order') {
      createdAt = ` ORDER BY I.card_order ASC `;
    } else {
      createdAt = ` ORDER BY I.timestamp DESC `;
    }

    const fromTimestamp = fromDate ? moment(fromDate).startOf('day').unix() : null;
    const toTimestamp = toDate ? moment(toDate).endOf('day').unix() : null;

    const conditions = [];

    // complete status
    if (complete_status) {
      conditions.push(`I.complete_status = ${complete_status}`);
    } else {
      conditions.push(`I.complete_status = 1`);
    }

    // date condition
    if (fromTimestamp && toTimestamp) {
      conditions.push(`I.installation_date BETWEEN ${fromTimestamp} AND ${toTimestamp}`);
    } else if (fromTimestamp) {
      conditions.push(`I.installation_date >= ${fromTimestamp}`);
    } else if (toTimestamp) {
      conditions.push(`I.installation_date <= ${toTimestamp}`);
    }

    // role conditions
    let whereCondition = '';
    if (userType !== 1 && userType !== 8 && userType !== 24 && userType !== 0 && hasPermission) {
      whereCondition = `(
        EXISTS (
          SELECT 1
          FROM project_members pm
          JOIN staff_members sm ON sm.id = pm.staff_id
          WHERE pm.project_id = I.project_id
            AND sm.user_id = ${userId}
        )
        OR (ura.id = ${userId} AND ura.user_type <> 3)
        OR FIND_IN_SET(${userId}, I.team_id)
      )`;
    }

    let whereCondition2 = '';
    if (userType == 1 || userType == 8) {
      whereCondition2 = '';
    } else if (userType == 3) {
      whereCondition = '';
      whereCondition2 = `pr.user_id = ${userId}`;
    } else if (userType == 24) {
      whereCondition = '';
      whereCondition2 = `dr.user_id = ${userId}`;
    } else {
      whereCondition2 = `(
        FIND_IN_SET(${userId}, I.team_id)
        OR u1.id = ${userId}
        OR u2.id = ${userId}
        OR pmm.user_id = ${userId}
        OR (ura.id = ${userId} AND ura.user_type <> 3)
      )`;
    }

    if (whereCondition) conditions.push(whereCondition);
    if (whereCondition2) conditions.push(whereCondition2);

    // user type joins
    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 if (userType == 24) {
      userTypeCondition = ``;
    } 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 u3 ON p.added_by = u3.id
        LEFT JOIN (
          SELECT
            pm.project_id,
            COALESCE(u4.id, u5.id) AS user_id
          FROM project_members pm
          LEFT JOIN partners p ON p.id = pm.partner_id
          LEFT JOIN users u4 ON p.user_id = u4.id
          LEFT JOIN staff_members s ON s.id = pm.staff_id
          LEFT JOIN users u5 ON s.user_id = u5.id
        ) pmm ON pmm.project_id = p.id AND pmm.user_id = ${userId}
      `;
    }

    // tab condition
    if (tab) {
      if (tab === 'Awaiting') {
        conditions.push(`I.status = 1`);
      } else if (tab === 'In-transit') {
        conditions.push(`(I.status = 2 OR (I.status = 4 AND COALESCE(temp.published, 0) != 1) OR COALESCE(I.pending_review, 0) = 2)`);
      } else if (tab === 'Delivered') {
        conditions.push(`I.status = 3`);
      } else if (tab === 'Review') {
        conditions.push(`I.status = 4 AND COALESCE(temp.published, 0) = 1 AND COALESCE(I.pending_review, 0) = 1`);
      } else if (tab === 'Completed') {
        conditions.push(`I.status = 4 AND COALESCE(temp.published, 0) = 1 AND COALESCE(I.pending_review, 0) = 0`);
      } else if (tab === 'Failed') {
        conditions.push(`I.status = 5`);
      } else if (tab === 'Assigned') {
        conditions.push(`I.status = 6`);
      } else if (tab === 'Hold') {
        conditions.push(`I.status = 7`);
      } else {
        conditions.push(`I.status != 0`);
      }
    }

    // project condition
    if (project_id) {
      conditions.push(`I.project_id = ${project_id}`);
    }

    // search condition
    if (search) {
      const normalizedSearch = search.toLowerCase().trim().replace(/["'/\-]/g, '');
      conditions.push(`(
        LOWER(COALESCE(I.name, '')) LIKE '%${normalizedSearch}%'
        OR LOWER(COALESCE(r.description, '')) LIKE '%${normalizedSearch}%'
        OR LOWER(COALESCE(c.customer_name, '')) LIKE '%${normalizedSearch}%'
        OR LOWER(COALESCE(com.name, '')) LIKE '%${normalizedSearch}%'
        OR LOWER(COALESCE(ptp.name, '')) LIKE '%${normalizedSearch}%'
        OR LOWER(COALESCE(pc.name, '')) LIKE '%${normalizedSearch}%'
        OR LOWER(COALESCE(r.doc_number, '')) LIKE '%${normalizedSearch}%'
      )`);
    }

    // client filter
    if (filterClient && filterClient.length > 0) {
      conditions.push(`c.customer_id IN (${filterClient.join(',')})`);
    }

    // project type filter
    if (filterProjectType === 1) {
      conditions.push(`r.project_type = 1`);
    } else if (filterProjectType === 2) {
      conditions.push(`r.project_type = 2`);
    } else if (filterProjectType === 'NULL') {
      conditions.push(`r.project_type IS NULL`);
    } else {
      conditions.push(`(r.project_type IS NULL OR r.project_type IS NOT NULL)`);
    }

    // installation acceptance
    conditions.push(`r.installation_acceptance IS NULL`);

    const combinedWhere = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';

    const query = await sequelize.query(`
      SELECT
        c.*,
        temp.published,
        I.*,
        ptp.name AS job_class_name,
        pc.name AS job_category_name,
        p.cover_image,
        p.start_date,
        p.name,
        r.doc_number,
        lcs.avatarUrl AS service_image,
        r.is_split_order,
        r.for_parent_facility,
        r.origin_facility_id,
        cop_specific.product_name AS build_product_name,
        cop_specific.product_notes AS product_notes,
        r.min_payment_status,
        CASE
          WHEN I.product_id IS NOT NULL THEN prod.name
          WHEN I.labour_cost_service_id IS NOT NULL THEN NULL
          ELSE cop_agg.product_name
        END AS product_name,
        CASE
          WHEN pc.id = 12 AND I.labour_cost_service_id = 10 THEN NULL
          WHEN I.labour_cost_service_id IS NOT NULL THEN lcs.name
          ELSE NULL
        END AS service_name,
        team_users.implementation_manager,
        r.project_type,
        u.profile_pic AS manager_profile_photo,
        dr.name AS driver_name,
        com.name AS community_name,
        ra_agg.attachmentUrl,
        ra_agg.approved,
        rl.order_id AS customer_order_product_id,
        CASE WHEN pf_agg.project_id IS NOT NULL THEN 1 ELSE 0 END AS has_project_files,
        part.thumbnail AS customer_logo
      FROM installations I

      LEFT JOIN projects p ON p.id = I.project_id
      LEFT JOIN project_categories pc ON pc.id = p.project_category
      LEFT JOIN project_types ptp ON ptp.id = p.project_type
      LEFT JOIN products prod ON I.product_id = prod.id
      ${userTypeCondition}
      JOIN requests r ON r.id = I.request_id
      LEFT JOIN users ura ON r.added_by = ura.id
      LEFT JOIN labour_cost_services lcs ON lcs.id = I.labour_cost_service_id
      LEFT JOIN customer_orders c ON c.request_id = r.id
      LEFT JOIN partners part ON c.customer_id = part.id
      LEFT JOIN request_locations rl ON rl.id = I.location_id
      LEFT JOIN community com ON com.id = p.community
        AND FIND_IN_SET(c.customer_id, com.clients) > 0
        AND com.status = 1
      LEFT JOIN driver dr ON dr.id = I.driver_id

      -- aggregated temp
      LEFT JOIN (
        SELECT
          installation_id,
          MAX(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS published
        FROM installation_temp_fields
        GROUP BY installation_id
      ) temp ON temp.installation_id = I.id

      -- aggregated team users
      LEFT JOIN (
        SELECT
          i2.id AS installation_id,
          GROUP_CONCAT(DISTINCT CONCAT(u2.first_name, ' ', COALESCE(u2.last_name, '')) SEPARATOR ', ') AS implementation_manager,
          MAX(u2.profile_pic) AS profile_pic
        FROM installations i2
        LEFT JOIN users u2 ON FIND_IN_SET(u2.id, i2.team_id)
        GROUP BY i2.id
      ) team_users ON team_users.installation_id = I.id

      LEFT JOIN users u ON FIND_IN_SET(u.id, I.team_id)

      -- aggregated customer order products
      LEFT JOIN (
        SELECT
          order_id,
          GROUP_CONCAT(DISTINCT product_name SEPARATOR ', ') AS product_name
        FROM customer_order_products
        GROUP BY order_id
      ) cop_agg ON cop_agg.order_id = c.id

      -- specific customer order product
      LEFT JOIN customer_order_products cop_specific
        ON cop_specific.order_id = c.id
        AND (I.product_id IS NULL OR cop_specific.product_id = I.product_id)

      -- aggregated attachments
      LEFT JOIN (
        SELECT
          request_id,
          COALESCE(
            MAX(CASE WHEN approved = 1 THEN name END),
            MAX(name)
          ) AS attachmentUrl,
          COALESCE(
            MAX(CASE WHEN approved = 1 THEN 1 END),
            MAX(approved)
          ) AS approved
        FROM request_attachments
        WHERE file_type LIKE 'image/%' OR file_type = 'img'
        GROUP BY request_id
      ) ra_agg ON ra_agg.request_id = I.request_id

      -- project file existence only
      LEFT JOIN (
        SELECT DISTINCT project_id
        FROM project_files
      ) pf_agg ON pf_agg.project_id = I.project_id

      ${combinedWhere}
      GROUP BY I.id
      ${createdAt}
      ${limit}
    `);

    let formattedResult = [];

    if (query[0]?.length > 0) {
      const resultMap = new Map();

      query[0].forEach((item) => {
        const requestId = item.request_id;
        const attachment = {
          attachmentUrl: item.attachmentUrl || null,
          approved: item.approved || null,
        };

        if (!resultMap.has(requestId)) {
          resultMap.set(requestId, { ...item, attachments: [attachment] });
        } else {
          resultMap.set(item.id, { ...item, attachments: [attachment] });
        }

        if (attachment.approved === 1) {
          resultMap.get(requestId).attachments.push(attachment);
        }
      });

      formattedResult = Array.from(resultMap.values());
    }

    return formattedResult.map((record) => parseDecimalFields(record));
  } catch (error) {
    console.error('Error fetching appointments:', error);
    return [];
  }
};

const fetchAllApointmentsForFilter = async (userId = 0, userType = 0, hasPermission = false,sort,fromDate,toDate, tab,complete_status) => {
  try {
      let createdAt = ''
      if(sort==='new'){
         createdAt = `ORDER BY I.id DESC `
      }else if(sort==='old'){
          createdAt = `ORDER BY I.id ASC `
      }else if(sort === 'custom_order'){
        createdAt=`ORDER BY I.card_order ASC`
       }else{
         createdAt=`ORDER BY I.timestamp DESC`
      }

      const fromTimestamp = fromDate ? moment(fromDate).startOf('day').unix() : null;
      const toTimestamp = toDate ? moment(toDate).endOf('day').unix() : null;

      let dateCondition = '';
      if (fromTimestamp && toTimestamp) {
        dateCondition = ` I.installation_date BETWEEN ${fromTimestamp} AND ${toTimestamp}`;
      } else if (fromTimestamp) {
        dateCondition = ` I.installation_date >= ${fromTimestamp}`;
      } else if (toTimestamp) {
        dateCondition = ` I.installation_date <= ${toTimestamp}`;
      }

      let whereCondition = '';
      if (userType !== 1 && userType !== 8 && userType !== 0 && !hasPermission) {
        whereCondition = ` ( EXISTS (
            SELECT 1
            FROM project_members pm
            JOIN staff_members sm ON sm.id = pm.staff_id
            WHERE pm.project_id = I.project_id AND sm.user_id = ${userId}
        ) OR (ura.id = ${userId} AND ura.user_type <> 3) OR FIND_IN_SET(${userId}, I.team_id) ) `;
      }

      let tabCondition = '';
      if (tab) {
          // if (tab === 'Ongoing') {
          //     tabCondition = ` I.status != 4`;
          // } else if (tab === 'Completed') {
          //     tabCondition = ` I.status = 4`;
          // }
          if (tab === 'Awaiting') {
            tabCondition = ` I.status = 1`;
          }else if (tab === 'In-transit') {
            // tabCondition = ` I.status = 2 OR (I.status = 4 AND COALESCE(temp.published, 0) != 1)`;
            tabCondition = `(I.status = 2 OR (I.status = 4 AND COALESCE(temp.published, 0) != 1) OR COALESCE(I.pending_review, 0) = 2)`;
            // tabCondition = `i.status = 2`
          }else if (tab === 'Delivered') {
            tabCondition = ` I.status = 3`;
          }else if (tab === 'Review') {
            tabCondition = ` I.status = 4 AND COALESCE(temp.published, 0) = 1 AND COALESCE(I.pending_review, 0) = 1`;
          }else if (tab === 'Completed') {
            tabCondition = ` I.status = 4 AND COALESCE(temp.published, 0) = 1 AND COALESCE(I.pending_review, 0) = 0`;
            // tabCondition= `I.status = 4`
          }else if (tab === 'Failed') {
            tabCondition = ` I.status = 5`;
          }else if (tab === 'Assigned') {
            tabCondition = ` I.status = 6`;
          }else if (tab === 'Hold') {
            tabCondition = ` I.status = 7`;
          }else{
            tabCondition = ` I.status != 0`;
          }
      }
      const completeStatusCondition = complete_status
        ? ` I.complete_status = ${complete_status} `
        : ` I.complete_status = 1`;

      const finalCondition = [
        completeStatusCondition,
        whereCondition,
        dateCondition,
        tabCondition,
      ].filter(Boolean);

      const combinedWhere = finalCondition.length > 0 ? `WHERE ${finalCondition.join(' AND ')}` : '';
      
      let query = await sequelize.query(`SELECT c.*, I.*,ptp.name AS job_class_name,pc.name AS job_category_name, p.cover_image, p.start_date,p.name AS Project_name, r.min_payment_status,
          GROUP_CONCAT(DISTINCT CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) SEPARATOR ', ') AS implementation_manager, 
          u.profile_pic AS manager_profile_photo, dr.name AS driver_name, com.name AS community_name,
          COALESCE(MAX(CASE WHEN ra.approved = 1 THEN ra.name END), MAX(ra.name)) AS attachmentUrl, 
          COALESCE(MAX(CASE WHEN ra.approved = 1 THEN 1 END), MAX(ra.approved)) AS approved,
          GROUP_CONCAT(DISTINCT lcs.name SEPARATOR ', ') AS service_name,
        CONCAT(COALESCE(c.customer_address, ''), ', ', COALESCE(c.customer_phone, ''), ', ', COALESCE(com.name, ''), ', ', COALESCE(c.customer_name, '')) AS description
          FROM installations I
          LEFT JOIN staff_members sm ON FIND_IN_SET(sm.id, I.team_id)
          LEFT JOIN project_members pm ON pm.project_id = I.project_id
          LEFT JOIN users u ON FIND_IN_SET(u.id, I.team_id)
          LEFT JOIN projects p ON p.id = I.project_id
          LEFT JOIN project_types ptp ON ptp.id=p.project_type
          LEFT JOIN project_categories pc ON pc.id=p.project_category
          LEFT JOIN requests r ON r.id = I.request_id
          left join users ura on r.added_by=ura.id
          LEFT JOIN request_services rs ON rs.request_id = r.id
          LEFT JOIN labour_cost_services lcs ON lcs.id = rs.labour_cost_service_id
          LEFT JOIN request_attachments ra ON ra.request_id = I.request_id AND (ra.file_type LIKE 'image/%' OR ra.file_type = 'img')
          LEFT JOIN customer_orders c ON c.request_id = r.id
          LEFT JOIN community com ON com.id=p.community and FIND_IN_SET(c.customer_id, com.clients) > 0 AND com.status = 1
          LEFT JOIN driver dr ON dr.id = I.driver_id
          LEFT JOIN (
              SELECT installation_id, 
                      MAX(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS published
              FROM installation_temp_fields
              GROUP BY installation_id
              ) temp ON temp.installation_id = I.id
          ${combinedWhere}
          AND r.installation_acceptance IS NULL
          GROUP BY p.id
          `);
          // GROUP BY I.id, I.request_id

      let formattedResult = [];

      if (query[0]?.length > 0) {
          const resultMap = new Map();
          query[0].forEach(item => {
              const requestId = item.request_id;
              const attachment = { attachmentUrl: item.attachmentUrl || null, approved: item.approved || null };
              if (!resultMap.has(requestId)) {
                  resultMap.set(requestId, { ...item, attachments: [attachment] });
              } else {
                  resultMap.set(item.id, { ...item, attachments: [attachment] });
              }
              if (attachment.approved === 1) {
                  resultMap.get(requestId).attachments.push(attachment);
              }
          });
          formattedResult = Array.from(resultMap.values());
      }
      return formattedResult.map(record => parseDecimalFields(record));
  } catch (error) {
      console.error("Error fetching appointments:", error);
      return [];
  }
}



const fetchTotalCount = async (userId = 0, userType = 0, hasPermission = false, fromDate, toDate, tab, project_id, search = '', complete_status,filterClient, filterProjectType) => {
  const fromTimestamp = fromDate ? moment(fromDate).startOf('day').unix() : null;
  const toTimestamp = toDate ? moment(toDate).endOf('day').unix() : null;

  let dateCondition = '';
  if (fromTimestamp && toTimestamp) {
    dateCondition = ` i.installation_date BETWEEN ${fromTimestamp} AND ${toTimestamp}`;
  } else if (fromTimestamp) {
    dateCondition = ` i.installation_date >= ${fromTimestamp}`;
  } else if (toTimestamp) {
    dateCondition = ` i.installation_date <= ${toTimestamp}`;
  }

  let whereCondition = '';
  if (userType !== 1 && userType !== 8 && userType !== 0 && !hasPermission) {
    whereCondition = ` ( EXISTS (
        SELECT 1
        FROM project_members pm
        JOIN staff_members sm ON sm.id = pm.staff_id
        WHERE pm.project_id = i.project_id AND sm.user_id = ${userId}
    ) OR (ura.id = ${userId} AND ura.user_type <> 3) OR FIND_IN_SET(${userId}, i.team_id)) `;
  }

  let whereCondition2 = '';
  if (userType == 1 || userType == 8) {
    whereCondition2 = '';
  } else if (userType == 3) {
    whereCondition = '';
    whereCondition2 = ` pr.user_id=${userId} `;
  } else {
    whereCondition2 = ` (FIND_IN_SET(${userId}, i.team_id) OR u1.id = ${userId} OR u2.id = ${userId} OR pmm.user_id = ${userId} OR (ura.id = ${userId} AND ura.user_type <> 3)) `;
  }

  // Define userTypeCondition without duplicating the 'requests' join
  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 u3 ON p.added_by = u3.id 
                          LEFT JOIN (SELECT pm.project_id, COALESCE(u4.id, u5.id) AS user_id 
                                     FROM project_members pm 
                                     LEFT JOIN partners p ON p.id = pm.partner_id 
                                     LEFT JOIN users u4 ON p.user_id = u4.id 
                                     LEFT JOIN staff_members s ON s.id = pm.staff_id 
                                     LEFT JOIN users u5 ON s.user_id = u5.id) pmm 
                          ON pmm.project_id = p.id AND pmm.user_id = ${userId} `;
  }

  let tabCondition = '';
  switch (tab) {
    case 'Awaiting':
      tabCondition = ` i.status = 1`;
      break;
    case 'In-transit':
      // tabCondition = ` i.status = 2 OR (i.status = 4 AND COALESCE(temp.published, 0) != 1)`;
      tabCondition = `(i.status = 2 OR (i.status = 4 AND COALESCE(temp.published, 0) != 1) OR COALESCE(i.pending_review, 0) = 2)`;
      // tabCondition = `i.status = 2`
      break;
    case 'Delivered':
      tabCondition = ` i.status = 3`;
      break;
    case 'Review':
      tabCondition = ` i.status = 4 AND COALESCE(temp.published, 0) = 1 AND COALESCE(i.pending_review, 0) = 1`;
      break;
    case 'Completed':
      tabCondition = ` i.status = 4 AND COALESCE(temp.published, 0) = 1 AND COALESCE(i.pending_review, 0) = 0`;
      // tabCondition= `i.status = 4`
      break;
    case 'Failed':
      tabCondition = ` i.status = 5`;
      break;
    case 'Assigned':
      tabCondition = ` i.status = 6`;
      break;
    case 'Hold':
      tabCondition = ` i.status = 7`;
      break;
    default:
      tabCondition = ` i.status != 0`;
      break;
  }

  let projectCondition = project_id ? ` i.project_id = ${project_id}` : '';
  let searchCondition;
  // let searchCondition = search ? ` (i.name LIKE '%${search.toLowerCase()}%') ` : '';
    // let searchCondition = search ? ` (r.description LIKE '%${search.toLowerCase()}%') ` : '';
    // let searchCondition = search ? `(LOWER(COALESCE(r.description, '')) LIKE '%${search.toLowerCase().trim()}%' AND r.description IS NOT NULL AND r.description != '')` : '';
      // if (search) {
      //   const normalizedSearch = search.toLowerCase().trim();
      //   searchCondition = `LOWER(COALESCE(r.description, '')) LIKE '%${normalizedSearch}%'`;
      // }
      if (search) {
        const normalizedSearch = search
          .toLowerCase()
          .trim()
          .replace(/["\/\\-]/g, '')
          .replace(/'/g, "''");
      
        searchCondition = `(
          LOWER(COALESCE(i.name, '')) LIKE '%${normalizedSearch}%' OR
          LOWER(COALESCE(r.description, '')) LIKE '%${normalizedSearch}%' OR
          LOWER(COALESCE(c.customer_name, '')) LIKE '%${normalizedSearch}%' OR
          LOWER(COALESCE(com.name, '')) LIKE '%${normalizedSearch}%' OR
          LOWER(COALESCE(ptp.name, '')) LIKE '%${normalizedSearch}%' OR
          LOWER(COALESCE(pc.name, '')) LIKE '%${normalizedSearch}%' OR
          LOWER(COALESCE(r.doc_number, '')) LIKE '%${normalizedSearch}%'
        )`;
        console.log('🔎 Final Search Condition:', searchCondition);
      }

  let completeStatusCondition = complete_status ? ` i.complete_status = ${complete_status} ` : ` i.complete_status = 1`;
  let clientCondition = '';
  if (filterClient && filterClient.length > 0) {
      clientCondition = ` c.customer_id IN (${filterClient.join(',')})`;
  }
  
  let combinedConditions = [completeStatusCondition];
  if (whereCondition) combinedConditions.push(whereCondition);
  if (whereCondition2) combinedConditions.push(whereCondition2);
  if (dateCondition) combinedConditions.push(dateCondition);
  if (tabCondition) combinedConditions.push(tabCondition);
  if (projectCondition) combinedConditions.push(projectCondition);
  if (searchCondition) combinedConditions.push(searchCondition);
  if (clientCondition) combinedConditions.push(clientCondition);

  if (filterProjectType === 1) {
    combinedConditions.push(`r.project_type = 1`);
  } else if (filterProjectType === 2) {
    combinedConditions.push(`r.project_type = 2`);
  } else if (filterProjectType === 'NULL') {
    combinedConditions.push(`r.project_type IS NULL`);
  } else {
    // combinedConditions.push(`(r.isFromDesignOrder = 1 OR (r.isFromDesignOrder != 1 AND r.project_type IS NOT NULL))`); // Installation pagination issue fix
    combinedConditions.push(`(r.project_type IS NULL OR r.project_type IS NOT NULL)`);
  }

  // const finalWhereClause = combinedConditions.length > 0 ? `WHERE ${combinedConditions.join(' AND ')}` : '';
  const finalWhereClause = combinedConditions.length > 0
  ? `WHERE ${combinedConditions.join(' AND ')} AND r.installation_acceptance IS NULL`
  : `WHERE r.installation_acceptance IS NULL`;


  let query = await sequelize.query(`
    SELECT COUNT(DISTINCT i.id) AS totalCount 
    FROM installations i
    LEFT JOIN projects p ON p.id = i.project_id
    LEFT JOIN project_categories pc ON pc.id=p.project_category
    LEFT JOIN project_types ptp ON ptp.id=p.project_type
    ${userTypeCondition}
    JOIN requests r ON r.id = i.request_id
    left join users ura on r.added_by=ura.id
    LEFT JOIN labour_cost_services lcs ON lcs.id = i.labour_cost_service_id
    LEFT JOIN products pds ON pds.id = i.product_id
    LEFT JOIN request_attachments ra ON ra.request_id = i.request_id AND (ra.file_type LIKE 'image/%' OR ra.file_type = 'img')
    LEFT JOIN customer_orders c ON c.request_id = r.id
    LEFT JOIN request_locations rl ON rl.id = i.location_id
    LEFT JOIN community com ON com.id=p.community and FIND_IN_SET(c.customer_id, com.clients) > 0 AND com.status = 1
    LEFT JOIN driver dr ON dr.id = i.driver_id
    LEFT JOIN users u ON FIND_IN_SET(u.id, i.team_id)
    LEFT JOIN (
      SELECT installation_id, 
              MAX(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS published
      FROM installation_temp_fields
      GROUP BY installation_id
      ) temp ON temp.installation_id = i.id
    ${finalWhereClause}
  `);

  return query[0][0].totalCount;
};


const fetchApointmentById=async(id)=>{
    let query=await sequelize.query(`SELECT c.*,temp.published, I.*,pc.name AS job_category_name,r.design_order_id,ptp.name AS job_class_name,ct.name as installation_temp_name,CONCAT(u3.first_name, ' ', u3.last_name) AS added_by_name,u3.user_type AS added_by_user_type, p.cover_image,pr.name AS product_name,cop.product_name AS order_product_name,cop.product_notes AS product_notes,cop.product_price AS order_product_price,pr.price,rcs.name AS service_name,rcs.avatarUrl AS service_image,p.project_category,r.created_at,r.min_payment_status,cop.installer_facility_id,rs.installer_facility_id as service_installer_facility_id,r.status AS request_status, p.status AS proj_status, r.quotation, r.is_request_estimate, r.quotation_status,r.is_split_order,
      v.name AS vehicle_name,v.vehicle_image AS vehicle_image,v.model as vehicle_model,v.vehicle_number as vehicle_number,
      rl.address,rl.city,rl.state,rl.zip,concat(u.first_name,' ',coalesce(u.last_name,'')) as implementation_manager,r.doc_number,r.description,
      rsl.address As service_address,rsl.city As service_city,rsl.state As service_state,rsl.zip As service_zip, com.name AS community_name,
       r.isFromDesignOrder, r.design_order_id,
      rp.id AS payment_id, rp.amount AS payment_amount, rp.title AS payment_title, rp.timestamp AS payment_timestamp,dr.user_id AS driver_userId,dr.name AS driver_name,dr.profile_photo as driver_image,rt.name AS route_name `
    +`   ,group_concat(distinct dr.user_id) AS driver_userIds, group_concat(distinct dr.name) AS driver_names,group_concat(distinct dr.profile_photo) AS driver_images, u1.fcmToken,u2.fcmToken as installerFcmToken, ` 
    +`   group_concat(distinct u.id) AS team_userIds, group_concat(distinct concat(u.first_name,' ',coalesce(u.last_name,''))) AS team_names,group_concat(distinct u.profile_pic) AS team_images ` 
    +` FROM installations I`
    +` LEFT JOIN vehicles v ON v.id = I.vehicle_id`
    +` LEFT JOIN projects p ON p.id = I.project_id`
    +` LEFT JOIN requests r ON r.id = I.request_id`
    +` left join users ura on r.added_by=ura.id`
    +` LEFT JOIN customer_orders c ON c.request_id = r.id`
    +` LEFT JOIN products pr ON I.product_id = pr.id`
    +` LEFT JOIN project_types ptp ON ptp.id=p.project_type`
    +` LEFT JOIN project_categories pc ON pc.id=p.project_category`
    +` LEFT JOIN community com ON com.id=p.community and FIND_IN_SET(c.customer_id, com.clients) > 0 AND com.status = 1`
    +` LEFT JOIN request_locations rl ON I.location_id = rl.id`
    +` LEFT JOIN  labour_cost_services rcs ON I.labour_cost_service_id = rcs.id`
    +` LEFT JOIN request_service_locations rsl ON I.location_id = rsl.id`
    +` LEFT JOIN request_services rs ON rsl.service_id = rs.id`
    +` LEFT JOIN customer_order_products cop ON rl.order_id = cop.id AND I.product_index = cop.product_index`
    +` LEFT JOIN request_payments rp ON rp.request_id = r.id`
    +` LEFT JOIN staff_members sm on I.team_id=sm.user_id`
    +` LEFT JOIN users u2 on u2.id=sm.user_id`
   +` LEFT JOIN driver dr1 ON dr1.id = I.driver_id`
   +` LEFT JOIN users u1 ON u1.id = dr1.user_id`
    +` LEFT JOIN custom_field_templates ct on ct.id = I.installation_temp_id`
    //  +` LEFT JOIN users u on u.id=I.team_id`
    // +` LEFT JOIN driver dr on dr.id = I.driver_id`
    +` LEFT JOIN users u ON FIND_IN_SET(u.id, I.team_id) > 0 `
    +` LEFT JOIN  driver dr ON FIND_IN_SET(dr.id, I.driver_id) > 0`
    +` LEFT JOIN routes rt on rt.id = I.route_id`
    +` LEFT JOIN users u3 on u3.id = r.added_by `
    +`
      LEFT JOIN (
              SELECT installation_id, 
                      MAX(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS published
              FROM installation_temp_fields
              GROUP BY installation_id
              ) temp ON temp.installation_id = I.id
    `
    +` WHERE I.id = ${id}`
    +` GROUP BY I.id, c.id, p.id, pr.id, rl.id, rsl.id, rp.id, rt.id, ct.id, u1.id `
    +` ORDER BY I.id DESC`)
    let data=[]
    if (query[0].length > 0) {
        data = query[0];
    }
    return data.map(record => parseDecimalFields(record));
}

const addInstallationNote = async (data) => {
    try{
        const result = await InstallationNotes.create(data)
        if(result){
            return result;
        }else{
            return false;
        }
    }
    catch(error){
        console.log("addInstallationNote error : ",error);
    }
}

const editInstallationNote = async (data,editId) => {
    try{
        const result = await InstallationNotes.update(data,{
            where : {
                id : editId
            }
        })
        if(result){
            return result;
        }else{
            return false;
        }
    }
    catch(error){
        console.log("editInstallationNote error : ",error);
    }
}

const deleteInstallationNote = async (deleteId) => {
    try{
        const result = await InstallationNotes.destroy({
            where : {
                id : deleteId
            }
        })
        if(result){
            return result;
        }else{
            return false;
        }
    }
    catch(error){
        console.log("deleteInstallationNote error : ",error);
    }
}

const fetchNotesByInstallationId = async (installationId) => {
    try{
        const result = await sequelize.query(
            `SELECT ins.*,CONCAT(u.first_name,' ',COALESCE(u.last_name,'')) AS authorized_by`
            +` FROM installation_notes ins`
            +` LEFT JOIN users u on u.id = ins.added_by`
            +` WHERE installation_id = ${installationId}`
            +` ORDER BY ins.id DESC`)
        if(result[0]){
            return result[0];
        }else{
            return false;
        }
    }
    catch(error){
        console.log("deleteInstallationNote error : ",error);
    }
}

const fetchNotesByRequestId = async (request_id) => {
    try{
        const result = await sequelize.query(
            `SELECT ins.*,CONCAT(u.first_name,' ',COALESCE(u.last_name,'')) AS authorized_by`
            +` FROM installation_notes ins`
            +` LEFT JOIN installations i on i.id=ins.installation_id`
            +` LEFT JOIN users u on u.id = ins.added_by`
            +` WHERE i.request_id = ${request_id}`
            +` ORDER BY ins.id DESC`)
        if(result[0]){
            return result[0];
        }else{
            return false;
        }
    }
    catch(error){
        console.log("fetchNotesByRequestId error : ",error);
        return false;
    }
}

const getAllInstallationTemplate=async(getAll=false)=>{
    try {
      let whereCondition=""
      if(getAll){
       whereCondition=" or cft.status=7"
      }
        let result = await sequelize.query(`
                  SELECT 
               cft.*,
               CONCAT(u.first_name, ' ', u.last_name) AS authorized_by     
           FROM 
               custom_field_templates cft
           LEFT JOIN 
              users u ON cft.added_by = u.id       
          WHERE 
              cft.status = 6 ${whereCondition}
          ORDER BY 
              cft.id DESC;
          `);
        if (!empty(result)) {
          if (result[0].length > 0) {
            let data = result[0];
            return data;
          } else {
            return [];
          }
        } else {
          return [];
        }
              
    } catch (error) {
        console.log(error)
    }
}
const getInstTemplateDataById=async(id)=>{
    try {
        let result = await sequelize.query(`
        select cft.*, company_name as authorized_by from custom_field_templates as cft
         left join users as u on cft.added_by= u.id where cft.id = ${id}
        `)
        let partnerId=0
        let teamId=0
        if(result.length>0){
            const templateData = [];
            for(const data of result[0]){
            const rows = [];
            const columns = [];
            const fields = [];

            const distinctRows = await sequelize.query(`select distinct row_id from custom_fields cf where cf.template_id=${data.id} and cf.status=1`)
                   if (distinctRows.length > 0) {
                 for (const row of distinctRows[0]) {
                    rows.push(row.row_id);
                  }
                }
           const distinctColumns = await sequelize.query(`select distinct row_id,column_id from custom_fields cf where cf.template_id=${data.id} and cf.status=1`)
           if (distinctColumns.length > 0) {
             for (const row of distinctColumns[0]) {
             columns[row.row_id] = columns[row.row_id] || [];
             columns[row.row_id].push(row.column_id);
             }
           }

           let select = ""
           let join = ""
           
           if (!empty(partnerId)) {         
             select = " ,pcf.id as partner_custom_field_id, pcf.value as partner_custom_field_value"
             join = ` left join partner_custom_fields pcf on pcf.field_id=cf.id and pcf.partner_id=${partnerId}`
   
           }
           if (!empty(teamId)) {
             select = " ,pcf.id as partner_custom_field_id, pcf.value as partner_custom_field_value";
             join = ` left join team_custom_fields pcf on pcf.field_id=cf.id and pcf.team_id=${teamId}`
   
           }
           const result2 = await sequelize.query(` select cf.*, company_name as authorized_by ${select} from custom_fields as cf `
             + ` ${join}`
             + ` left join users as u on cf.added_by= u.id`
             + ` where cf.template_id=${data.id} and cf.status=1`
             + ` order by cf.position_order,cf.id`)
   
   
           if (result2[0].length > 0) {
             for (const row of result2[0]) {
               fields[row.column_id] = fields[row.column_id] || [];
               fields[row.column_id].push(row);
             }
           }
           const formData = {
            id: data.template_uid,
            template_name: data.name,
            settings: {},
            stages: {
              [data.stage_uid]: {
                id: data.stage_uid,
                settings: {},
                rows: rows,
              },
            },
            rows: {},
            columns: {},
            fields: {},
          };
    
          for (const key in columns) {
  
            formData.rows[key] = {
              id: key,
              config: {
                fieldset: false,
                legend: '',
                inputGroup: false,
              },
              attrs: {
                className: 'f-row',
              },
            };
  
            for (const col of columns[key]) {
  
              formData.rows[key].columns = formData.rows[key].columns || [];
              formData.rows[key].columns.push(col);
            }
          }
  
          for (const key in fields) {
            formData.columns[key] = {
              id: key,
              config: {
                width: `${100 / fields[key].length}%`,
              },
              className: [],
              fields: [],
            };
  
            for (const f of fields[key]) {
              formData.columns[key].fields.push(f.field_uid);
            }
          }
  
          for (const key in fields) {
            for (const f of fields[key]) {
  
  
              let formDataField = {
                partner_custom_field_id: f.partner_custom_field_id,
                partner_custom_field_value: f.partner_custom_field_value,
                field_id: f.id,
                id: f.field_uid,
                tag: f.html_tag,
                config: { label: f.name !== '' ? f.name : ' ' },
                attrs: {
                  required: f.is_required === 1 ? true : false,
                  description: f.help_text,
                },
              };
              formDataField.attrs.placeholder = f.placeholder ? f.placeholder : '';
              formDataField.attrs.date_format = f.date_format ? f.date_format : '';
              formDataField.attrs.pattern = f.pattern ? f.pattern : '';
  
              if (!empty(f.content)) {
                formDataField.content = f.content;
              }
              formDataField.required = f.is_required
  
              formDataField.attrs.href = f.a_href ? f.a_href : '';
  
              if (f.a_target !== undefined) {
                formDataField.attrs.target = f.a_target === 1 ? true : false;
              }
  
              if (f.tag_uid === 'header' && f.default_select) {
                formDataField.attrs.tag?.forEach((headerTag, keyTag) => {
                  if (f.default_select === headerTag.value) {
                    formDataField.attrs.tag[keyTag].selected = true;
                  }
                });
              }
  
  
              formData.fields[f.field_uid] = formDataField;
             
              let customField
              if (teamId==0) {
                console.log("Nushannus", f.tag_uid);
                customField = await getCustomFieldValues(partnerId, f.id,);
                console.log("Nushannus", customField);
              }
              else {
                console.log("Midh$$$$$$$$$", teamId);
                customField =  await getCustomFieldValues(teamId, f.id, 1);
              }
              if (f.tag_uid === 'select' || f.tag_uid === 'radio-group' || f.tag_uid === 'checkbox-group' || f.tag_uid == 'autocomplete') {
                formData.fields[f.field_uid].options = [];
  
  
                let query = await sequelize.query(` select cfo.*, company_name as authorized_by from custom_field_options as cfo `
                  + ` left join users as u on cfo.added_by=u.id`
                  + ` where cfo.field_id=${f.id} and cfo.status=1 order by cfo.position_order,cfo.id`)
                if (query[0].length > 0) {
                  for (const row of query[0]) {
                    formData.fields[f.field_uid].options.push({
                      label: row.option_name,
                      value: row.id,
                      selected: row.selected === 1 ? true : false,
                    });
  
                    if (row.selected === 1) {
                      formData.fields[f.field_uid].content = row.option_name;
                    }
  
                  }
                }
              }
              if (!empty(customField)) {
                formData.fields[f.field_uid].value = customField.map(obj => obj.value).join(', ');
              }
             
            }
          }
          templateData.push(formData);
            }
            return templateData
        }else{
            return []
        }
    } catch (error) {
        console.log(error)
    }
}
const getCustomFieldValues = async (id, field_id, internal = 0) => {
    let whereCondition = ""
    let from = ""
    if (internal == 0) {
      whereCondition = " pcf.partner_id=" + id
      from = " partner_custom_fields pcf"
    }
    else {
      whereCondition = " pcf.team_id=" + id
      from = " team_custom_fields pcf"
    }
    let query = await sequelize.query(` select value `
      + ` from ${from}`
      + ` left join custom_fields cf on pcf.field_id=cf.id`
      + ` where pcf.status=1 and cf.status=1 and pcf.field_id=${field_id} and ${whereCondition}`)
    console.log("######$$$$$$$$$$$$NUshhhhh", query[0]);
    if (query[0].length > 0) {
  
      return query[0];
    }
    else {
      return 0;
    }
  
  }

  const updateBatchData = async (table, data) => {
    for (const row of data) {
      const duplicateData = [];
      for (const [key, value] of Object.entries(row)) {
        duplicateData.push(`${key}='${value}'`);
      }
  
      const insertString = `INSERT INTO ${table} SET ${duplicateData.join(', ')}`;
      const updateString = `ON DUPLICATE KEY UPDATE ${duplicateData.join(', ')}`;
      const sql = `${insertString} ${updateString}`;
  
      try {
        const [result] = await sequelize.query(sql);      
      } catch (error) {     
        console.error(error);
      }
    }
    return true;
  }
  const getTemplateDataId=async(id,inst_id)=>{
    try {
      let result = await sequelize.query(`SELECT it.*,cf.name,cf.content FROM installation_temp_fields it `+ 
      `left join custom_fields cf on cf.id=it.field_id where it.template_id=${id} and it.installation_id = ${inst_id}
      `)
      if(!empty(result)){
        return result[0]
      }else{
        return false
      }
    } catch (error) {
      console.log(error)
    }
  }
  const updateInstTemplateField = async(data,field_id)=>{
    try {
      let result = InstallationTemplateField.update(data,{
        where:{
          field_id:field_id
        },returning:true
      })
      if(result){
        return true
      }else{
        return false
      }
    } catch (error) {
      console.log(error)
    }
  }
  
  const deleteInstallationByProjectId=async(projId)=>{
    try{
      const result = await Installation.destroy({
          where : {
            project_id : projId
          }
      })
      if(result){
          return true;
      }else{
          return false;
      }
    }
    catch(error){
        console.log("deleteInstallationNote error : ",error);
        return false;
    }
  }

  const deleteInstallationUsingProjectId = async (projId) => {
  try {
    const result = await sequelize.query(
      `DELETE FROM installations WHERE project_id = :projId`,
      {
        replacements: { projId },
        type: sequelize.QueryTypes.DELETE
      }
    );
    return true;
  } catch (error) {
    console.error("deleteInstallationNote SQL error:", error);
    return false;
  }
};

  const getInstallationsByProjectId = async (projId) => {
    try {
      const installations = await Installation.findAll({
        where: {
          project_id: projId
        }
      });
  
      return installations;
    } catch (error) {
      console.log("getInstallationsByProjectId error: ", error);
      return [];
    }
  };


  const updateCardOrderProject = async (cards) => {
    if (!cards || !cards.length) {
      return false;
    }
    try {
      const updatePromises = cards.map((card, index) => {
        return Installation.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 updateInstallationDataById= async(id,data)=>{
    try {
      let result = Installation.update(data,{
        where:{
          id:id
        },returning:true
      })
      if(result){
        return true
      }else{
        return false
      }
    } catch (error) {
      console.log(error)
    }
  }

  const findInstallationById = async (id) => {
    try {
        const results = await sequelize.query(
            `SELECT 
                i.*, 
                c.customer_id,r.for_parent_facility,r.origin_facility_request_id,r.origin_facility_id
            FROM installations i
            LEFT JOIN customer_orders c ON c.request_id = i.request_id
            LEFT JOIN requests r ON r.id = i.request_id
            WHERE i.id = :id`, 
            {
                replacements: { id: id }, 
                type: sequelize.QueryTypes.SELECT 
            }
        );

        if (results && results.length > 0) {
            return results[0]; 
        } else {
            console.log(`Installation with id ${id} not found`);
            return null;
        }
    } catch (error) {
        console.error("Error finding installation:", error);
        return null;
    }
};

const findInstallationsByRequestId = async (requestId) => {
  try {
    const results = await sequelize.query(
      `SELECT 
          i.*, 
          c.customer_id
      FROM installations i
      LEFT JOIN customer_orders c ON c.request_id = i.request_id
      WHERE i.request_id = :requestId`, 
      {
        replacements: { requestId: requestId }, 
        type: sequelize.QueryTypes.SELECT 
      }
    );

    if (results && results.length > 0) {
      return results;
    } else {
      console.log(`No installations found for request_id ${requestId}`);
      return null;
    }
  } catch (error) {
    console.error("Error finding installations:", error);
    return null;
  }
};


  const updateInstallationDataByRequestId = async(id,data)=>{
    try {
      let result = Installation.update(data,{
        where:{
          request_id:id
        },returning:true
      })
      if(result){
        return true
      }else{
        return false
      }
    } catch (error) {
      console.log(error)
      return false
    }
  }
  

  const findInstallationDataById = async (id) => {
    try {
     
      const results = await sequelize.query(
        `SELECT * FROM installations WHERE id = :id`, 
        {
          replacements: { id: id }, 
          type: sequelize.QueryTypes.SELECT 
        }
      );
  
     
      if (results && results.length > 0) {
        return results[0]; 
      } else {
        console.log(`Installation with id ${id} not found`);
        return null;
      }
    } catch (error) {
      console.error("Error finding installation:", error);
      return null;
    }
  };


  // const fetchDetailsForNotification = async (id,driverId) => {
  //   try {
  //     const result = await sequelize.query(
  //       `SELECT i.*, 
  //               d.*, 
  //               u.fcmToken,GROUP_CONCAT(DISTINCT instUsr.fcmToken) AS installerFcmToken,   
  //               i.name AS installation_name,
  //               d.name AS driver_name
  //        FROM installations i
  //        LEFT JOIN driver d ON d.id = :driverId
  //        LEFT JOIN users u ON u.id = d.user_id
  //        LEFT JOIN users instUsr ON FIND_IN_SET(instUsr.id, i.team_id)
  //        WHERE i.id = :id GROUP BY i.id`,
  //       {
  //         replacements: { id,driverId },
  //         type: sequelize.QueryTypes.SELECT,
  //       }
  //     );
  
  //     return result;
  //   } catch (error) {
  //     console.error('Error fetching details:', error);
  //     throw error;
  //   }
  // };
 
   const fetchDetailsForNotification = async (id,driverId) => {
    try {
      const result = await sequelize.query(
        `SELECT i.*, 
                d.*, 
                u.fcmToken,u2.fcmToken AS installerFcmToken, 
                i.name AS installation_name,
                d.name AS driver_name
         FROM installations i
         LEFT JOIN driver d ON d.id = :driverId
         LEFT JOIN users u ON u.id = d.user_id
         LEFT JOIN users u2 ON u2.id = i.team_id
         WHERE i.id = :id`,
        {
          replacements: { id,driverId },
          type: sequelize.QueryTypes.SELECT,
        }
      );
  
      return result;
    } catch (error) {
      console.error('Error fetching details:', error);
      throw error;
    }
  };

  const findInstallationDataByRequestId = async (id) => {
    try {
     
      const results = await sequelize.query(
        // `SELECT * FROM installations WHERE request_id = :id`, 
        `SELECT installations.*, 
  d.name AS driver_name,u.fcmToken
  FROM installations 
  LEFT JOIN driver d ON installations.driver_id = d.id
  LEFT JOIN users u ON u.id = d.user_id
  WHERE installations.request_id = :id;`,
        {
          replacements: { id: id }, 
          type: sequelize.QueryTypes.SELECT 
        }
      );
  
     
      if (results && results.length > 0) {
        return results; 
      } else {
        console.log(`Installation with id ${id} not found`);
        return null;
      }
    } catch (error) {
      console.error("Error finding installation:", error);
      return null;
    }
  };


const fetchInstallationDataById = async (id) => {
  try {
    const query = await sequelize.query(`
      SELECT 
        i.*,
        sm.user_id AS team_user_id,
        sm.first_name AS team_firstname,
        sm.last_name AS team_lastname,
        sm.email AS team_email,
        sm.phone AS team_phone,sm.hourly_rate,stt.id as timelog_id
      FROM installations i
      LEFT JOIN staff_members sm ON sm.user_id = i.team_id
      left join staff_task_timelogs stt on stt.user_id = sm.user_id AND stt.installation_id = i.id AND stt.end_time IS NULL
      WHERE i.id = :id
    `, {
      replacements: { id },
      type: sequelize.QueryTypes.SELECT
    });

    if (query.length === 0) return [];

    const installation = parseDecimalFields(query[0]);

    // Fetch team data separately
    const teamData = {
      user_id: installation.team_user_id,
      firstname: installation.team_firstname,
      lastname: installation.team_lastname,
      email: installation.team_email,
      phone: installation.team_phone,
      hourly_rate: installation.hourly_rate,
      timelog_id: installation.timelog_id,
    };

    // Clean up team fields from main object
    delete installation.team_user_id;
    delete installation.team_first_name;
    delete installation.team_last_name;
    delete installation.team_email;
    delete installation.team_phone;

    // Fetch multiple drivers if driver_id is comma-separated
    const driverIds = (installation.driver_id || '')
      .split(',')
      .map(id => id.trim())
      .filter(id => id !== '');

    let driverData = [];

    if (driverIds.length > 0) {
      driverData = await sequelize.query(`
        SELECT 
          d.*,
          stt.id AS timelog_id
        FROM driver d
        LEFT JOIN staff_task_timelogs stt 
          ON stt.user_id = d.user_id AND stt.installation_id = :installationId AND stt.end_time IS NULL
        WHERE d.id IN (:driverIds) GROUP BY d.id
      
      `, {
        replacements: { driverIds,  installationId: installation.id },
        type: sequelize.QueryTypes.SELECT
      });
    }

    return {
      ...installation,
      teamData: [teamData],
      driverData
    };
  } catch (error) {
    console.error("Error fetching installation data:", error);
    return error;
  }
};

  

  const getInstallationCostDetailsForQbProductioncat = async (installId) => {
    try {
      const [result] = await sequelize.query(`
          SELECT 
            i.name AS job_name, 
            i.id, 
            i.status, 
            p.name AS product_name, 
            lcs.name AS service_name,
            r.doc_number,r.id AS requestId,r.syncWithQuickbooks,
            pt.quickBookId AS projectTypeQuickBookId,
            -- Product Total
            CASE 
              WHEN i.product_id IS NOT NULL THEN (
                SELECT COALESCE(SUM(jc.total), 0)
                FROM job_costs jc
                INNER JOIN customer_order_products cop ON cop.id = jc.customer_order_product_id
                INNER JOIN request_locations rl ON rl.order_id = cop.id
                WHERE rl.id = i.location_id AND jc.taken = 1
              )
              ELSE 0
            END AS productTotal,

            -- Service Total
            CASE 
              WHEN i.product_id IS NOT NULL THEN (
                SELECT COALESCE(SUM(rs.jobcost_total_cost), 0)
                FROM request_services rs
                WHERE rs.product_id = i.product_id 
                  AND rs.request_id = i.request_id
                  AND rs.recipe_id IS NOT NULL
                  AND rs.taken = 1
              )
              WHEN i.product_id IS NULL AND i.labour_cost_service_id IS NOT NULL THEN (
                SELECT COALESCE(SUM(rs.jobcost_total_cost), 0)
                FROM request_services rs
                INNER JOIN request_service_locations rsl ON rsl.service_id = rs.id
                WHERE rsl.id = i.location_id AND rs.taken = 1
              )
              ELSE 0
            END AS serviceTotal,

                        -- Additional Material Total
            CASE
              WHEN i.product_id IS NOT NULL THEN (
                SELECT COALESCE(SUM(jc.total), 0)
                FROM job_costs jc
                WHERE jc.for_product = i.location_id AND jc.taken = 1
              )
              WHEN i.labour_cost_service_id IS NOT NULL THEN (
                SELECT COALESCE(SUM(jc.total), 0)
                FROM job_costs jc
                WHERE jc.for_service = i.labour_cost_service_id AND jc.taken = 1
              )
              ELSE 0
            END AS additionalMaterialTotal

          FROM installations i
          LEFT JOIN products p ON p.id = i.product_id
          LEFT JOIN labour_cost_services lcs ON lcs.id = i.labour_cost_service_id
          LEFT JOIN requests r ON r.id = i.request_id
          LEFT JOIN project_types pt ON pt.id = r.project_type
          WHERE i.id = :installId `, 
        {
          replacements: { installId },
          type: sequelize.QueryTypes.SELECT
        }
      );
  
      return result;
    } catch (error) {
      console.error("Error fetching job cost total:", error);
      throw error;
    }
  };


  const getInstallationCostDetailsForQb = async (installId) => {
    try {
      let oldFunctionResponse = await getInstallationCostDetailsForQbProductioncat(installId);
      if(oldFunctionResponse?.productTotal  > 0){
        return oldFunctionResponse;
      }
      const [result] = await sequelize.query(`
        SELECT 
          i.name AS job_name, 
          i.id, 
          i.status, 
          p.name AS product_name, 
          lcs.name AS service_name,
          r.doc_number,r.id AS requestId,r.syncWithQuickbooks,
          pt.quickBookId AS projectTypeQuickBookId,
          -- PRODUCT TOTAL
          COALESCE((
            SELECT SUM(DISTINCT jc.total)
            FROM material_inventory mi
            JOIN job_costs jc 
              ON jc.product_id = mi.id
            WHERE mi.material_id = i.product_id
              AND jc.taken = 1
          ), 0) AS productTotal,
  
          -- SERVICE TOTAL
          CASE 
            WHEN i.product_id IS NOT NULL THEN (
              SELECT COALESCE(SUM(rs.jobcost_total_cost), 0)
              FROM request_services rs
              WHERE rs.product_id = i.product_id 
                AND rs.request_id = i.request_id
                AND rs.recipe_id IS NOT NULL
                AND rs.taken = 1
            )
            WHEN i.product_id IS NULL 
                 AND i.labour_cost_service_id IS NOT NULL THEN (
              SELECT COALESCE(SUM(rs.jobcost_total_cost), 0)
              FROM request_services rs
              INNER JOIN request_service_locations rsl 
                ON rsl.service_id = rs.id
              WHERE rsl.id = i.location_id 
                AND rs.taken = 1
            )
            ELSE 0
          END AS serviceTotal,
  
          -- Additional Material Total
          CASE
            WHEN i.product_id IS NOT NULL THEN (
              SELECT COALESCE(SUM(jc.total), 0)
              FROM job_costs jc
              WHERE jc.for_product = i.location_id AND jc.taken = 1
            )
            WHEN i.labour_cost_service_id IS NOT NULL THEN (
              SELECT COALESCE(SUM(jc.total), 0)
              FROM job_costs jc
              WHERE jc.for_service = i.labour_cost_service_id AND jc.taken = 1
            )
            ELSE 0
          END AS additionalMaterialTotal
  
        FROM installations i
        LEFT JOIN products p 
          ON p.id = i.product_id
        LEFT JOIN labour_cost_services lcs 
          ON lcs.id = i.labour_cost_service_id
        LEFT JOIN requests r 
          ON r.id = i.request_id
        LEFT JOIN project_types pt 
          ON pt.id = r.project_type
        WHERE i.id = :installId
      `, {
        replacements: { installId },
        type: sequelize.QueryTypes.SELECT
      });
  
  
      return result;
  
    } catch (error) {
      console.error("Error fetching installation cost details:", error);
      throw error;
    }
  };
  

  
  
  // (async function(){
  //   const result = await getInstallationCostDetailsForQb(2337);
  //   console.log("result",result)
  // })()

//   const updateRequestsToInProduction = async (requestId) => {
//   try {
//     const result = await sequelize.query(
//       `UPDATE installations 
//        SET status = 0 
//        WHERE request_id = :requestId`,
//       {
//         replacements: { requestId },
//         type: sequelize.QueryTypes.UPDATE,
//       }
//     );
//     return result;
//   } catch (error) {
//     console.error("Error updating installations status to 0:", error);
//     throw new Error("Failed to update installations to in-production.");
//   }
// };

const updateRequestsToInProduction = async (requestId) => {
  const t = await sequelize.transaction();
  try {
    // Update installations.status = 0
    // let querry = allCreatedTask.length < 0 ? `` : ``
    await sequelize.query(
      `DELETE i
 FROM installations AS i
 LEFT JOIN projects AS p ON p.request_id = i.request_id
 WHERE i.request_id = :requestId AND (p.status IS NULL OR p.status != 1);
`,
//       `DELETE i
// FROM installations AS i
// LEFT JOIN projects AS p ON p.request_id = i.request_id
// WHERE i.request_id = :requestId AND (p.status IS NULL OR p.status != 1);
// `,
      {
        replacements: { requestId },
        transaction: t,
        type: sequelize.QueryTypes.UPDATE,
      }
    );

    // Update projects.status = 1
    await sequelize.query(
      `UPDATE projects
       SET status = 1
       WHERE request_id = :requestId AND status != 1`,
      {
        replacements: { requestId },
        transaction: t,
        type: sequelize.QueryTypes.UPDATE,
      }
    );

    await t.commit();
    return true;
  } catch (error) {
    await t.rollback();
    console.error("Error updating to in-production:", error);
    throw new Error("Failed to update installations and project status.");
  }
}; 

const getProjectFilesByProjectId = async (projectId) => {
  try {
    const result = await sequelize.query(
      `SELECT 
        pf.*
      FROM project_files pf
      WHERE pf.project_id = :projectId 
      ORDER BY pf.id DESC`,
      {
        replacements: { projectId },
        type: sequelize.QueryTypes.SELECT
      }
    );

    return result;
  } catch (error) {
    console.error("Error fetching project files:", error);
    throw new Error("Failed to fetch project files.");
  }
}; 

const getInstallationByTeamId = async (team_id,forAddTimesheet) => {
  try {
    let whereCondition = '';
    if(forAddTimesheet){
      whereCondition += ` AND i.status != 4`;
    }
    const result = await sequelize.query(
      `SELECT i.name, i.id, i.project_id, i.request_id
        FROM installations i
        WHERE 
          i.status != 0 
          AND FIND_IN_SET(:team_id, i.team_id) > 0
          ${whereCondition}
        GROUP BY i.request_id`,
      {
        replacements: { team_id },
        type: sequelize.QueryTypes.SELECT
      }
    );

    return result?.length > 0 ? result : false;
  } catch (error) {
    console.error("Error fetching installation by driver id:", error);
    throw new Error("Failed to fetch installation by driver id.");
  }
}

const getInstallationTaskByRequestId = async (project_id) => {
  try {
    const result = await sequelize.query(
      `SELECT i.name, i.id, r.project_category,
        CASE
          WHEN r.project_category = 12 THEN i.name
          WHEN i.product_id IS NOT NULL THEN p.name
          WHEN i.labour_cost_service_id IS NOT NULL THEN lcs.name
          ELSE i.name
        END AS installation_name
        FROM installations i
        LEFT JOIN products p ON i.product_id = p.id AND i.product_id IS NOT NULL
        LEFT JOIN requests r ON i.request_id = r.id
        LEFT JOIN labour_cost_services lcs ON i.labour_cost_service_id = lcs.id AND i.labour_cost_service_id IS NOT NULL
        WHERE i.project_id = :project_id GROUP BY i.id`,
      {
        replacements: { project_id },
        type: sequelize.QueryTypes.SELECT
      }
    );
    return result?.length > 0 ? result : false;
  } catch (error) {
    console.error("Error fetching installation task by request id:", error);
    throw new Error("Failed to fetch installation task by request id.");
  }
}
// 
// uncomment this if the above one fails

module.exports={getAllProjects,getAllServiceProjects,getAllServicesForCalendar,getAllInstallServicesForCalendar,getProducts,getProductsBuildOrder,getProductsNoChargeOrder,getServices,getLocations,getServiceLocations,getTotalPaidAmountByRequestId,addInstallationAppointment,fetchAllApointments,fetchApointmentById,
    updateInstallationDate,getAllCustomerOrders,addInstallationNote, editInstallationNote, deleteInstallationNote, fetchNotesByInstallationId,
    getAllInstallationTemplate,getInstallationCostDetailsForQb,
    getInstTemplateDataById,
    updateBatchData,
    getTemplateDataId,
    updateInstTemplateField,
    updateInstallationStatus,
    updateInstallationHoldStatus,
    updateDueDate,
    fetchTotalCount,
    deleteInstallationByProjectId,
    updateInstallationAddress,
    updateCardOrderProject,
    updateInstallationDataById,
    updateInstallationDataByRequestId,
    fetchAllApointmentsForFilter,
    fetchNotesByRequestId,
    findInstallationDataById,
    fetchDetailsForNotification,
    findInstallationDataByRequestId,
    addInstallationTabCount,
    updateInstallationTabCount,
    getInstallationTabCountByUserId,
    getInstallationTabCountByUserData,
    installationTabIncrementField,
    getInstallationTabCountByUser,
    addPickupTabCount,
    updatePickupTabCount,
    getPickupTabCountByUserId,
    getPickupTabCountByUserData,
    pickupTabIncrementField,
    addShippingTabCount,
    updateShippingTabCount,
    getShippingTabCountByUserId,
    getShippingTabCountByUserData,
    shippingTabIncrementField,
    getShippingTabCountByUser,
    getInstallCountByUser,
    findInstallationById,
    getPickupTabCountByUser,
    getInstallationsByProjectId,
    findInstallationsByRequestId,
    fetchInstallationDataById,
    deleteInstallationUsingProjectId,
    updateRequestsToInProduction,
    getProjectFilesByProjectId,
    getInstallationByTeamId,
    getInstallationTaskByRequestId,
    fetchAllApointmentsOptimizedForCalendar
}