const sequelize = require("../config/dbconnection")
const Sequelize = require('sequelize')
const fs = require('node:fs');
const util = require('node:util');
const { pipeline } = require('node:stream');
const { empty } = require("../Helpers/Common");
const Request=require("../models/request")
const pump = util.promisify(pipeline);
const { Op } = require('sequelize');

const ProjectTasks = sequelize.define('project_tasks', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    project_id: { type: Sequelize.INTEGER, allowNull: false, },
    project_task_uid: { type: Sequelize.STRING, allowNull: false, },
    name: { type: Sequelize.STRING, allowNull: false },
    task_uid: { type: Sequelize.INTEGER, allowNull: false, },
    description: { type: Sequelize.STRING, allowNull: false },
    is_manually_added:{ type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
    start_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    due_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    cover_image: { type: Sequelize.STRING, allowNull: true,defaultValue:null },
    product_id: { type: Sequelize.INTEGER, allowNull: true,defaultValue:null },
    service_id: { type: Sequelize.INTEGER, allowNull: true,defaultValue:null },
    reporter_id: { type: Sequelize.INTEGER, allowNull: false,defaultValue:1 },
    priority: { type: Sequelize.TINYINT, allowNull: false, },
    status: { type: Sequelize.TINYINT, allowNull: false },
    userid: { type: Sequelize.TINYINT, allowNull: false, },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    complete: { type: Sequelize.TINYINT, allowNull: false, },
    complete_sync: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    from_product: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    order_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue:null },
    customer_order_product_id:{ type: Sequelize.INTEGER, allowNull: true, defaultValue:null },
    request_location_id:{ type: Sequelize.INTEGER, allowNull: true, defaultValue:null },
    facility_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null }
}, { timestamps: false })
const ProjectSubTasks = sequelize.define('project_subtasks', {
    ID: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    task_id: { type: Sequelize.INTEGER, allowNull: false, },
    skill_ids :{type:Sequelize.CHAR,allowNull:true},
    name: { type: Sequelize.STRING, allowNull: false },
    task_uid: { type: Sequelize.INTEGER, allowNull: false, },
    description: { type: Sequelize.STRING, allowNull: false },
    start_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    due_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },

    reporter_id: { type: Sequelize.INTEGER, allowNull: false,defaultValue:1 },
    priority: { type: Sequelize.TINYINT, allowNull: false, },
    status: { type: Sequelize.TINYINT, allowNull: false },
    userid: { type: Sequelize.TINYINT, allowNull: false, },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    complete: { type: Sequelize.TINYINT, allowNull: false, },
    complete_sync: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
}, { timestamps: false })
const ProjectTaskFiles = sequelize.define('project_task_files', {
    ID: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    userid: { type: Sequelize.INTEGER, allowNull: false, },
    task_id: { type: Sequelize.INTEGER, allowNull: false, },
   extension: { type: Sequelize.STRING, allowNull: false,defaultValue:"img" },
   file_size: { type: Sequelize.INTEGER, allowNull: false,defaultValue:"2"  },
   file_type: { type: Sequelize.STRING, allowNull: false,defaultValue:"img" },
   file_url: { type: Sequelize.STRING, allowNull: false },
   timestamp: { type: Sequelize.INTEGER, allowNull: false },
   upload_file_name: { type: Sequelize.STRING, allowNull: false,defaultValue:"img"  },
},{timestamps:false})
const ProjectFiles = sequelize.define('project_files', {
    ID: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    userid: { type: Sequelize.INTEGER, allowNull: false, },
   project_id: { type: Sequelize.INTEGER, allowNull: false, },
   extension: { type: Sequelize.STRING, allowNull: false,defaultValue:"img" },
   file_size: { type: Sequelize.INTEGER, allowNull: false,defaultValue:"2"  },
   file_type: { type: Sequelize.STRING, allowNull: false,defaultValue:"img" },
   file_url: { type: Sequelize.STRING, allowNull: false },
   timestamp: { type: Sequelize.INTEGER, allowNull: false },
   upload_file_name: { type: Sequelize.STRING, allowNull: false,defaultValue:"img"  },
},{timestamps:false})

const staffTaskTimelogs = sequelize.define('staff_task_timelogs',{
    id: {type: Sequelize.INTEGER, allowNull: false,primaryKey:true ,autoIncrement:true},
    user_id: {type: Sequelize.INTEGER, allowNull: false},
    job_id: {type: Sequelize.INTEGER, allowNull: true, defaultValue:null},
    task_id: {type: Sequelize.INTEGER, allowNull: true, defaultValue:null},
    service_id: {type: Sequelize.INTEGER, allowNull: true, defaultValue:null},
    sub_task_id: {type: Sequelize.STRING, allowNull: true, defaultValue:null},
    installation_id: {type: Sequelize.STRING, allowNull: true, defaultValue:null},
    start_time: {type: Sequelize.INTEGER, allowNull: false},
    end_time: {type: Sequelize.INTEGER, allowNull: true, defaultValue:null},
    status: {type: Sequelize.INTEGER, allowNull: false},
    noSubTask: {type: Sequelize.INTEGER, allowNull: false, defaultValue:0},
    isDriver: {type: Sequelize.INTEGER, allowNull: false, defaultValue:0}
},{timestamps:false})

const ProjectSubTaskMembers = sequelize.define('project_subtask_members',{
    id: { type: Sequelize.INTEGER, allowNull: false,primaryKey:true ,autoIncrement:true},
    subtask_id: {type: Sequelize.INTEGER, allowNull: false},
    staff_id: {type: Sequelize.INTEGER, allowNull: true,defaultValue:null},
    partner_id: {type: Sequelize.INTEGER,allowNull: true,defaultValue:null},
    contact_id: {type: Sequelize.INTEGER,allowNull: true,defaultValue:null},
    lead_id: {type: Sequelize.INTEGER,allowNull: true,defaultValue:null},
},{timestamps:false})

const DesignOrderTasks = sequelize.define('design_order_tasks', {
    ID: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    design_order_id: { type: Sequelize.INTEGER, allowNull: false, },
    name: { type: Sequelize.STRING, allowNull: false },
    description: { type: Sequelize.STRING, allowNull: false },
    start_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    due_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    cover_image: { type: Sequelize.STRING, allowNull: true,defaultValue:null },
    product_id: { type: Sequelize.INTEGER, allowNull: true,defaultValue:null },
    service_id: { type: Sequelize.INTEGER, allowNull: true,defaultValue:null },
    reporter_id: { type: Sequelize.INTEGER, allowNull: false,defaultValue:1 },
    priority: { type: Sequelize.TINYINT, allowNull: false, },
    status: { type: Sequelize.TINYINT, allowNull: false },
    userid: { type: Sequelize.TINYINT, allowNull: false, },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    complete: { type: Sequelize.TINYINT, allowNull: false, },
    complete_sync: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    from_product: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
}, { timestamps: false, tableName:'design_order_tasks' })

const Stopwatch = sequelize.define('stopwatch', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    user_id: { type: Sequelize.INTEGER, allowNull: false },
    job_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    subtask_id: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    start_time: { type: Sequelize.INTEGER, allowNull: false },
    end_time: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    status: { type: Sequelize.TINYINT, allowNull: false },
}, { freezeTableName: true, timestamps: false });

const addStopwatch = async (data) => {
    try {
        const results = await Stopwatch.create(data);
        if (results) {
            return results.dataValues.id;
        } else {
            return false;
        }
    } catch (error) {
        console.error("Error adding stopwatch entry: ", error);
        return false;
    }
};

const updateStopwatch = async (id, userId, data) => {
    try {
        const updatedRows = await Stopwatch.update(data, {
            where: {
                id: id,
                user_id: userId
            }
        });
        if (updatedRows && updatedRows[0] > 0) {
            return true;
        } else {
            return false;
        }
    } catch (error) {
        console.error("Error updating stopwatch entry: ", error);
        return false;
    }
};

const deleteStopwatchByStopwatchId = async (stopwatchId, userId) => {
    try {
        const deleted = await Stopwatch.destroy({
            where: {
                id: stopwatchId,
                user_id: userId
            }
        });
        return deleted > 0;
    } catch (error) {
        console.error("Error deleting stopwatch entry by ID: ", error);
        return false;
    }
};
const deleteStopwatchBySubtask = async (subtaskId, userId) => {
    try {
        const deleted = await Stopwatch.destroy({
            where: {
                subtask_id: subtaskId,
                user_id: userId
            }
        });

        return deleted > 0;
    } catch (error) {
        console.error("Error deleting stopwatch entries: ", error);
        return false;
    }
};

const deleteStopwatchByJobId = async (jobId) => {
    try {
        const deleted = await Stopwatch.destroy({
            where: {
                job_id: jobId
            }
        });
        return deleted >= 0;
    } catch (error) {
        console.error("Error deleting stopwatch entries by job:", error);
        return false;
    }
};


const fetchStopwatch = async (userId, subtaskId = null, stopwatchId = null, status = null) => {
    try {
        const whereClause = { user_id: userId };
        if (subtaskId) {
            whereClause.subtask_id = subtaskId;
        }
        if (stopwatchId) {
            whereClause.id = stopwatchId; // Add stopwatchId to where clause if provided
        }
        if (status !== null) {
            whereClause.status = status;
        }
        const results = await Stopwatch.findAll({
            where: whereClause
        });
        if (results.length > 0) {
            return results;
        } else {
            return false;
        }
    } catch (error) {
        console.error("Error fetching stopwatch data: ", error);
        return false;
    }
};

const getTaskUid = async (id) => {
    let query = await sequelize.query(`SELECT coalesce(max(task_uid),0) as count from project_tasks `
        + ` where project_id=${id}`)
    if (query[0].length === 1) {
        return parseInt(query[0][0].count) + 1;
    }
    else {
        return 1;
    }

}

const getSubTaskUid = async (id) => {
    let query = await sequelize.query(`SELECT coalesce(max(task_uid),0) as count from project_subtasks `
    + ` where task_id=${id}`)
    if (query[0].length === 1) {
        return parseInt(query[0][0].count) + 1;
    }
    else {
        return 1;
    }

}

const addProjectTask = async (data) => {

    try {
        let results = await ProjectTasks.create(data)
       if (results) {
            return results.id;
        }
        else {
            
            return false;
        }
        
    }
    catch (error) {
        console.error("Error adding the project tak", error);
        return false;
    }
}
const addDesignOrderTask = async (data) => {
    try {
        let results = await DesignOrderTasks.create(data)
        if (results) {
            return results.id;
        }
        else {
            return false;
        }
    }catch (error) {
        console.error("Error adding the Design Order Task", error);
        return false;
    }
}

const AddMembersToSubTask=async(data)=>{
    try {
        let results = await ProjectSubTaskMembers.create(data)
        if (results) {
            return true
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.error("Error adding the project tak", error);
        return false;
    }
}
const updateProjectTask = async (data, id) => {

    try {
        console.log("#############Project",data);
        console.log("#############Projectid",id);
        const updatedRows = await ProjectTasks.update(data, {
            where: {
                id: id
            },
            returning: true,
        });
        console.log("#############Project",updatedRows);
        if (updatedRows) {
            if (updatedRows[1] > 0) {
                return true;
            }
            else {

                return false;
            }
        }

    }
    catch (error) {
        console.error("Error updating project:", error);
        return false;
    }

}
const updateDesignOrderTask = async (data, id) => {
    try {
        const updatedRows = await DesignOrderTasks.update(data, {
            where: {
                id: id
            },
            returning: true,
        });
        if (updatedRows) {
            if (updatedRows[1] > 0) {
                return true;
            }
            else {
                return false;
            }
        }
    }catch (error) {
        console.error("Error updating project:", error);
        return false;
    }
}
const updateProjectTaskByProjectId = async (data, id) => {

    try {
        console.log("#############Project",data);
        console.log("#############Projectid",id);
        const updatedRows = await ProjectTasks.update(data, {
            where: {
                project_id: id,
                status: {
                    [Sequelize.Op.ne]: 0
                }
                
            },
            returning: true,
        });
        console.log("#############Project",updatedRows);
        if (updatedRows) {
            if (updatedRows[1] > 0) {
                return true;
            }
            else {

                return false;
            }
        }

    }
    catch (error) {
        console.error("Error updating project:", error);
        return false;
    }

}
const updateProjectTaskByProjectIdExcludeCompleted = async (data, id) => {

    try {
        console.log("#############Project",data);
        console.log("#############Projectid",id);
        const updatedRows = await ProjectTasks.update(data, {
            where: {
                project_id: id,
                status: {
                    [Op.ne]: 0,
                    [Op.ne]: 3
                }

            },
            returning: true,
        });
        console.log("#############Project",updatedRows);
        if (updatedRows) {
            if (updatedRows[1] > 0) {
                return true;
            }
            else {

                return false;
            }
        }

    }
    catch (error) {
        console.error("Error updating project:", error);
        return false;
    }

}
const updateProjectSubTaskByProjectId = async (data, projectId) => {
    try {
        console.log("############# Project Data:", data);
        console.log("############# Project ID:", projectId);

        // Step 1: Find all task_ids in ProjectTasks that belong to the specified project_id
        const projectTasks = await ProjectTasks.findAll({
            attributes: ['id'],
            where: {
                project_id: projectId
            }
        });

        // Extract task_ids from the result
        const taskIds = projectTasks.map(task => task.id);

        if (taskIds.length === 0) {
            console.log("No tasks found for the given project ID.");
            return false;
        }

        // Step 2: Update ProjectSubTasks where task_id is in the list of found task_ids
        const [numberOfAffectedRows, affectedRows] = await ProjectSubTasks.update(data, {
            where: {
                task_id: taskIds,
                status: {
                    [Sequelize.Op.ne]: 0
                }
            },
            returning: true,
        });

        console.log("############# Updated Rows:", affectedRows);

        // Return true if any rows were updated
        return numberOfAffectedRows > 0;
    } catch (error) {
        console.error("Error updating project:", error);
        return false;
    }
};

const getProjectTaskById = async (id) => {
    let query = await sequelize.query(`select pt.*,p.request_id,pc.name AS job_category_name,p.project_category, concat(u.first_name,' ',u.last_name) as authorized_by,p.name as project_name,p.short_code from project_tasks pt`
        + ` left join users u on pt.userid=u.id`
        + ` left join projects p on pt.project_id=p.id`
        + ` LEFT JOIN project_categories pc ON pc.id=p.project_category `
        + ` where pt.ID=${id}`
    )
    if (query[0].length === 1) {
        return query[0];
    }
    else {
        return false;
    }
}
const getProjectDataById = async (id) => {
    let query = await sequelize.query(`select p.*, p.id AS project_id from projects p where p.id=${id}`)
    if (query[0].length === 1) {
        return query[0];
    }
    else {
        return false;
    }
}

//previos
// const getAllProjectTasks = async (id, userId, userType,hasPermission) => {
//     console.log("##################Hifiiiiiiiiiiiiiiiiiiiiiiibbbbbbbbbbbbbbi");
//     let query = ''
//     if (userType === 1 || userType === 8 || hasPermission) {
//         query = await sequelize.query(`SELECT DISTINCT pt.*,cp.id AS cop_id,rs.id AS rs_id, cp.map_service AS product_map_service,rs.map_service AS service_map_service,concat(rl.address,' ',rl.city,' ',rl.state,' ',rl.zip) as location,concat(rsl.address,' ',rsl.city,' ',rsl.state,' ',rsl.zip) as service_location, cp.location_quantity,rs.qty AS service_qty,rs.rfp, coalesce(tm.count, 0) as member_count, coalesce(tf.count, 0) as file_count, coalesce(pp.count, 0) as post_count, coalesce(ps.count, 0) as subtask_count, coalesce(pcs.count, 0) as subtask_completed_count, ra.name AS product_design, ra.approved FROM project_tasks pt `
//             + ` LEFT JOIN (select count(ID) as count, task_id from project_task_members group by task_id) tm ON tm.task_id=pt.ID `
//             + ` LEFT JOIN (select count(ID) as count, task_id from project_task_files group by task_id) tf ON tf.task_id=pt.ID `
//             + ` LEFT JOIN (select count(ID) as count, task_id from project_posts where status!=2 group by task_id) pp ON pp.task_id=pt.ID `
//             + ` LEFT JOIN (select count(ID) as count, task_id from project_subtasks where status!=0 group by task_id) ps ON ps.task_id=pt.ID `
//             + ` LEFT JOIN (select count(ID) as count, task_id from project_subtasks where status=3 group by task_id) pcs ON pcs.task_id=pt.ID `
//             + ` LEFT JOIN projects p ON p.id=pt.project_id`
//             + ` LEFT JOIN requests r ON p.request_id = r.id`
//             + ` LEFT JOIN request_attachments ra ON ra.product_id = pt.product_id AND ra.request_id=p.request_id AND ra.approved=1 AND ra.status=1 AND (ra.file_type LIKE 'image%' OR ra.file_type = 'img') `
//             +` left join customer_orders co on co.request_id=r.id `
//             +` LEFT join customer_order_products cp on cp.order_id=co.id AND (cp.product_id = pt.product_id OR (cp.product_id IS NULL AND pt.product_id IS NULL)) and cp.product_index=pt.order_id `
//             +` LEFT join request_services rs on rs.request_id=r.id AND (rs.labour_cost_service_id = pt.service_id OR (rs.labour_cost_service_id IS NULL AND pt.service_id IS NULL)) and rs.product_index=pt.order_id `
//             +`  left join request_locations rl on rl.order_id=cp.id `
//             +`  left join request_service_locations rsl on rsl.service_id=rs.id `
//             + ` WHERE pt.project_id = ${id} AND pt.status !=0 GROUP BY pt.ID ORDER BY pt.ID DESC `)
//     } else {
//         query = await sequelize.query("SELECT pt.*,cp.id AS cop_id,rs.id AS rs_id, cp.map_service AS product_map_service, rs.map_service AS service_map_service, coalesce(tm.count, 0) as member_count, coalesce(tf.count, 0) as file_count,concat(rl.address,' ',rl.city,' ',rl.state,' ',rl.zip) as location,concat(rsl.address,' ',rsl.city,' ',rsl.state,' ',rsl.zip) as service_location,rs.qty AS service_qty, cp.location_quantity, coalesce(pp.count, 0) as post_count, coalesce(ps.count, 0) as subtask_count, coalesce(pcs.count, 0) as subtask_completed_count, ra.name AS product_design, rs.rfp, ra.approved FROM project_tasks pt"
//             + " LEFT JOIN staff_members s1 ON s1.id=pt.reporter_id"
//             + " LEFT JOIN users u1 ON s1.user_id=u1.id "
//             + ` LEFT JOIN (select pm.task_id, COALESCE(u.id,u2.id) as user_id from project_task_members pm left join partners p on p.id=pm.partner_id `
//             + ` left join users u on p.user_id=u.id left join staff_members s on s.id=pm.staff_id left join users u2 on s.user_id=u2.id) pmm ON pmm.task_id=pt.ID and pmm.user_id=${userId}`
//             + ` LEFT JOIN (select count(ID) as count, task_id from project_task_members group by task_id) tm ON tm.task_id=pt.ID `
//             + ` LEFT JOIN (select count(ID) as count, task_id from project_task_files group by task_id) tf ON tf.task_id=pt.ID `
//             + ` LEFT JOIN (select count(ID) as count, task_id from project_posts where status!=2 group by task_id) pp ON pp.task_id=pt.ID `
//             + ` LEFT JOIN (select count(ID) as count, task_id from project_subtasks where status!=0 group by task_id) ps ON ps.task_id=pt.ID `
//             + ` LEFT JOIN (select count(ID) as count, task_id from project_subtasks where status=3 group by task_id) pcs ON pcs.task_id=pt.ID `
//             // + ` WHERE (u1.id = ${userId} or pmm.user_id = ${userId}) AND pt.project_id = ${id} AND pt.status !=0 ORDER BY pt.ID DESC`)
//             + ` LEFT JOIN projects p ON p.id=pt.project_id`
//             + ` LEFT JOIN requests r ON p.request_id = r.id`
//             + ` LEFT JOIN request_attachments ra ON ra.product_id = pt.product_id AND ra.request_id=p.request_id AND (ra.file_type LIKE 'image%' OR ra.file_type = 'img')`
//             +` left join customer_orders co on co.request_id=r.id `
//             +` LEFT join customer_order_products cp on cp.order_id=co.id AND (cp.product_id = pt.product_id OR (cp.product_id IS NULL AND pt.product_id IS NULL)) and cp.product_index=pt.order_id `
//             +` LEFT join request_services rs on rs.request_id=r.id AND (rs.labour_cost_service_id = pt.service_id OR (rs.labour_cost_service_id IS NULL AND pt.service_id IS NULL)) and rs.product_index=pt.order_id `
//             +`  left join request_locations rl on rl.order_id=cp.id `
//             +`  left join request_service_locations rsl on rsl.service_id=rs.id `
//             + ` WHERE  pt.project_id = ${id} AND pt.status !=0 GROUP BY pt.ID ORDER BY pt.ID DESC`)
//     }
//     let data = [];

//     if (query[0]?.length > 0) {
//         data = await Promise.all(query[0]?.map(async (row) => {
//           console.log("#rowrow##########",row);
//           if(row?.cop_id){
//             const queryResult = await sequelize.query(`SELECT * FROM request_locations WHERE order_id = ${row.cop_id}`);
//             row.locations = queryResult[0]?.length > 0 ? queryResult[0] : [];
//           }else {
//             row.locations = [];
//           }
//         // data = query[0];
//         return row;
//         }))
//     }
//     return data;
// }

const getAllProjectTasks = async (id, userId, userType, hasPermission) => {
    console.log("################## Fetching Project Tasks ##################");
    let query = '';

    if (userType === 1 || userType === 8 || hasPermission) {
        query = await sequelize.query(`
            SELECT DISTINCT pt.*, cp.id AS cop_id,prod.inhouse,prod.isTrafficCatalog,
                cp.product_notes AS product_notes, rs.id AS rs_id, cp.isAdditionalProduct, rs.isAdditionalProduct AS service_isAdditionalProduct,cp.is_single_card,
                cp.map_service AS product_map_service, rs.map_service AS service_map_service,lcs.avatarUrl,lcs.workflow AS service_workflow,prod.name as productName,
                concat(rl.address,' ',rl.city,' ',rl.state,' ',rl.zip) AS location1, 
                concat(rsl.address,' ',rsl.city,' ',rsl.state,' ',rsl.zip) AS service_location1, 
                concat(
                    COALESCE(rl.address, ''), ' ', 
                    COALESCE(rl.city, ''), ' ', 
                    COALESCE(rl.state, ''), ' ', 
                    COALESCE(rl.zip, '')
                ) AS location, 
                concat(
                    COALESCE(rsl.address, ''), ' ', 
                    COALESCE(rsl.city, ''), ' ', 
                    COALESCE(rsl.state, ''), ' ', 
                    COALESCE(rsl.zip, '')
                ) AS service_location, 
                 rs.service_notes,
                cp.location_quantity, rs.qty AS service_qty, rs.rfp, 
                coalesce(tm.count, 0) AS member_count, 
                coalesce(tf.count, 0) AS file_count, 
                coalesce(pp.count, 0) AS post_count, 
                coalesce(ps.count, 0) AS subtask_count, 
                coalesce(pcs.count, 0) AS subtask_completed_count, 
                ra.name AS product_design, ra.approved,cp.delivery_type_id,cp.installer_facility_id,rs.delivery_type_id AS service_delivery_type_id
            FROM project_tasks pt
            LEFT JOIN (SELECT COUNT(ID) AS count, task_id FROM project_task_members GROUP BY task_id) tm ON tm.task_id = pt.ID
            LEFT JOIN (SELECT COUNT(ID) AS count, task_id FROM project_task_files GROUP BY task_id) tf ON tf.task_id = pt.ID
            LEFT JOIN (SELECT COUNT(ID) AS count, task_id FROM project_posts WHERE status != 2 GROUP BY task_id) pp ON pp.task_id = pt.ID
            LEFT JOIN (SELECT COUNT(ID) AS count, task_id FROM project_subtasks WHERE status != 0 GROUP BY task_id) ps ON ps.task_id = pt.ID
            LEFT JOIN (SELECT COUNT(ID) AS count, task_id FROM project_subtasks WHERE status = 3 GROUP BY task_id) pcs ON pcs.task_id = pt.ID
            LEFT JOIN projects p ON p.id = pt.project_id
            LEFT JOIN requests r ON p.request_id = r.id
            LEFT JOIN customer_orders co ON co.request_id = r.id
            LEFT JOIN customer_order_products cp ON cp.order_id = co.id AND cp.product_index = pt.order_id
            LEFT JOIN request_services rs ON rs.request_id = r.id AND 
                (pt.service_id IS NOT NULL AND pt.product_id IS NULL AND rs.id = pt.customer_order_product_id) OR 
                (pt.product_id IS NOT NULL AND rs.product_index = pt.order_id)
            LEFT JOIN request_locations rl ON rl.id = pt.request_location_id
            LEFT JOIN request_service_locations rsl ON rsl.service_id = rs.id
            LEFT JOIN labour_cost_services lcs ON lcs.id = pt.service_id
            LEFT JOIN products prodold ON prodold.id = rs.product_id
            LEFT JOIN products prod ON prod.id = COALESCE(pt.product_id, rs.product_id)
            LEFT JOIN request_attachments ra ON FIND_IN_SET(pt.request_location_id, ra.location_ids) > 0
                AND ra.request_id = p.request_id 
                AND ra.approved = 1 
                AND ra.status = 1
            WHERE pt.project_id = ${id} 
                AND pt.status != 0 
            GROUP BY pt.ID 
            ORDER BY pt.ID DESC
        `);
    } else {
        query = await sequelize.query(`
            SELECT pt.*, cp.id AS cop_id,prod.inhouse,prod.isTrafficCatalog, rs.id AS rs_id, cp.isAdditionalProduct, rs.isAdditionalProduct AS service_isAdditionalProduct,cp.is_single_card,
                cp.map_service AS product_map_service, rs.map_service AS service_map_service,lcs.avatarUrl, lcs.workflow AS service_workflow,
                coalesce(tm.count, 0) AS member_count, 
                coalesce(tf.count, 0) AS file_count, 
                concat(rl.address,' ',rl.city,' ',rl.state,' ',rl.zip) AS location1, 
                concat(rsl.address,' ',rsl.city,' ',rsl.state,' ',rsl.zip) AS service_location1, 
                concat(
                    COALESCE(rl.address, ''), ' ', 
                    COALESCE(rl.city, ''), ' ', 
                    COALESCE(rl.state, ''), ' ', 
                    COALESCE(rl.zip, '')
                ) AS location, 
                concat(
                    COALESCE(rsl.address, ''), ' ', 
                    COALESCE(rsl.city, ''), ' ', 
                    COALESCE(rsl.state, ''), ' ', 
                    COALESCE(rsl.zip, '')
                ) AS service_location, 
                rs.qty AS service_qty, cp.location_quantity, 
                coalesce(pp.count, 0) AS post_count, 
                coalesce(ps.count, 0) AS subtask_count, 
                coalesce(pcs.count, 0) AS subtask_completed_count, 
                ra.name AS product_design, rs.rfp, ra.approved,cp.delivery_type_id,cp.installer_facility_id 
            FROM project_tasks pt
            LEFT JOIN staff_members s1 ON s1.id = pt.reporter_id
            LEFT JOIN users u1 ON s1.user_id = u1.id
            LEFT JOIN (SELECT pm.task_id, COALESCE(u.id,u2.id) AS user_id FROM project_task_members pm 
                LEFT JOIN partners p ON p.id = pm.partner_id 
                LEFT JOIN users u ON p.user_id = u.id 
                LEFT JOIN staff_members s ON s.id = pm.staff_id 
                LEFT JOIN users u2 ON s.user_id = u2.id) pmm ON pmm.task_id = pt.ID AND pmm.user_id = ${userId}
            LEFT JOIN (SELECT COUNT(ID) AS count, task_id FROM project_task_members GROUP BY task_id) tm ON tm.task_id = pt.ID
            LEFT JOIN (SELECT COUNT(ID) AS count, task_id FROM project_task_files GROUP BY task_id) tf ON tf.task_id = pt.ID
            LEFT JOIN (SELECT COUNT(ID) AS count, task_id FROM project_posts WHERE status != 2 GROUP BY task_id) pp ON pp.task_id = pt.ID
            LEFT JOIN (SELECT COUNT(ID) AS count, task_id FROM project_subtasks WHERE status != 0 GROUP BY task_id) ps ON ps.task_id = pt.ID
            LEFT JOIN (SELECT COUNT(ID) AS count, task_id FROM project_subtasks WHERE status = 3 GROUP BY task_id) pcs ON pcs.task_id = pt.ID
            LEFT JOIN projects p ON p.id = pt.project_id
            LEFT JOIN requests r ON p.request_id = r.id
            LEFT JOIN customer_orders co ON co.request_id = r.id
            LEFT JOIN customer_order_products cp ON cp.order_id = co.id AND cp.product_index = pt.order_id AND cp.order_id = p.request_id
            LEFT JOIN request_services rs ON rs.request_id = r.id AND 
                (pt.service_id IS NOT NULL AND pt.product_id IS NULL AND rs.id = pt.customer_order_product_id) OR 
                (pt.product_id IS NOT NULL AND rs.product_index = pt.order_id)
            LEFT JOIN request_locations rl ON rl.id = pt.request_location_id
            LEFT JOIN request_service_locations rsl ON rsl.service_id = rs.id
            LEFT JOIN labour_cost_services lcs ON lcs.id = rs.labour_cost_service_id
            LEFT JOIN products prodold ON prodold.id = rs.product_id
            LEFT JOIN products prod ON prod.id = COALESCE(pt.product_id, rs.product_id)
            LEFT JOIN request_attachments ra ON FIND_IN_SET(pt.request_location_id, ra.location_ids) > 0
                AND ra.request_id = p.request_id 
                AND (ra.file_type LIKE 'image%' OR ra.file_type = 'img' OR ra.file_type = 'pdf')
            WHERE pt.project_id = ${id} 
                AND pt.status != 0 
            GROUP BY pt.ID 
            ORDER BY pt.ID DESC
        `);
    }

    let data = [];

    if (query[0]?.length > 0) {
        data = await Promise.all(query[0]?.map(async (row) => {
            console.log("#rowrow##########", row);
            if (row?.cop_id) {
                const queryResult = await sequelize.query(`SELECT * FROM request_locations WHERE order_id = ${row.cop_id}`);
                row.locations = queryResult[0]?.length > 0 ? queryResult[0] : [];
            } else {
                row.locations = [];
            }
            
            // Check if any subtasks have status=2 (in progress)
            const subtaskQuery = await sequelize.query(`
                SELECT COUNT(*) as count 
                FROM project_subtasks 
                WHERE task_id = ${row.ID} AND status = 2
            `);
            row.isTaskInprogress = subtaskQuery[0][0]?.count > 0;
            
            return row;
        }));
    }
    return data;
};



const getAllDesignOrderTasks = async (id, userId, userType,hasPermission) => {
    let query = ''
    // if (userType === 1 || userType === 8 || hasPermission) {
        query = await sequelize.query(`SELECT DISTINCT dot.*,
                    do.name AS design_order_name, 
                    do.total_cost, 
                    do.status AS design_order_status, 
                    do.timestamp AS design_order_timestamp,
                    doa.id AS attachment_id, 
                    doa.name AS attachment_name, 
                    doa.file_type AS attachment_file_type, 
                    doa.approved AS attachment_approved, 
                    concat(dost.name, ' ', dost.description) AS sub_task_details,
                    dost.priority AS sub_task_priority, 
                    dost.status AS sub_task_status, 
                    coalesce(tm.count, 0) AS member_count, 
                    coalesce(da.count, 0) AS attachment_count
                FROM design_order_tasks dot
                LEFT JOIN design_orders do ON dot.design_order_id = do.id
                LEFT JOIN design_order_attachments doa ON doa.design_order_id = do.id
                LEFT JOIN design_order_sub_task dost ON dost.design_order_id = do.id AND dost.task_id = dot.id
                LEFT JOIN (
                    SELECT COUNT(id) AS count, task_id 
                    FROM design_order_sub_task 
                    GROUP BY task_id
                ) tm ON tm.task_id = dost.id
                LEFT JOIN (
                    SELECT COUNT(id) AS count, design_order_id 
                    FROM design_order_attachments 
                    GROUP BY design_order_id
                ) da ON da.design_order_id = do.id
                WHERE dot.design_order_id = ${id} AND dot.status !=0 GROUP BY dot.ID ORDER BY dot.ID DESC `)
    // } else {
    //     query = await sequelize.query("SELECT pt.*,cp.id AS cop_id,rs.id AS rs_id, cp.map_service AS product_map_service, rs.map_service AS service_map_service, coalesce(tm.count, 0) as member_count, coalesce(tf.count, 0) as file_count,concat(rl.address,' ',rl.city,' ',rl.state,' ',rl.zip) as location,concat(rsl.address,' ',rsl.city,' ',rsl.state,' ',rsl.zip) as service_location,rs.qty AS service_qty, cp.location_quantity, coalesce(pp.count, 0) as post_count, coalesce(ps.count, 0) as subtask_count, coalesce(pcs.count, 0) as subtask_completed_count, ra.name AS product_design, rs.rfp, ra.approved FROM project_tasks pt"
    //         + " LEFT JOIN staff_members s1 ON s1.id=pt.reporter_id"
    //         + " LEFT JOIN users u1 ON s1.user_id=u1.id "
    //         + ` LEFT JOIN (select pm.task_id, COALESCE(u.id,u2.id) as user_id from project_task_members pm left join partners p on p.id=pm.partner_id `
    //         + ` left join users u on p.user_id=u.id left join staff_members s on s.id=pm.staff_id left join users u2 on s.user_id=u2.id) pmm ON pmm.task_id=pt.ID and pmm.user_id=${userId}`
    //         + ` LEFT JOIN (select count(ID) as count, task_id from project_task_members group by task_id) tm ON tm.task_id=pt.ID `
    //         + ` LEFT JOIN (select count(ID) as count, task_id from project_task_files group by task_id) tf ON tf.task_id=pt.ID `
    //         + ` LEFT JOIN (select count(ID) as count, task_id from project_posts where status!=2 group by task_id) pp ON pp.task_id=pt.ID `
    //         + ` LEFT JOIN (select count(ID) as count, task_id from project_subtasks where status!=0 group by task_id) ps ON ps.task_id=pt.ID `
    //         + ` LEFT JOIN (select count(ID) as count, task_id from project_subtasks where status=3 group by task_id) pcs ON pcs.task_id=pt.ID `
    //         // + ` WHERE (u1.id = ${userId} or pmm.user_id = ${userId}) AND pt.project_id = ${id} AND pt.status !=0 ORDER BY pt.ID DESC`)
    //         + ` LEFT JOIN projects p ON p.id=pt.project_id`
    //         + ` LEFT JOIN requests r ON p.request_id = r.id`
    //         + ` LEFT JOIN request_attachments ra ON ra.product_id = pt.product_id AND ra.request_id=p.request_id AND (ra.file_type LIKE 'image/%' OR ra.file_type = 'img')`
    //         +` left join customer_orders co on co.request_id=r.id `
    //         +` LEFT join customer_order_products cp on cp.order_id=co.id AND (cp.product_id = pt.product_id OR (cp.product_id IS NULL AND pt.product_id IS NULL)) and cp.product_index=pt.order_id `
    //         +` LEFT join request_services rs on rs.request_id=r.id AND (rs.labour_cost_service_id = pt.service_id OR (rs.labour_cost_service_id IS NULL AND pt.service_id IS NULL)) and rs.product_index=pt.order_id `
    //         +`  left join request_locations rl on rl.order_id=cp.id `
    //         +`  left join request_service_locations rsl on rsl.service_id=rs.id `
    //         + ` WHERE  pt.project_id = ${id} AND pt.status !=0 GROUP BY pt.ID ORDER BY pt.ID DESC`)
    // }
    let data = [];

    if (query[0]?.length > 0) {
        data = query[0];
    }
    return data;
}
const getAllLocationsForTSInstallation = async (id, mapService = false, product_id = false, product_index = false) => {
    console.log("################## Start Fetching Locations ##################");
  
    // Use replacements for parameterized queries to prevent SQL injection
    const replacements = { projectId: id };
  
    let locationQuery = `
      SELECT 
        rl.address, 
        rl.city, 
        rl.state, 
        rl.zip, 
        rl.order_id AS cop_id
      FROM request_locations rl
      LEFT JOIN customer_order_products cp ON rl.order_id = cp.id
      LEFT JOIN project_tasks pt ON cp.product_index = pt.order_id
      WHERE pt.project_id = :projectId
      ${mapService ? ' AND cp.map_service = 1 ' : ''}
      ${product_id ? ' AND cp.product_id = :productId ' : ''}
      ${product_index ? ' AND cp.product_index = :productIndex ' : ''}
    `;
  
    if (product_id) replacements.productId = product_id;
    if (product_index) replacements.productIndex = product_index;
  
    try {
      // Fetch locations
      const locations = await sequelize.query(locationQuery, {
        replacements,
        type: sequelize.QueryTypes.SELECT,
      });
  
      return locations;
    } catch (error) {
      console.error("Error fetching locations:", error);
      throw error;
    }
  };
  

  //previos
// const getProjectTaskBytaskId = async (id) => {
//     let query = await sequelize.query(`SELECT pt.*, p.name AS project_name, concat(u.first_name, ' ', u.last_name) as authorized_by, coalesce(tm.count, 0) as member_count, coalesce(tf.count, 0) as file_count, ra.name AS product_design, ra.approved FROM project_tasks pt `
//     + ` LEFT JOIN users u ON pt.userid=u.id `
//     +` LEFT JOIN (select count(ID) as count, task_id from project_task_members group by task_id) tm ON tm.task_id=pt.ID `
//     + ` LEFT JOIN (select count(ID) as count, task_id from project_task_files group by task_id) tf ON tf.task_id=pt.ID `
//     + ` LEFT JOIN projects p ON p.id=pt.project_id`
//     + ` LEFT JOIN requests r ON p.request_id = r.id`
//     + ` LEFT JOIN request_attachments ra ON ra.product_id = pt.product_id AND ra.request_id=p.request_id AND (ra.file_type LIKE 'image%' OR ra.file_type = 'img')`
//     + ` WHERE pt.ID = ${id} AND pt.status !=0 GROUP BY pt.ID ORDER BY pt.ID DESC`)
//     if (query[0].length === 1) {
//         return query[0];
//     }
//     else {
//         return false;
//     }
// }

//new

const getProjectTaskBytaskId = async (id) => {
    let query = await sequelize.query(`
        SELECT pt.*, cp.isAdditionalProduct, rs.isAdditionalProduct AS service_isAdditionalProduct,
               p.name AS project_name,lcs.avatarUrl, lcs.workflow AS service_workflow, prod.recipe_id,
               concat(u.first_name, ' ', u.last_name) AS authorized_by, 
               coalesce(tm.count, 0) AS member_count, 
               coalesce(tf.count, 0) AS file_count, 
               ra.name AS product_design, 
               ra.approved ,
               cp.delivery_type_id,
               cp.installer_facility_id,
               cp.workflow AS customer_order_product_workflow,
               rs.workflow AS request_service_workflow,
               rs.delivery_type_id AS service_delivery_type_id,
               cp.product_notes AS product_notes,
                  rs.service_notes,
               COALESCE(cpp.delivery_type_id,rss.delivery_type_id) AS task_delivery_type 
        FROM project_tasks pt 
        LEFT JOIN users u ON pt.userid = u.id
        LEFT JOIN (SELECT COUNT(ID) AS count, task_id FROM project_task_members GROUP BY task_id) tm ON tm.task_id = pt.ID 
        LEFT JOIN (SELECT COUNT(ID) AS count, task_id FROM project_task_files GROUP BY task_id) tf ON tf.task_id = pt.ID 
        LEFT JOIN projects p ON p.id = pt.project_id 
        LEFT JOIN requests r ON p.request_id = r.id 
        LEFT JOIN customer_orders co ON co.request_id = r.id
        LEFT JOIN customer_order_products cp ON cp.order_id = co.id AND cp.product_index = pt.order_id
        LEFT JOIN request_services rss ON rss.request_id = r.id AND pt.is_manually_added = 1
        LEFT JOIN customer_order_products cpp ON cp.order_id AND pt.is_manually_added = 1
        LEFT JOIN request_services rs ON rs.request_id = r.id AND 
            (pt.service_id IS NOT NULL AND pt.product_id IS NULL AND rs.id = pt.customer_order_product_id) OR 
            (pt.product_id IS NOT NULL AND rs.product_index = pt.order_id)
        LEFT JOIN labour_cost_services lcs ON lcs.id = pt.service_id
        LEFT JOIN products prod ON prod.id = pt.product_id
        LEFT JOIN request_attachments ra ON FIND_IN_SET(pt.request_location_id, ra.location_ids) > 0
            AND ra.request_id = p.request_id
        WHERE pt.ID = ${id} 
            AND pt.status != 0 
        GROUP BY pt.ID 
        ORDER BY pt.ID DESC
    `);

    return query[0].length === 1 ? query[0] : false;
};


const addProjectSubTask = async (data) => {
    try {
        let results = await ProjectSubTasks.create(data)
        console.log("######################ADDProject",results);
        if (results) {
            return results.dataValues.ID;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.error("Error adding the project sub task ", error);
        return false;
    }
}
const getProjectSubTaskById = async (id) => {
    let query = await sequelize.query(`select ps.*, concat(u.first_name,' ',u.last_name) as authorized_by, p.name as project_name,p.short_code,pt.task_uid as mtask_uid,pt.project_id, GROUP_CONCAT(pm.staff_id SEPARATOR ',') AS task_members from project_subtasks ps`
        + ` left join users u on ps.userid=u.id`
        + ` left join project_tasks pt on ps.task_id=pt.ID`
        +` left join project_subtask_members pm on pm.subtask_id=ps.ID`
        + ` left join projects p on pt.project_id=p.id`
        + ` where ps.ID=${id}`
    )
    if (query[0].length === 1) {
        return query[0];
    }
    else {
        return false;
    }
}
const getAllProjectSubTaskBytaskId = async (id, userType, userId) => {
    let query = ""
    console.log("###########UserType",userType);
    if (userType != 1 && userType != 8 && userType !=3) {
        query = await sequelize.query(`SELECT pt.* FROM project_subtasks pt `
            + ` LEFT JOIN staff_members s1 ON s1.id=pt.reporter_id `
            + ` LEFT JOIN users u1 ON s1.user_id=u1.id`
            + ` LEFT JOIN (select pm.subtask_id, COALESCE(u.id,u2.id) as user_id from project_subtask_members pm `
            + ` left join partners p on p.id=pm.partner_id left join users u on p.user_id=u.id left join staff_members s on s.id=pm.staff_id `
            + ` left join users u2 on s.user_id=u2.id) pmm ON pmm.subtask_id=pt.ID and pmm.user_id=${userId}   `
            + ` WHERE (u1.id = ${userId} or pmm.user_id = ${userId} ) AND pt.task_id = ${id} AND pt.status !=0 ORDER BY pt.ID DESC`)
            // + ` WHERE  pt.task_id = ${id} AND pt.status !=0 ORDER BY pt.ID DESC`)
        console.log("############PRoject Sub task");
        }
    else {
        query = await sequelize.query(`SELECT pt.* FROM project_subtasks pt 
      
        WHERE pt.task_id = ${id} AND pt.status !=0 ORDER BY pt.ID DESC`)
    }
    let data = [];

    if (query[0].length > 0) {
        data= await Promise.all(query[0].map(async row=>{
            console.log("################PM",row);
            row.teamMembers=[]
            let query2=await sequelize.query(`select pm.*,u.profile_pic as profile_pic,s.hourly_rate,u.id as userId,concat(s.first_name, ' ', coalesce(s.last_name, '')) as name from project_subtask_members pm  
            left join staff_members s on s.id=pm.staff_id
           left join users u on u.id=s.user_id
            where pm.subtask_id=${row.ID}`)
          if(query2.length>0){
            row.teamMembers=query2[0]
          }
          return row
        }))
        

    }
    return data;
}
const getAllProjectSubTaskBytaskIdWithTeam = async (id, userType, userId,teamId = null) => {
    let query = ""
    console.log("###########UserType",userType);
    if (userType != 1 && userType != 8 && userType !=3) {
        query = await sequelize.query(`SELECT pt.* FROM project_subtasks pt `
            + ` LEFT JOIN staff_members s1 ON s1.id=pt.reporter_id `
            + ` LEFT JOIN users u1 ON s1.user_id=u1.id`
            + ` LEFT JOIN (select pm.subtask_id, COALESCE(u.id,u2.id) as user_id from project_subtask_members pm `
            + ` left join partners p on p.id=pm.partner_id left join users u on p.user_id=u.id left join staff_members s on s.id=pm.staff_id `
            + ` left join users u2 on s.user_id=u2.id) pmm ON pmm.subtask_id=pt.ID and pmm.user_id=${userId}   `
            + ` left join project_subtask_members psm on psm.subtask_id = pt.ID AND psm.staff_id = ${teamId} `
            + ` WHERE (u1.id = ${userId} or pmm.user_id = ${userId} ) AND pt.task_id = ${id} AND pt.status !=0 ORDER BY pt.ID DESC`)
            // + ` WHERE  pt.task_id = ${id} AND pt.status !=0 ORDER BY pt.ID DESC`)
        console.log("############PRoject Sub task");
        }
    else {
        query = await sequelize.query(`SELECT pt.* FROM project_subtasks pt 
            left join project_subtask_members psm on psm.subtask_id = pt.ID AND psm.staff_id = ${teamId}
        WHERE pt.task_id = ${id} AND psm.staff_id = ${teamId} AND pt.status !=0 ORDER BY pt.ID DESC`)
    }
    let data = [];

    if (query[0].length > 0) {
        data= await Promise.all(query[0].map(async row=>{
            console.log("################PM",row);
            row.teamMembers=[]
            let query2=await sequelize.query(`select pm.*,u.profile_pic as profile_pic,s.hourly_rate,u.id as userId,concat(s.first_name, ' ', coalesce(s.last_name, '')) as name from project_subtask_members pm  
            left join staff_members s on s.id=pm.staff_id
           left join users u on u.id=s.user_id
            where pm.subtask_id=${row.ID}`)
          if(query2.length>0){
            row.teamMembers=query2[0]
          }
          return row
        }))
        

    }
    return data;
}
const updateProjectSubTask = async (data, id) => {

    try {
      
        const updatedRows = await ProjectSubTasks.update(data, {
            where: {
                ID: id
            },
            returning: true,
        });
        console.log("##############Update updatedRows",updatedRows);
        if (updatedRows) {
            if (updatedRows[1] > 0) {
                return true;
            }
            else {

                return false;
            }
        }

    }
    catch (error) {
        console.error("Error updating project sub task:", error);
        return false;
    }

}
const updateProjectSubTaskByTaskId = async (data, id) => {

    try {
      
        const updatedRows = await ProjectSubTasks.update(data, {
            where: {
                task_id: id
            },
            returning: true,
        });
        console.log("##############Update updatedRows",updatedRows);
        if (updatedRows) {
            if (updatedRows[1] > 0) {
                return true;
            }
            else {

                return false;
            }
        }

    }
    catch (error) {
        console.error("Error updating project sub task:", error);
        return false;
    }

}
const deleteProjectTask = async (id, status = 0) => {

    try {
        const deleted = await ProjectTasks.destroy({
            where: {
                id: id
            },
        });

        if (deleted) {
            return deleted;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.log("Delete error......", error)
    }
}

const deleteProjectTaskByServiceId = async (id) => {

    try {
        // First, find all project tasks with the given customer_order_product_id
        const projectTasks = await ProjectTasks.findAll({
            where: {
                customer_order_product_id: id
            },
            attributes: ['id']
        });

        if (projectTasks && projectTasks.length > 0) {
            // Get all task IDs
            const taskIds = projectTasks.map(task => task.id);
            
            // Delete all subtasks that belong to these tasks
            const deletedSubtasks = await ProjectSubTasks.destroy({
                where: {
                    task_id: taskIds
                }
            });
            
            console.log(`Deleted ${deletedSubtasks} subtasks for tasks: ${taskIds.join(', ')}`);
            
            // Now delete the main project tasks
            const deletedTasks = await ProjectTasks.destroy({
                where: {
                    customer_order_product_id: id
                },
            });

            console.log(`Deleted ${deletedTasks} project tasks for customer_order_product_id: ${id}`);
            
            return {
                deletedTasks: deletedTasks,
                deletedSubtasks: deletedSubtasks,
                taskIds: taskIds
            };
        }
        else {
            console.log(`No project tasks found for customer_order_product_id: ${id}`);
            return false;
        }
    }
    catch (error) {
        console.log("Delete error......", error)
        return false;
    }
}

const deleteProjectTaskByProjectId = async (id, status = 0) => {
    try {
        const deleted = await ProjectTasks.destroy({
            where: {
                project_id: id,
                [Sequelize.Op.or]: [
                    { product_id: { [Sequelize.Op.ne]: null } },
                    { service_id: { [Sequelize.Op.ne]: null } }
                ]
            },
        });

        if (deleted) {
            return deleted;
        }
        else {
            return false;
        }
    } catch (error) {
        console.log("Delete error......", error)
        return false;
    }
}
const deleteProjectSubTasksAndMembers = async (taskId) => {
    try {
        const allSubTasks = await fetchSubTasksByTaskId(taskId);
        const subTaskIds = allSubTasks?.map(st => st?.id);

        if (subTaskIds?.length > 0) {
            await ProjectSubTaskMembers.destroy({
                where: { subtask_id: { [Sequelize.Op.in]: subTaskIds } },
            });

            await ProjectSubTasks.destroy({
                where: { id: { [Sequelize.Op.in]: subTaskIds } },
            });
        }
        return true;
    } catch (error) {
        console.error("Delete error:", error);
        return false;
    }
};
const deleteDesignOrderTask = async (id, status = 0) => {
    try {
        const deleted = await DesignOrderTasks.destroy({
            where: {
                id: id
            },
        });
        if (deleted) {
            return deleted;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.log("Delete error......", error)
        return false;
    }
}
const deleteProjectSubTask = async (id, status = 0) => {

    try {
        const deleted = await ProjectSubTasks.destroy({
            where: {
                id: id
            },
        });

        if (deleted) {
            return deleted;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.log("Delete error......", error)
    }
}
const deleteProjectMembersBySubtaskId = async (id, status = 0) => {

    try {
        const deleted = await ProjectSubTaskMembers.destroy({
            where: {
                subtask_id: id
            },
        });

        if (deleted) {
            return deleted;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.log("Delete error......", error)
    }
}
const getProjectMemberById = async (id) => {
    let query = await sequelize.query(`select pm.*, coalesce(p.name,concat(s.first_name,' ',coalesce(s.last_name,''))) as name, coalesce(u.email,u2.email) as email from project_members pm`
        + `  LEFT JOIN partners p ON p.id=pm.partner_id `
        + ` LEFT JOIN users u ON p.user_id=u.id `
        + ` LEFT JOIN staff_members s ON s.id=pm.staff_id `
        + ` LEFT JOIN users u2 ON s.user_id=u2.id `
        + ` where pm.id=${id}`)
        if (query[0].length === 1) {
            return query[0];
        }
        else {
            return false;
        }    
}
const addAttachments=async(data)=>{
    console.log(data,'----------data')
try{
    let results = await ProjectTaskFiles.create(data)
    if (results) {
        return results.dataValues.ID;
    }
    else {
        return false;
    }
}
catch(error){
    console.error("Error adding the project attachment ", error);
    return false;
}
}
const getAttachments=async(projectId,taskId)=>{
    let query = await sequelize.query(`select pf.* from project_task_files pf
    left join project_tasks pt on pt.ID=pf.task_id
    left join projects p on pt.project_id=p.id
    where  pt.project_id=${projectId} and pf.task_id=${taskId}`)
    console.log("####################");
    if (query[0].length > 0) {
        return query[0];
    }
    else {
        return [];
    }
}
const deleteProjectTaskAttachment=async(ids)=>{
    console.log("hioiiiiiiiiiiiiii",ids.join());

    // const deleted = await ProjectTaskFiles.destroy({
    //     where: {
    //         ID: {
    //             [sequelize.in]: [ids]
    //         }
    //     }
    // });
    let deleted=await sequelize.query(`delete from project_task_files where ID in (${ids.join()})`)
    console.log("###########",deleted);
    if (deleted) {
        return deleted;
    }
    else {
        return false;
    }
}
const deleteProjectTaskAttachmentByTaskId=async(taskId)=>{
    const deleted = await ProjectTaskFiles.destroy({
        where: {
            task_id: taskId
        }
    });
    if (deleted) {
        return deleted;
    }
    else {
        return false;
    }
}
const addProjectAttachments=async(data)=>{
    try{
        let results = await ProjectFiles.create(data)
        if (results) {
            return results.dataValues.ID;
        }
        else {
            return false;
        }
    }
    catch(error){
        console.error("Error adding the project attachment ", error);
        return false;
    }
}
const deleteProjectAttachments=async(id)=>{
    try{
        let results = await ProjectFiles.destroy({
            where : { project_id:id }
        })
        if (results) {
            return true;
        }
        else {
            return false;
        }
    }
    catch(error){
        console.error("Error deleting the project attachments ", error);
        return false;
    }
}
const deleteProjectAttachmentsByURL=async(url, projectId)=>{
    try{
        let results = await ProjectFiles.destroy({
            where : { file_url:url, project_id:projectId }
        })
        if (results) {
            return true;
        }
        else {
            return false;
        }
    }
    catch(error){
        console.error("Error deleting the project attachment ", error);
        return false;
    }
}
const getProjectAttachments=async(projectId)=>{
    let query = await sequelize.query(`select pf.* from project_files  pf
    
    left join projects p on pf.project_id=p.id
    where  pf.project_id=${projectId} `)
    if (query[0].length > 0) {
        return query[0];
    }
    else {
        return [];
    }       
}

const getAllProjectAttachments = async (projectId) => {
    const [[{ request_id }]] = await sequelize.query(`
        SELECT request_id FROM projects WHERE id = ${projectId}
    `);

    const [projectFiles] = await sequelize.query(`
        SELECT 
            pf.*, p.request_id,
            1 as status
        FROM project_files pf
        LEFT JOIN projects p ON pf.project_id = p.id
        WHERE pf.project_id = ${projectId}
    `);

    const [requestAttachments] = await sequelize.query(`
        SELECT 
            ra.id as ID,
            ${projectId} as project_id,
            'other' as extension,
            ra.file_size,
            'application/pdf' as file_type,
            ra.name as upload_file_name,
            ra.name as file_url,
            ra.timestamp,
            ra.request_id,
            true as not_approved_image
        FROM request_attachments ra
        WHERE ra.request_id = ${request_id}
        AND ra.approved = 0 AND ra.status = 1
    `);

    const combinedAttachments = [...projectFiles, ...requestAttachments];

    return combinedAttachments ;
};



// const getAllProjectAttachments = async (projectId) => {
//     // Step 1: Get the request_id for this project
//     const [[{ request_id }]] = await sequelize.query(`
//         SELECT request_id FROM projects WHERE id = ${projectId}
//     `);

//     // Step 2: Get approved project files
//     const [projectFiles] = await sequelize.query(`
//         SELECT 
//             pf.ID as id,
//             pf.userid,
//             pf.project_id,
//             pf.extension,
//             pf.file_size,
//             pf.file_type,
//             pf.file_url,
//             pf.timestamp,
//             pf.upload_file_name,
//             1 as status -- Approved
//         FROM project_files pf
//         WHERE pf.project_id = ${projectId}
//     `);

//     // Step 3: Get unapproved request attachments with same request_id
//     const [requestAttachments] = await sequelize.query(`
//         SELECT 
//             ra.id,
//             NULL as userid,
//             NULL as project_id,
//             ra.extension,
//             ra.file_size,
//             ra.file_type,
//             ra.name as upload_file_name,
//             ra.name as file_url,
//             ra.timestamp,
//         FROM request_attachments ra
//         WHERE ra.request_id = ${request_id}
//         AND ra.approved = 0
//     `);

//     // Step 4: Combine both arrays
//     const combinedAttachments = [...projectFiles, ...requestAttachments];

//     return { attachments: combinedAttachments };
// };

const deleteProjectAttachment=async(ids)=>{
    // const deleted = await ProjectFiles.destroy({
    //     where: {
    //         id: {
    //             [Op.in]: ids
    //         }
    //     }
    // });
    let deleted=await sequelize.query(`delete from project_files where ID in (${ids.join()})`)
    if (deleted) {
        return deleted;
    }
    else {
        return false;
    }
}
const addStaffTaskTimelog = async(data)=>{
    try{
        const results = await staffTaskTimelogs.create(data);
        if (results) {
            return results.dataValues.id;
        }
        else {
            return false;
        }
    }catch(error){
        console.error("Error adding the staff job timelog ", error);
        return false;
    }
}
const updateStaffTaskTimelog = async(data, taskId, projectId, installationId, userId, timeLogId,selectedService)=>{
    try{
        // const results = await staffTaskTimelogs.update(data,{
        //     where : { [Sequelize.Op.and]: [
        //         { task_id: taskId }, 
        //         { job_id: projectId }, 
        //         { user_id: userId }, 
        //         { id: timeLogId }
        //     ] }
        // });

        const whereCondition = {
            [Sequelize.Op.and]: [
                ...(taskId ? [{ task_id: taskId }] : []),
                ...(projectId ? [{ job_id: projectId }] : []),
                ...(selectedService ? [{ service_id: selectedService }] : []),
                ...(installationId?.length > 0 
                    ? [{ installation_id: { [Sequelize.Op.in]: installationId } }] 
                    : []),
                { user_id: userId },
                { id: timeLogId }
            ]
        };

        const results = await staffTaskTimelogs.update(data, {
            where: whereCondition
        });

        if (results) {
            return true;
        }
        else {
            return false;
        }
    }catch(error){
        console.error("Error updating the staff job timelog ", error);
        return false;
    }
}

const updateInstallerTaskTimelog = async (
  data,                        // the columns you want to change
  taskId        = null,
  projectId     = null,
  installationId,              // number | number[]
  userId,
  timeLogId     = null
) => {
  try {
    /* ---------- build the dynamic WHERE clause ---------- */
    const where = {};

    // (required) installer on this card
    where.user_id = userId;

    // (required) this installation (one id or many)
    if (Array.isArray(installationId)) {
      where.installation_id = { [Sequelize.Op.in]: installationId };
    } else {
      where.installation_id = installationId;
    }
    where.end_time = { [Sequelize.Op.is]: null };
    // (optional) only if provided
    if (taskId)    where.task_id = taskId;
    if (projectId) where.job_id  = projectId;
    

    /* ---------- run the update ---------- */
    const [affectedRows] = await staffTaskTimelogs.update(data, { where });

    return affectedRows > 0;       // true if at least one row changed
  } catch (error) {
    console.error('Error updating staff timelog:', error);
    return false;
  }
};

const updateLogStatus = async(data, id)=>{
    try{
        const results = await staffTaskTimelogs.update(data,{
            where : { id:id }
        });
        if (results) {
            return true;
        }
        else {
            return false;
        }
    }catch(error){
        console.error("Error updating the staff job timelog ", error);
        return false;
    }
}

const fetchStaffTaskTimelog = async(id=null,userId=null,taskId=null,projectId=null,logIdArray=null, installationId=null ,isTimeandMaterial=false)=>{
    try{
        let whereCondition='';
        if(logIdArray?.length > 0){
            whereCondition += ` AND st.id IN (${logIdArray.join(',')}) `
        }else{
            whereCondition += ` AND st.status=1 `
        }
        if(id){
            whereCondition += ` AND st.id = ${id} `
        }
        if(userId){
            whereCondition += ` AND st.user_id = ${userId} `
        }
        if(projectId){
            whereCondition += ` AND st.job_id = ${projectId} `
        }

        let baseCondition = '';
        // let baseCondition = '1=1';
        if(isTimeandMaterial){
            baseCondition = `st.service_id IS NOT NULL`;
        }else if (installationId) {
            baseCondition = `st.installation_id IS NOT NULL`;
            // baseCondition = `st.installation_id = ${installationId}`;
        }else if (taskId) {
            baseCondition = `st.task_id = ${taskId}`;
        }


        const results = await sequelize.query(`SELECT st.*,CONCAT(u.first_name,' ',u.last_name) AS staff_name `
        +` FROM staff_task_timelogs st `
        +` LEFT JOIN users u ON u.id = st.user_id `
        +` WHERE ${baseCondition} ${whereCondition}`)
        // +` WHERE st.status=1 ${whereCondition} `)
        if (results[0]?.length) {
            return results[0];
        }
        else {
            return false;
        }
    }catch(error){
        console.error("Error fetching the staff job timelog ", error);
        return false;
    }
}

const fetchStaffSubtaskTimelog = async (id = null, userId = null, subtaskId = null, projectId = null, logIdArray = null, installationId = null) => {
    try {
        let whereCondition = '';
        
        // If logIdArray is provided, include the log IDs in the query.
        if (logIdArray?.length > 0) {
            whereCondition += ` AND st.id IN (${logIdArray.join(',')}) `;
        } else {
            whereCondition += ` AND st.status=1 `;
        }
        
        // Add conditions for specific id, userId, projectId
        if (id) {
            whereCondition += ` AND st.id = ${id} `;
        }
        if (userId) {
            whereCondition += ` AND st.user_id = ${userId} `;
        }
        if (projectId) {
            whereCondition += ` AND st.job_id = ${projectId} `;
        }

        let baseCondition = '';
        
        // Base condition for filtering based on sub_task_id or installationId
        if (installationId) {
            baseCondition = `st.installation_id IS NOT NULL`;
        } else if (subtaskId) {
            baseCondition = `st.sub_task_id = ${subtaskId}`;
        }

        // Run the query to get data based on the constructed conditions
        const results = await sequelize.query(`
            SELECT 
                st.*, 
                CONCAT(u.first_name, ' ', u.last_name) AS staff_name
            FROM staff_task_timelogs st
            LEFT JOIN users u ON u.id = st.user_id
            WHERE ${baseCondition} ${whereCondition}
        `);

        // If results are found, return them; otherwise, return false
        if (results[0]?.length) {
            return results[0];
        } else {
            return false;
        }
    } catch (error) {
        console.error("Error fetching the staff subtask timelog", error);
        return false;
    }
};

const fetchStaffUserProjectTimelog = async (userId = null, projectId = null, logIdArray = null) => {
    try {
        let whereCondition = '';
        
        if (logIdArray?.length > 0) {
            whereCondition += `st.id IN (${logIdArray.join(',')}) `;
        } else {
            whereCondition += `st.status=1 `;
        }
        
        if (userId) {
            whereCondition += ` AND st.user_id = ${userId} `;
        }
        if (projectId) {
            whereCondition += ` AND st.job_id = ${projectId} `;
        }

        let baseCondition = 'st.installation_id IS NOT NULL';

        const query = `
            SELECT 
                st.*, 
                CONCAT(u.first_name, ' ', u.last_name) AS staff_name
            FROM staff_task_timelogs st
            LEFT JOIN users u ON u.id = st.user_id
            WHERE ${whereCondition}
        `;

        console.log("Generated Query: ", query); 

        const results = await sequelize.query(query);

        if (results[0]?.length) {
            return results[0];
        } else {
            return false;
        }
    } catch (error) {
        console.error("Error fetching the staff user project timelog", error);
        return false; 
    }
};

const fetchStaffTimelogsByProjectId = async (
  id = null,
  userId = null,
  projectId = null,
  logIdArray = null,
  installationId = null
) => {
    console.log('projectIdprojectId',projectId)
  try {
    let whereCondition = '';

    if (logIdArray?.length > 0) {
      whereCondition += ` AND st.id IN (${logIdArray.join(',')}) `;
    } else {
      whereCondition += ` AND st.status = 1 `;
    }

    if (id) {
      whereCondition += ` AND st.id = ${id} `;
    }

    if (userId) {
      whereCondition += ` AND st.user_id = ${userId} `;
    }

    if (projectId) {
      whereCondition += ` AND st.job_id = ${projectId} `;
    }

    // Use baseCondition as always true
    let baseCondition = '1=1';

    if (installationId !== null) {
      baseCondition += ` AND st.installation_id IS NOT NULL`;
    }

    const results = await sequelize.query(
      `SELECT st.*, CONCAT(u.first_name, ' ', u.last_name) AS staff_name 
       FROM staff_task_timelogs st 
       LEFT JOIN users u ON u.id = st.user_id 
       WHERE ${baseCondition} ${whereCondition}`
    );

    if (results[0]?.length) {
      return results[0];
    } else {
      return false;
    }
  } catch (error) {
    console.error('Error fetching staff timelogs by projectId:', error);
    return false;
  }
};


const fetchStaffTaskTimelogForInstallerAndDriver = async(id=null,userId=null,taskId=null,projectId=null,logIdArray=null, installationId=null)=>{
    try{
        let whereCondition='';
        if(logIdArray?.length > 0){
            whereCondition += ` AND st.id IN (${logIdArray.join(',')}) `
        }else{
            whereCondition += ` AND st.status=1 `
        }
        if(id){
            whereCondition += ` AND st.id = ${id} `
        }
        if(userId){
            whereCondition += ` AND st.user_id = ${userId} `
        }
        // if(projectId){
        //     whereCondition += ` AND st.job_id = ${projectId} `
        // }

        let baseCondition = '';
        if (installationId) {
            // baseCondition = `st.installation_id IS NOT NULL`;
            baseCondition = `st.installation_id = ${installationId}`;
        }else if (taskId) {
            baseCondition = `st.task_id = ${taskId}`;
        }


        const results = await sequelize.query(`SELECT st.*,CONCAT(u.first_name,' ',u.last_name) AS staff_name `
        +` FROM staff_task_timelogs st `
        +` LEFT JOIN users u ON u.id = st.user_id `
        +` WHERE ${baseCondition} ${whereCondition}`)
        // +` WHERE st.status=1 ${whereCondition} `)
        if (results[0]?.length) {
            return results[0];
        }
        else {
            return false;
        }
    }catch(error){
        console.error("Error fetching the staff job timelog ", error);
        return false;
    }
}

const  fetchClockedInStaffTaskTimelog = async(userId=null,projectId=null,taskId=null,isDriver=false, instIds=null,hasAcceptedRoutesPermission=false)=>{
    try{
        let whereCondition='';
        if(projectId){
            whereCondition = ` AND st.job_id = ${projectId} `
        }
        if(taskId){
            whereCondition += ` AND st.task_id = ${taskId} `
        }
        if(userId){
            whereCondition += ` AND st.user_id = ${userId} `
        }
        if(isDriver){
            whereCondition += ` AND st.isDriver = 1 `
        }
        if(instIds){
            whereCondition += ` AND st.installation_id IN (${instIds}) `;
        }
        const results = await sequelize.query(`SELECT st.*,i.driver_is_installer, pt.name AS task_name, `
        // + ` sm.id AS staff_id, sm.hourly_rate AS staff_hourly_rate, `
        + ` CASE WHEN st.isDriver = 1 THEN d.hourly_rate ELSE sm.hourly_rate END AS hourly_rate, `
        + ` CASE WHEN st.isDriver = 1 THEN d.id ELSE sm.id END AS staff_id, `
        + ` p.name AS project_name, CONCAT(u.first_name,' ',u.last_name) AS staff_name `
        + ` FROM staff_task_timelogs st `
        + ` LEFT JOIN users u ON u.id = st.user_id `
        + ` LEFT JOIN project_tasks pt ON pt.id = st.task_id `
        + ` LEFT JOIN staff_members sm ON sm.user_id = u.id `
        + ` LEFT JOIN driver d ON d.user_id = u.id `
        + ` LEFT JOIN installations i ON i.id = st.installation_id `
        + ` LEFT JOIN projects p ON p.id = st.job_id `
        + ` WHERE st.end_time IS NULL ${whereCondition} `)
        if (results[0]?.length) {
            return results[0];
        } else {
            return false;
        }
    }catch(error){
        console.error("Error fetching the staff job timelog ", error);
        return false;
    }
}

const fetchAllStaffTaskTimelog = async(search = '', page = 1, limit = 10, orderBy = 'id', order = 'DESC', source = 'regular', staffFilter = [], projectFilter = [], taskFilter = [], dateFrom = null, dateTo = null)=>{
    try{
        // Calculate offset for pagination
        const offset = (page - 1) * limit;

        let query, countQuery, results, countResults, totalCount;

        if (source === 'design') {
            // Query for design orders
            // Build search condition for design orders
            let searchCondition = '';
            if (search && search.trim() !== '') {
                const escapedSearch = search.replace(/'/g, "''").trim();
                searchCondition = ` AND (
                    LOWER(CONCAT(u.first_name,' ',u.last_name)) LIKE LOWER('%${escapedSearch}%') OR
                    LOWER(do.name) LIKE LOWER('%${escapedSearch}%') OR
                    LOWER(dos.labour_cost_service_id) LIKE LOWER('%${escapedSearch}%')
                )`;
            }

            // Build staff filter condition
            let staffCondition = '';
            if (staffFilter && staffFilter.length > 0) {
                staffCondition = ` AND dst.user_id IN (${staffFilter.join(',')})`;
            }

            // Build project filter condition
            let projectCondition = '';
            if (projectFilter && projectFilter.length > 0) {
                projectCondition = ` AND dst.design_order_id IN (${projectFilter.join(',')})`;
            }

            // Build task filter condition
            let taskCondition = '';
            if (taskFilter && taskFilter.length > 0) {
                taskCondition = ` AND dst.task_id IN (${taskFilter.join(',')})`;
            }

            // Build date filter condition
            let dateCondition = '';
            if (dateFrom || dateTo) {
                if (dateFrom && dateTo) {
                    dateCondition = ` AND DATE(FROM_UNIXTIME(dst.end_time)) BETWEEN '${dateFrom}' AND '${dateTo}'`;
                } else if (dateFrom) {
                    dateCondition = ` AND DATE(FROM_UNIXTIME(dst.end_time)) >= '${dateFrom}'`;
                } else if (dateTo) {
                    dateCondition = ` AND DATE(FROM_UNIXTIME(dst.end_time)) <= '${dateTo}'`;
                }
            }

            // Build order by condition for design orders
            const orderByMap = {
                'id': 'dst.id',
                'staff_name': 'staff_name',
                'project_name': 'do.name',
                'task_name': 'lcs.name',
                'sub_task_name': 'dst.sub_task_id',
                'end_time': 'dst.end_time',
                'total_hours': 'dst.end_time'
            };
            const dbOrderBy = orderByMap[orderBy] || 'dst.id';
            const sortOrder = (order === 'asc' || order === 'desc') ? order.toUpperCase() : 'DESC';

            query = `SELECT dst.*, CONCAT(u.first_name,' ',u.last_name) AS staff_name,
                    do.name AS project_name, lcs.name AS task_name,
                    dst.sub_task_id AS sub_task_name, dst.design_order_id AS project_id
                    FROM design_order_staff_task_timelogs dst
                    LEFT JOIN users u ON u.id = dst.user_id
                    LEFT JOIN design_orders do ON do.id = dst.design_order_id
                    LEFT JOIN design_order_services dos ON dos.id = dst.task_id
                    JOIN labour_cost_services lcs ON dos.labour_cost_service_id = lcs.id
                    WHERE dst.end_time IS NOT NULL AND do.id IS NOT NULL AND u.id IS NOT NULL ${searchCondition}${staffCondition}${projectCondition}${taskCondition}${dateCondition}
                    ORDER BY ${dbOrderBy} ${sortOrder} LIMIT ${limit} OFFSET ${offset}`;

            countQuery = `SELECT COUNT(*) as total
                        FROM design_order_staff_task_timelogs dst
                        LEFT JOIN users u ON u.id = dst.user_id
                        LEFT JOIN design_orders do ON do.id = dst.design_order_id
                        LEFT JOIN design_order_services dos ON dos.id = dst.task_id
                        WHERE dst.end_time IS NOT NULL AND do.id IS NOT NULL AND u.id IS NOT NULL ${searchCondition}${staffCondition}${projectCondition}${taskCondition}${dateCondition}`;

            results = await sequelize.query(query);
            countResults = await sequelize.query(countQuery);
            totalCount = countResults[0]?.[0]?.total || 0;

        } else if (source === 'installation') {
            // Query for regular projects (existing logic)
            // Build search condition
            let searchCondition = '';
            if (search && search.trim() !== '') {
                const escapedSearch = search.replace(/'/g, "''").trim();
                searchCondition = ` AND (
                    LOWER(CONCAT(u.first_name,' ',u.last_name)) LIKE LOWER('%${escapedSearch}%') OR
                    LOWER(p.name) LIKE LOWER('%${escapedSearch}%') OR
                    LOWER(
                        CASE
                            WHEN r.project_category = 12 THEN i.name
                            WHEN i.product_id IS NOT NULL THEN prod.name
                            WHEN i.labour_cost_service_id IS NOT NULL THEN lcs.name
                            ELSE i.name
                        END
                    ) LIKE LOWER('%${escapedSearch}%')
                )`;
            }

            // Build staff filter condition
            let staffCondition = '';
            if (staffFilter && staffFilter.length > 0) {
                staffCondition = ` AND st.user_id IN (${staffFilter.join(',')})`;
            }

            // Build project filter condition
            let projectCondition = '';
            if (projectFilter && projectFilter.length > 0) {
                projectCondition = ` AND st.job_id IN (${projectFilter.join(',')})`;
            }

            // Build task filter condition
            let taskCondition = '';
            if (taskFilter && taskFilter.length > 0) {
                taskCondition = ` AND st.installation_id IN (${taskFilter.join(',')})`;
            }

            // Build date filter condition
            let dateCondition = '';
            if (dateFrom || dateTo) {
                if (dateFrom && dateTo) {
                    dateCondition = ` AND DATE(FROM_UNIXTIME(st.end_time)) BETWEEN '${dateFrom}' AND '${dateTo}'`;
                } else if (dateFrom) {
                    dateCondition = ` AND DATE(FROM_UNIXTIME(st.end_time)) >= '${dateFrom}'`;
                } else if (dateTo) {
                    dateCondition = ` AND DATE(FROM_UNIXTIME(st.end_time)) <= '${dateTo}'`;
                }
            }

            // Build order by condition
            const orderByMap = {
                'id': 'st.id',
                'staff_name': 'staff_name',
                'project_name': 'p.name',
                'end_time': 'st.end_time',
                'total_hours': 'st.end_time'
            };
            const dbOrderBy = orderByMap[orderBy] || 'st.id';
            const sortOrder = (order === 'asc' || order === 'desc') ? order.toUpperCase() : 'DESC';

            query = `SELECT st.*,CONCAT(u.first_name,' ',u.last_name) AS staff_name, p.name AS project_name,
                    st.job_id AS project_id, i.id AS installation_id_val,
                    CASE
                        WHEN r.project_category = 12 THEN i.name
                        WHEN i.product_id IS NOT NULL THEN prod.name
                        WHEN i.labour_cost_service_id IS NOT NULL THEN lcs.name
                        ELSE i.name
                    END AS installation_name
                    FROM staff_task_timelogs st
                    LEFT JOIN users u ON u.id = st.user_id
                    LEFT JOIN projects p ON p.id = st.job_id
                    LEFT JOIN installations i ON i.id = st.installation_id
                    LEFT JOIN requests r ON i.request_id = r.id
                    LEFT JOIN products prod ON i.product_id = prod.id AND i.product_id IS NOT NULL
                    LEFT JOIN labour_cost_services lcs ON i.labour_cost_service_id = lcs.id AND i.labour_cost_service_id IS NOT NULL
                    WHERE st.end_time IS NOT NULL AND p.id is not null and u.id is not null AND st.installation_id is not null ${searchCondition}${staffCondition}${projectCondition}${taskCondition}${dateCondition}
                    ORDER BY ${dbOrderBy} ${sortOrder} LIMIT ${limit} OFFSET ${offset}`;

            countQuery = `SELECT COUNT(*) as total
                         FROM staff_task_timelogs st
                         LEFT JOIN users u ON u.id = st.user_id
                         LEFT JOIN projects p ON p.id = st.job_id
                         LEFT JOIN installations i ON i.id = st.installation_id
                         LEFT JOIN requests r ON i.request_id = r.id
                         LEFT JOIN products prod ON i.product_id = prod.id AND i.product_id IS NOT NULL
                         LEFT JOIN labour_cost_services lcs ON i.labour_cost_service_id = lcs.id AND i.labour_cost_service_id IS NOT NULL
                         WHERE st.end_time IS NOT NULL AND p.id is not null and u.id is not null AND st.installation_id is not null ${searchCondition}${staffCondition}${projectCondition}${taskCondition}${dateCondition}`;

            results = await sequelize.query(query);
            countResults = await sequelize.query(countQuery);
            totalCount = countResults[0]?.[0]?.total || 0;
        }
        else {
            // Query for regular projects (existing logic)
            // Build search condition
            let searchCondition = '';
            if (search && search.trim() !== '') {
                const escapedSearch = search.replace(/'/g, "''").trim();
                searchCondition = ` AND (
                    LOWER(CONCAT(u.first_name,' ',u.last_name)) LIKE LOWER('%${escapedSearch}%') OR
                    LOWER(p.name) LIKE LOWER('%${escapedSearch}%') OR
                    LOWER(pt.name) LIKE LOWER('%${escapedSearch}%')
                )`;
            }

            // Build staff filter condition
            let staffCondition = '';
            if (staffFilter && staffFilter.length > 0) {
                staffCondition = ` AND st.user_id IN (${staffFilter.join(',')})`;
            }

            // Build project filter condition
            let projectCondition = '';
            if (projectFilter && projectFilter.length > 0) {
                projectCondition = ` AND st.job_id IN (${projectFilter.join(',')})`;
            }

            // Build task filter condition
            let taskCondition = '';
            if (taskFilter && taskFilter.length > 0) {
                taskCondition = ` AND st.task_id IN (${taskFilter.join(',')})`;
            }

            // Build date filter condition
            let dateCondition = '';
            if (dateFrom || dateTo) {
                if (dateFrom && dateTo) {
                    dateCondition = ` AND DATE(FROM_UNIXTIME(st.end_time)) BETWEEN '${dateFrom}' AND '${dateTo}'`;
                } else if (dateFrom) {
                    dateCondition = ` AND DATE(FROM_UNIXTIME(st.end_time)) >= '${dateFrom}'`;
                } else if (dateTo) {
                    dateCondition = ` AND DATE(FROM_UNIXTIME(st.end_time)) <= '${dateTo}'`;
                }
            }

            // Build order by condition
            const orderByMap = {
                'id': 'st.id',
                'staff_name': 'staff_name',
                'project_name': 'p.name',
                'task_name': 'pt.name',
                'sub_task_name': 'pst.name',
                'end_time': 'st.end_time',
                'total_hours': 'st.end_time'
            };
            const dbOrderBy = orderByMap[orderBy] || 'st.id';
            const sortOrder = (order === 'asc' || order === 'desc') ? order.toUpperCase() : 'DESC';

            query = `SELECT st.*,CONCAT(u.first_name,' ',u.last_name) AS staff_name, p.name AS project_name,
                    pt.name AS task_name, pst.name AS sub_task_name, st.job_id AS project_id
                    FROM staff_task_timelogs st
                    LEFT JOIN users u ON u.id = st.user_id
                    LEFT JOIN projects p ON p.id = st.job_id
                    LEFT JOIN project_tasks pt ON pt.id = st.task_id
                    LEFT JOIN project_subtasks pst ON pst.id = st.sub_task_id
                    WHERE st.end_time IS NOT NULL AND p.id is not null and u.id is not null AND pt.id is not null  ${searchCondition}${staffCondition}${projectCondition}${taskCondition}${dateCondition}
                    ORDER BY ${dbOrderBy} ${sortOrder} LIMIT ${limit} OFFSET ${offset}`;

            countQuery = `SELECT COUNT(*) as total
                         FROM staff_task_timelogs st
                         LEFT JOIN users u ON u.id = st.user_id
                         LEFT JOIN projects p ON p.id = st.job_id
                         LEFT JOIN project_tasks pt ON pt.id = st.task_id
                         WHERE st.end_time IS NOT NULL AND p.id is not null and u.id is not null AND pt.id is not null ${searchCondition}${staffCondition}${projectCondition}${taskCondition}${dateCondition}`;

            results = await sequelize.query(query);
            countResults = await sequelize.query(countQuery);
            totalCount = countResults[0]?.[0]?.total || 0;
        }

        if (results[0]?.length) {
            return {
                data: results[0],
                totalCount: totalCount,
                page: page,
                limit: limit
            };
        }
        else {
            return {
                data: [],
                totalCount: 0,
                page: page,
                limit: limit
            };
        }
    }catch(error){
        console.error("Error fetching the staff job timelog ", error);
        return {
            data: [],
            totalCount: 0,
            page: page || 1,
            limit: limit || 10
        };
    }
}

const fetchAllStaffTaskTimelogByProjectId = async(userId=null,projectId=null,taskId=null)=>{
    try{
        let whereCondition='';
        if(projectId){
            whereCondition = ` AND st.job_id = ${projectId} `
        }
        if(taskId){
            whereCondition += ` AND st.task_id = ${taskId} `
        }
        if(userId){
            whereCondition += ` AND st.user_id = ${userId} `
        }
        const results = await sequelize.query(`SELECT st.*,po.amount,CONCAT(u.first_name,' ',u.last_name) AS staff_name, `
        // +` coalesce(sm.hourly_rate,0) AS hourly_rate, sm.id AS team_id, `
        +` CASE WHEN st.isDriver = 1 THEN d.hourly_rate ELSE sm.hourly_rate END AS hourly_rate, `
        +` CASE WHEN st.isDriver = 1 THEN d.id ELSE sm.id END AS team_id, `
        +` p.name AS project_name, pt.name AS task_name, pst.name AS sub_task_name ,lcs.name AS service_name, pt.is_manually_added`
        +` FROM staff_task_timelogs st `
        +` LEFT JOIN payouts po ON po.task_id = st.task_id `
        +` LEFT JOIN users u ON u.id = st.user_id `
        +` LEFT JOIN request_services rs ON rs.id = st.service_id `
        +` LEFT JOIN labour_cost_services lcs ON lcs.id = rs.labour_cost_service_id `
        +` LEFT JOIN staff_members sm ON u.id = sm.user_id `
        +` LEFT JOIN driver d ON d.user_id = u.id `
        +` LEFT JOIN projects p ON p.id = st.job_id `
        +` LEFT JOIN project_tasks pt ON pt.id = st.task_id `
        +` LEFT JOIN project_subtasks pst ON pst.id = st.sub_task_id `
        +` WHERE st.end_time IS NOT NULL ${whereCondition} `
        +` GROUP BY st.id, sm.hourly_rate, p.name, pt.name, pst.name, sm.id `)
        if (results[0]?.length) {
            return results[0];
        }
        else {
            return [];
        }
    }catch(error){
        console.error("Error fetching the staff job timelog ", error);
        return [];
    }
}

const fetchStaffMembersByRequestId = async (requestId) => {
    console.log('requestIdddddd', requestId)
    try {
        const results = await sequelize.query(`
            SELECT 
                st.*,
                CASE 
                    WHEN d.hourly_rate IS NULL OR d.hourly_rate = 0 THEN 20 
                    ELSE d.hourly_rate 
                END AS hourly_rate
            FROM staff_task_timelogs st 
            LEFT JOIN users u ON u.id = st.user_id 
            LEFT JOIN driver d ON u.id = d.user_id 
            LEFT JOIN projects p ON p.id = st.job_id
            LEFT JOIN requests r ON r.id = p.request_id 
            WHERE r.id = :requestId
            GROUP BY u.id
        `, {
            replacements: { requestId },
            type: sequelize.QueryTypes.SELECT
        });

        if (results.length) {
            return results;
        } else {
            return [];
        }
    } catch (error) {
        console.error("Error fetching staff members by request_id", error);
        return [];
    }
};


const fetchSubTaskName = async(id)=>{
    try{
        const results = await sequelize.query(`SELECT pst.name AS sub_task_name FROM project_subtasks pst WHERE pst.id = ${id}`)
        if (results[0]?.length) {
            return results[0];
        }
        else {
            return false;
        }
    }catch(error){
        console.error("Error fetching the sub task name ", error);
        return false;
    }
}

const fetchDesignOrderSubTaskName = async(id)=>{
    try{
        const results = await sequelize.query(`SELECT dost.name AS sub_task_name FROM design_order_sub_task dost WHERE dost.id = ${id}`)
        if (results[0]?.length) {
            return results[0];
        }
        else {
            return false;
        }
    }catch(error){
        console.error("Error fetching design order subtask name ", error);
        return false;
    }
}

const fetchSubTasksByTaskId = async(id)=>{
    try{
        const [results] = await sequelize.query(`SELECT id FROM project_subtasks WHERE task_id = ${id}`)
        return results.length ? results : [];
    }catch(error){
        console.error("Error fetching the sub task name ", error);
        return [];
    }
}

const getTaskIdsByCustomerOrderProductId = async (customerOrderProductId) => {
    try {
        const tasks = await ProjectTasks.findAll({
            where: { customer_order_product_id: customerOrderProductId },
            attributes: ['id']
        });
        return tasks?.map(task => task.id) || [];
    } catch (error) {
        console.error("Error fetching task ids by customer_order_product_id ", error);
        return [];
    }
}

// For service tasks, project_tasks.customer_order_product_id stores request_services.id.
// We restrict to service-type tasks (service_id present, product_id null) to avoid mixing product tasks.
const getTaskIdsByRequestServiceId = async (requestServiceId) => {
    try {
        const tasks = await ProjectTasks.findAll({
            where: {
                customer_order_product_id: requestServiceId,
                service_id: { [Op.ne]: null },
                product_id: null
            },
            attributes: ['id']
        });
        return tasks?.map(task => task.id) || [];
    } catch (error) {
        console.error("Error fetching task ids by request_service_id ", error);
        return [];
    }
};

const projectSubTaskSkills =async(taskId)=>{
    let result = await sequelize.query(`SELECT pst.skill_ids FROM project_subtasks AS pst WHERE pst.task_id = ${taskId}`)
    let skillIdsArray = result[0].map(item => {
        if (!item.skill_ids) {
            return [];
        }
        return item.skill_ids.split(',').map(id => parseInt(id, 10));
    });
    let combinedSkillIds = [].concat(...skillIdsArray);

    let data =[]
    if(!empty(combinedSkillIds) && combinedSkillIds.length > 0){
        data = await sequelize.query(`SELECT s.* FROM services AS s WHERE s.id IN (${combinedSkillIds.join(',')})`)
        return data[0]
    }else{
        return []
    }
    
}

const parseNumericIdsFromCsv = (value) =>
    String(value || "")
        .match(/\d+/g)
        ?.map((id) => Number(id))
        .filter((id) => Number.isFinite(id) && id > 0) || [];

const getProjectSubTasksWithSkillsByProjectId = async (projectId) => {
    try {
        const safeProjectId = Number(projectId);
        if (!Number.isFinite(safeProjectId) || safeProjectId <= 0) {
            return [];
        }

        const [results] = await sequelize.query(
            `SELECT ps.id, ps.skill_ids
             FROM project_subtasks ps
             INNER JOIN project_tasks pt ON pt.id = ps.task_id
             WHERE pt.project_id = :projectId
               AND ps.status != 0`
            , { replacements: { projectId: safeProjectId } }
        );
        return results || [];
    } catch (error) {
        console.error("Error fetching project subtasks with skills", error);
        return [];
    }
}

/**
 * Match subtasks to staff the same way as get-all-users-by-service / getTeamMembersBasedOnSkills:
 * use MySQL FIND_IN_SET(skillId, sm.services) instead of parsing services in JS.
 * @param {number} projectId
 * @param {number[]} staffIds
 * @param {object[]|null} preloadedSubtasks optional rows from getProjectSubTasksWithSkillsByProjectId (avoids duplicate query)
 */
const getSubtaskStaffPairsBySkillFindInSet = async (projectId, staffIds = [], preloadedSubtasks = null) => {
    const safeProjectId = Number(projectId);
    const safeStaffIds = (staffIds || [])
        .map((id) => Number(id))
        .filter((id) => Number.isFinite(id) && id > 0);

    if (!Number.isFinite(safeProjectId) || safeProjectId <= 0 || !safeStaffIds.length) {
        return [];
    }

    const subtasks =
        preloadedSubtasks != null
            ? preloadedSubtasks
            : await getProjectSubTasksWithSkillsByProjectId(safeProjectId);
    const pairs = [];

    for (const row of subtasks) {
        const subtaskId = Number(row?.id ?? row?.ID);
        const skillIds = parseNumericIdsFromCsv(row?.skill_ids);
        if (!subtaskId || !skillIds.length) continue;

        const findInSetOr = skillIds
            .map((sid) => `FIND_IN_SET(${Number(sid)}, sm.services)`)
            .join(' OR ');

        const [matches] = await sequelize.query(
            `SELECT sm.id AS staff_id
             FROM staff_members sm
             WHERE sm.status = 1
               AND sm.id IN (:staffIds)
               AND (${findInSetOr})`,
            { replacements: { staffIds: safeStaffIds } }
        );

        for (const m of matches || []) {
            pairs.push({
                subtask_id: subtaskId,
                staff_id: Number(m.staff_id),
            });
        }
    }

    return pairs;
};

const getExistingProjectSubTaskMemberPairs = async (subtaskIds = [], memberIds = []) => {
    try {
        const safeSubtaskIds = (subtaskIds || [])
            .map((id) => Number(id))
            .filter((id) => Number.isFinite(id) && id > 0);
        const safeMemberIds = (memberIds || [])
            .map((id) => Number(id))
            .filter((id) => Number.isFinite(id) && id > 0);

        if (!safeSubtaskIds.length || !safeMemberIds.length) {
            return [];
        }

        const [results] = await sequelize.query(
            `SELECT psm.subtask_id, psm.staff_id
             FROM project_subtask_members psm
             WHERE psm.subtask_id IN (:subtaskIds)
               AND psm.staff_id IN (:memberIds)`,
            { replacements: { subtaskIds: safeSubtaskIds, memberIds: safeMemberIds } }
        );
        return results || [];
    } catch (error) {
        console.error("Error fetching existing subtask member pairs", error);
        return [];
    }
}

const deleteStaffTaskTimelogs = async (projId) => {
    try {
        const deleted = await staffTaskTimelogs.destroy({
            where: {
                job_id: projId
            },
        });
        if (deleted) {
            return true;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.log("Delete error......", error)
        return false;
    }
}

const deleteStaffTaskTimelogsForPayout = async (projId,taskId,staffUserId,startTime,endTime) => {
    try {
        console.log('projId,taskId,staffUserId',projId,taskId,staffUserId)
        const deleted = await staffTaskTimelogs.destroy({
            where: {
                job_id: projId,
                task_id:taskId,
                user_id:staffUserId,
                start_time:startTime,
                end_time: endTime
            },
        });
        if (deleted) {
            return true;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.log("Delete error......", error)
        return false;
    }
}

const deleteStaffTaskTimelogsByServiceId = async (serviceId, projectId) => {
    try {
        // Delete from staff_task_timelogs table
        const deletedTimelogs = await staffTaskTimelogs.destroy({
            where: {
                service_id: serviceId,
                job_id: projectId
            },
        });

        // Delete from payouts table
        const deletedPayouts = await sequelize.query(
            `DELETE FROM payouts WHERE service_id = :serviceId AND project_id = :projectId`,
            {
                replacements: { serviceId, projectId },
                type: sequelize.QueryTypes.DELETE
            }
        );
        
        // Extract the number of deleted rows from the result array
        const deletedPayoutsCount = Array.isArray(deletedPayouts) && deletedPayouts[0] ? deletedPayouts[0] : 0;
        const totalDeleted = (deletedTimelogs || 0) + (deletedPayoutsCount || 0);
        
        return {
            status: 1,
            message: `Successfully deleted ${deletedTimelogs || 0} staff task timelogs and ${deletedPayoutsCount || 0} payouts for service ${serviceId}`,
            deletedTimelogs: deletedTimelogs || 0,
            deletedPayouts: deletedPayoutsCount || 0,
            totalDeleted
        };
    } catch (error) {
        console.error("Error deleting staff task timelogs and payouts by service ID:", error);
        return {
            status: 0,
            message: 'Error deleting records',
            error: error.message,
            deletedTimelogs: 0,
            deletedPayouts: 0,
            totalDeleted: 0
        };
    }
}

const deleteManuallyAddedTaskTimelogsAndPayouts = async (projectId) => {
    try {
        // Delete staff timelogs where project_id matches and service_id and installation_id are NULL
        const deletedTimelogs = await staffTaskTimelogs.destroy({
            where: {
                job_id: projectId,
                service_id: null,
                installation_id: null
            },
        });

        // Delete payouts where project_id matches and service_id and installation_id are NULL
        const deletedPayouts = await sequelize.query(
            `DELETE FROM payouts WHERE project_id = :projectId AND (service_id IS NULL OR service_id = 0) AND (installation_id IS NULL OR installation_id = 0)`,
            {
                replacements: { projectId },
                type: sequelize.QueryTypes.DELETE
            }
        );
        
        const totalDeleted = (deletedTimelogs || 0) + (deletedPayouts || 0);
        
        return {
            status: 1,
            message: `Successfully deleted ${deletedTimelogs || 0} staff task timelogs and ${deletedPayouts || 0} payouts for manually added tasks`,
            deletedTimelogs: deletedTimelogs || 0,
            deletedPayouts: deletedPayouts || 0,
            totalDeleted
        };
    } catch (error) {
        console.error("Error deleting manually added task timelogs and payouts:", error);
        return {
            status: 0,
            message: 'Error deleting manually added task records',
            error: error.message,
            deletedTimelogs: 0,
            deletedPayouts: 0,
            totalDeleted: 0
        };
    }
}

// const deleteTimelogsAndPayoutsByProductTasks = async (projectId) => {
//     try {
//         // Delete all staff timelogs where service_id IS NULL AND installation_id IS NULL for this project
//         const deletedTimelogs = await staffTaskTimelogs.destroy({
//             where: {
//                 job_id: projectId,
//                 service_id: null,
//                 installation_id: null
//             },
//         });

//         // Delete all payouts where service_id IS NULL AND installation_id IS NULL for this project
//         const deletedPayoutsResult = await sequelize.query(
//             `DELETE FROM payouts WHERE project_id = :projectId 
//                 AND service_id IS NULL 
//                 AND installation_id IS NULL`,
//             {
//                 replacements: { projectId },
//                 type: sequelize.QueryTypes.DELETE
//             }
//         );
        
//         // Extract the number of deleted rows from the result array
//         const deletedPayouts = Array.isArray(deletedPayoutsResult) && deletedPayoutsResult[0] ? deletedPayoutsResult[0] : 0;
        
//         const totalDeleted = (deletedTimelogs || 0) + (deletedPayouts || 0);
        
//         return {
//             status: 1,
//             message: `Successfully deleted ${deletedTimelogs || 0} staff task timelogs and ${deletedPayouts || 0} payouts with NULL service_id/installation_id`,
//             deletedTimelogs: deletedTimelogs || 0,
//             deletedPayouts: deletedPayouts || 0,
//             totalDeleted
//         };
//     } catch (error) {
//         console.error("Error deleting timelogs and payouts by product tasks:", error);
//         return {
//             status: 0,
//             message: 'Error deleting production labor task records',
//             error: error.message,
//             deletedTimelogs: 0,
//             deletedPayouts: 0,
//             totalDeleted: 0
//         };
//     }
// }

const deleteTimelogsAndPayoutsByProductTasks = async (projectId) => {
    try {
        // Step 1: Get the task IDs from project_tasks where project_id matches and product_id is NOT NULL
        const projectTasks = await sequelize.query(
            `SELECT id FROM project_tasks WHERE project_id = :projectId AND product_id IS NOT NULL`,
            {
                replacements: { projectId },
                type: sequelize.QueryTypes.SELECT
            }
        );
        // Extract the task_ids from the result (projectTasks is an array of objects)
        const taskIds = projectTasks.map(task => task.id);
        console.log('taskIdsssss', taskIds)

        // Step 2: Delete all staff timelogs where:
        // 1. service_id IS NULL and installation_id IS NULL, or
        // 2. task_id matches the found project_tasks task_ids
        const deletedTimelogs = await staffTaskTimelogs.destroy({
            where: {
                job_id: projectId,
                [Op.or]: [
                    // Condition 1: service_id and installation_id are NULL
                    {
                        service_id: null,
                        installation_id: null
                    },
                    // Condition 2: task_id matches the taskIds
                    {
                        task_id: {
                            [Op.in]: taskIds // `Op.in` is used to check if task_id matches any value in taskIds
                        }
                    }
                ]
            }
        });
        const deletedPayoutsResult = await sequelize.query(
            `DELETE FROM payouts WHERE project_id = :projectId
                AND (
                    (service_id IS NULL AND installation_id IS NULL) 
                    OR task_id IN (:taskIds)
                )`,
            {
                replacements: { projectId, taskIds },
                type: sequelize.QueryTypes.DELETE
            }
        );
        
        // Extract the number of deleted rows from the result array (if the result is an array)
        const deletedPayouts = Array.isArray(deletedPayoutsResult) && deletedPayoutsResult[0] ? deletedPayoutsResult[0] : 0;
        
        // Step 4: Calculate the total number of deleted records
        const totalDeleted = (deletedTimelogs || 0) + (deletedPayouts || 0);
        
        return {
            status: 1,
            message: `Successfully deleted ${deletedTimelogs || 0} staff task timelogs and ${deletedPayouts || 0} payouts with NULL service_id/installation_id matching project tasks.`,
            deletedTimelogs: deletedTimelogs || 0,
            deletedPayouts: deletedPayouts || 0,
            totalDeleted
        };
    } catch (error) {
        console.error("Error deleting timelogs and payouts by product tasks:", error);
        return {
            status: 0,
            message: 'Error deleting production labor task records',
            error: error.message,
            deletedTimelogs: 0,
            deletedPayouts: 0,
            totalDeleted: 0
        };
    }
};


const getProjectDesignOrderSubTaskById = async (id) => {
    let query = await sequelize.query(`select ps.*, concat(u.first_name,' ',u.last_name) as authorized_by, `
        + ` p.name as design_order_name,pt.design_order_id, `
        + ` GROUP_CONCAT(pm.staff_id SEPARATOR ',') AS task_members from design_order_sub_task ps`
        + ` left join users u on ps.userid=u.id`
        + ` left join design_order_services pt on ps.task_id=pt.id`
        + ` left join design_order_project_subtask_members pm on pm.subtask_id=ps.id`
        + ` left join design_orders p on pt.design_order_id=p.id`
        + ` where ps.id=${id}`
    )
    if (query[0].length === 1) {
        return query[0];
    }
    else {
        return false;
    }
}

//previos
// const fetchAllAttachmentByTask = async (taskId) => {
//     try {
//         let query = await sequelize.query(
//             `SELECT ra.* FROM request_attachments ra INNER JOIN project_tasks pt ON pt.product_id = ra.product_id LEFT JOIN projects p ON p.id = pt.project_id WHERE p.request_id = ra.request_id and pt.Id = :taskId`,
//             {
//                 replacements: { taskId },
//                 type: sequelize.QueryTypes.SELECT
//             }
//         );

//         return query.length > 0 ? query : false;
//     } catch (error) {
//         console.error("Error fetching attachments:", error);
//         return false;
//     }
// };


const fetchAllAttachmentByTask = async (taskId) => {
    try {
        let query = await sequelize.query(
            `SELECT ra.* 
             FROM request_attachments ra 
             INNER JOIN project_tasks pt ON FIND_IN_SET(pt.request_location_id, ra.location_ids) > 0
             LEFT JOIN projects p ON p.id = pt.project_id 
             WHERE p.request_id = ra.request_id 
             AND pt.Id = :taskId`,
            {
                replacements: { taskId },
                type: sequelize.QueryTypes.SELECT
            }
        );

        return query.length > 0 ? query : [];
    } catch (error) {
        console.error("Error fetching attachments:", error);
        return [];
    }
};

const updateProjectTaskByCustomerOrderProductId = async (data, id) => {

    try {
        console.log("#############Project",data);
        console.log("#############Projectid",id);
        const updatedRows = await ProjectTasks.update(data, {
            where: {
                customer_order_product_id: id
            },
            returning: true,
        });
        console.log("#############Project",updatedRows);
        if (updatedRows) {
            if (updatedRows[1] > 0) {
                return true;
            }
            else {

                return false;
            }
        }

    }
    catch (error) {
        console.error("Error updating project:", error);
        return false;
    }

}

const updateProjectTaskByRequestLocationId = async (data, id) => {
    try {
        const updatedRows = await ProjectTasks.update(data, {
            where: {
                request_location_id: id
            },
            returning: true,
        });
        if (updatedRows) {
            if (updatedRows[1] > 0) {
                return true;
            }
            else {
                return false;
            }
        }
    }
    catch (error) {
        console.error("Error updating project:", error);
        return false;
    }
}

const getTasksByProjectId = async(projectId)=> {
    try {
      const tasks = await ProjectTasks.findAll({
        where: { project_id: projectId },
        raw: true,
      })
      return tasks
    } catch (error) {
      console.error('Error fetching tasks:', error)
      return []
    }
  }


const getTaskDetailsForqb = async (id) => {
  try {
    const [result] = await sequelize.query(`
      SELECT 
        t.name AS taskName, 
        p.name AS jobName 
      FROM project_tasks t
      LEFT JOIN projects p ON t.project_id = p.id
      WHERE t.id = :id
    `, {
      replacements: { id },
      type: sequelize.QueryTypes.SELECT
    });

    return result || null;
  } catch (error) {
    console.error("Error in getTaskDetailsForqb:", error);
    return null;
  }
};

const getServicesFromProductTask = async (taskId) => {
    console.log('taskIdddddddddd', taskId)
    try {
      // First find the project task by ID
      const projectTask = await ProjectTasks.findOne({
        where: { id: taskId },
        raw: true
      });
  
      if (!projectTask) {
        return null;
      }
  
      // If product_id exists, fetch related request services
      if (projectTask.product_id) {
        const requestServices = await sequelize.query(`
          SELECT rs.*, pt.*, p.request_id AS project_request_id
        FROM project_tasks pt
        LEFT JOIN projects p ON p.id = pt.project_id
        LEFT JOIN request_services rs 
          ON rs.product_id = pt.product_id 
         AND rs.request_id = p.request_id
        WHERE pt.id = :taskId
        AND rs.labour_cost_service_id != 10 
        `, {
          replacements: { taskId },
          type: sequelize.QueryTypes.SELECT
        });
  
        return {
          projectTask,
          requestServices: requestServices || []
        };
      }
    //   else{
    //     const requestServices = await sequelize.query(`
    //         SELECT pt.*, p.request_id AS project_request_id
    //       FROM project_tasks pt
    //       LEFT JOIN projects p ON p.id = pt.project_id
    //       WHERE pt.id = :taskId
    //       `, {
    //         replacements: { taskId },
    //         type: sequelize.QueryTypes.SELECT
    //       });
    
    //       return {
    //         projectTask,
    //         requestServices: requestServices || []
    //       };
    //   }
  
      // If no product_id, return just the project task
      return {
        projectTask,
        requestServices: []
      };
  
    } catch (error) {
      console.error("Error in getServicesFromProductTask:", error);
      return null;
    }
  }
  
  const getJobDetailsForqb = async (projectId) => {
    try {
      const [result] = await sequelize.query(`
        SELECT 
          p.name AS jobName,r.doc_number,r.id AS requestId,
          pt.quickBookId AS projectTypeQuickBookId,r.syncWithQuickbooks
        FROM projects p
        LEFT JOIN requests r ON r.id = p.request_id
        LEFT JOIN project_types pt ON pt.id = r.project_type
        WHERE p.id = :projectId
      `, {
        replacements: { projectId },
        type: sequelize.QueryTypes.SELECT
      });
  
      return result || null;
    } catch (error) {
      console.error("Error in getTaskDetailsForqb:", error);
      return null;
    }
  };
  const getJobDetailsFromInstallationIdForqb = async (installationId) => {
    try {
      const [result] = await sequelize.query(`
        SELECT 
          i.name AS jobName,r.doc_number,r.id AS requestId,
          pt.quickBookId AS projectTypeQuickBookId,r.syncWithQuickbooks
        FROM installations i
        LEFT JOIN requests r ON r.id = i.request_id
        LEFT JOIN project_types pt ON pt.id = r.project_type
        WHERE i.id = :installationId
      `, {
        replacements: { installationId },
        type: sequelize.QueryTypes.SELECT
      });
  
      return result || null;
    } catch (error) {
      console.error("Error in getTaskDetailsForqb:", error);
      return null;
    }
  };

  const fetchAllFilesFromRequest = async (requestId, projectId) => {
    try {
      let finalRequestId = requestId;
  
      // ✅ If requestId is missing but projectId is present
      if (projectId && !requestId) {
        const [projectRow] = await sequelize.query(
          `
          SELECT p.request_id 
          FROM projects p
          WHERE p.id = :projectId
          LIMIT 1
          `,
          {
            replacements: { projectId },
            type: sequelize.QueryTypes.SELECT
          }
        );
  
        if (projectRow?.request_id) {
          finalRequestId = projectRow.request_id;
        }
      }
  
      // if still no requestId, just return empty
      if (!finalRequestId) {
        return [];
      }
  
      // ✅ Fetch attachments by finalRequestId
      const result = await sequelize.query(
        `
        SELECT ra.* 
        FROM request_attachments ra
        WHERE ra.request_id = :requestId
        `,
        {
          replacements: { requestId: finalRequestId },
          type: sequelize.QueryTypes.SELECT
        }
      );
  
      return result || [];
    } catch (error) {
      console.error("Error in fetchAllFilesFromRequest:", error);
      return [];
    }
  };
  
  const fetAllTaskByProjectId = async (projectId) => {
    try {
      const result = await sequelize.query(`
        SELECT * FROM project_tasks WHERE project_id = :projectId
      `, {
        replacements: { projectId },
        type: sequelize.QueryTypes.SELECT
      });
      return result || [];
    } catch (error) {
      console.error("Error in fetAllTaskByProjectId:", error);
      return [];
    }
  };

module.exports = {
    getTaskUid, addProjectTask, getProjectTaskById, getProjectDataById, getAllProjectTasks, getAllDesignOrderTasks, getProjectTaskBytaskId,
    updateProjectTask, getSubTaskUid, addProjectSubTask, getProjectSubTaskById, getAllProjectSubTaskBytaskId, getAllProjectSubTaskBytaskIdWithTeam,
    updateProjectSubTask, deleteProjectTask, deleteProjectSubTask,getProjectMemberById,addAttachments,
    getAttachments,deleteProjectTaskAttachment,addProjectAttachments,deleteProjectAttachments,deleteProjectAttachmentsByURL,getProjectAttachments,deleteProjectAttachment,
    addStaffTaskTimelog, updateStaffTaskTimelog, fetchStaffTaskTimelog, fetchClockedInStaffTaskTimelog,updateLogStatus,
    fetchAllStaffTaskTimelog, getAllLocationsForTSInstallation,getServicesFromProductTask,fetchDesignOrderSubTaskName,
    fetchSubTaskName,fetchAllFilesFromRequest,fetAllTaskByProjectId,
    projectSubTaskSkills,
    getProjectSubTasksWithSkillsByProjectId,
    getSubtaskStaffPairsBySkillFindInSet,
    getExistingProjectSubTaskMemberPairs,
    addDesignOrderTask,
    updateDesignOrderTask,
    deleteDesignOrderTask,
    AddMembersToSubTask,
    fetchSubTasksByTaskId,
    deleteProjectTaskByProjectId,
    deleteProjectSubTasksAndMembers,
    deleteProjectTaskAttachmentByTaskId,
    deleteProjectMembersBySubtaskId,
    updateProjectSubTaskByTaskId,updateProjectTaskByProjectId,updateProjectTaskByProjectIdExcludeCompleted, fetchAllStaffTaskTimelogByProjectId,
    updateProjectSubTaskByProjectId, deleteStaffTaskTimelogs, deleteStaffTaskTimelogsForPayout, deleteStaffTaskTimelogsByServiceId, deleteManuallyAddedTaskTimelogsAndPayouts, deleteTimelogsAndPayoutsByProductTasks,
    getProjectDesignOrderSubTaskById,fetchAllAttachmentByTask,updateProjectTaskByCustomerOrderProductId,
    updateProjectTaskByRequestLocationId,updateInstallerTaskTimelog,fetchStaffMembersByRequestId,
    getTasksByProjectId,getAllProjectAttachments,fetchStaffTaskTimelogForInstallerAndDriver,
    getTaskDetailsForqb,fetchStaffTimelogsByProjectId, fetchStaffSubtaskTimelog,
    fetchStaffUserProjectTimelog,getJobDetailsForqb,deleteProjectTaskByServiceId,getJobDetailsFromInstallationIdForqb,
    getTaskIdsByCustomerOrderProductId,
    getTaskIdsByRequestServiceId,
    addStopwatch, updateStopwatch, deleteStopwatchBySubtask, deleteStopwatchByJobId, fetchStopwatch,deleteStopwatchByStopwatchId
}