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

const Driver= sequelize.define('driver',{
    id:{ type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name:{type:Sequelize.STRING,allowNull:false,defaultValue:null},
    email:{type:Sequelize.TEXT,allowNull:false,defaultValue:null},
    phone:{type:Sequelize.STRING,allowNull:false,defaultValue:null},
    phone_country_code : {type : Sequelize.STRING,allowNull:false,defaultValue:1},
    blood_group:{type:Sequelize.STRING,allowNull:true,defaultValue:null},
    licence_number:{type:Sequelize.TINYINT,allowNull:true,defaultValue:null},
    licence_image:{type:Sequelize.TEXT,allowNull:true,defaultValue:null},
    gender:{type:Sequelize.STRING,allowNull:true,defaultValue:null},
    address:{type:Sequelize.TEXT,allowNull:true,defaultValue:null},
    profile_photo:{type:Sequelize.TEXT,allowNull:true,defaultValue:null},
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    verified:{type: Sequelize.TINYINT, allowNull: false, defaultValue: 0},
    user_id: { type: Sequelize.INTEGER, allowNull: true },
    team_id: { type: Sequelize.INTEGER, allowNull: true },
    hourly_rate: { type: Sequelize.INTEGER, allowNull: true, defaultValue: 0 },
},{timestamps:false,tableName:'driver'})


const addDriver=async(data)=>{
    const result = await Driver.create(data)
    return result
}

const updateDriver=async(data,id)=>{ 
    let  result = await Driver.update(data,{
        where:{
            id:id
        },returning:true
    })

    if(result){
        return true
    }else{
        return false
    }
}

const getAllDrivers=async(userId=null, userType=null)=>{
    let whereCondition = ` dr.status != 2 `;
    if(userType == 24){
        whereCondition = ` dr.user_id = ${userId} `
    }
    let result = await sequelize.query(`select dr.*,ur.name AS role,ur.user_permissions from driver dr`
        + ` left join users u on u.id=dr.user_id`
        + ` left join user_roles ur on ur.id=u.user_type`
        + ` where ${whereCondition} `)
    if(result[0].length>0){
        return result[0]
    }else{
        return []
    }
}
const getAllfilterdDrivers=async(userId=null, userType=null)=>{
    let whereCondition = ` dr.status != 2 `;
    if(userType == 24){
        whereCondition = ` dr.user_id = ${userId} `
    }
    let result = await sequelize.query(`select dr.*,ur.name AS role,ur.user_permissions from driver dr`
        + ` left join users u on u.id=dr.user_id`
        + ` left join user_roles ur on ur.id=u.user_type`
        + ` where ${whereCondition} and dr.team_id is null `)
    if(result[0].length>0){
        return result[0]
    }else{
        return []
    }
}

 const getAllActiveDrivers=async()=>{
    let result = await sequelize.query(`select dr.* from driver as dr where status = 1`)
    if(result[0].length>0){
        return result[0]
    }else{
        return []
    }
}

 const getAllVerifiedActiveDrivers=async()=>{
    try{
        let result = await sequelize.query(`SELECT d.* FROM driver d WHERE d.status=1 AND d.verified=1`)
        if(result[0].length>0){
            return result[0]
        }else{
            return false
        }
    } catch(error){
        console.log("Error : ",error);
    }
}

const getDriverByuserId=async(id)=>{
    try {
        let result = await sequelize.query(
            // `select dr.*,u.admin_password as password from driver dr 
            //  inner join users as u on dr.user_id = u.id where dr.status != 2 and dr.user_id = ${id}`
            `SELECT dr.*, u.admin_password AS password, u.fcmToken FROM driver dr 
            INNER JOIN users AS u ON dr.user_id = u.id  WHERE dr.status != 2 AND dr.user_id = ${id}
`
            )
        if(!empty(result)&& result[0].length===1){
          return  result[0]
        }else{
            return []
        }
    } catch (error) {
        console.log(error)
    }
}

 const getDriverById=async(id)=>{
    try {
        let result = await sequelize.query(`select dr.*,u.admin_password as password,u.username from driver as dr inner join users as u on dr.user_id = u.id where dr.status != 2 and dr.id = ${id}`)
        if(!empty(result)&& result[0].length===1){
          return  result[0]
        }else{
            return []
        }
    } catch (error) {
        console.log(error)
    }
 }
const getUserDataById = async (userId) => {
  try {
    const result = await sequelize.query(`
      SELECT 
        u.id AS user_id,
        u.username,
        u.admin_password AS password,
        u.user_type,
        dr.id AS driver_id
        -- Add more specific columns from sm and dr as needed
      FROM users u
      LEFT JOIN staff_members sm ON sm.user_id = u.id
      LEFT JOIN driver dr ON dr.user_id = u.id
      WHERE u.id = ${userId} AND u.status != 2
    `);

    if (!empty(result) && result[0].length === 1) {
      return result[0][0]; // return one user
    } else {
      return [];
    }
  } catch (error) {
    console.log(error);
    return [];
  }
};



const getAssignedRoutesByDriver = async(id) => {
    const result = await sequelize.query(`SELECT i.route_id,COUNT(*) as installation_count,d.name AS driver_name,r.name AS route_name,r.unique_id,i.name AS installation_name,i.due_date FROM installations i `
        +` LEFT JOIN driver d ON i.driver_id = d.id `
        +` LEFT JOIN routes r ON i.route_id = r.id `
        // +` WHERE i.status=6 AND i.driver_id=${id} `
          +` WHERE i.status=6 AND  FIND_IN_SET(${id}, i.driver_id) `
       
        +` GROUP BY i.route_id, d.name, r.name ORDER BY i.route_id DESC `)
    if(result[0].length>0){
        return result[0]
    }else{
        return false
    }
}

const getAcceptedRoutesByDriver = async(id) => {
    const result = await sequelize.query(`SELECT i.route_id,COUNT(*) as installation_count,temp.published,d.name AS driver_name,r.name AS route_name,r.unique_id,i.name AS installation_name,i.due_date FROM installations i `
        +` LEFT JOIN driver d ON i.driver_id = d.id `
        +` LEFT JOIN routes r ON i.route_id = r.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`
        // +` WHERE i.status=2 AND i.driver_id=${id} `
        //  +` WHERE i.status=2 AND  FIND_IN_SET(${id}, i.driver_id) `
          +` WHERE (i.status = 2 OR (i.status = 4 AND COALESCE(temp.published, 0) != 1)) AND  FIND_IN_SET(${id}, i.driver_id) AND NOT (i.status = 4 AND i.driver_is_installer !=2)  `
        
        +` GROUP BY i.route_id, d.name, r.name ORDER BY i.route_id DESC `)
    if(result[0].length>0){
        return result[0]
    }else{
        return false
    }
}



// const getAcceptedRoutes = async (id, isInstaller, userId,userType,hasAcceptedRoutesPermission) => {
//     console.log('hasAcceptedRoutesPermission',hasAcceptedRoutesPermission);
//     // Build conditional driver condition
//     // const driverCondition = isInstaller
//     //     ? `i.team_id = :userId`
//     //     : `FIND_IN_SET(:id, i.driver_id)`;
//     //multi installer
//     // const driverCondition = isInstaller
//     //     ? `FIND_IN_SET(:userId, i.team_id)`
//     //     : `FIND_IN_SET(:id, i.driver_id)`;

//     // let driverCondition = 
//     //      ` FIND_IN_SET(${id}, i.driver_id) `

//     // if(hasAcceptedRoutesPermission){
//     //     driverCondition = ` `
//     // }else{
//     //     driverCondition = ` FIND_IN_SET(${id}, i.driver_id) `
//     // }

//     const driverCondition = hasAcceptedRoutesPermission
//         ? '1 = 1'
//         : ` FIND_IN_SET(${id}, i.driver_id) `;
//     const query = `
//         SELECT 
//             i.route_id,
//             i.project_id,
//             i.request_id,
//             COUNT(*) as installation_count,
//             temp.published,
//             d.name AS driver_name,
//             r.name AS route_name,
//             r.unique_id,
//             i.name AS installation_name,
//             i.due_date,
//             GROUP_CONCAT(DISTINCT i.request_id ORDER BY i.request_id) AS request_ids ,
//             cop_specific.product_name as build_product_name,
//             cop_specific.product_notes AS product_notes,
//             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 i.labour_cost_service_id IS NOT NULL THEN lcs.name
//                 ELSE NULL
//             END AS service_name
//         FROM installations i
//         LEFT JOIN driver d ON i.driver_id = d.id
//         LEFT JOIN routes r ON i.route_id = r.id
//         LEFT JOIN products prod ON i.product_id = prod.id
//         LEFT JOIN labour_cost_services lcs ON lcs.id = i.labour_cost_service_id
//         LEFT JOIN requests rq ON rq.id = i.request_id
//         LEFT JOIN customer_orders c ON c.request_id = rq.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 
//                 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.status = 2 OR (i.status = 4 AND COALESCE(temp.published, 0) != 1))
//             AND ${driverCondition}
//             AND NOT (i.status = 4 AND i.driver_is_installer != 2)
//         GROUP BY i.route_id, prod.id, lcs.id
//         ORDER BY i.route_id DESC
//     `;

//     const replacements = isInstaller
//         ? { userId }
//         : { id };

//     const result = await sequelize.query(query, {
//         replacements,
//         type: sequelize.QueryTypes.SELECT
//     });
//     if (result.length > 0) {
//         const formattedResult = [];

//         // Loop over each route and fetch the corresponding request names for each request_id
//         for (const item of result) {
//             const acceptedRoutes = [];

//             // Split request_ids and loop over each one
//             const requestIds = item.request_ids.split(',');

//             for (const requestId of requestIds) {
//                 // Corrected query with proper usage of :requestId placeholder
//                 const requestQuery = `
//                     SELECT 
//                         requests.name,
//                         requests.participation_ends as due_date,
//                         requests.doc_number as job_number, 
//                         p.name as client_name,
//                         c.name as community_name
//                     FROM requests
//                     LEFT JOIN request_partners pp ON pp.request_id = :requestId
//                     LEFT JOIN community c ON c.id = requests.community
//                     LEFT JOIN partners p ON p.id = pp.partner_id
//                     WHERE requests.id = :requestId
//                 `;
            
//                 // Execute the request query
//                 const requestData = await sequelize.query(requestQuery, {
//                     replacements: { requestId }, // use replacement for parameterized query
//                     type: sequelize.QueryTypes.SELECT
//                 });
            
//                 // If data is found, push the result to the acceptedRoutes array
//                 if (requestData.length > 0) {
//                     acceptedRoutes.push({
//                         job_name: requestData[0].name,
//                         job_number: requestData[0].job_number,
//                         client_name: requestData[0].client_name,
//                         community_name: requestData[0].community_name,
//                         due_date: requestData[0].due_date
//                     });
//                 }
//             }

//             // Push the modified item with the accepted_routes field
//             formattedResult.push({
//                 ...item,
//                 accepted_routes: acceptedRoutes
//             });
//         }

//         return formattedResult;
//     }

//     return result.length > 0 ? result : [];
// };

const getAcceptedRoutes = async (id, isInstaller, userId,userType,hasAcceptedRoutesPermission) => {
    console.log('hasAcceptedRoutesPermission',hasAcceptedRoutesPermission);
    // Build conditional driver condition
    // const driverCondition = isInstaller
    //     ? `i.team_id = :userId`
    //     : `FIND_IN_SET(:id, i.driver_id)`;
    //multi installer
    // const driverCondition = isInstaller
    //     ? `FIND_IN_SET(:userId, i.team_id)`
    //     : `FIND_IN_SET(:id, i.driver_id)`;

    // let driverCondition = 
    //      ` FIND_IN_SET(${id}, i.driver_id) `

    // if(hasAcceptedRoutesPermission){
    //     driverCondition = ` `
    // }else{
    //     driverCondition = ` FIND_IN_SET(${id}, i.driver_id) `
    // }

    const driverCondition = hasAcceptedRoutesPermission
        ? '1 = 1'
        : ` FIND_IN_SET(${id}, i.driver_id) `;
    const query = `
        SELECT 
            i.route_id,
            i.project_id,
            i.request_id,
            COUNT(*) as installation_count,
            temp.published,
            d.name AS driver_name,
            r.name AS route_name,
            r.unique_id,
            i.name AS installation_name,
            i.due_date,
            GROUP_CONCAT(DISTINCT i.request_id ORDER BY i.request_id) AS request_ids 
        FROM installations i
        LEFT JOIN driver d ON i.driver_id = d.id
        LEFT JOIN routes r ON i.route_id = r.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
        WHERE 
            (i.status = 2 OR (i.status = 4 AND COALESCE(temp.published, 0) != 1))
            AND ${driverCondition}
            AND NOT (i.status = 4 AND i.driver_is_installer != 2)
        GROUP BY i.route_id
        ORDER BY i.route_id DESC
    `;

    const replacements = isInstaller
        ? { userId }
        : { id };

    const result = await sequelize.query(query, {
        replacements,
        type: sequelize.QueryTypes.SELECT
    });
    if (result.length > 0) {
        const formattedResult = [];

        // Loop over each route and fetch the corresponding request names for each request_id
        for (const item of result) {
            const acceptedRoutes = [];

            // Split request_ids and loop over each one
            const requestIds = item.request_ids.split(',');

            for (const requestId of requestIds) {
                // Corrected query with proper usage of :requestId placeholder
                const requestQuery = `
                    SELECT 
                        requests.name,
                        requests.participation_ends as due_date,
                        requests.doc_number as job_number, 
                        p.name as client_name,
                        c.name as community_name
                    FROM requests
                    LEFT JOIN request_partners pp ON pp.request_id = :requestId
                    LEFT JOIN community c ON c.id = requests.community
                    LEFT JOIN partners p ON p.id = pp.partner_id
                    WHERE requests.id = :requestId
                `;
            
                // Execute the request query
                const requestData = await sequelize.query(requestQuery, {
                    replacements: { requestId }, // use replacement for parameterized query
                    type: sequelize.QueryTypes.SELECT
                });
            
                // If data is found, push the result to the acceptedRoutes array
                if (requestData.length > 0) {
                    acceptedRoutes.push({
                        job_name: requestData[0].name,
                        job_number: requestData[0].job_number,
                        client_name: requestData[0].client_name,
                        community_name: requestData[0].community_name,
                        due_date: requestData[0].due_date
                    });
                }
            }

            // Push the modified item with the accepted_routes field
            formattedResult.push({
                ...item,
                accepted_routes: acceptedRoutes
            });
        }

        return formattedResult;
    }

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


// const getAcceptedRoutes = async (id, isInstaller, userId,userType) => {
 
//     let query = ''
//     // Build conditional driver condition
//     const driverCondition = isInstaller
//         ? `i.team_id = ${userId}`
//         : `FIND_IN_SET(${id}, i.driver_id)`;
 
//         if(userType === 19 || userType === 24 || id){
//             query = `
//                 SELECT
//                     i.route_id,
//                     COUNT(*) as installation_count,
//                     temp.published,
//                     d.name AS driver_name,
//                     r.name AS route_name,
//                     r.unique_id,
//                     i.name AS installation_name,
//                     i.due_date
//                 FROM installations i
//                 LEFT JOIN driver d ON i.driver_id = d.id
//                 LEFT JOIN routes r ON i.route_id = r.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
//                 WHERE
//                     (i.status = 2 OR (i.status = 4 AND COALESCE(temp.published, 0) != 1))
//                     AND ${driverCondition}
//                     AND NOT (i.status = 4 AND i.driver_is_installer != 2)
//                     AND i.complete_status = 1
//                 GROUP BY i.route_id, d.name, r.name
//                 ORDER BY i.route_id DESC
//             `;
//             }
//         else if (userType === 1 || userType === 8) {
//             // Admin or Super Admin – fetch all routes
//             query = `
//                 SELECT
//                     i.route_id,
//                     COUNT(*) as installation_count,
//                     temp.published,
//                     d.name AS driver_name,
//                     r.name AS route_name,
//                     r.unique_id,
//                     i.name AS installation_name,
//                     i.due_date
//                 FROM installations i
//                 LEFT JOIN driver d ON i.driver_id = d.id
//                 LEFT JOIN routes r ON i.route_id = r.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
//                 WHERE
//                     (i.status = 2 OR (i.status = 4 AND COALESCE(temp.published, 0) != 1))
//                     AND NOT (i.status = 4 AND i.driver_is_installer != 2)
//                     AND i.complete_status = 1
//                 GROUP BY i.route_id, r.name
//                 ORDER BY i.route_id DESC
//             `;
//         }else{
//             query = `
//                 SELECT
//                     i.route_id,
//                     COUNT(*) as installation_count,
//                     temp.published,
//                     d.name AS driver_name,
//                     r.name AS route_name,
//                     r.unique_id,
//                     i.name AS installation_name,
//                     i.due_date
//                 FROM installations i
//                 INNER JOIN project_members pm ON pm.project_id = i.project_id
//                 INNER JOIN staff_members sm ON sm.id = pm.staff_id
//                 LEFT JOIN driver d ON i.driver_id = d.id
//                 LEFT JOIN routes r ON i.route_id = r.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
//                 WHERE
//                     (i.status = 2 OR (i.status = 4 AND COALESCE(temp.published, 0) != 1))
//                     AND NOT (i.status = 4 AND i.driver_is_installer != 2)
//                     AND i.complete_status = 1
//                     AND sm.user_id =${userId}
//                 GROUP BY i.route_id, r.name
//                 ORDER BY i.route_id DESC
//             `;
//         }
   
//     const result = await sequelize.query(query, {
//         type: sequelize.QueryTypes.SELECT
//     });
//     return result.length > 0 ? result : false;
// };

const getAssignedInstallationsByRoute = async(driverId,routeId) => {
    const result = await sequelize.query(`SELECT i.*,ps.name AS product_name,
        ct.name as installation_temp_name,pr.name AS customers_name,pr.phone AS customers_phone, 
        concat(pr.address,', ',pr.city,', ',st.name,', ',pr.zip_code) AS customers_address,
         GROUP_CONCAT(DISTINCT CONCAT(u.first_name, ' ', COALESCE(u.last_name, ''))
                     SEPARATOR ', ')
            AS implementation_manager,
        d.name AS driver_name,r.name AS route_name FROM installations i `
        +` LEFT JOIN driver d ON i.driver_id = d.id `
        +` LEFT JOIN users  u ON FIND_IN_SET(u.id, i.team_id) `
        +` LEFT JOIN routes r ON i.route_id = r.id `
        +` LEFT JOIN requests rq ON rq.id = i.request_id`
        +` LEFT JOIN custom_field_templates ct on ct.id = i.installation_temp_id`
        +` LEFT JOIN products ps ON ps.id = i.product_id`
        +` LEFT JOIN customer_orders c ON c.request_id = rq.id`
        +` LEFT JOIN partners pr ON pr.id = c.customer_id`
        +` LEFT JOIN states st ON st.id = pr.state_id`
        // +` WHERE i.status=6 AND i.driver_id=${driverId} AND i.route_id=${routeId} `
         +` WHERE i.status=6 AND FIND_IN_SET(${driverId}, i.driver_id) AND i.route_id=${routeId} `
        +` GROUP BY i.id ORDER BY i.id DESC `)
    if(result[0].length>0){
        return result[0]
    }else{
        return false
    }
}

// const getAcceptedInstallationsByRoute = async(driverId,routeId) => {
//     const result = await sequelize.query(`SELECT i.*,ps.name AS product_name,ct.name as installation_temp_name,pr.name AS customers_name,pr.phone AS customers_phone, concat(pr.address,', ',pr.city,', ',st.name,', ',pr.zip_code) AS customers_address,concat(u.first_name,' ',coalesce(u.last_name,'')) as implementation_manager,d.name AS driver_name,r.name AS route_name FROM installations i `
//         +` LEFT JOIN driver d ON i.driver_id = d.id `
//         +` LEFT JOIN users u on u.id=i.team_id`
//         +` LEFT JOIN routes r ON i.route_id = r.id `
//         +` LEFT JOIN products ps ON ps.id = i.product_id`
//         +` LEFT JOIN custom_field_templates ct on ct.id = i.installation_temp_id`
//         +` LEFT JOIN requests rq ON rq.id = i.request_id`
//         +` LEFT JOIN customer_orders c ON c.request_id = rq.id`
//         +` LEFT JOIN partners pr ON pr.id = c.customer_id`
//         +` LEFT JOIN states st ON st.id = pr.state_id`
//         // +` WHERE i.status=2 AND i.driver_id=${driverId} AND i.route_id=${routeId} `
//         //  +` WHERE i.status=2 AND FIND_IN_SET(${driverId}, i.driver_id) AND i.route_id=${routeId} `
//         +` WHERE i.status=2 AND FIND_IN_SET(${driverId}, i.driver_id) AND i.route_id=${routeId} `
//         +` ORDER BY i.id DESC `)
//     if(result[0].length>0){
//         return result[0]
//     }else{
//         return false
//     }
// }

const getAcceptedInstallationsByRoute = async(driverId, routeId) => {
    const result = await sequelize.query(`
        SELECT 
            i.*, 
            ps.name AS product_name, 
            ct.name as installation_temp_name, 
            pr.name AS customers_name, 
            pr.phone AS customers_phone, 
            CONCAT(pr.address, ', ', pr.city, ', ', st.name, ', ', pr.zip_code) AS customers_address,
            CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) as implementation_manager,
            d.name AS driver_name, 
            r.name AS route_name,
            temp.published
        FROM installations i
        LEFT JOIN driver d ON i.driver_id = d.id
        LEFT JOIN users u on u.id = i.team_id
        LEFT JOIN routes r ON i.route_id = r.id
        LEFT JOIN products ps ON ps.id = i.product_id
        LEFT JOIN custom_field_templates ct on ct.id = i.installation_temp_id
        LEFT JOIN requests rq ON rq.id = i.request_id
        LEFT JOIN customer_orders c ON c.request_id = rq.id
        LEFT JOIN partners pr ON pr.id = c.customer_id
        LEFT JOIN states st ON st.id = pr.state_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
        WHERE 
            (i.status = 2 OR (i.status = 4 AND COALESCE(temp.published, 0) != 1))
            AND FIND_IN_SET(${driverId}, i.driver_id) 
            AND i.route_id = ${routeId}
            AND NOT (i.status = 4 AND i.driver_is_installer !=2)  
        ORDER BY i.id DESC
    `);

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

const getAcceptedInstallationsByRouteForApp = async (driverId, routeId, isInstaller, userId, userType, hasAcceptedRoutesPermission) => {
    let driverCondition = '';
    const replacements = { routeId };

    if (hasAcceptedRoutesPermission) {
        // Permission bypass: show all installations for this route regardless of driver
        driverCondition = '1 = 1';
    } else if (isInstaller) {
        driverCondition = 'FIND_IN_SET(:userId,i.team_id) ';
        replacements.userId = userId;
    } else if (driverId) {
        driverCondition = 'FIND_IN_SET(:driverId, i.driver_id)';
        replacements.driverId = driverId;
    } else {
        // Optional: handle the case where driverId is null and user is not installer
        return false;
    }

    const query = `
        SELECT 
            i.*, 
            cop_specific.product_name as build_product_name,
            cop_specific.product_notes AS product_notes,
            CASE 
                WHEN i.product_id IS NOT NULL THEN ps.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,
            ct.name as installation_temp_name, 
            pr.name AS customers_name, 
            p.project_type,
            pr.phone AS customers_phone, 
            CONCAT(pr.address, ', ', pr.city, ', ', st.name, ', ', pr.zip_code) AS customers_address,
            CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) as implementation_manager,
            d.name AS driver_name, 
            r.name AS route_name,
            rq.doc_number AS doc_number,
            rq.syncWithQuickbooks as syncWithQuickbooks,
            rq.est_doc_number as est_doc_number,
            rq.installation_acceptance as installation_acceptance,
            com.name AS community_name,
            p.project_category,
            ra.name AS product_design,
            v.name AS vehicle_name,
            v.model AS vehicle_model,
            v.vehicle_number AS vehicle_number,
            temp.published
        FROM installations i
        LEFT JOIN driver d ON i.driver_id = d.id
        LEFT JOIN users u on u.id = i.team_id
        LEFT JOIN routes r ON i.route_id = r.id
        LEFT JOIN products ps ON ps.id = i.product_id
        LEFT JOIN labour_cost_services lcs ON lcs.id = i.labour_cost_service_id
        LEFT JOIN custom_field_templates ct on ct.id = i.installation_temp_id
        LEFT JOIN requests rq ON rq.id = i.request_id
        LEFT JOIN customer_orders c ON c.request_id = rq.id
        LEFT JOIN partners pr ON pr.id = c.customer_id
        LEFT JOIN states st ON st.id = pr.state_id
        LEFT JOIN projects p ON p.id = i.project_id
        LEFT JOIN community com ON com.id = p.community
        LEFT JOIN vehicles v ON v.id = i.vehicle_id
        LEFT JOIN request_attachments ra ON FIND_IN_SET(i.location_id, ra.location_ids) > 0
            AND ra.request_id = rq.id
            AND ra.approved = 1
            AND ra.status = 1
        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 project_categories pc ON pc.id = p.project_category
        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.status = 2 OR (i.status = 4 AND COALESCE(temp.published, 0) != 1))
            AND ${driverCondition}
            AND i.route_id = :routeId
            AND NOT (i.status = 4 AND i.driver_is_installer != 2)
        GROUP BY i.id
        ORDER BY i.id DESC
    `;

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

    return result.length > 0 ? result : false;
};

// const getAcceptedInstallationsByRouteForApp = async (driverId, routeId, isInstaller, userId, userType) => {
//     let query = '';
//     const replacements = { routeId };

//     if (userType === 1 || userType === 8) {
//         // Admin or Super Admin – fetch all installations for the route
//         query = `
//             SELECT 
//                 i.*, 
//                 ps.name AS product_name, 
//                 ct.name as installation_temp_name, 
//                 pr.name AS customers_name, 
//                 pr.phone AS customers_phone, 
//                 CONCAT(pr.address, ', ', pr.city, ', ', st.name, ', ', pr.zip_code) AS customers_address,
//                 CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) as implementation_manager,
//                 d.name AS driver_name, 
//                 r.name AS route_name,
//                 temp.published
//             FROM installations i
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN users u on u.id = i.team_id
//             LEFT JOIN routes r ON i.route_id = r.id
//             LEFT JOIN products ps ON ps.id = i.product_id
//             LEFT JOIN custom_field_templates ct on ct.id = i.installation_temp_id
//             LEFT JOIN requests rq ON rq.id = i.request_id
//             LEFT JOIN customer_orders c ON c.request_id = rq.id
//             LEFT JOIN partners pr ON pr.id = c.customer_id
//             LEFT JOIN states st ON st.id = pr.state_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
//             WHERE 
//                 (i.status = 2 OR (i.status = 4 AND COALESCE(temp.published, 0) != 1))
//                 AND i.route_id = :routeId
//                 AND NOT (i.status = 4 AND i.driver_is_installer != 2)
//                 AND i.complete_status = 1
//             ORDER BY i.id DESC
//         `;
//     } else if (userType === 19 || userType === 24 || driverId) {
//         // Installer or Driver userType – filter by driver/team
//         let driverCondition = '';
//         if (isInstaller) {
//             driverCondition = 'i.team_id = :userId';
//             replacements.userId = userId;
//         } else if (driverId) {
//             driverCondition = 'FIND_IN_SET(:driverId, i.driver_id)';
//             replacements.driverId = driverId;
//         } else {
//             return false; // Invalid access
//         }

//         query = `
//             SELECT 
//                 i.*, 
//                 ps.name AS product_name, 
//                 ct.name as installation_temp_name, 
//                 pr.name AS customers_name, 
//                 pr.phone AS customers_phone, 
//                 CONCAT(pr.address, ', ', pr.city, ', ', st.name, ', ', pr.zip_code) AS customers_address,
//                 CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) as implementation_manager,
//                 d.name AS driver_name, 
//                 r.name AS route_name,
//                 temp.published
//             FROM installations i
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN users u on u.id = i.team_id
//             LEFT JOIN routes r ON i.route_id = r.id
//             LEFT JOIN products ps ON ps.id = i.product_id
//             LEFT JOIN custom_field_templates ct on ct.id = i.installation_temp_id
//             LEFT JOIN requests rq ON rq.id = i.request_id
//             LEFT JOIN customer_orders c ON c.request_id = rq.id
//             LEFT JOIN partners pr ON pr.id = c.customer_id
//             LEFT JOIN states st ON st.id = pr.state_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
//             WHERE 
//                 (i.status = 2 OR (i.status = 4 AND COALESCE(temp.published, 0) != 1))
//                 AND ${driverCondition}
//                 AND i.route_id = :routeId
//                 AND NOT (i.status = 4 AND i.driver_is_installer != 2)
//                 AND i.complete_status = 1
//             ORDER BY i.id DESC
//         `;
//     } else {
//         query = `
//             SELECT 
//                 i.*, 
//                 ps.name AS product_name, 
//                 ct.name as installation_temp_name, 
//                 pr.name AS customers_name, 
//                 pr.phone AS customers_phone, 
//                 CONCAT(pr.address, ', ', pr.city, ', ', st.name, ', ', pr.zip_code) AS customers_address,
//                 CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) as implementation_manager,
//                 d.name AS driver_name, 
//                 r.name AS route_name,
//                 temp.published
//             FROM installations i  
//             INNER JOIN project_members pm ON pm.project_id = i.project_id
//             INNER JOIN staff_members sm ON sm.id = pm.staff_id
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN users u on u.id = i.team_id
//             LEFT JOIN routes r ON i.route_id = r.id
//             LEFT JOIN products ps ON ps.id = i.product_id
//             LEFT JOIN custom_field_templates ct on ct.id = i.installation_temp_id
//             LEFT JOIN requests rq ON rq.id = i.request_id
//             LEFT JOIN customer_orders c ON c.request_id = rq.id
//             LEFT JOIN partners pr ON pr.id = c.customer_id
//             LEFT JOIN states st ON st.id = pr.state_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
//             WHERE 
//                 (i.status = 2 OR (i.status = 4 AND COALESCE(temp.published, 0) != 1))
//                 AND i.route_id = :routeId
//                 AND NOT (i.status = 4 AND i.driver_is_installer != 2)
//                 AND sm.user_id =${userId}
//                 AND i.complete_status = 1
//             ORDER BY i.id DESC
//         `;
//     }
//     const result = await sequelize.query(query, {
//         replacements,
//         type: sequelize.QueryTypes.SELECT
//     });

//     return result.length > 0 ? result : false;
// };

const getDeliveredRoutesByDriver = async(id) => {
    const result = await sequelize.query(`SELECT i.route_id,COUNT(*) as installation_count,d.name AS driver_name,r.name AS route_name,r.unique_id,i.name AS installation_name,i.due_date FROM installations i `
        +` LEFT JOIN driver d ON i.driver_id = d.id `
        +` LEFT JOIN routes r ON i.route_id = r.id `
        +` WHERE (i.status = 3 OR i.status = 4) AND i.driver_id=${id} `
        +` GROUP BY i.route_id, d.name, r.name ORDER BY i.route_id DESC `)
    if(result[0].length>0){
        return result[0]
    }else{
        return false
    }
}
const getDeliveredRoutesForApp = async (id, isInstaller, userId,userType) => {
    // Determine filter condition based on user type
    const filterCondition = isInstaller
        ? 'i.team_id = :userId'
        : 'i.driver_id = :id';

    // SQL query with dynamic condition
    const query = `
        SELECT 
            i.route_id,
            COUNT(*) as installation_count,
            d.name AS driver_name,
            r.name AS route_name,
            r.unique_id,
            i.name AS installation_name,
            i.due_date,temp.published
        FROM installations i
        LEFT JOIN driver d ON i.driver_id = d.id
        LEFT JOIN routes r ON i.route_id = r.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
        WHERE 
            (i.status = 3 OR (i.status = 4 AND COALESCE(temp.published, 0) = 1))
            AND ${filterCondition}
        GROUP BY i.route_id, d.name, r.name
        ORDER BY i.route_id DESC
    `;

    // Use correct replacements
    const replacements = isInstaller ? { userId } : { id };

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

    return result.length > 0 ? result : false;
};


// const getDeliveredRoutesForApp = async (id, isInstaller, userId, userType) => {
//     let query = '';
//     let replacements = {};

//     // Check userType and build the appropriate query
//     if (userType === 19 || userType === 24) {
//         // Original logic for installers and drivers
//         const filterCondition = isInstaller
//             ? 'i.team_id = :userId'
//             : 'i.driver_id = :id';

//         query = `
//             SELECT 
//                 i.route_id,
//                 COUNT(*) as installation_count,
//                 d.name AS driver_name,
//                 r.name AS route_name,
//                 r.unique_id,
//                 i.name AS installation_name,
//                 i.due_date
//             FROM installations i
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN routes r ON i.route_id = r.id
//             WHERE i.complete_status = 1 AND
//                 (i.status = 3 OR i.status = 4)
//                 AND ${filterCondition}
//             GROUP BY i.route_id, d.name, r.name
//             ORDER BY i.route_id DESC
//         `;

//         replacements = isInstaller ? { userId } : { id };

//     } else if (userType === 1 || userType === 8) {
//         // Admin/Manager logic (no specific user filter)
//         query = `
//             SELECT 
//                 i.route_id,
//                 COUNT(*) as installation_count,
//                 d.name AS driver_name,
//                 r.name AS route_name,
//                 r.unique_id,
//                 i.name AS installation_name,
//                 i.due_date
//             FROM installations i
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN routes r ON i.route_id = r.id
//             WHERE i.complete_status =1 AND
//                 (i.status = 3 OR i.status = 4)
//             GROUP BY i.route_id, r.name
//             ORDER BY i.route_id DESC
//         `;
//     } else {
//           query = `
//            SELECT 
//         i.route_id,
//         COUNT(*) as installation_count,
//         d.name AS driver_name,
//         r.name AS route_name,
//         r.unique_id,
//         i.name AS installation_name,
//         i.due_date
//     FROM installations i
//     INNER JOIN project_members pm ON pm.project_id = i.project_id
//     INNER JOIN staff_members sm ON sm.id = pm.staff_id
//     LEFT JOIN driver d ON i.driver_id = d.id
//     LEFT JOIN routes r ON i.route_id = r.id
//     WHERE i.complete_status = 1
//       AND (i.status = 3 OR i.status = 4)
//       AND sm.user_id = ${userId}
//     GROUP BY i.route_id,r.name
//     ORDER BY i.route_id DESC
//         `;
//     }

//     // Execute the query
//     const result = await sequelize.query(query, {
//         replacements,
//         type: sequelize.QueryTypes.SELECT
//     });

//     return result.length > 0 ? result : false;
// };


const getDeliveredInstallationsByRoute = async(driverId,routeId) => {
    const result = await sequelize.query(`SELECT i.*,ps.name AS product_name,ct.name as installation_temp_name,pr.name AS customers_name,pr.phone AS customers_phone, concat(pr.address,', ',pr.city,', ',st.name,', ',pr.zip_code) AS customers_address,concat(u.first_name,' ',coalesce(u.last_name,'')) as implementation_manager,d.name AS driver_name,r.name AS route_name FROM installations i `
        +` LEFT JOIN driver d ON i.driver_id = d.id `
        +` LEFT JOIN users u on u.id=i.team_id`
        +` LEFT JOIN routes r ON i.route_id = r.id `
        +` LEFT JOIN requests rq ON rq.id = i.request_id`
        +` LEFT JOIN custom_field_templates ct on ct.id = i.installation_temp_id`
        +` LEFT JOIN products ps ON ps.id = i.product_id`
        +` LEFT JOIN customer_orders c ON c.request_id = rq.id`
        +` LEFT JOIN partners pr ON pr.id = c.customer_id`
        +` LEFT JOIN states st ON st.id = pr.state_id`
        +` WHERE (i.status = 3 OR i.status = 4) AND i.driver_id=${driverId} AND i.route_id=${routeId} `
        +` ORDER BY i.id DESC `)
    if(result[0].length>0){
        return result[0]
    }else{
        return false
    }
}

const getDeliveredInstallationsByRouteforApp = async (driverId, routeId, isInstaller, userId,userType) => {
    const condition = isInstaller
        ? 'i.team_id = :userId'
        : 'i.driver_id = :driverId';

    const query = `
        SELECT 
            i.*,
            ps.name AS product_name,
            ct.name AS installation_temp_name,
            pr.name AS customers_name,
            pr.phone AS customers_phone,
            CONCAT(pr.address, ', ', pr.city, ', ', st.name, ', ', pr.zip_code) AS customers_address,
            CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) AS implementation_manager,
            d.name AS driver_name,
            r.name AS route_name,temp.published
        FROM installations i
        LEFT JOIN driver d ON i.driver_id = d.id
        LEFT JOIN users u ON u.id = i.team_id
        LEFT JOIN routes r ON i.route_id = r.id
        LEFT JOIN requests rq ON rq.id = i.request_id
        LEFT JOIN custom_field_templates ct ON ct.id = i.installation_temp_id
        LEFT JOIN products ps ON ps.id = i.product_id
        LEFT JOIN customer_orders c ON c.request_id = rq.id
        LEFT JOIN partners pr ON pr.id = c.customer_id
        LEFT JOIN states st ON st.id = pr.state_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
        WHERE i.complete_status = 1 AND
            (i.status = 3 OR (i.status = 4 AND COALESCE(temp.published, 0) = 1))
            AND ${condition}
            AND i.route_id = :routeId
        ORDER BY i.id DESC
    `;

    const replacements = {
        routeId,
        ...(isInstaller ? { userId } : { driverId })
    };

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

    return result.length > 0 ? result : false;
};


// const getDeliveredInstallationsByRouteforApp = async (driverId, routeId, isInstaller, userId, userType) => {
//     let query = '';
//     let replacements = { routeId };

//     if (userType === 19 || userType === 24) {
//         const condition = isInstaller
//             ? 'i.team_id = :userId'
//             : 'i.driver_id = :driverId';

//         query = `
//             SELECT 
//                 i.*,
//                 ps.name AS product_name,
//                 ct.name AS installation_temp_name,
//                 pr.name AS customers_name,
//                 pr.phone AS customers_phone,
//                 CONCAT(pr.address, ', ', pr.city, ', ', st.name, ', ', pr.zip_code) AS customers_address,
//                 CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) AS implementation_manager,
//                 d.name AS driver_name,
//                 r.name AS route_name
//             FROM installations i
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN users u ON u.id = i.team_id
//             LEFT JOIN routes r ON i.route_id = r.id
//             LEFT JOIN requests rq ON rq.id = i.request_id
//             LEFT JOIN custom_field_templates ct ON ct.id = i.installation_temp_id
//             LEFT JOIN products ps ON ps.id = i.product_id
//             LEFT JOIN customer_orders c ON c.request_id = rq.id
//             LEFT JOIN partners pr ON pr.id = c.customer_id
//             LEFT JOIN states st ON st.id = pr.state_id
//             WHERE i.complete_status = 1
//               AND (i.status = 3 OR i.status = 4)
//               AND ${condition}
//               AND i.route_id = :routeId
//             ORDER BY i.id DESC
//         `;

//         Object.assign(replacements, isInstaller ? { userId } : { driverId });

//     } else if (userType === 1 || userType === 8) {
//         // Admin/manager version (no team_id or driver_id filter)
//         query = `
//             SELECT 
//                 i.*,
//                 ps.name AS product_name,
//                 ct.name AS installation_temp_name,
//                 pr.name AS customers_name,
//                 pr.phone AS customers_phone,
//                 CONCAT(pr.address, ', ', pr.city, ', ', st.name, ', ', pr.zip_code) AS customers_address,
//                 CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) AS implementation_manager,
//                 d.name AS driver_name,
//                 r.name AS route_name
//             FROM installations i
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN users u ON u.id = i.team_id
//             LEFT JOIN routes r ON i.route_id = r.id
//             LEFT JOIN requests rq ON rq.id = i.request_id
//             LEFT JOIN custom_field_templates ct ON ct.id = i.installation_temp_id
//             LEFT JOIN products ps ON ps.id = i.product_id
//             LEFT JOIN customer_orders c ON c.request_id = rq.id
//             LEFT JOIN partners pr ON pr.id = c.customer_id
//             LEFT JOIN states st ON st.id = pr.state_id
//             WHERE i.complete_status = 1
//               AND (i.status = 3 OR i.status = 4)
//               AND i.route_id = :routeId
//             ORDER BY i.id DESC
//         `;
//     } else {
//        query = `
//             SELECT 
//                 i.*,
//                 ps.name AS product_name,
//                 ct.name AS installation_temp_name,
//                 pr.name AS customers_name,
//                 pr.phone AS customers_phone,
//                 CONCAT(pr.address, ', ', pr.city, ', ', st.name, ', ', pr.zip_code) AS customers_address,
//                 CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) AS implementation_manager,
//                 d.name AS driver_name,
//                 r.name AS route_name
//             FROM installations i
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN users u ON u.id = i.team_id
//             LEFT JOIN routes r ON i.route_id = r.id
//             LEFT JOIN requests rq ON rq.id = i.request_id
//             LEFT JOIN custom_field_templates ct ON ct.id = i.installation_temp_id
//             LEFT JOIN products ps ON ps.id = i.product_id
//             LEFT JOIN customer_orders c ON c.request_id = rq.id
//             LEFT JOIN partners pr ON pr.id = c.customer_id
//             LEFT JOIN states st ON st.id = pr.state_id
//             INNER JOIN project_members pm ON pm.project_id = i.project_id
//             INNER JOIN staff_members sm ON sm.id = pm.staff_id
//             WHERE i.complete_status = 1
//               AND (i.status = 3 OR i.status = 4)
//                AND sm.user_id =${userId}
//               AND i.route_id = ${routeId}
//             ORDER BY i.id DESC
//         `;
//     }

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

//     return result.length > 0 ? result : false;
// };


const getFailedRoutesByDriver = async(id) => {
    const result = await sequelize.query(`SELECT i.route_id,COUNT(*) as installation_count,d.name AS driver_name,r.name AS route_name,r.unique_id,i.name AS installation_name,i.due_date FROM installations i `
        +` LEFT JOIN driver d ON i.driver_id = d.id `
        +` LEFT JOIN routes r ON i.route_id = r.id `
        +` WHERE i.status=5 AND i.driver_id=${id} `
        +` GROUP BY i.route_id, d.name, r.name ORDER BY i.route_id DESC `)
    if(result[0].length>0){
        return result[0]
    }else{
        return false
    }
}
const getFailedRoutesforApp = async (id, isInstaller, userId) => {
    const condition = isInstaller
        ? 'i.team_id = :userId'
        : 'i.driver_id = :id';

    const query = `
        SELECT 
            i.route_id,
            COUNT(*) AS installation_count,
            d.name AS driver_name,
            r.name AS route_name,
            r.unique_id,
            i.name AS installation_name,
            i.due_date
        FROM installations i
        LEFT JOIN driver d ON i.driver_id = d.id
        LEFT JOIN routes r ON i.route_id = r.id
        WHERE 
            i.status = 5
            AND ${condition}
        GROUP BY i.route_id, d.name, r.name
        ORDER BY i.route_id DESC
    `;

    const replacements = isInstaller ? { userId } : { id };

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

    return result.length > 0 ? result : false;
};


// const getFailedRoutesforApp = async (id, isInstaller, userId, userType) => {
//     let query = '';
//     let replacements = {};

//     if (userType === 19 || userType === 24 || id) {
//         const condition = isInstaller
//             ? 'i.team_id = :userId'
//             : 'FIND_IN_SET(:id, i.driver_id)';

//         query = `
//             SELECT 
//                 i.route_id,
//                 COUNT(*) AS installation_count,
//                 d.name AS driver_name,
//                 r.name AS route_name,
//                 r.unique_id,
//                 i.name AS installation_name,
//                 i.due_date
//             FROM installations i
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN routes r ON i.route_id = r.id
//             WHERE 
//                 i.status = 5
//                 AND ${condition}
//             GROUP BY i.route_id, d.name, r.name
//             ORDER BY i.route_id DESC
//         `;

//         replacements = isInstaller ? { userId } : { id };

//     } else if (userType === 1 || userType === 8) {
//         // Admin/SuperAdmin – get all failed installations
//         query = `
//             SELECT 
//                 i.route_id,
//                 COUNT(*) AS installation_count,
//                 d.name AS driver_name,
//                 r.name AS route_name,
//                 r.unique_id,
//                 i.name AS installation_name,
//                 i.due_date
//             FROM installations i
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN routes r ON i.route_id = r.id
//             WHERE 
//                 i.status = 5
//             GROUP BY i.route_id,r.name
//             ORDER BY i.route_id DESC
//         `;

//     } else {
//         // Team member – filter via project_members and staff_members
//         query = `
//             SELECT 
//                 i.route_id,
//                 COUNT(*) AS installation_count,
//                 d.name AS driver_name,
//                 r.name AS route_name,
//                 r.unique_id,
//                 i.name AS installation_name,
//                 i.due_date
//             FROM installations i
//             INNER JOIN project_members pm ON pm.project_id = i.project_id
//             INNER JOIN staff_members sm ON sm.id = pm.staff_id
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN routes r ON i.route_id = r.id
//             WHERE 
//                 i.status = 5
//                 AND sm.user_id = :userId
//             GROUP BY i.route_id, r.name
//             ORDER BY i.route_id DESC
//         `;

//         replacements = { userId };
//     }

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

//     return result.length > 0 ? result : false;
// };


const getFailedInstallationsByRoute = async(driverId,routeId) => {
    const result = await sequelize.query(`SELECT i.*,ps.name AS product_name,ct.name as installation_temp_name,pr.name AS customers_name,pr.phone AS customers_phone, concat(pr.address,', ',pr.city,', ',st.name,', ',pr.zip_code) AS customers_address,concat(u.first_name,' ',coalesce(u.last_name,'')) as implementation_manager,d.name AS driver_name,r.name AS route_name FROM installations i `
        +` LEFT JOIN driver d ON i.driver_id = d.id `
        +` LEFT JOIN users u on u.id=i.team_id`
        +` LEFT JOIN routes r ON i.route_id = r.id `
        +` LEFT JOIN requests rq ON rq.id = i.request_id`
        +` LEFT JOIN products ps ON ps.id = i.product_id`
        +` LEFT JOIN custom_field_templates ct on ct.id = i.installation_temp_id`
        +` LEFT JOIN customer_orders c ON c.request_id = rq.id`
        +` LEFT JOIN partners pr ON pr.id = c.customer_id`
        +` LEFT JOIN states st ON st.id = pr.state_id`
        +` WHERE i.status=5 AND i.driver_id=${driverId} AND i.route_id=${routeId} `
        +` ORDER BY i.id DESC `)
    if(result[0].length>0){
        return result[0]
    }else{
        return false
    }
}

const getFailedInstallationsByRouteForApp = async (driverId, routeId, isInstaller, userId) => {
    const condition = isInstaller
        ? 'i.team_id = :userId'
        : 'i.driver_id = :driverId';

    const query = `
        SELECT 
            i.*,
            ps.name AS product_name,
            ct.name AS installation_temp_name,
            pr.name AS customers_name,
            pr.phone AS customers_phone,
            CONCAT(pr.address, ', ', pr.city, ', ', st.name, ', ', pr.zip_code) AS customers_address,
            CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) AS implementation_manager,
            d.name AS driver_name,
            r.name AS route_name
        FROM installations i
        LEFT JOIN driver d ON i.driver_id = d.id
        LEFT JOIN users u ON u.id = i.team_id
        LEFT JOIN routes r ON i.route_id = r.id
        LEFT JOIN requests rq ON rq.id = i.request_id
        LEFT JOIN products ps ON ps.id = i.product_id
        LEFT JOIN custom_field_templates ct ON ct.id = i.installation_temp_id
        LEFT JOIN customer_orders c ON c.request_id = rq.id
        LEFT JOIN partners pr ON pr.id = c.customer_id
        LEFT JOIN states st ON st.id = pr.state_id
        WHERE 
            i.status = 5
            AND ${condition}
            AND i.route_id = :routeId
        ORDER BY i.id DESC
    `;

    const replacements = {
        routeId,
        ...(isInstaller ? { userId } : { driverId })
    };

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

    return result.length > 0 ? result : false;
};


// const getFailedInstallationsByRouteForApp = async (driverId, routeId, isInstaller, userId, userType) => {
//     let query = '';
//     const replacements = { routeId };

//     if (userType === 1 || userType === 8) {
//         // Admin or Super Admin – fetch all failed installations for the route
//         query = `
//             SELECT 
//                 i.*,
//                 ps.name AS product_name,
//                 ct.name AS installation_temp_name,
//                 pr.name AS customers_name,
//                 pr.phone AS customers_phone,
//                 CONCAT(pr.address, ', ', pr.city, ', ', st.name, ', ', pr.zip_code) AS customers_address,
//                 CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) AS implementation_manager,
//                 d.name AS driver_name,
//                 r.name AS route_name
//             FROM installations i
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN users u ON u.id = i.team_id
//             LEFT JOIN routes r ON i.route_id = r.id
//             LEFT JOIN requests rq ON rq.id = i.request_id
//             LEFT JOIN products ps ON ps.id = i.product_id
//             LEFT JOIN custom_field_templates ct ON ct.id = i.installation_temp_id
//             LEFT JOIN customer_orders c ON c.request_id = rq.id
//             LEFT JOIN partners pr ON pr.id = c.customer_id
//             LEFT JOIN states st ON st.id = pr.state_id
//             WHERE 
//                 i.status = 5
//                 AND i.route_id = :routeId
//             ORDER BY i.id DESC
//         `;

//     } else if (userType === 19 || userType === 24 ) {
//         // Installer or Driver – restrict by team_id or driver_id
//         const condition = isInstaller
//             ? 'i.team_id = :userId'
//             : 'FIND_IN_SET(:driverId, i.driver_id)';
        
//         if (isInstaller) {
//             replacements.userId = userId;
//         } else {
//             replacements.driverId = driverId;
//         }

//         query = `
//             SELECT 
//                 i.*,
//                 ps.name AS product_name,
//                 ct.name AS installation_temp_name,
//                 pr.name AS customers_name,
//                 pr.phone AS customers_phone,
//                 CONCAT(pr.address, ', ', pr.city, ', ', st.name, ', ', pr.zip_code) AS customers_address,
//                 CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) AS implementation_manager,
//                 d.name AS driver_name,
//                 r.name AS route_name
//             FROM installations i
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN users u ON u.id = i.team_id
//             LEFT JOIN routes r ON i.route_id = r.id
//             LEFT JOIN requests rq ON rq.id = i.request_id
//             LEFT JOIN products ps ON ps.id = i.product_id
//             LEFT JOIN custom_field_templates ct ON ct.id = i.installation_temp_id
//             LEFT JOIN customer_orders c ON c.request_id = rq.id
//             LEFT JOIN partners pr ON pr.id = c.customer_id
//             LEFT JOIN states st ON st.id = pr.state_id
//             WHERE 
//                 i.status = 5
//                 AND ${condition}
//                 AND i.route_id = :routeId
//             ORDER BY i.id DESC
//         `;

//     } else {
//         // Team members – via project_members and staff_members
//         query = `
//             SELECT 
//                 i.*,
//                 ps.name AS product_name,
//                 ct.name AS installation_temp_name,
//                 pr.name AS customers_name,
//                 pr.phone AS customers_phone,
//                 CONCAT(pr.address, ', ', pr.city, ', ', st.name, ', ', pr.zip_code) AS customers_address,
//                 CONCAT(u.first_name, ' ', COALESCE(u.last_name, '')) AS implementation_manager,
//                 d.name AS driver_name,
//                 r.name AS route_name
//             FROM installations i
//             INNER JOIN project_members pm ON pm.project_id = i.project_id
//             INNER JOIN staff_members sm ON sm.id = pm.staff_id
//             LEFT JOIN driver d ON i.driver_id = d.id
//             LEFT JOIN users u ON u.id = i.team_id
//             LEFT JOIN routes r ON i.route_id = r.id
//             LEFT JOIN requests rq ON rq.id = i.request_id
//             LEFT JOIN products ps ON ps.id = i.product_id
//             LEFT JOIN custom_field_templates ct ON ct.id = i.installation_temp_id
//             LEFT JOIN customer_orders c ON c.request_id = rq.id
//             LEFT JOIN partners pr ON pr.id = c.customer_id
//             LEFT JOIN states st ON st.id = pr.state_id
//             WHERE 
//                 i.status = 5
//                 AND i.route_id = ${routeId}
//                 AND sm.user_id = ${userId}
//             ORDER BY i.id DESC
//         `;

       
//     }

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

//     return result.length > 0 ? result : false;
// };


const updateDriverPassword = async (driverId, hashedPassword,adminPassword) => {
    try {
      // Validate inputs
      if (!driverId) {
        throw new Error("Driver ID and new password are required.");
      }
  
      // SQL query to update the password
      const query = `
      UPDATE users
      JOIN driver ON users.id = driver.user_id
      SET 
        users.password = :hashedPassword,
        users.admin_password = :adminPassword
      WHERE driver.id = :driverId
    `;
  
      // Execute the query with replacements
      const [results] = await sequelize.query(query, {
        replacements: { driverId, hashedPassword,adminPassword  },
      });
  
      if (results.affectedRows > 0) {
        return true
      } else {
        return false
      }
    } catch (error) {
      
      throw new Error(error.message || "Failed to update password.");
    }
  };


  const getDriverHourlyRate = async (userId) => {
  try {
    // Raw SQL - returns an array of plain objects
    const [driver] = await sequelize.query(
      `SELECT d.hourly_rate
       FROM   driver d
       WHERE  d.user_id = :userId
       LIMIT  1`,
      {
        replacements: { userId },
        type: Sequelize.QueryTypes.SELECT,   // gives plain JS objects
      }
    );

    return driver ? driver.hourly_rate : null; // null if not found
  } catch (error) {
    console.error('getDriverHourlyRate error:', error);
    return null;
  }
};
  

module.exports={
    addDriver,
    updateDriver,
    getAllDrivers,getAllfilterdDrivers,
    getDriverById,
    getDriverByuserId,
    getAllActiveDrivers,
    getAllVerifiedActiveDrivers,
    getAssignedRoutesByDriver,
    getAssignedInstallationsByRoute,
    getAcceptedRoutesByDriver,
    getAcceptedInstallationsByRoute,
    getDeliveredRoutesByDriver,getDeliveredInstallationsByRoute,
    getFailedRoutesByDriver,getFailedInstallationsByRoute,
    updateDriverPassword,
    getAcceptedRoutes,getAcceptedInstallationsByRouteForApp,getDeliveredRoutesForApp,
    getDeliveredInstallationsByRouteforApp,getFailedRoutesforApp,getFailedInstallationsByRouteForApp,
    getUserDataById,getDriverHourlyRate
}