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

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 Route = sequelize.define('routes',{
    id:{ type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name:{type:Sequelize.TEXT,allowNull:false},
    description:{type:Sequelize.TEXT,allowNull:true},
    zipcodes :{type:Sequelize.TEXT,allowNull:true,defaultValue:null},
    assigned_driver:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
    added_by: {type:Sequelize.TINYINT(200),allowNull:false},
    timestamp:{type:Sequelize.INTEGER,allowNull:false},
    status:{type:Sequelize.TINYINT,allowNull:false},
    unique_id:{type:Sequelize.TEXT,allowNull:false},
    type:{type:Sequelize.INTEGER,allowNull:false,defaultValue:1},
    client_id:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
    community_id:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
    assigned_sales_order:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
    assigned:{type:Sequelize.INTEGER,allowNull:false,defaultValue:0},
},{tableName:'routes',timestamps:false})


const DriverSelected_Route = sequelize.define('driver_selected_routes',{
    id:{ type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    route_id:{type:Sequelize.INTEGER,allowNull:true},
    installation_id:{type:Sequelize.INTEGER,allowNull:true},
    driver_id:{type:Sequelize.STRING,allowNull:true},
    installler_id:{type:Sequelize.INTEGER,allowNull:true},
    status:{type:Sequelize.TINYINT,allowNull:true},
},{tableName:'driver_selected_routes',timestamps:false})

const RouteLocations = sequelize.define('route_locations',{
  id:{ type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  route_id:{ type: Sequelize.INTEGER, allowNull:false },
  route_product_id:{ type: Sequelize.INTEGER, allowNull:false, defaultValue:0 },
  city:{ type:Sequelize.STRING(300), allowNull:true, defaultValue:null },
  address:{ type:Sequelize.STRING(300), allowNull:true, defaultValue:null },
  state:{ type:Sequelize.STRING(300), allowNull:true, defaultValue:null },
  wholeAddress:{ type:Sequelize.TEXT, allowNull:true, defaultValue:null },
  zip:{ type:Sequelize.STRING(50), allowNull:true, defaultValue:null },
  latitude:{ type:Sequelize.STRING(300), allowNull:true, defaultValue:null },
  longitude:{ type:Sequelize.STRING(300), allowNull:true, defaultValue:null },
  starting_point:{ type:Sequelize.INTEGER, allowNull:true, defaultValue:null },
  ending_point:{ type:Sequelize.INTEGER, allowNull:true, defaultValue:null },
  color:{ type:Sequelize.TEXT, allowNull:true, defaultValue:null },
  index_order:{ type:Sequelize.INTEGER, allowNull:true, defaultValue:null },
  timestamp:{ type:Sequelize.INTEGER, allowNull:false },
  added_by:{ type:Sequelize.INTEGER, allowNull:false },
  status:{ type:Sequelize.TINYINT, allowNull:false, defaultValue:1 },
},{ tableName:'route_locations', timestamps:false })

const RouteProducts = sequelize.define('route_products',{
  id:{ type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  product_id:{ type: Sequelize.INTEGER, allowNull:false },
  route_id:{ type: Sequelize.INTEGER, allowNull:false },
  qty:{ type:Sequelize.INTEGER, allowNull:false, defaultValue:0 },
  color:{ type:Sequelize.TEXT, allowNull:true, defaultValue:null },
  status:{ type:Sequelize.TINYINT, allowNull:false, defaultValue:1 },
},{ tableName:'route_products', timestamps:false })

const createRoute = async(data)=>{
    try {
        let result = await Route.create(data)
        if(result){
            return result.dataValues.id
        }else{
            return false
        }
    } catch (error) {
        console.log(error)
    }
}

// const getAllRoutes=async(rowPerpage,page,searchData,whereCondition='')=>{
//   const offset = (page - 1) * rowPerpage;
//   let content = ''
//   if(searchData){
//       content=` and r.name LIKE '%${searchData}%'`
//   }
//   let content2 = ''
//   if(rowPerpage){
//       content2 = ` LIMIT ${rowPerpage}`
//   }
//   let content3 = ''
//   if(offset){
//       content3 = ` OFFSET ${offset}`
//   }
//   // SELECT r.*, >>>> SELECT DISTINCT r.id, r.*,
//   let result = await sequelize.query(`
//     SELECT DISTINCT r.id, r.*,COALESCE(rp.product_count, 0) AS product_count, COALESCE(rl.location_count, 0) AS location_count,
//     req.name as installation_name,co.customer_name,c.name AS project_name,i.installation_temp_id,i.driver_is_installer,i.driver_id,i.team_id
//     FROM routes r
//     LEFT JOIN customer_orders co on r.assigned_sales_order = co.request_id
//     LEFT JOIN requests req on r.assigned_sales_order = req.id
//     LEFT JOIN community c on req.community = c.id
//     LEFT JOIN (SELECT route_id, COUNT(*) AS product_count FROM route_products GROUP BY route_id) rp ON rp.route_id = r.id
//     LEFT JOIN (SELECT route_id, COUNT(*) AS location_count FROM route_locations GROUP BY route_id ) rl ON rl.route_id = r.id
//     LEFT JOIN installations i ON i.request_id = r.assigned_sales_order
//     where r.status = 1 ${whereCondition} ${content}
//     ORDER BY r.id DESC
//     ${content2} ${content3}
//   `);
// const countResult = await sequelize.query(`SELECT COUNT(r.id) as count FROM routes r WHERE r.status = 1 ${whereCondition}`);
// const totalCount = countResult[0][0].count;
// const countResult1 = await sequelize.query(`SELECT COUNT(id) as count FROM routes WHERE status = 1 AND type = 1`);
// const psCount = countResult1[0][0].count;
// const countResult2 = await sequelize.query(`SELECT COUNT(id) as count FROM routes WHERE status = 1 AND type = 2`);
// const tsCount = countResult2[0][0].count;
//   if(!empty(result)){
//       return {
//           data: result[0],
//           totalCount: totalCount,
//           psCount:psCount,
//           tsCount:tsCount
//       };
//   }else{
//       return []
//   }
// }

// const getAllRoutes=async(rowPerpage,page,searchData,whereCondition='')=>{
//   const offset = (page - 1) * rowPerpage;
//   let content = ''
//   if(searchData){
//       content=` and r.name LIKE '%${searchData}%'`
//   }
//   let content2 = ''
//   if(rowPerpage){
//       content2 = ` LIMIT ${rowPerpage}`
//   }
//   let content3 = ''
//   if(offset){
//       content3 = ` OFFSET ${offset}`
//   }
//   // SELECT r.*, >>>> SELECT DISTINCT r.id, r.*,
//   let result = await sequelize.query(`
//     SELECT DISTINCT r.id, r.*,COALESCE(rp.product_count, 0) AS product_count, COALESCE(rl.location_count, 0) AS location_count,
//     COALESCE(cop.sales_product_count, 0) AS sales_product_count,COALESCE(copr.sales_rfp_product_count, 0) AS sales_rfp_product_count,
//     COALESCE(rss.sales_addl_service_count, 0) AS sales_addl_service_count,COALESCE(rs.sales_rfp_service_count, 0) AS sales_rfp_service_count,
//     req.name as installation_name,co.customer_name,c.name AS project_name,i.installation_temp_id,i.driver_is_installer,i.driver_id,i.team_id,req.doc_number AS doc_number

//     FROM routes r
//     LEFT JOIN customer_orders co on r.assigned_sales_order = co.request_id
//     LEFT JOIN requests req on r.assigned_sales_order = req.id
//     LEFT JOIN community c on req.community = c.id
//     LEFT JOIN (SELECT route_id, COUNT(*) AS product_count FROM route_products GROUP BY route_id) rp ON rp.route_id = r.id
//     LEFT JOIN (SELECT route_id, COUNT(*) AS location_count FROM route_locations GROUP BY route_id ) rl ON rl.route_id = r.id

//     LEFT JOIN (SELECT order_id, COUNT(*) AS sales_product_count FROM customer_order_products WHERE rfp=0 AND material = 0 GROUP BY order_id) cop ON cop.order_id = co.id
//     LEFT JOIN (SELECT order_id, COUNT(*) AS sales_rfp_product_count FROM customer_order_products WHERE rfp=1 AND material = 0 GROUP BY order_id) copr ON copr.order_id = co.id
//     LEFT JOIN (SELECT request_id, COUNT(*) AS sales_addl_service_count FROM request_services WHERE rfp=0 AND recipe_id IS NULL GROUP BY request_id) rss ON rss.request_id = r.assigned_sales_order
//     LEFT JOIN (SELECT request_id, COUNT(*) AS sales_rfp_service_count FROM request_services WHERE rfp=1 AND recipe_id IS NULL GROUP BY request_id) rs ON rs.request_id = r.assigned_sales_order


//     LEFT JOIN installations i ON i.request_id = r.assigned_sales_order
//     where r.status = 1 ${whereCondition} ${content}
//     ORDER BY r.id DESC
//     ${content2} ${content3}
//   `);
// const countResult = await sequelize.query(`SELECT COUNT(r.id) as count FROM routes r WHERE r.status = 1 ${whereCondition}`);
// const totalCount = countResult[0][0].count;
// // const countResult1 = await sequelize.query(`SELECT COUNT(id) as count FROM routes WHERE status = 1 AND type = 1`);
// // const psCount = countResult1[0][0].count;
// // const countResult2 = await sequelize.query(`SELECT COUNT(id) as count FROM routes WHERE status = 1 AND type = 2`);
// // const tsCount = countResult2[0][0].count;
//   if(!empty(result)){
//       return {
//           data: result[0],
//           totalCount: totalCount,
//           psCount:0,
//           tsCount:0
//       };
//   }else{
//       return []
//   }
// }

const getAllRoutes = async (rowPerpage, page, searchData, whereCondition = '', filterProjectType) => {
  const offset = (page - 1) * rowPerpage;
  let content = '';
  
  if (filterProjectType === 1) {
    whereCondition += ` AND req.project_type = 1`;
  } else if (filterProjectType === 2) {
    whereCondition += ` AND req.project_type = 2`;
  } else if (filterProjectType === 'NULL') {
    whereCondition += ` AND req.project_type IS NULL`;
  }
  //whereCondition += ` AND (req.isFromDesignOrder = 1 OR (req.isFromDesignOrder != 1 AND req.project_type IS NOT NULL))`;

  if (searchData) {
    // content = ` AND r.name LIKE '%${searchData}%'`;
    content = ` AND (r.name LIKE '%${searchData}%' OR r.description LIKE '%${searchData}%' OR r.unique_id LIKE '%${searchData}%')`;
  }
  let content2 = '';
  if (rowPerpage) {
    content2 = ` LIMIT ${rowPerpage}`;
  }
  let content3 = '';
  if (offset) {
    content3 = ` OFFSET ${offset}`;
  }
  let orderByClause = 'ORDER BY r.id DESC'; // Default for PS
  if (whereCondition.includes('r.type = 2')) {
    orderByClause = 'ORDER BY r.id DESC'; // For TS
  }

  let result = await sequelize.query(`
    SELECT r.id, r.*, 
      COALESCE(rp.product_count, 0) AS product_count, 
      COALESCE(rl.location_count, 0) AS location_count,
      COALESCE(cop.sales_product_count, 0) AS sales_product_count,
      COALESCE(copr.sales_rfp_product_count, 0) AS sales_rfp_product_count,
      COALESCE(rss.sales_addl_service_count, 0) AS sales_addl_service_count,
      COALESCE(rs.sales_rfp_service_count, 0) AS sales_rfp_service_count,
      COALESCE(slc.sales_location_count, 0) AS sales_location_count,
      req.name AS installation_name,
      co.customer_name,
      c.name AS project_name,
      i.installation_temp_id,
      i.driver_is_installer,
      i.driver_id,
      i.team_id,
      req.doc_number AS doc_number,
      pt.name AS wed_client_name,
      c2.name AS wed_project_name,
      req.project_type
    FROM routes r
    LEFT JOIN customer_orders co ON r.assigned_sales_order = co.request_id
    LEFT JOIN requests req ON r.assigned_sales_order = req.id
    LEFT JOIN community c ON req.community = c.id
    LEFT JOIN community c2 ON r.community_id = c2.id
    LEFT JOIN partners pt ON r.client_id = pt.id
    LEFT JOIN (SELECT route_id, COUNT(*) AS product_count FROM route_products GROUP BY route_id) rp ON rp.route_id = r.id
    LEFT JOIN (SELECT route_id, COUNT(*) AS location_count FROM route_locations GROUP BY route_id) rl ON rl.route_id = r.id
     LEFT JOIN (SELECT order_id, COUNT(*) AS sales_product_count FROM customer_order_products WHERE rfp = 0 AND material = 0 GROUP BY order_id) cop ON cop.order_id = co.id
LEFT JOIN (
  SELECT cop_inner.order_id, COUNT(*) AS sales_location_count
  FROM customer_order_products cop_inner
  JOIN request_locations rl ON rl.order_id = cop_inner.id
  WHERE cop_inner.rfp = 0 AND cop_inner.material = 0
  GROUP BY cop_inner.order_id
) slc ON slc.order_id = co.id
    LEFT JOIN (SELECT order_id, COUNT(*) AS sales_rfp_product_count FROM customer_order_products WHERE rfp = 1 AND material = 0 GROUP BY order_id) copr ON copr.order_id = co.id
    LEFT JOIN (SELECT request_id, COUNT(*) AS sales_addl_service_count FROM request_services WHERE rfp = 0 AND recipe_id IS NULL GROUP BY request_id) rss ON rss.request_id = r.assigned_sales_order
    LEFT JOIN (SELECT request_id, COUNT(*) AS sales_rfp_service_count FROM request_services WHERE rfp = 1 AND recipe_id IS NULL GROUP BY request_id) rs ON rs.request_id = r.assigned_sales_order
    LEFT JOIN installations i ON i.request_id = r.assigned_sales_order
    WHERE r.status = 1 ${whereCondition} ${content}
    GROUP BY r.id
    ${orderByClause}
    ${content2} ${content3}
  `);

  // Count Query
  const countResult = await sequelize.query(`
    SELECT COUNT(DISTINCT r.id) AS count 
    FROM routes r 
    LEFT JOIN requests req ON r.assigned_sales_order = req.id
    WHERE r.status = 1 ${whereCondition}
  `);

  const totalCount = countResult[0][0].count;

  if (!empty(result)) {
    return {
      data: result[0],
      totalCount: totalCount,
      psCount: 0,
      tsCount: 0,
    };
  } else {
    return [];
  }
};

const getAllUnAssignedRoutes=async()=>{
   
    let result = await sequelize.query(`SELECT r.*
    FROM routes r where r.status = 1 and r.assigned_driver is null
    ORDER BY r.id DESC `);
const countResult = await sequelize.query('SELECT COUNT(id) as count FROM routes');
const totalCount = countResult[0][0].count;
    if(!empty(result)){
        return {
            data: result[0],
            totalCount: totalCount
        };
    }else{
        return []
    }
}

const getRouteDatabyId=async(id)=>{
    try {
        let result = await sequelize.query(`select r.* from routes r where r.id=${id}`)
        if(!empty(result)){
            return result[0]
        }else{
            return []
        }
    } catch (error) {
        console.log(error)
    }
}
const getRouteDatabyRequestId = async (req_id) => {
  console.log("Request ID:", req_id);

  try {
      let result = await sequelize.query(`SELECT r.* FROM routes r WHERE r.assigned_sales_order = ${req_id}`, {
          type: sequelize.QueryTypes.SELECT,
      });

      if (result && result.length > 0) {
          return result[0].id;
      } else {
          return false;
      }
  } catch (error) {
      console.log("Error fetching route:", error);
      return false;
  }
};

const getRouteDatabySalesOrderId = async (salesOrderId) => {
  console.log("salesOrderId: ", salesOrderId);

  try {
      let result = await sequelize.query(`SELECT r.* FROM routes r WHERE r.assigned_sales_order = ${salesOrderId}`, {
          type: sequelize.QueryTypes.SELECT,
      });

      if (result && result.length > 0) {
        return result[0];
      } else {
        return false;
      }
  } catch (error) {
      console.log("Error getRouteDatabySalesOrderId:", error);
      return false;
  }
};


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

const getInstallationByZipcode = async(zipcode) => {
    try{
        const result = await sequelize.query(`SELECT i.*,lcs.name AS service_name,pds.name AS product_name,lcs.avatarUrl AS service_image,rl.order_id AS customer_order_product_id,CONCAT(i.address,',',i.city,',',i.state,',',i.zipcode) AS installationAddress FROM installations i`
        +` LEFT JOIN request_locations rl ON rl.id = i.location_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 `
        +` WHERE i.zipcode=${zipcode} AND (i.status=2) GROUP BY i.id`)
        // +` WHERE i.zipcode=${zipcode} AND (i.status=1 OR i.status=5)`)----previous
        // SELECT i.* FROM installations i WHERE zipcode=678671 AND (status=1 OR status=5)---old
        if(result[0].length>0){
            return result[0]
        }else{
            return false
        }
    }catch(error){
        console.log('getInstallationByZipcode : ',error);
    }
}

const addDriverSelectedRoutes=async(data)=>{
    let result = await DriverSelected_Route.create(data)
    if(result){
        return true
    }else{
        return false
    }
}



const updateStatusByInstallationId = async (installationId, newStatus) => {
    try {
      // Define the SQL query
      const sqlQuery = `
        UPDATE driver_selected_routes 
        SET status = :newStatus 
        WHERE installation_id = :installationId
      `;
  
      // Execute the SQL query
      const [updatedRows] = await sequelize.query(sqlQuery, {
        replacements: { newStatus, installationId }
      });
  
      if (updatedRows) {
        console.log(`Status updated successfully for installation ID ${installationId}`);
        return updatedRows
      } else {
        console.log(`No record found for installation ID ${installationId}`);
      }
    } catch (error) {
      console.error('Error updating status:', error);
    }
  };
  



const findByDriverIdAndDeleteIfStatus1 = async (driverId) => {
    try {
      const routes = await DriverSelected_Route.findAll({
        where: {
          driver_id: {
            [Op.like]: `%${driverId}%` 
          }
        }
      });
  
      const routesWithStatus1 = routes.filter(route => route.status === 1);
  
      console.log(`Found ${routes.length} routes for driver ID ${driverId}`);
      console.log(`Found ${routesWithStatus1.length} routes with status === 1`);
  
      if (routes.length === routesWithStatus1.length) {
        const deletedRows = await DriverSelected_Route.destroy({
          where: {
            driver_id: {
              [Op.like]: `%${driverId}%`
            }
          }
        });
  
        console.log(`Deleted ${deletedRows} routes for driver ID ${driverId}`);
      } else {
        routes.forEach(route => {
          console.log(`Route ID: ${route.route_id}, Installation ID: ${route.installation_id}, Status: ${route.status}`);
        });
      }
  
      return routes;
    } catch (error) {
      console.error('Error finding/deleting routes by driver ID:', error);
      return null;
    }
  };
  


// const findByDriverId = async (driverId,isInstaller,userId) => {
//     try {
//       const allRoutes = await DriverSelected_Route.findAll();
  
//       console.log('allRoutesallRoutes',allRoutes)
//       // Filter routes where driver_id includes the specified driverId
//       const filteredRoutes = allRoutes.filter(route => {
//         const driverIds = route.driver_id.split(',').map(id => id.trim());
//         return driverIds.includes(driverId?.toString());
//       });
  
//       console.log(`Found ${filteredRoutes.length} routes for driver ID ${driverId}`);
//       filteredRoutes.forEach(route => {
//         console.log(`Route ID: ${route.route_id}, Installation ID: ${route.installation_id}, Status: ${route.status}`);
//       });
  
//       return filteredRoutes;
//     } catch (error) {
//       console.error('Error finding routes by driver ID:', error);
//       return null;
//     }
// };
  


const findByDriverId = async (driverId, isInstaller, userId) => {
  try {
    const allRoutes = await DriverSelected_Route.findAll();

    console.log('dataaaaaaaaaaaallRoutes', allRoutes);
    const filteredRoutes = allRoutes.filter(route => {
      if (isInstaller) {
        // TEMPORARY workaround for DB typo: installler_id
        const installerId = route.dataValues?.installler_id;
        return installerId?.toString() === userId?.toString();
      } else {
        const driverIds = route.driver_id?.split(',').map(id => id.trim());
        return driverIds?.includes(driverId?.toString());
      }
    });

    console.log(`Found ${filteredRoutes.length} routes for ${isInstaller ? 'installer' : 'driver'} ID ${isInstaller ? userId : driverId}`);
    return filteredRoutes;
  } catch (error) {
    console.error('Error finding routes by driver ID:', error);
    return null;
  }
};




// const deleteDriverRoute = async (installationId, driverId,userId) => {
//   try {
//     const result = await DriverSelected_Route.destroy({
//       where: {
//         installation_id: installationId,
//         driver_id: driverId,
//       }
//     });

//     if (result) {
//       console.log(`Successfully deleted ${result} record(s).`);
//     } else {
//       console.log("No record found with the given criteria.");
//     }
//   } catch (error) {
//     console.error("Error deleting record:", error);
//   }
// };
  

const deleteDriverRoute = async (installationId, driverId, userId) => {
  try {
    const result = await DriverSelected_Route.destroy({
      where: {
        installation_id: installationId,
        [Op.or]: [
          { driver_id: driverId },
          { installler_id: userId }
        ]
      }
    });
    return result;
  } catch (error) {
    console.error("Error deleting driver route:", error);
    throw error;
  }
};


const getAllAssignedRoutes=async()=>{
  let result = await sequelize.query(`SELECT r.*
    FROM routes r
    LEFT JOIN installations i ON r.id = i.route_id
    WHERE i.status=2
    ORDER BY r.id DESC;
  `);
  const countResult = await sequelize.query('SELECT COUNT(id) as count FROM routes');
  const totalCount = countResult[0][0].count;
  if(!empty(result)){
      return {
          data: result[0],
          totalCount: totalCount
      };
  }else{
      return []
  }
}


const getInstallationByDriverId = async (driverId,status) => {
  try {
    let result =null
    if(status){
      result= await sequelize.query(`
        SELECT i.*,lcs.name AS service_name,pds.name AS product_name,lcs.avatarUrl AS service_image,rl.order_id AS customer_order_product_id, CONCAT(i.address, ',', i.city, ',', i.state, ',', i.zipcode) AS installationAddress
        FROM installations i
        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_locations rl ON rl.id = i.location_id 
        WHERE 
          i.status = :status
          AND FIND_IN_SET(:driverId, i.driver_id)  GROUP BY i.id
      `, {
        replacements: { driverId,status },
        type: sequelize.QueryTypes.SELECT,
      });
    }else{

    result= await sequelize.query(`
      SELECT 
        i.*, 
        CONCAT(i.address, ',', i.city, ',', i.state, ',', i.zipcode) AS installationAddress
      FROM 
        installations i
      WHERE 
        i.status=2
        AND FIND_IN_SET(:driverId, i.driver_id)
    `, {
      replacements: { driverId },
      type: sequelize.QueryTypes.SELECT,
    });
  }

    if (result.length > 0) {
      return result;
    } else {
      return false;
    }
  } catch (error) {
    console.log('getInstallationByDriverId : ', error);
    return false;
  }
};


const getInstallationByStatus = async (status) => {
  try {
    const result = await sequelize.query(
      `
      SELECT 
        i.*,lcs.name AS service_name,pds.name AS product_name,lcs.avatarUrl AS service_image, rl.order_id AS customer_order_product_id, 
        CONCAT(i.address, ', ', i.city, ', ', i.state, ', ', i.zipcode) AS installationAddress
      FROM installations i
      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_locations rl ON rl.id = i.location_id 
      WHERE i.status = :status GROUP BY i.id`,
      {
        replacements: { status },
        type: sequelize.QueryTypes.SELECT,
      }
    );

    return result.length > 0 ? result : false;
  } catch (error) {
    console.error('Error in getInstallationByStatus:', error);
    return false;
  }
};

const fetchAllUnassignedSalesorderFromAppointments = async (userId = 0, userType = 0, hasPermission = false, routeId = null) => {
  try {
      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}
        )`;
      }
      let whereCondition2 = '';
      if(userType == 1 || userType == 8){
        whereCondition2 = '';
      }else if(userType == 3){
        whereCondition = ''
        whereCondition2 = ` pr.user_id=${userId} `;
      }else{
        whereCondition2 = ` (u1.id = ${userId} or u2.id = ${userId} or pmm.user_id =${userId} ) `
      }
    
      let userTypeCondition = '';
      if(userType == 1 || userType == 8){
        userTypeCondition = ''
      } else if(userType == 3){
        userTypeCondition = ` left join request_partners rp on rp.request_id=p.request_id left join partners pr on pr.id=rp.partner_id `
      }else{
        userTypeCondition = ` LEFT JOIN staff_members s1 ON s1.id=p.project_lead  
          LEFT JOIN users u1 ON s1.user_id=u1.id 
          LEFT JOIN staff_members s2 ON s2.id=p.default_assignee 
          LEFT JOIN users u2 ON s2.user_id=u2.id
          LEFT JOIN users 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(routeId){
        tabCondition += ` ptp.id = 1 AND (I.status = 1 OR I.route_id = ${routeId}) `;
      }else{
        tabCondition += ` ptp.id = 1 AND I.status = 1 `
      }


      // Combine conditions carefully
      let finalCondition = [];
      if (whereCondition) {
          finalCondition.push(whereCondition);
      }
      if (whereCondition2) {
          finalCondition.push(whereCondition2);
      }
      if (tabCondition) {
          finalCondition.push(tabCondition);
      }
      const combinedWhere = finalCondition.length > 0 ? `WHERE ${finalCondition.join(' AND ')}` : '';
      
      let query = await sequelize.query(`SELECT r.name, c.*, I.*,pc.name AS job_category_name,ptp.name AS job_class_name, p.cover_image, p.start_date,p.name,r.doc_number, 
          CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) 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 
          FROM installations I
          LEFT JOIN staff_members sm ON sm.id = I.team_id
          LEFT JOIN project_members pm ON pm.project_id = I.project_id
          LEFT JOIN users u ON 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
          ${userTypeCondition}
          LEFT JOIN requests r ON r.id = I.request_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
          ${combinedWhere}
          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 addRouteLocations = async (data) => {
  try {
    const result = await RouteLocations.create(data);
    return result ? result?.dataValues?.id : false;
  } catch (error) {
    console.error('AddRouteLocations error: ', error.message);
    return false;
  }
};

const updateRouteLocation = async (id, data) => {
  try {
    const result = await RouteLocations.update(data,{
      where : { id : id }
    });
    return result ? true : false;
  } catch (error) {
    console.error('updateRouteLocation error: ', error.message);
    return false;
  }
};

const fetchAllTSRoutes = async (sales_order_id = null) => {
  try {
    let whereCondition = '';
    if (sales_order_id) {
      whereCondition = ` OR assigned_sales_order = ${sales_order_id} `
    }
    const result = await sequelize.query(`SELECT * FROM routes WHERE status=1 AND type= 2 AND (assigned_sales_order IS NULL ${whereCondition}) ORDER BY id desc`)
    if(result[0]?.length>0){
      return result[0];
    }else{
      return null
    }
  } catch (error) {
    console.error('fetchAllTSRoutes error: ', error.message);
    return null;
  }
};

const fetchAllRouteLocations = async () => {
  try {
    const result = await sequelize.query(`SELECT * FROM route_locations WHERE status=1`)
    if(result[0]?.length>0){
      return result[0];
    }else{
      return null
    }
  } catch (error) {
    console.error('fetchAllRouteLocations error: ', error.message);
    return null;
  }
};

const fetchRouteLocationsById = async (id) => {
  try {
    const result = await sequelize.query(`SELECT * FROM route_locations WHERE id=${id}`)
    if(result[0]?.length>0){
      return result[0];
    }else{
      return null
    }
  } catch (error) {
    console.error('fetchRouteLocationsById error: ', error.message);
    return null;
  }
};

const fetchRouteLocationsBySalesId = async (route_id) => {
  try {
    const result = await sequelize.query(`SELECT rl.*,cop.id FROM request_locations rl 
      LEFT JOIN customer_order_products cop ON cop.id=rl.order_id
      LEFT JOIN customer_orders co ON co.id=cop.order_id
      LEFT JOIN requests r ON r.id=co.request_id
      LEFT JOIN installations i ON i.request_id=r.id
      WHERE r.id=${route_id} AND rl.status=1`)
    if(result[0]?.length>0){
      return result[0];
    }else{
      return null
    }
  } catch (error) {
    console.error('fetchRouteLocationsByRouteId error: ', error.message);
    return null;
  }
};

const fetchAddressByRouteId = async (route_id) => {
  try {
    const result = await sequelize.query(
      `SELECT r.*, co.* FROM routes r 
       LEFT JOIN community co ON co.id = r.community_id
       WHERE r.id = :route_id`,
      {
        replacements: { route_id },
        type: sequelize.QueryTypes.SELECT,
      }
    );

    return result.length > 0 ? result : null;
  } catch (error) {
    console.error('fetchAddressByRouteId error: ', error.message);
    return null;
  }
};

const fetchRouteLocationsByRouteId = async (route_id) => {
  try {
    // Fetch main data (Products & Orders without request locations)
    const mainData = await sequelize.query(
      `SELECT rl.*, p.name AS product_name,rp.qty AS quantity, rql.order_id AS customer_order_product_id,
        rql.id AS request_location_id, rp.product_id, r.assigned_sales_order AS request_id,
        c.name AS community_name, req.doc_number, pt.name AS customer_name, req.name AS project_name
      FROM route_locations rl 
      LEFT JOIN route_products rp ON rp.id = rl.route_product_id
      LEFT JOIN products p ON p.id = rp.product_id
      LEFT JOIN routes r ON r.id = rp.route_id
      LEFT JOIN request_locations rql ON rql.route_location_id = rl.id
      LEFT JOIN requests req ON req.id = r.assigned_sales_order
      LEFT JOIN community c ON c.id = req.community
      LEFT JOIN customer_orders co ON co.request_id = req.id
      LEFT JOIN partners pt ON pt.id = co.customer_id
      WHERE rl.route_id = ? AND rl.status = 1 
      GROUP BY rl.id`,
      { replacements: [route_id], type: sequelize.QueryTypes.SELECT }
    );

    console.log('mainData:', mainData);
    if (mainData?.some((item) => item?.request_location_id)) {
      const mappedData = await Promise.all(
        mainData.map(async (product) => {
          const locationId = product.request_location_id;
    
          // 🔹 Fetch attachments for the product using `locationId`
          const attachmentResult = await sequelize.query(
            `SELECT ra.*, CONCAT(u.first_name,' ',u.last_name) AS approved_by_name 
             FROM request_attachments ra
             LEFT JOIN users u ON u.id = ra.approved_by
             WHERE ra.status = :status 
             AND ra.approved = :approved 
             AND (ra.file_type LIKE 'image/%' OR ra.file_type = :imgType) 
             AND FIND_IN_SET(:locationId, ra.location_ids)`,
            {
              type: sequelize.QueryTypes.SELECT,
              replacements: {
                status: 1,
                approved: 1,
                imgType: 'img',
                locationId,
              },
            }
          );
    
          console.log('Product Attachments:', attachmentResult);
    
          // 🔹 Fetch location-specific attachments
          const locationAttachments = await sequelize.query(
            `SELECT ra.*, CONCAT(u.first_name,' ',u.last_name) AS approved_by_name, 
                    cop.product_qty AS quantity
             FROM request_attachments ra
             LEFT JOIN users u ON u.id = ra.approved_by
             LEFT JOIN request_locations rl ON rl.id = :locId
             LEFT JOIN customer_order_products cop ON rl.order_id = cop.id
             WHERE ra.status = :status 
             AND ra.approved = :approved 
             AND (ra.file_type LIKE 'image/%' OR ra.file_type = :imgType) 
             AND FIND_IN_SET(rl.id, ra.location_ids)`,
            {
              type: sequelize.QueryTypes.SELECT,
              replacements: {
                status: 1,
                approved: 1,
                imgType: 'img',
                locId: locationId,
              },
            }
          );
    
          return {
            ...product,
            request_location_id: locationId || null,
            attachments: locationAttachments?.length > 0 ? locationAttachments : [],
          };
        })
      );
    
      return mappedData.length > 0 ? mappedData : null;
    } else {
      return mainData.length > 0 ? mainData : null;
    }
    
    // if (mainData?.some((item) => item?.request_location_id)) {
    //   const mappedData = await Promise.all(
    //     mainData.map(async (product) => {
    //       const locationId = product.request_location_id;
    //       // 🔹 Fetch attachments for the product using `locationId`
    //       const attachmentResult = await sequelize.query(
    //         `SELECT ra.*, CONCAT(u.first_name,' ',u.last_name) AS approved_by_name 
    //         FROM request_attachments ra
    //         LEFT JOIN users u ON u.id = ra.approved_by
    //         WHERE ra.status = :status 
    //         AND ra.approved = :approved 
    //         AND (ra.file_type LIKE 'image/%' OR ra.file_type = :imgType) 
    //         AND FIND_IN_SET(:locationId, ra.location_ids)`,
    //         // AND FIND_IN_SET(:productId, ra.product_ids)`,
    //         {
    //           type: sequelize.QueryTypes.SELECT,
    //           replacements: {
    //             status: 1,
    //             approved: 1,
    //             imgType: 'img',
    //             locationId: locationId,
    //           }
    //         }
    //       );

    //       console.log('Product Attachments:', attachmentResult);

    //       const locationAttachmentResults = {};
    //       const locationAttachments = await sequelize.query(
    //         `SELECT ra.*, CONCAT(u.first_name,' ',u.last_name) AS approved_by_name, cop.product_qty AS quantity
    //         FROM request_attachments ra
    //         LEFT JOIN users u ON u.id = ra.approved_by
    //         LEFT JOIN request_locations rl ON rl.id = :locId
    //         LEFT JOIN customer_order_products cop ON rl.order_id = cop.id
    //         WHERE ra.status = :status 
    //         AND ra.approved = :approved 
    //         AND (ra.file_type LIKE 'image/%' OR ra.file_type = :imgType) 
    //         AND FIND_IN_SET(rl.id, ra.location_ids)`,
    //         // AND FIND_IN_SET(cop.id, ra.product_ids)`,
    //         {
    //           type: sequelize.QueryTypes.SELECT,
    //           replacements: {
    //             status: 1,
    //             approved: 1,
    //             imgType: 'img',
    //             locId: locationId,
    //           }
    //         }
    //       );
    //       locationAttachmentResults[locationId] = locationAttachments.length > 0 ? locationAttachments : [];

    //       // If multiple locations exist for the same product, create separate entries for each
    //       return ({
    //           ...product,
    //           request_location_id: locationId || null,
    //           attachments: locationAttachmentResults[locationId] || []
    //         })
    //     })
    //   );
    //   const finalData = mappedData.flat();
    //   return finalData.length > 0 ? finalData : null;
    // }else {
    //   return mainData.length > 0 ? mainData : null;
    // }
  } catch (error) {
    console.error('fetchRouteLocationsByRouteId error: ', error.message);
    return null;
  }
};

// const fetchRouteLocationsByRouteId = async (route_id) => {
//   try {
//     // Fetch main data (Products & Orders without request locations)
//     const mainData = await sequelize.query(
//       `SELECT rl.*, p.name AS product_name,rp.qty AS quantity, cop.id AS customer_order_product_id, rp.product_id, 
//               r.assigned_sales_order AS request_id 
//       FROM route_locations rl 
//       LEFT JOIN route_products rp ON rp.id = rl.route_product_id
//       LEFT JOIN products p ON p.id = rp.product_id
//       LEFT JOIN routes r ON r.id = rp.route_id
//       LEFT JOIN customer_orders co ON co.request_id = r.assigned_sales_order
//       LEFT JOIN customer_order_products cop 
//         ON cop.order_id = co.id 
//         AND cop.product_id = rp.product_id
//       WHERE rl.route_id = ? AND rl.status = 1 
//       GROUP BY rl.id`,
//       { replacements: [route_id], type: sequelize.QueryTypes.SELECT }
//     );

//     // Fetch request locations separately
//     const requestLocations = await sequelize.query(
//       `SELECT rql.id AS request_location_id, 
//               rql.order_id AS customer_order_product_id, 
//               rql.address, 
//               rql.city, 
//               rql.state, 
//               rql.zip,
//               co.request_id, 
//               r.id AS route_id
//       FROM request_locations rql
//       JOIN customer_order_products cop ON cop.id = rql.order_id
//       JOIN customer_orders co ON cop.order_id = co.id
//       JOIN routes r ON co.request_id = r.assigned_sales_order
//       WHERE r.id = ? 
//       GROUP BY rql.id`,
//       { replacements: [route_id], type: sequelize.QueryTypes.SELECT }
//     );

//     console.log('mainData:', mainData);
//     console.log('requestLocations:', requestLocations);
//     if(requestLocations?.length > 0){
//       // Convert requestLocations into a Map of arrays (to store multiple locations per product)
//       const locationMap = new Map();
//       const locationMapArray = [];
//       requestLocations.forEach(loc => {
//           const key = `${loc.customer_order_product_id}_${loc.request_id}_${loc.route_id}_${loc.address || ''}_${loc.city || ''}_${loc.state || ''}`; //_${loc.zip || ''}

//           if (!locationMap.has(key)) {
//               locationMap.set(key, []);
//               locationMapArray.push(key)
//           }
//           locationMap.get(key).push(loc.request_location_id);
//       });

//       // Map request_location_id to mainData, assigning multiple locations if available
//       const mappedData = await Promise.all(
//         mainData.map(async (product) => {
//           const key = `${product.customer_order_product_id}_${product.request_id}_${product.route_id}_${product.address || ''}_${product.city || ''}_${product.state || ''}`; //_${product.zip || ''}
//           const matchingLocations = locationMap.get(key) || [];

//           // 🔹 Fetch attachments for the product using `product_ids`
//           const attachmentResult = await sequelize.query(
//             `SELECT ra.*, CONCAT(u.first_name,' ',u.last_name) AS approved_by_name 
//             FROM request_attachments ra
//             LEFT JOIN users u ON u.id = ra.approved_by
//             WHERE ra.status = :status 
//             AND ra.approved = :approved 
//             AND (ra.file_type LIKE 'image/%' OR ra.file_type = :imgType) 
//             AND FIND_IN_SET(:locationId, ra.location_ids)`,
//             // AND FIND_IN_SET(:productId, ra.product_ids)`,
//             {
//               type: sequelize.QueryTypes.SELECT,
//               replacements: {
//                 status: 1,
//                 approved: 1,
//                 imgType: 'img',
//                 // productId: product.customer_order_product_id,
//                 locationId: product.id,
//               }
//             }
//           );

//           console.log('Product Attachments:', attachmentResult);
//           // product.attachments = attachmentResult.length > 0 ? attachmentResult : [];

//           const locationAttachmentResults = {};
//           if (matchingLocations.length > 0) {
//             for (const locId of matchingLocations) {
//               console.log('locId', locId)
//               const locationAttachments = await sequelize.query(
//                 `SELECT ra.*, CONCAT(u.first_name,' ',u.last_name) AS approved_by_name, cop.product_qty AS quantity
//                 FROM request_attachments ra
//                 LEFT JOIN users u ON u.id = ra.approved_by
//                 LEFT JOIN request_locations rl ON rl.id = :locId
//                 LEFT JOIN customer_order_products cop ON rl.order_id = cop.id
//                 WHERE ra.status = :status 
//                 AND ra.approved = :approved 
//                 AND (ra.file_type LIKE 'image/%' OR ra.file_type = :imgType) 
//                 AND FIND_IN_SET(rl.id, ra.location_ids)`,
//                 // AND FIND_IN_SET(cop.id, ra.product_ids)`,
//                 {
//                   type: sequelize.QueryTypes.SELECT,
//                   replacements: {
//                     status: 1,
//                     approved: 1,
//                     imgType: 'img',
//                     locId: locId,
//                   }
//                 }
//               );
//               locationAttachmentResults[locId] = locationAttachments.length > 0 ? locationAttachments : [];
//             }
//           }
//           // If multiple locations exist for the same product, create separate entries for each
//           return matchingLocations.length > 0
//             ? matchingLocations.map(locId => ({
//                 ...product,
//                 key:key,
//                 locationMapArray:locationMapArray,
//                 requestLocations,
//                 request_location_id: locId,
//                 attachments: locationAttachmentResults[locId] || []
//               }))
//             : [{ ...product, 
//                 key:key,
//                 locationMapArray:locationMapArray, 
//                 request_location_id: null,
//                 attachments: []
//               }];
//         })
//       );
//       const finalData = mappedData.flat();
//       return finalData.length > 0 ? finalData : null;
//     }else {
//       return mainData.length > 0 ? mainData : null;
//     }
//   } catch (error) {
//     console.error('fetchRouteLocationsByRouteId error: ', error.message);
//     return null;
//   }
// };

const deleteRouteLocations = async (id) => {
  try {
    const result = await RouteLocations.destroy({ where: { id } });
    return result > 0;
  } catch (error) {
    console.error('deleteRouteLocations error: ', error.message);
    return false;
  }
};

const deleteRouteLocationsByRouteId = async (route_id) => {
  try {
    const result = await RouteLocations.destroy({ where: { route_id } });
    return result > 0;
  } catch (error) {
    console.error('deleteRouteLocationsByRouteId error: ', error.message);
    return false;
  }
};

const addRouteProducts = async (data) => {
  try {
    const result = await RouteProducts.create(data);
    return result ? result.dataValues.id : false;
  } catch (error) {
    console.error('addRouteProducts error: ', error.message);
    return false;
  }
};

const deleteRouteProducts = async (id) => {
  try {
    const result = await RouteProducts.destroy({ where: { id } });
    return result > 0;
  } catch (error) {
    console.error('deleteRouteProducts error: ', error.message);
    return false;
  }
};

const deleteRouteProductsByRouteId = async (route_id) => {
  try {
    const result = await RouteProducts.destroy({ where: { route_id } });
    return result > 0;
  } catch (error) {
    console.error('deleteRouteProductsByRouteId error: ', error.message);
    return false;
  }
};

// const fetchRouteProductsByRouteId = async (route_id,sales_order_id) => {
//   try {
//     const result = await sequelize.query(`SELECT * FROM route_products WHERE route_id=${route_id} AND status=1`)

//     if(result[0]?.length>0){
//       return result[0];
//     }else{
//       return null
//     }
//   } catch (error) {
//     console.error('fetchRouteProductsByRouteId error: ', error.message);
//     return null;
//   }
// };

const fetchRouteProductsByRouteId = async (route_id, sales_order_id = null) => {
  try {
    // Step 1: Get all route products
    const [routeProducts] = await sequelize.query(`
      SELECT * FROM route_products WHERE route_id = ${route_id} AND status = 1 ORDER BY id ASC;
    `);

    // If no sales order provided, return all route products
    if (!sales_order_id) {
      return routeProducts.length > 0 ? routeProducts : null;
    }

    // Step 2: Get product IDs from sales order
    const [copResults] = await sequelize.query(`
      SELECT cop.product_id
      FROM requests r
      LEFT JOIN customer_orders co ON co.request_id = r.id
      LEFT JOIN customer_order_products cop ON cop.order_id = co.id
      WHERE r.id = ${sales_order_id}
    `);

    // Extract product IDs
    const salesOrderProductIds = copResults.map((item) => item.product_id);
    console.log('copResults', copResults)
    console.log('routeProducts', routeProducts)

    // Step 3: Filter routeProducts to match only those in sales order
    const filteredProducts = routeProducts.filter((rp) =>
      salesOrderProductIds.includes(rp.product_id)
    );

    return filteredProducts.length > 0 ? filteredProducts : null;

  } catch (error) {
    console.error('fetchRouteProductsByRouteId error:', error.message);
    return null;
  }
};


const fetchRouteProductsBySalesId = async (route_id) => {
  try {
    const result = await sequelize.query(`SELECT * FROM route_products WHERE route_id=${route_id} AND status=1`)
    if(result[0]?.length>0){
      return result[0];
    }else{
      return null
    }
  } catch (error) {
    console.error('fetchRouteProductsByRouteId error: ', error.message);
    return null;
  }
};
module.exports={
    RoutesModel:Route,
    createRoute,
    getAllRoutes,
    getRouteDatabyId,
    updateRoute,
    getAllUnAssignedRoutes,
    getInstallationByZipcode,
    addDriverSelectedRoutes,
    updateStatusByInstallationId,
    findByDriverIdAndDeleteIfStatus1,
    findByDriverId,
    deleteDriverRoute,
    getAllAssignedRoutes,
    getInstallationByDriverId,
    fetchAllUnassignedSalesorderFromAppointments,
    getInstallationByStatus,
    addRouteLocations,
    updateRouteLocation,
    fetchAllRouteLocations,
    fetchRouteLocationsById,
    fetchRouteLocationsByRouteId,
    deleteRouteLocations,
    deleteRouteLocationsByRouteId,
    fetchAllTSRoutes,
    addRouteProducts,
    deleteRouteProducts,
    deleteRouteProductsByRouteId,
    fetchRouteProductsByRouteId,
    fetchRouteLocationsBySalesId,
    fetchRouteProductsBySalesId,
    getRouteDatabySalesOrderId,
    getRouteDatabyRequestId,
    fetchAddressByRouteId
}