from ..services.mysql_service import get_db_connection

class Staff:
    @staticmethod
    def get_all_staff(limit=10, team_gid=None):
        """Fetch a list of all staff, with a limit, including their default team name."""
        conn = get_db_connection()
        if not conn:
            return []

        try:
            cursor = conn.cursor(dictionary=True)
            
            params = []
            
            query = """
                SELECT 
                    e.pid, 
                    e.first_name, 
                    e.last_name,
                    ug.group_name as team_name
                FROM 
                    hris_core.employee_information_tbl e
                LEFT JOIN
                    intra.tag_group tg ON e.pid = tg.user_id AND tg.workspace_default = 'Y'
                LEFT JOIN
                    intra.user_group ug ON tg.group_id = ug.GID AND ug.workspace_status = 1
                WHERE
                    e.status = 'hired'
            """
            
            if team_gid:
                query += " AND tg.group_id = %s"
                params.append(team_gid)

            query += " ORDER BY e.first_name, e.last_name LIMIT %s"
            params.append(limit)
            
            cursor.execute(query, tuple(params))
            users = cursor.fetchall()
            return users
        except Exception as e:
            print(f"Error fetching all staff: {e}")
            return []
        finally:
            if conn and conn.is_connected():
                cursor.close()
                conn.close()

    @staticmethod
    def get_all_hired_staff_for_update(team_gid=None):
        """Fetch a list of all hired staff for scheduler updates, including their default team name."""
        conn = get_db_connection()
        if not conn:
            return []

        try:
            cursor = conn.cursor(dictionary=True)
            
            params = []
            
            query = """
                SELECT 
                    e.pid, 
                    e.first_name, 
                    e.last_name,
                    ug.group_name as team_name
                FROM 
                    hris_core.employee_information_tbl e
                LEFT JOIN
                    intra.tag_group tg ON e.pid = tg.user_id AND tg.workspace_default = 'Y'
                LEFT JOIN
                    intra.user_group ug ON tg.group_id = ug.GID AND ug.workspace_status = 1
                WHERE
                    e.status = 'hired'
            """
            
            if team_gid:
                query += " AND tg.group_id = %s"
                params.append(team_gid)

            query += " ORDER BY e.first_name, e.last_name"
            
            cursor.execute(query, tuple(params))
            users = cursor.fetchall()
            return users
        except Exception as e:
            print(f"Error fetching all hired staff for update: {e}")
            return []
        finally:
            if conn and conn.is_connected():
                cursor.close()
                conn.close()

    @staticmethod
    def search_by_name(name, team_gid=None):
        """Search for staff by name, including their default team name."""
        conn = get_db_connection()
        if not conn:
            return []

        try:
            cursor = conn.cursor(dictionary=True)
            params = []

            query = """
                SELECT 
                    e.pid, 
                    e.first_name, 
                    e.last_name,
                    ug.group_name as team_name
                FROM 
                    hris_core.employee_information_tbl e
                LEFT JOIN
                    intra.tag_group tg ON e.pid = tg.user_id AND tg.workspace_default = 'Y'
                LEFT JOIN
                    intra.user_group ug ON tg.group_id = ug.GID AND ug.workspace_status = 1
                WHERE 
                    CONCAT(e.first_name, ' ', e.last_name) LIKE %s
                    AND e.status = 'hired'
            """
            search_term = f"%{name}%"
            params.append(search_term)

            if team_gid:
                query += " AND tg.group_id = %s"
                params.append(team_gid)
            
            query += " LIMIT 10"

            cursor.execute(query, tuple(params))
            users = cursor.fetchall()
            return users
        except Exception as e:
            print(f"Error searching staff by name: {e}")
            return []
        finally:
            if conn and conn.is_connected():
                cursor.close()
                conn.close()

    @staticmethod
    def get_by_pid(pid):
        """Fetch staff details by PID, including their default team name."""
        conn = get_db_connection()
        if not conn:
            return None

        try:
            cursor = conn.cursor(buffered=True, dictionary=True)
            query = """
                SELECT 
                    e.pid, 
                    e.first_name, 
                    e.last_name,
                    e.company_email, 
                    e.profile_photo_path,
                    p.name as position,
                    ug.group_name as team_name
                FROM 
                    hris_core.employee_information_tbl e
                JOIN 
                    hris_core.position_levels_tbl p ON e.position_id = p.id
                LEFT JOIN
                    intra.tag_group tg ON e.pid = tg.user_id AND tg.workspace_default = 'Y'
                LEFT JOIN
                    intra.user_group ug ON tg.group_id = ug.GID AND ug.workspace_status = 1
                WHERE 
                    e.pid = %s
            """
            cursor.execute(query, (pid,))
            user = cursor.fetchone()
            return user
        except Exception as e:
            print(f"Error fetching staff by PID: {e}")
            return None
        finally:
            if conn and conn.is_connected():
                cursor.close()
                conn.close()

    @staticmethod
    def get_all_staff_by_team(team_gid):
        """Fetch all hired staff for a specific team, including their email."""
        conn = get_db_connection()
        if not conn:
            return []

        try:
            cursor = conn.cursor(dictionary=True)
            query = """
                SELECT 
                    e.pid, 
                    e.first_name, 
                    e.last_name,
                    e.company_email
                FROM 
                    hris_core.employee_information_tbl e
                JOIN
                    intra.tag_group tg ON e.pid = tg.user_id AND tg.workspace_default = 'Y'
                WHERE
                    e.status = 'hired' AND tg.group_id = %s
            """
            cursor.execute(query, (team_gid,))
            users = cursor.fetchall()
            return users
        except Exception as e:
            print(f"Error fetching staff by team GID: {e}")
            return []
        finally:
            if conn and conn.is_connected():
                cursor.close()
                conn.close()