def get_member_count(self) -> int:
    """Get the current number of team members."""
    from sqlalchemy.orm import object_session

    session = object_session(self)
    if session is None:
        return len([m for m in self.members if m.is_active])

    count = session.query(func.count(EmailTeamMember.id)).filter(
        EmailTeamMember.team_id == self.id,
        EmailTeamMember.is_active.is_(True)
    ).scalar()
    return count or 0

# Add to existing imports at top of file
from typing import Any, Dict, List, Optional, Tuple  # Add Dict, Any
from sqlalchemy import func, select  # Add func

def get_member_counts_batch(self, team_ids: List[str]) -> Dict[str, int]:
    """Get member counts for multiple teams in a single query.

    This is a synchronous method following the existing service pattern.
    Note: Like other sync SQLAlchemy calls, this will block the event
    loop in async contexts. For typical team counts this is acceptable.

    Args:
        team_ids: List of team UUIDs

    Returns:
        Dict mapping team_id to member count

    Raises:
        Exception: Re-raises any database errors after rollback

    Examples:
        >>> from unittest.mock import Mock
        >>> service = TeamManagementService(Mock())
        >>> service.get_member_counts_batch([])
        {}
    """
    if not team_ids:
        return {}

    try:
        # Single query for all teams
        results = self.db.query(
            EmailTeamMember.team_id,
            func.count(EmailTeamMember.id).label("count")
        ).filter(
            EmailTeamMember.team_id.in_(team_ids),
            EmailTeamMember.is_active.is_(True)
        ).group_by(EmailTeamMember.team_id).all()

        self.db.commit()  # Release transaction to avoid idle-in-transaction

        # Build result dict, defaulting to 0 for teams with no members
        counts = {str(row.team_id): row.count for row in results}
        return {tid: counts.get(tid, 0) for tid in team_ids}
    except Exception as e:
        self.db.rollback()
        logger.error(f"Failed to get member counts for teams: {e}")
        raise

def get_user_roles_batch(self, user_email: str, team_ids: List[str]) -> Dict[str, Optional[str]]:
    """Get a user's role in multiple teams in a single query.

    Args:
        user_email: Email of the user
        team_ids: List of team UUIDs

    Returns:
        Dict mapping team_id to role (or None if not a member)

    Raises:
        Exception: Re-raises any database errors after rollback
    """
    if not team_ids:
        return {}

    try:
        # Single query for all teams
        results = self.db.query(
            EmailTeamMember.team_id,
            EmailTeamMember.role
        ).filter(
            EmailTeamMember.user_email == user_email,
            EmailTeamMember.team_id.in_(team_ids),
            EmailTeamMember.is_active.is_(True)
        ).all()

        self.db.commit()  # Release transaction to avoid idle-in-transaction

        # Build result dict - teams with no membership return None
        roles = {str(row.team_id): row.role for row in results}
        return {tid: roles.get(tid) for tid in team_ids}
    except Exception as e:
        self.db.rollback()
        logger.error(f"Failed to get user roles for {user_email}: {e}")
        raise

def get_pending_join_requests_batch(self, user_email: str, team_ids: List[str]) -> Dict[str, Optional[Any]]:
    """Get pending join requests for a user across multiple teams in a single query.

    Args:
        user_email: Email of the user
        team_ids: List of team UUIDs to check

    Returns:
        Dict mapping team_id to pending EmailTeamJoinRequest (or None if no pending request)

    Raises:
        Exception: Re-raises any database errors after rollback
    """
    if not team_ids:
        return {}

    try:
        # Single query for all pending requests across teams
        results = self.db.query(EmailTeamJoinRequest).filter(
            EmailTeamJoinRequest.user_email == user_email,
            EmailTeamJoinRequest.team_id.in_(team_ids),
            EmailTeamJoinRequest.status == "pending"
        ).all()

        self.db.commit()  # Release transaction to avoid idle-in-transaction

        # Build result dict - only one pending request per team expected
        requests = {str(req.team_id): req for req in results}
        return {tid: requests.get(tid) for tid in team_ids}
    except Exception as e:
        self.db.rollback()
        logger.error(f"Failed to get pending join requests for {user_email}: {e}")
        raise

raw_teams = await team_service.get_user_teams(user_email)
user_teams = []
for team in raw_teams:
    user_role = await team_service.get_user_role_in_team(user_email, team.id)
    team_dict = {
        "id": str(team.id),
        "name": str(team.name),
        "member_count": team.get_member_count(),  # N+1 HERE
        "role": user_role or "member",
    }
    user_teams.append(team_dict)

raw_teams = await team_service.get_user_teams(user_email)

# Batch fetch all data in 2 queries instead of 2N queries
team_ids = [str(team.id) for team in raw_teams]
member_counts = await team_service.get_member_counts_batch_cached(team_ids)
user_roles = team_service.get_user_roles_batch(user_email, team_ids)

user_teams = []
for team in raw_teams:
    try:
        team_id = str(team.id)
        team_dict = {
            "id": team_id,
            "name": str(team.name) if team.name else "",
            "type": str(getattr(team, "type", "organization")),
            "is_personal": bool(getattr(team, "is_personal", False)),
            "member_count": member_counts.get(team_id, 0),
            "role": user_roles.get(team_id) or "member",
        }
        user_teams.append(team_dict)
    except Exception as team_error:
        LOGGER.warning(f"Failed to serialize team {getattr(team, 'id', 'unknown')}: {team_error}")
        continue

user_teams = await team_service.get_user_teams(current_user.email)
public_teams = await team_service.discover_public_teams(current_user.email)

all_teams = []
for team in user_teams:
    user_role = await team_service.get_user_role_in_team(current_user.email, team.id)
    all_teams.append({
        "team": team,
        "relationship": "owner" if user_role == "owner" else "member",
        "member_count": team.get_member_count()  # N+1 HERE
    })

for team in public_teams:
    all_teams.append({
        "team": team,
        "member_count": team.get_member_count()  # N+1 HERE
    })

user_teams = await team_service.get_user_teams(current_user.email)
public_teams = await team_service.discover_public_teams(current_user.email)

# Batch fetch ALL data upfront - 3 queries instead of 3N queries
user_team_ids = [str(t.id) for t in user_teams]
public_team_ids = [str(t.id) for t in public_teams]
all_team_ids = user_team_ids + public_team_ids

member_counts = await team_service.get_member_counts_batch_cached(all_team_ids)
user_roles = team_service.get_user_roles_batch(current_user.email, user_team_ids)
pending_requests = team_service.get_pending_join_requests_batch(current_user.email, public_team_ids)

all_teams = []

# Add user's teams (owned and member)
for team in user_teams:
    team_id = str(team.id)
    user_role = user_roles.get(team_id)
    relationship = "owner" if user_role == "owner" else "member"
    all_teams.append({
        "team": team,
        "relationship": relationship,
        "member_count": member_counts.get(team_id, 0)
    })

# Add public teams user can join
for team in public_teams:
    team_id = str(team.id)
    all_teams.append({
        "team": team,
        "relationship": "join",
        "member_count": member_counts.get(team_id, 0),
        "pending_request": pending_requests.get(team_id)  # None if no pending request
    })

if current_user.is_admin:
    teams, _ = await team_service.list_teams()
else:
    teams = await team_service.get_user_teams(current_user.email)

teams_html = ""
for team in teams:
    member_count = team.get_member_count()  # N+1 HERE
    teams_html += f"<div>...{member_count}...</div>"

if current_user.is_admin:
    teams, _ = await team_service.list_teams()
else:
    teams = await team_service.get_user_teams(current_user.email)

# Batch fetch member counts with caching
team_ids = [str(team.id) for team in teams]
member_counts = await team_service.get_member_counts_batch_cached(team_ids)

teams_html = ""
for team in teams:
    member_count = member_counts.get(str(team.id), 0)
    teams_html += f"<div>...{member_count}...</div>"

team_responses = [
    TeamResponse(
        id=team.id,
        name=team.name,
        member_count=team.get_member_count(),  # N+1 HERE
        ...
    )
    for team in teams
]

# Batch fetch member counts with caching
team_ids = [str(team.id) for team in teams]
member_counts = await service.get_member_counts_batch_cached(team_ids)

team_responses = [
    TeamResponse(
        id=team.id,
        name=team.name,
        member_count=member_counts.get(str(team.id), 0),
        ...
    )
    for team in teams
]

team_responses = [
    DiscoverableTeamResponse(
        id=team.id,
        name=team.name,
        member_count=team.get_member_count(),  # N+1 HERE
        ...
    )
    for team in teams
]

# Batch fetch member counts with caching
team_ids = [str(team.id) for team in teams]
member_counts = await service.get_member_counts_batch_cached(team_ids)

team_responses = [
    DiscoverableTeamResponse(
        id=team.id,
        name=team.name,
        member_count=member_counts.get(str(team.id), 0),
        ...
    )
    for team in teams
]

"""Add partial index for team member count queries

Revision ID: xxx
Revises: <previous>
Create Date: 2026-01-04
"""
from typing import Sequence, Union

from alembic import op
from sqlalchemy import inspect

revision: str = "xxx"
down_revision: Union[str, Sequence[str], None] = "<previous>"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def _index_exists(table_name: str, index_name: str) -> bool:
    """Check if an index already exists."""
    conn = op.get_bind()
    inspector = inspect(conn)
    try:
        existing = inspector.get_indexes(table_name)
        return any(idx["name"] == index_name for idx in existing)
    except Exception:
        return False


def upgrade() -> None:
    """Add partial index for team member count queries."""
    conn = op.get_bind()
    dialect = conn.dialect.name

    if dialect == "postgresql":
        # PostgreSQL: Use partial index (most efficient for WHERE is_active = true)
        # Note: NOT using CONCURRENTLY since that requires running outside transaction
        # For production with large tables, consider running CONCURRENTLY manually:
        #   CREATE INDEX CONCURRENTLY idx_email_team_members_team_active_partial
        #   ON email_team_members(team_id) WHERE is_active = true;
        if not _index_exists("email_team_members", "idx_email_team_members_team_active_partial"):
            op.execute("""
                CREATE INDEX idx_email_team_members_team_active_partial
                ON email_team_members(team_id)
                WHERE is_active = true
            """)
            print("✓ Created partial index idx_email_team_members_team_active_partial")
    else:
        # SQLite/MySQL: Regular composite index (no partial index support)
        if not _index_exists("email_team_members", "idx_email_team_members_team_active_count"):
            op.create_index(
                "idx_email_team_members_team_active_count",
                "email_team_members",
                ["team_id", "is_active"]
            )
            print("✓ Created composite index idx_email_team_members_team_active_count")


def downgrade() -> None:
    """Remove the partial index."""
    conn = op.get_bind()
    dialect = conn.dialect.name

    if dialect == "postgresql":
        op.execute("DROP INDEX IF EXISTS idx_email_team_members_team_active_partial")
    else:
        try:
            op.drop_index("idx_email_team_members_team_active_count", "email_team_members")
        except Exception:
            pass  # Index may not exist

# Team member count cache settings
team_member_count_cache_enabled: bool = Field(
    default=True,
    description="Enable Redis caching for team member counts"
)
team_member_count_cache_ttl: int = Field(
    default=300,
    ge=30,
    le=3600,
    description="TTL in seconds for team member count cache (default: 5 minutes)"
)

# In create_team() (line ~204, ~216) - after adding creator as owner:
await self.invalidate_team_member_count_cache(str(team.id))

# In add_member_to_team() (line ~486) - after successful add:
await self.invalidate_team_member_count_cache(str(team_id))

# In remove_member_from_team() (line ~516) - after successful remove:
await self.invalidate_team_member_count_cache(str(team_id))

# In approve_join_request() (line ~1057) - after creating new member:
await self.invalidate_team_member_count_cache(str(join_request.team_id))

# In update_member_role() (line ~588):
# Note: update_member_role only changes role, not is_active, so no invalidation needed