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

const getAllWorkflow=async()=>{
    try {
        let result = await sequelize.query(`
        select sm.*, CONCAT(sm.first_name, ' ', sm.last_name) AS userName from staff_members as sm where sm.status = 1 order by sm.id desc
        `)
        if(!empty(result)){
            if (result[0].length > 0) {
                data = result[0];
              }
              return data;
        }else{
            return false
        }
    } catch (error) {
        console.log(error)
    }
}

const getTeamMembersByServiceId = async (serviceId, all = false) => {
    try {
        let whereCondition = "";
        if (all) {
            whereCondition = "sm.status != 2";
        } else {
            whereCondition = "sm.status = 1";
        }

        // Add service filter if serviceId is provided
        if (serviceId) {
            whereCondition += ` AND FIND_IN_SET(${serviceId}, sm.services)`;
        }

        let result = await sequelize.query(`
            SELECT 
                sm.id AS staff_id,
                sm.user_id AS id,
                sm.user_id,
                CONCAT(sm.first_name, ' ', COALESCE(sm.last_name, '')) AS name,
                sm.first_name,
                sm.last_name,
                sm.email,
                sm.phone,
                sm.hourly_rate,
                sm.services,
                sm.status
            FROM staff_members sm 
            WHERE ${whereCondition} 
            ORDER BY sm.id DESC
        `);

        if (!empty(result)) {
            if (result[0].length > 0) {
                return result[0];
            }
        }
        return [];
    } catch (error) {
        console.log("Error in getTeamMembersByServiceId:", error);
        return [];
    }
}

const addStaffToProject = async (data, projectId) => {
    try {
        const { staff_id, sub_task_id } = data;

        // Check if staff member already exists in this project/subtask
        const existingMember = await sequelize.query(`
            SELECT id 
            FROM project_members 
            WHERE staff_id = ${staff_id} 
              AND project_id = ${projectId}
              AND sub_task_id = ${sub_task_id}
        `);

        if (existingMember[0].length > 0) {
            return {
                status: 0,
                message: 'Staff member already exists in this project/subtask',
                data: existingMember[0][0]
            };
        }

        // Add staff member to project with subtask
        const result = await sequelize.query(`
            INSERT INTO project_members (staff_id, project_id, sub_task_id) 
            VALUES (${staff_id}, ${projectId}, ${sub_task_id})
        `);

        if (result[0].affectedRows > 0) {
            return {
                status: 1,
                message: 'Staff member added to project successfully',
                data: {
                    id: result[0].insertId,
                    staff_id,
                    project_id: projectId,
                    sub_task_id
                }
            };
        } else {
            return {
                status: 0,
                message: 'Failed to add staff member to project'
            };
        }
    } catch (error) {
        console.log("Error in addStaffToProject:", error);
        return {
            status: 0,
            message: 'Internal server error',
            error: error.message
        };
    }
};


// const addStaffToProject = async (staffId, projectId) => {
//     try {
//         // Check if staff member already exists in project
//         const existingMember = await sequelize.query(`
//             SELECT id FROM project_members 
//             WHERE staff_id = ${staffId} AND project_id = ${projectId}
//         `);

//         if (existingMember[0].length > 0) {
//             return {
//                 status: 0,
//                 message: 'Staff member already exists in this project',
//                 data: existingMember[0][0]
//             };
//         }

//         // Add staff member to project
//         const result = await sequelize.query(`
//             INSERT INTO project_members (staff_id, project_id) 
//             VALUES (${staffId}, ${projectId})
//         `);

//         if (result[0].affectedRows > 0) {
//             return {
//                 status: 1,
//                 message: 'Staff member added to project successfully',
//                 data: {
//                     id: result[0].insertId,
//                     staff_id: staffId,
//                     project_id: projectId
//                 }
//             };
//         } else {
//             return {
//                 status: 0,
//                 message: 'Failed to add staff member to project'
//             };
//         }
//     } catch (error) {
//         console.log("Error in addStaffToProject:", error);
//         return {
//             status: 0,
//             message: 'Internal server error',
//             error: error.message
//         };
//     }
// }
module.exports={
    getAllWorkflow,
    getTeamMembersByServiceId,
    addStaffToProject,
}