import { Request, Response } from 'express';
import { PaginationHelper } from '../helpers/paginate';
import db from '../models';
const ProjectMasterModel = db.ProjectMaster;
const UserModel = db.User;
const ProjectRoleModel = db.ProjectRoleMaster;
const ProjectStaffModel = db.ProjectStaffing;
const ClientMasterModel = db.ClientMaster;
import { Op } from 'sequelize';
import { UserData } from '../helpers/userToken';


//create project roles
export const createProjectRole = async (req: Request, res: Response): Promise<void> => {
    try {
        const { id, role } = req.body;

        if(id) {
            const projectRole = await ProjectRoleModel.findOne({ where: { id } });
            if(!projectRole) {
                res.status(404).json({
                    success: false,
                    message: 'Project Role not found',
                });
            }

            //update
            await projectRole.update({ role }, { where: { id } });

            res.status(200).json({
                success: true,
                message: 'Project Role Updated',
                data: projectRole,
            });
        } else {
            //create
            const projectRole = await ProjectRoleModel.create({ role });

            res.status(200).json({
                success: true,
                message: 'project Role Created',
                data: projectRole
            });
        }

    } catch (error) {
        console.error('Error creating project role:', error);
        res.status(500).json({
            success: false,
            message: 'Internal server error',
            error: error instanceof Error ? error.message : 'Unknown error'
        });
    }
};

//get project roles
export const getProjectRoles = async (req: Request, res: Response): Promise<void> => {
    try {
        const { id, search } = req.query;
        let whereClause: any = {
            is_active: true
        };

        if(id) {
            whereClause.id = id
        }

        // Add search condition if search parameter exists
        if (search && typeof search === 'string') {
            whereClause = {
                ...whereClause,
                role: {
                    [Op.iLike]: `%${search}%`
                }
            };
        }

        const roles = await ProjectRoleModel.findAll({
            where: whereClause,
            order: [['id', 'ASC']],
            attributes: { 
                exclude: ['createdAt', 'updatedAt'] 
            },
        })

        res.status(200).json({
            success: true,
            message: "All Project Roles",
            data: roles,
        });
    } catch (error) {
        console.error('Error Fetching Project Roles:', error);
        res.status(500).json({
            success: false,
            message: "We hit a snag! Our team is looking into it.",
            error: error instanceof Error ? error.message : 'Unknown error'
        });
    }
}

//get staffing management
export const getStaffingAssignment = async (req: Request, res: Response): Promise<void> => {
    try {

        const whereClause: any = { is_active: true };

        const [stats] = await db.sequelize.query(`
            SELECT
                SUM(CASE WHEN total_utilization >= 80 AND total_utilization <= 100 THEN 1 ELSE 0 END) AS ongoing_projects,
                SUM(CASE WHEN total_utilization < 80 THEN 1 ELSE 0 END) AS available,
                SUM(CASE WHEN total_utilization > 100 THEN 1 ELSE 0 END) AS overloaded
            FROM (
                SELECT user_id, SUM(utilization_percentage) AS total_utilization
                FROM project_staffings
                WHERE is_active = true
                GROUP BY user_id
            ) AS user_utilization
        `);

        const staffing_stats = {
            ongoing_projects: parseInt(stats[0]?.ongoing_projects as string) || 0,
            available: parseInt(stats[0]?.available as string) || 0,
            overloaded: parseInt(stats[0]?.overloaded as string) || 0
        };

        const assignments = await ProjectStaffModel.findAll({
            where: whereClause,
            include: [
                { 
                    model: ProjectMasterModel, 
                    as: 'project',
                    required: false,
                    attributes: {exclude: ['createdAt', 'updatedAt']},
                    include: [
                        {
                            model: ClientMasterModel,
                            as: 'client',
                            required: false,
                            attributes: {exclude: ['createdAt', 'updatedAt']},
                        }
                    ]
                },
                { 
                    model: UserModel, 
                    as: 'user',
                    required: false,
                    attributes: {exclude: ['createdAt', 'updatedAt', 'token']},
                },
                { 
                    model: ProjectRoleModel, 
                    as: 'role',
                    required: false,
                    attributes: {exclude: ['createdAt', 'updatedAt']},
                },
            ],
            order: [['user_id', 'ASC']],
        });

        // Group by user_id
        const grouped: any = {};

        for(const assignment of assignments) {
            const userId = assignment.user_id;
            if(!grouped[userId]) {
                grouped[userId] = {
                    user_id: userId,
                    total_utilization: 0,
                    assignments: []
                };
            };
            grouped[userId].total_utilization += assignment.utilization_percentage;
            grouped[userId].assignments.push({
                id: assignment.id,
                project: assignment.project,
                role: assignment.role,
                user: assignment.user,
                utilization_type: assignment.utilization_type,
                utilization_percentage: assignment.utilization_percentage,
            });
        }

        //convert object into array
        const result = Object.values(grouped);

        res.status(200).json({
            success: true,
            message: 'All Staffing Assignments',
            staffing_stats,
            data: result,
            
        });
    } catch (error) {
        console.error('Error Fetching Staffing Assignment:', error);
        res.status(500).json({
            success: false,
            message: "We hit a snag! Our team is looking into it.",
            error: error instanceof Error ? error.message : 'Unknown error'
        });
    }
}

//create staffing assignment
export const createStaffAssignment = async (req: Request, res: Response): Promise<void> => {
    try {
      const {
            id,
            project_id,
            user_id,
            project_role_master_id,
            utilization_type,
            utilization_percentage,
            type
      } = req.body;

      if(type === 'delete'){
            if (!id) {
                res.status(404).json({ success: false, message: "ID is required for deletion." });
                return;
            }
          
            const projectDelete = await ProjectStaffModel.findOne({ where: { id } })  

            if (!projectDelete) {
                res.status(404).json({ success: false, message: "Project Not Found" });
                return;
            }

            const project = await ProjectStaffModel.update(
                {
                is_active: false,
                },
                {
                    where: { id }
                }
            );

            res.status(200).json({
                success: true,
                message: 'Staffing assignment inactive successfully',
                data: project,
            });

      } else if(id) {
        const assignment = await ProjectStaffModel.findOne({ where: { id } });
        if(!assignment) {
            res.status(404).json({
                success: false,
                message: 'Staffing assignment not found',
            });
            return;
        }
        //update
        await assignment.update({
            project_role_master_id,
            utilization_type,
            utilization_percentage,
        });
        res.status(200).json({
            success: true,
            message: 'Staffing assignment updated successfully',
            data: assignment,
        });
      } else {
        const project = await ProjectMasterModel.findOne({
            where: { id: project_id, is_active: true },
        });
        if (!project) {
            res.status(404).json({
                success: false,
                message: 'Project not found.'
            });
            return;
        }

        console.log('client_id:', project.client_id);

        const existingAssignment = await ProjectStaffModel.findOne({
            where: {
                project_id,
                user_id,
                is_active: true
            }
        });     

        if (existingAssignment) {
            res.status(404).json({
                success: false,
                message: 'User is already assigned to this project.'
            });
            return;
        }
        
        //create
        const assignment = await ProjectStaffModel.create({
            project_id,
            user_id,
            project_role_master_id,
            utilization_type,
            utilization_percentage,
        });
        res.status(200).json({
            success: true,
            message: 'Staffing assignment created successfully',
            data: {
                assignment,
                client_id: project.client_id,
            }
        });
      }

    } catch (error) {
        console.error('Error creating staffing assignment:', error);
        res.status(500).json({
            success: false,
            message: 'Internal server error',
            error: error instanceof Error ? error.message : 'Unknown error'
        });
    }
};  

//get staffing users
export const getStaffingUsers = async (req: Request, res: Response): Promise<void> => {
    try {
        const users = await UserModel.findAll({
            where: { isActive: true },
            attributes: { exclude: ['createdAt', 'updatedAt', 'token'] },
            include: [
                {
                    model: ProjectStaffModel,
                    as: 'staffings',
                    required: false,
                    where: { is_active: true },
                    include: [
                        {
                            model: ProjectMasterModel,
                            as: 'project',
                            attributes: { exclude: ['createdAt', 'updatedAt'] },
                            include: [
                                {
                                    model: ClientMasterModel,
                                    as: 'client',
                                    required: false,
                                    attributes: { exclude: ['createdAt', 'updatedAt'] }
                                }
                            ]
                        },
                        {
                            model: ProjectRoleModel,
                            as: 'role',
                            attributes: { exclude: ['createdAt', 'updatedAt'] }
                        }
                    ]
                }
            ],
            order: [['createdAt', 'ASC']]
        });

        res.status(200).json({
            success: true,
            message: 'All staffing users',
            data: users
        });

    } catch (error) {
        console.error('Error Fetching Staffing Users:', error);
        res.status(500).json({
            success: false,
            message: "We hit a snag! Our team is looking into it.",
            error: error instanceof Error ? error.message : 'Unknown error'
        });
    }
}

//get staffing projects
export const getStaffingProjects = async (req: Request, res: Response): Promise<void> => {
    try {
        const { client_id, search } = req.query;

        const projectWhereClause: any = { is_active: true };
        if (search) {
            projectWhereClause.project = { [Op.iLike]: `%${search}%` };
        }
        if (client_id) {
            projectWhereClause.client_id = client_id;
        }

        const projects = await ProjectMasterModel.findAll({
            where: projectWhereClause,
            attributes: { exclude: ['createdAt', 'updatedAt'] },
            include: [
                {
                    model: ClientMasterModel,
                    as: 'client',
                    required: false,
                    where: { is_active: true },
                    attributes: { exclude: ['createdAt', 'updatedAt'] }
                },
                {
                    model: ProjectStaffModel,
                    as: 'staffings',
                    required: false,
                    where: { is_active: true },
                    include: [
                        {
                            model: ProjectRoleModel,
                            as: 'role',
                            attributes: { exclude: ['createdAt', 'updatedAt'] }
                        },
                        {
                            model: UserModel,
                            as: 'user',
                            attributes: { exclude: ['createdAt', 'updatedAt', 'token'] }
                        }
                    ]
                }
            ],
            order: [['createdAt', 'ASC']]
        });

        res.status(200).json({
            success: true,
            message: 'All Staffing Projects',
            count: projects.length,
            data: projects
        });

    } catch (error) {
        console.error('Error Fetching Staffing Projects:', error);
        res.status(500).json({
            success: false,
            message: "We hit a snag! Our team is looking into it.",
            error: error instanceof Error ? error.message : 'Unknown error'
        });
    }
}

//get project staffing assignment
export const getProjectStaffingAssignment = async (req: Request, res:Response): Promise<void> => {
    try {

        const whereClause: any = { is_active: true };

        const assignments = await ProjectStaffModel.findAll({
            where: whereClause,
            include: [
                { 
                    model: ProjectMasterModel, 
                    as: 'project',
                    required: false,
                    attributes: {exclude: ['createdAt', 'updatedAt']},
                    include: [
                        {
                            model: ClientMasterModel,
                            as: 'client',
                            required: false,
                            attributes: {exclude: ['createdAt', 'updatedAt']},
                        }
                    ]
                },
                { 
                    model: UserModel, 
                    as: 'user',
                    required: false,
                    attributes: {exclude: ['createdAt', 'updatedAt', 'token']},
                },
                { 
                    model: ProjectRoleModel, 
                    as: 'role',
                    required: false,
                    attributes: {exclude: ['createdAt', 'updatedAt']},
                },
            ],
            order: [['project_id', 'ASC']],
        });

        // Group by project_id
        const grouped: any = {};

        for(const assignment of assignments) {
            const projectId = assignment.project_id;
            if(!grouped[projectId]) {
                grouped[projectId] = {
                    project_id: projectId,
                    project: assignment.project,
                    total_utilization: 0,
                    members: []
                };
            };
            grouped[projectId].total_utilization += assignment.utilization_percentage;
            grouped[projectId].members.push({
                id: assignment.id,
                user: assignment.user,
                role: assignment.role,
                utilization_type: assignment.utilization_type,
                utilization_percentage: assignment.utilization_percentage,
            });
        }

        //convert object into array
        const result = Object.values(grouped);

        res.status(200).json({
            success: true,
            message: 'All Project Staffing Assignments',
            data: result,
            
        });
    } catch (error) {
        console.error('Error Fetching Project Staffing Assignment:', error);
        res.status(500).json({
            success: false,
            message: "We hit a snag! Our team is looking into it.",
            error: error instanceof Error ? error.message : 'Unknown error'
        });
    }
}