package team

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/access"
	storeagent "github.com/tidbcloud/AgentTeam/internal/store/agent"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
)

const (
	DefaultTeamDirectoryLimit        = 50
	MaxTeamDirectoryLimit            = 100
	DefaultTeamMentionCandidateLimit = 50
	MaxTeamMentionCandidateLimit     = 50
)

type teamViewer struct {
	memberID         string
	workspaceAdmin   bool
	workspaceOwner   bool
	workspaceRoleRaw string
}

// ListTeamsForUser lists Teams visible to one human caller.
func (store *Store) ListTeamsForUser(ctx context.Context, userID string, workspaceID string, filter ListTeamsFilter) ([]TeamView, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	viewer, err := loadTeamViewer(ctx, queryer, userID, workspaceID)
	if err != nil {
		return nil, err
	}
	status, err := normalizeStatusFilter(filter.Status)
	if err != nil {
		return nil, err
	}
	limit := boundedLimit(filter.Limit, DefaultTeamDirectoryLimit, MaxTeamDirectoryLimit)
	query := strings.ToLower(trim(filter.Query))
	pattern := "%" + query + "%"
	rows, err := queryer.Query(ctx, teamViewSelectSQL()+`
WHERE t.workspace_id = $1
  AND t.created_by_member_id = $2
  AND ($3 = '' OR t.status = $3)
  AND ($4 = '' OR LOWER(t.handle) LIKE $5 OR LOWER(t.display_name) LIKE $5 OR LOWER(t.description) LIKE $5)
ORDER BY t.display_name, t.handle, t.id
LIMIT $6
`, trim(workspaceID), viewer.memberID, status, query, pattern, limit)
	if err != nil {
		return nil, fmt.Errorf("list teams: %w", err)
	}
	defer rows.Close()
	var teams []TeamView
	for rows.Next() {
		view, err := scanTeamView(rows, viewer)
		if err != nil {
			return nil, fmt.Errorf("scan team view: %w", err)
		}
		teams = append(teams, view)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate team views: %w", err)
	}
	for index := range teams {
		if teams[index].Team.Status != StatusActive || trim(teams[index].Team.ChannelID) != "" {
			continue
		}
		view, err := store.ensureTeamChannelView(ctx, viewer, workspaceID, teams[index].Team.ID)
		if err != nil {
			return nil, err
		}
		teams[index] = view
	}
	return teams, nil
}

// GetTeamForUser loads one Team if the caller can discover it.
func (store *Store) GetTeamForUser(ctx context.Context, userID string, workspaceID string, teamID string) (TeamView, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return TeamView{}, err
	}
	viewer, err := loadTeamViewer(ctx, queryer, userID, workspaceID)
	if err != nil {
		return TeamView{}, err
	}
	view, err := loadTeamViewForViewer(ctx, queryer, viewer, workspaceID, teamID)
	if err != nil {
		return TeamView{}, err
	}
	if view.Team.Status == StatusActive && trim(view.Team.ChannelID) == "" {
		return store.ensureTeamChannelView(ctx, viewer, workspaceID, view.Team.ID)
	}
	return view, nil
}

// CreateTeamForUser creates one Team on behalf of an active workspace member.
func (store *Store) CreateTeamForUser(ctx context.Context, userID string, workspaceID string, params CreateTeamParams) (TeamView, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return TeamView{}, err
	}
	viewer, err := loadTeamViewer(ctx, queryer, userID, workspaceID)
	if err != nil {
		return TeamView{}, err
	}
	if err := validateUserAddedTeamMemberParams(ctx, queryer, workspaceID, userID, params.InitialMembers); err != nil {
		return TeamView{}, err
	}
	params.WorkspaceID = trim(workspaceID)
	params.CreatedByMemberID = viewer.memberID
	params.Visibility = VisibilityPrivate
	created, err := store.CreateTeam(ctx, params)
	if err != nil {
		return TeamView{}, err
	}
	return loadTeamViewForViewer(ctx, queryer, viewer, workspaceID, created.ID)
}

// UpdateTeamForUser updates editable Team profile fields.
func (store *Store) UpdateTeamForUser(ctx context.Context, userID string, workspaceID string, teamID string, params UpdateTeamParams) (TeamView, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return TeamView{}, err
	}
	viewer, err := requireTeamManager(ctx, queryer, userID, workspaceID, teamID)
	if err != nil {
		return TeamView{}, err
	}
	visibility := VisibilityPrivate
	params.Visibility = &visibility
	updated, err := store.UpdateTeam(ctx, workspaceID, teamID, params)
	if err != nil {
		return TeamView{}, err
	}
	return loadTeamViewForViewer(ctx, queryer, viewer, workspaceID, updated.ID)
}

// DeleteTeamForUser permanently deletes one Team. Only the Team creator can delete it.
func (store *Store) DeleteTeamForUser(ctx context.Context, userID string, workspaceID string, teamID string) error {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return err
	}
	if _, err := requireTeamManager(ctx, queryer, userID, workspaceID, teamID); err != nil {
		return err
	}
	return store.DeleteTeam(ctx, workspaceID, teamID)
}

// ListTeamMembersForUser lists active Team members for a visible Team.
func (store *Store) ListTeamMembersForUser(ctx context.Context, userID string, workspaceID string, teamID string) ([]TeamMemberView, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	if _, err := store.GetTeamForUser(ctx, userID, workspaceID, teamID); err != nil {
		return nil, err
	}
	return listTeamMemberViews(ctx, queryer, workspaceID, teamID)
}

// ListTeamMemberViews returns the active Agent member read models for trusted
// internal presenters. Authorization belongs to the calling workflow.
func (store *Store) ListTeamMemberViews(
	ctx context.Context,
	workspaceID string,
	teamID string,
) ([]TeamMemberView, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	return listTeamMemberViews(ctx, queryer, workspaceID, teamID)
}

// AddTeamMemberForUser adds one Team member when the caller can manage the Team.
func (store *Store) AddTeamMemberForUser(ctx context.Context, userID string, workspaceID string, teamID string, params AddTeamMemberParams) (TeamMemberView, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return TeamMemberView{}, err
	}
	viewer, err := requireTeamManager(ctx, queryer, userID, workspaceID, teamID)
	if err != nil {
		return TeamMemberView{}, err
	}
	if err := validateUserAddedTeamActor(ctx, queryer, workspaceID, userID, params.ActorKind, params.ActorID); err != nil {
		return TeamMemberView{}, err
	}
	params.WorkspaceID = trim(workspaceID)
	params.TeamID = trim(teamID)
	params.AddedByMemberID = viewer.memberID
	member, err := store.AddTeamMember(ctx, params)
	if err != nil {
		return TeamMemberView{}, err
	}
	return loadTeamMemberView(ctx, queryer, member.WorkspaceID, member.TeamID, member.ID)
}

// UpdateTeamMemberForUser updates Team member management metadata.
func (store *Store) UpdateTeamMemberForUser(ctx context.Context, userID string, workspaceID string, teamID string, teamMemberID string, params UpdateTeamMemberParams) (TeamMemberView, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return TeamMemberView{}, err
	}
	if _, err := requireTeamManager(ctx, queryer, userID, workspaceID, teamID); err != nil {
		return TeamMemberView{}, err
	}
	current, err := loadTeamMember(ctx, queryer, workspaceID, teamID, teamMemberID)
	if err != nil {
		return TeamMemberView{}, err
	}
	if err := validateUserUpdatedTeamMemberParams(current, params); err != nil {
		return TeamMemberView{}, err
	}
	member, err := store.UpdateTeamMember(ctx, workspaceID, teamID, teamMemberID, params)
	if err != nil {
		return TeamMemberView{}, err
	}
	return loadTeamMemberView(ctx, queryer, member.WorkspaceID, member.TeamID, member.ID)
}

// RemoveTeamMemberForUser soft-removes one Team member.
func (store *Store) RemoveTeamMemberForUser(ctx context.Context, userID string, workspaceID string, teamID string, teamMemberID string) error {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return err
	}
	if _, err := requireTeamManager(ctx, queryer, userID, workspaceID, teamID); err != nil {
		return err
	}
	return store.RemoveTeamMember(ctx, workspaceID, teamID, teamMemberID, time.Now().UTC())
}

// ListTeamMentionCandidatesForUser lists compact visible Team rows for mention pickers.
func (store *Store) ListTeamMentionCandidatesForUser(ctx context.Context, userID string, workspaceID string, query string, limit int, scopeID string) ([]TeamMentionCandidate, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	viewer, err := loadTeamViewer(ctx, queryer, userID, workspaceID)
	if err != nil {
		return nil, err
	}
	bounded := boundedLimit(limit, DefaultTeamMentionCandidateLimit, MaxTeamMentionCandidateLimit)
	search := strings.ToLower(trim(query))
	pattern := "%" + search + "%"
	rows, err := queryer.Query(ctx, teamCandidateSelectSQL()+`
WHERE t.workspace_id = $1
  AND t.created_by_member_id = $2
  AND t.status = $3
  AND ($4 = '' OR LOWER(t.handle) LIKE $5 OR LOWER(t.display_name) LIKE $5 OR LOWER(t.description) LIKE $5 OR EXISTS (
    SELECT 1
    FROM workspace_team_members leader_tm
    JOIN members leader_member
      ON leader_member.workspace_id = leader_tm.workspace_id
     AND leader_member.id = leader_tm.actor_id
     AND leader_member.type = 'agent'
     AND leader_member.status = 'active'
    JOIN agents leader_agent
      ON leader_agent.workspace_id = leader_tm.workspace_id
     AND leader_agent.member_id = leader_tm.actor_id
     AND leader_agent.status <> 'archived'
    WHERE leader_tm.workspace_id = t.workspace_id
      AND leader_tm.team_id = t.id
      AND leader_tm.actor_kind = 'agent'
      AND leader_tm.role = 'leader'
      AND leader_tm.removed_at IS NULL
      AND (
        LOWER(leader_agent.name) LIKE $5
        OR LOWER(leader_tm.actor_id) LIKE $5
        OR LOWER(COALESCE(NULLIF(leader_member.display_name, ''), '')) LIKE $5
      )
  ))
ORDER BY t.display_name, t.handle, t.id
LIMIT $6
`, trim(workspaceID), viewer.memberID, StatusActive, search, pattern, bounded)
	if err != nil {
		return nil, fmt.Errorf("list team mention candidates: %w", err)
	}
	defer rows.Close()
	var candidates []TeamMentionCandidate
	for rows.Next() {
		candidate, err := scanTeamMentionCandidate(rows)
		if err != nil {
			return nil, fmt.Errorf("scan team mention candidate: %w", err)
		}
		candidates = append(candidates, candidate)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate team mention candidates: %w", err)
	}
	if trim(scopeID) != "" {
		if err := annotateTeamMentionCandidateDeliveryPreview(ctx, queryer, workspaceID, trim(scopeID), viewer.memberID, candidates); err != nil {
			return nil, err
		}
	}
	return candidates, nil
}

func annotateTeamMentionCandidateDeliveryPreview(ctx context.Context, queryer coredb.Queryer, workspaceID string, scopeID string, authorMemberID string, candidates []TeamMentionCandidate) error {
	if len(candidates) == 0 {
		return nil
	}
	teamIDs := make([]string, 0, len(candidates))
	indexByTeamID := make(map[string]int, len(candidates))
	for index, candidate := range candidates {
		teamID := trim(candidate.Team.ID)
		if teamID == "" {
			continue
		}
		teamIDs = append(teamIDs, teamID)
		indexByTeamID[teamID] = index
	}
	if len(teamIDs) == 0 {
		return nil
	}
	if err := annotateTeamMentionCandidateScopeAgentCounts(ctx, queryer, workspaceID, scopeID, authorMemberID, candidates, indexByTeamID, teamIDs); err != nil {
		return err
	}
	edges, err := expandTeamMentionsForScope(ctx, queryer, ExpandTeamMentionsParams{
		WorkspaceID:    workspaceID,
		ScopeID:        scopeID,
		AuthorMemberID: authorMemberID,
		TeamIDs:        teamIDs,
	})
	if err != nil {
		return err
	}
	for index := range candidates {
		candidates[index].DeliveryPreview = true
	}
	seenActors := make(map[string]struct{}, len(edges))
	for _, edge := range edges {
		teamID := trim(edge.TeamID)
		actorKind := trim(edge.ActorKind)
		actorID := trim(edge.ActorID)
		if teamID == "" || actorKind == "" || actorID == "" {
			continue
		}
		seenKey := teamID + "\x00" + actorKind + "\x00" + actorID
		if _, ok := seenActors[seenKey]; ok {
			continue
		}
		seenActors[seenKey] = struct{}{}
		index, ok := indexByTeamID[teamID]
		if !ok {
			continue
		}
		switch actorKind {
		case ActorKindAgent:
			if edge.WakeOnTeamMention {
				candidates[index].ReachableAgentCount++
			}
		}
	}
	return nil
}

func annotateTeamMentionCandidateScopeAgentCounts(ctx context.Context, queryer coredb.Queryer, workspaceID string, scopeID string, authorMemberID string, candidates []TeamMentionCandidate, indexByTeamID map[string]int, teamIDs []string) error {
	ids := normalizeNonEmptyStrings(teamIDs)
	if len(ids) == 0 {
		return nil
	}
	requestedTeamsCTE, teamArgs, _, err := coredb.BuildStringValuesCTE("requested_teams", "team_id", ids, 4)
	if err != nil {
		return err
	}
	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s
SELECT tm.team_id, COUNT(DISTINCT tm.actor_id)
FROM requested_teams
JOIN workspace_team_members tm
  ON tm.workspace_id = $1
 AND tm.team_id = requested_teams.team_id
 AND tm.actor_kind = 'agent'
 AND tm.removed_at IS NULL
JOIN scope_memberships sm
  ON sm.scope_id = $2
 AND sm.member_id = tm.actor_id
 AND sm.removed_at IS NULL
 AND sm.archived_at IS NULL
 AND sm.effective_to_seq IS NULL
LEFT JOIN scope_memberships author_private_context
  ON author_private_context.scope_id = sm.scope_id
 AND author_private_context.member_id = $3
 AND author_private_context.owner_member_id = sm.owner_member_id
 AND author_private_context.visibility = 'private'
 AND author_private_context.removed_at IS NULL
 AND author_private_context.archived_at IS NULL
 AND author_private_context.effective_to_seq IS NULL
WHERE (
      sm.visibility = 'public'
      OR (sm.visibility = 'private' AND sm.owner_member_id = $3)
      OR (sm.visibility = 'private' AND sm.owner_member_id IS NOT NULL AND author_private_context.member_id IS NOT NULL)
)
GROUP BY tm.team_id
`, requestedTeamsCTE), append([]any{trim(workspaceID), trim(scopeID), trim(authorMemberID)}, teamArgs...)...)
	if err != nil {
		return fmt.Errorf("annotate team mention scope agent counts: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var teamID string
		var count int64
		if err := rows.Scan(&teamID, &count); err != nil {
			return fmt.Errorf("scan team mention scope agent count: %w", err)
		}
		index, ok := indexByTeamID[trim(teamID)]
		if !ok {
			continue
		}
		candidates[index].ScopeAgentCount = int(count)
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate team mention scope agent counts: %w", err)
	}
	return nil
}

func loadTeamViewer(ctx context.Context, queryer coredb.Queryer, userID string, workspaceID string) (teamViewer, error) {
	membership, err := access.LoadHumanWorkspaceMember(ctx, queryer, trim(userID), trim(workspaceID))
	if err != nil {
		return teamViewer{}, mapAccessStoreError(err)
	}
	roleRaw := trim(membership.WorkspaceRole)
	role := domain.WorkspaceRole(roleRaw)
	return teamViewer{
		memberID:         membership.Member.ID,
		workspaceAdmin:   access.HasWorkspaceAdminRole(role),
		workspaceOwner:   access.IsWorkspaceOwnerRole(role),
		workspaceRoleRaw: roleRaw,
	}, nil
}

func requireTeamManager(ctx context.Context, queryer coredb.Queryer, userID string, workspaceID string, teamID string) (teamViewer, error) {
	viewer, err := loadTeamViewer(ctx, queryer, userID, workspaceID)
	if err != nil {
		return teamViewer{}, err
	}
	team, err := loadTeam(ctx, queryer, workspaceID, teamID)
	if err != nil {
		return teamViewer{}, err
	}
	if team.CreatedByMemberID == viewer.memberID {
		return viewer, nil
	}
	return teamViewer{}, forbidden("team creator is required")
}

func loadTeamViewForViewer(ctx context.Context, queryer coredb.Queryer, viewer teamViewer, workspaceID string, teamID string) (TeamView, error) {
	view, err := scanTeamView(queryer.QueryRow(ctx, teamViewSelectSQL()+`
WHERE t.workspace_id = $1
  AND t.created_by_member_id = $2
  AND t.id = $3
`, trim(workspaceID), viewer.memberID, trim(teamID)), viewer)
	if err != nil {
		if coredb.IsNotFound(err) {
			return TeamView{}, notFound("team not found")
		}
		return TeamView{}, fmt.Errorf("load team view: %w", err)
	}
	return view, nil
}

func teamViewSelectSQL() string {
	return `
SELECT t.id, t.workspace_id, t.handle, t.display_name, t.description, t.avatar_url,
       t.visibility, t.status, COALESCE(t.channel_id, ''), t.created_by_member_id, t.created_at, t.updated_at, t.archived_at,
       COALESCE(mc.member_count, 0)
FROM workspace_teams t
LEFT JOIN (
    SELECT workspace_id, team_id, COUNT(*) AS member_count
    FROM workspace_team_members
    WHERE removed_at IS NULL
      AND actor_kind = 'agent'
    GROUP BY workspace_id, team_id
) mc ON mc.workspace_id = t.workspace_id AND mc.team_id = t.id
`
}

func scanTeamView(row coredb.Row, viewer teamViewer) (TeamView, error) {
	var view TeamView
	var archivedAt sql.NullTime
	var memberCount int64
	if err := row.Scan(
		&view.Team.ID,
		&view.Team.WorkspaceID,
		&view.Team.Handle,
		&view.Team.DisplayName,
		&view.Team.Description,
		&view.Team.AvatarURL,
		&view.Team.Visibility,
		&view.Team.Status,
		&view.Team.ChannelID,
		&view.Team.CreatedByMemberID,
		&view.Team.CreatedAt,
		&view.Team.UpdatedAt,
		&archivedAt,
		&memberCount,
	); err != nil {
		return TeamView{}, err
	}
	view.Team.ID = trim(view.Team.ID)
	view.Team.WorkspaceID = trim(view.Team.WorkspaceID)
	view.Team.Handle = trim(view.Team.Handle)
	view.Team.Visibility = trim(view.Team.Visibility)
	view.Team.Status = trim(view.Team.Status)
	view.Team.ChannelID = trim(view.Team.ChannelID)
	view.Team.CreatedByMemberID = trim(view.Team.CreatedByMemberID)
	view.Team.ArchivedAt = timePtrFromNull(archivedAt)
	view.MemberCount = int(memberCount)
	view.CanManage = view.Team.CreatedByMemberID == viewer.memberID
	view.CanDelete = view.CanManage
	return view, nil
}

func listTeamMemberViews(ctx context.Context, queryer coredb.Queryer, workspaceID string, teamID string) ([]TeamMemberView, error) {
	rows, err := queryer.Query(ctx, teamMemberViewSelectSQL()+`
WHERE tm.workspace_id = $1
  AND tm.team_id = $2
  AND tm.actor_kind = 'agent'
  AND tm.removed_at IS NULL
ORDER BY tm.sort_order, tm.added_at,
         COALESCE(NULLIF(agent.name, ''), NULLIF(m.display_name, ''), tm.actor_id),
         tm.actor_id, tm.id
`, trim(workspaceID), trim(teamID))
	if err != nil {
		return nil, fmt.Errorf("list team member views: %w", err)
	}
	defer rows.Close()
	var members []TeamMemberView
	for rows.Next() {
		view, err := scanTeamMemberView(rows)
		if err != nil {
			return nil, fmt.Errorf("scan team member view: %w", err)
		}
		members = append(members, view)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate team member views: %w", err)
	}
	return members, nil
}

func loadTeamMemberView(ctx context.Context, queryer coredb.Queryer, workspaceID string, teamID string, teamMemberID string) (TeamMemberView, error) {
	view, err := scanTeamMemberView(queryer.QueryRow(ctx, teamMemberViewSelectSQL()+`
WHERE tm.workspace_id = $1
  AND tm.team_id = $2
  AND tm.id = $3
  AND tm.actor_kind = 'agent'
`, trim(workspaceID), trim(teamID), trim(teamMemberID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return TeamMemberView{}, notFound("team member not found")
		}
		return TeamMemberView{}, fmt.Errorf("load team member view: %w", err)
	}
	return view, nil
}

func teamMemberViewSelectSQL() string {
	return `
SELECT tm.id, tm.workspace_id, tm.team_id, tm.actor_kind, tm.actor_id,
       tm.role,
       tm.role_label, tm.role_overlay_prompt, tm.role_overlay_version,
       tm.wake_on_team_mention, tm.sort_order, tm.added_by_member_id, tm.added_at, tm.updated_at, tm.removed_at,
       COALESCE(NULLIF(agent.name, ''), NULLIF(m.display_name, ''), tm.actor_id),
       COALESCE(agent.avatar_url, ''),
       COALESCE(agent.id, ''),
       COALESCE(NULLIF(agent.name, ''), tm.actor_id)
FROM workspace_team_members tm
LEFT JOIN members m
  ON tm.actor_kind = 'agent'
 AND m.workspace_id = tm.workspace_id
 AND m.id = tm.actor_id
LEFT JOIN agents agent
  ON tm.actor_kind = 'agent'
 AND agent.workspace_id = tm.workspace_id
 AND agent.member_id = tm.actor_id
`
}

func scanTeamMemberView(row coredb.Row) (TeamMemberView, error) {
	var view TeamMemberView
	var removedAt sql.NullTime
	var displayName string
	var avatarURL string
	var principalID string
	var targetID string
	if err := row.Scan(
		&view.Member.ID,
		&view.Member.WorkspaceID,
		&view.Member.TeamID,
		&view.Member.ActorKind,
		&view.Member.ActorID,
		&view.Member.Role,
		&view.Member.RoleLabel,
		&view.Member.RoleOverlayPrompt,
		&view.Member.RoleOverlayVersion,
		&view.Member.WakeOnTeamMention,
		&view.Member.SortOrder,
		&view.Member.AddedByMemberID,
		&view.Member.AddedAt,
		&view.Member.UpdatedAt,
		&removedAt,
		&displayName,
		&avatarURL,
		&principalID,
		&targetID,
	); err != nil {
		return TeamMemberView{}, err
	}
	view.Member.ID = trim(view.Member.ID)
	view.Member.WorkspaceID = trim(view.Member.WorkspaceID)
	view.Member.TeamID = trim(view.Member.TeamID)
	view.Member.ActorKind = trim(view.Member.ActorKind)
	view.Member.ActorID = trim(view.Member.ActorID)
	view.Member.Role = trim(view.Member.Role)
	if view.Member.Role == "" {
		view.Member.Role = MemberRoleMember
	}
	view.Member.AddedByMemberID = trim(view.Member.AddedByMemberID)
	view.Member.RemovedAt = timePtrFromNull(removedAt)
	view.ActorProfile = actorProfileForMember(
		view.Member,
		displayName,
		avatarURL,
		principalID,
		targetID,
	)
	return view, nil
}

func actorProfileForMember(
	member model.WorkspaceTeamMember,
	displayName string,
	avatarURL string,
	principalID string,
	targetID string,
) ActorProfile {
	profile := ActorProfile{
		ActorType:   member.ActorKind,
		PrincipalID: trim(principalID),
		TargetID:    trim(targetID),
		DisplayName: trim(displayName),
		AvatarURL:   trim(avatarURL),
	}
	if profile.DisplayName == "" {
		profile.DisplayName = member.ActorID
	}
	switch member.ActorKind {
	case ActorKindAgent:
		profile.MemberID = member.ActorID
		if profile.TargetID == "" {
			profile.TargetID = member.ActorID
		}
	}
	return profile
}

func teamCandidateSelectSQL() string {
	return `
SELECT t.id, t.workspace_id, t.handle, t.display_name, t.description, t.avatar_url,
       t.visibility, t.status, COALESCE(t.channel_id, ''), t.created_by_member_id, t.created_at, t.updated_at, t.archived_at,
       COALESCE(mc.member_count, 0)
FROM workspace_teams t
LEFT JOIN (
    SELECT workspace_id, team_id, COUNT(*) AS member_count
    FROM workspace_team_members
    WHERE removed_at IS NULL
      AND actor_kind = 'agent'
    GROUP BY workspace_id, team_id
) mc ON mc.workspace_id = t.workspace_id AND mc.team_id = t.id
`
}

func scanTeamMentionCandidate(row coredb.Row) (TeamMentionCandidate, error) {
	var candidate TeamMentionCandidate
	var archivedAt sql.NullTime
	var memberCount int64
	if err := row.Scan(
		&candidate.Team.ID,
		&candidate.Team.WorkspaceID,
		&candidate.Team.Handle,
		&candidate.Team.DisplayName,
		&candidate.Team.Description,
		&candidate.Team.AvatarURL,
		&candidate.Team.Visibility,
		&candidate.Team.Status,
		&candidate.Team.ChannelID,
		&candidate.Team.CreatedByMemberID,
		&candidate.Team.CreatedAt,
		&candidate.Team.UpdatedAt,
		&archivedAt,
		&memberCount,
	); err != nil {
		return TeamMentionCandidate{}, err
	}
	candidate.Team.ID = trim(candidate.Team.ID)
	candidate.Team.WorkspaceID = trim(candidate.Team.WorkspaceID)
	candidate.Team.Handle = trim(candidate.Team.Handle)
	candidate.Team.Visibility = trim(candidate.Team.Visibility)
	candidate.Team.Status = trim(candidate.Team.Status)
	candidate.Team.ChannelID = trim(candidate.Team.ChannelID)
	candidate.Team.CreatedByMemberID = trim(candidate.Team.CreatedByMemberID)
	candidate.Team.ArchivedAt = timePtrFromNull(archivedAt)
	candidate.MemberCount = int(memberCount)
	return candidate, nil
}

func mapAccessStoreError(err error) error {
	switch {
	case errors.Is(err, access.ErrForbidden):
		return forbidden("forbidden")
	case errors.Is(err, access.ErrNotFound):
		return notFound("not found")
	default:
		return err
	}
}

func normalizeStatusFilter(value string) (string, error) {
	status := trim(value)
	switch status {
	case "", StatusActive:
		return StatusActive, nil
	case StatusArchived:
		return StatusArchived, nil
	case "all":
		return "", nil
	default:
		return "", badRequest("team status filter must be active, archived, or all")
	}
}

func boundedLimit(value int, defaultValue int, maxValue int) int {
	if value <= 0 {
		return defaultValue
	}
	if value > maxValue {
		return maxValue
	}
	return value
}

// ListTeamsForAgentPage lists active Teams visible to an authenticated Team
// member using the indexed membership boundary and a stable name/handle/id
// keyset. It deliberately returns a small transport-neutral projection.
func (store *Store) ListTeamsForAgentPage(
	ctx context.Context,
	authenticated storeagent.AuthenticatedAgent,
	options AgentTeamListOptions,
) (AgentTeamPage, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return AgentTeamPage{}, err
	}
	workspaceID := trim(authenticated.WorkspaceID)
	memberID := trim(authenticated.MemberID)
	if workspaceID == "" || memberID == "" {
		return AgentTeamPage{}, forbidden("authenticated Agent Team member is required")
	}
	limit := boundedLimit(options.Limit, DefaultTeamDirectoryLimit, MaxTeamDirectoryLimit)
	query := strings.ToLower(trim(options.Query))
	pattern := "%" + query + "%"
	args := []any{workspaceID, memberID, query, pattern}
	where := `t.workspace_id = $1
  AND t.status = 'active'
  AND ($3 = '' OR LOWER(t.handle) LIKE $4 OR LOWER(t.display_name) LIKE $4 OR LOWER(t.description) LIKE $4)`
	if strings.TrimSpace(options.CursorTeamID) != "" {
		nameArg := len(args) + 1
		args = append(args, options.CursorName)
		handleArg := len(args) + 1
		args = append(args, options.CursorHandle)
		idArg := len(args) + 1
		args = append(args, strings.TrimSpace(options.CursorTeamID))
		where += fmt.Sprintf(`
  AND (t.display_name > $%d
    OR (t.display_name = $%d AND t.handle > $%d)
    OR (t.display_name = $%d AND t.handle = $%d AND t.id > $%d))`, nameArg, nameArg, handleArg, nameArg, handleArg, idArg)
	}
	limitArg := len(args) + 1
	args = append(args, limit+1)
	rows, err := queryer.Query(ctx, `
SELECT t.id, t.handle, t.display_name, t.description, viewer.role
FROM workspace_teams t
JOIN workspace_team_members viewer
  ON viewer.workspace_id = t.workspace_id
 AND viewer.team_id = t.id
 AND viewer.actor_kind = 'agent'
 AND viewer.actor_id = $2
 AND viewer.removed_at IS NULL
WHERE `+where+`
ORDER BY t.display_name, t.handle, t.id
LIMIT `+fmt.Sprintf("$%d", limitArg), args...)
	if err != nil {
		return AgentTeamPage{}, fmt.Errorf("list Teams for Agent: %w", err)
	}
	defer rows.Close()
	items := make([]AgentTeamSummary, 0, limit+1)
	for rows.Next() {
		var item AgentTeamSummary
		if err := rows.Scan(&item.ID, &item.Handle, &item.DisplayName, &item.Description, &item.Role); err != nil {
			return AgentTeamPage{}, fmt.Errorf("scan Team for Agent: %w", err)
		}
		items = append(items, item)
	}
	if err := rows.Err(); err != nil {
		return AgentTeamPage{}, fmt.Errorf("iterate Teams for Agent: %w", err)
	}
	hasMore := len(items) > limit
	if hasMore {
		items = items[:limit]
	}
	return AgentTeamPage{Teams: items, HasMore: hasMore}, nil
}

// ListTeamsForAgent is retained for non-paginated store callers. The Agent
// Runtime HTTP endpoint uses ListTeamsForAgentPage and never materializes an
// unbounded Team collection.
func (store *Store) ListTeamsForAgent(
	ctx context.Context,
	authenticated storeagent.AuthenticatedAgent,
	query string,
	limit int,
) ([]AgentTeamSummary, error) {
	page, err := store.ListTeamsForAgentPage(ctx, authenticated, AgentTeamListOptions{Query: query, Limit: limit})
	if err != nil {
		return nil, err
	}
	return page.Teams, nil
}

// GetTeamForAgent returns one active Team and its current member configuration
// when the authenticated Agent is a member of that Team.
func (store *Store) GetTeamForAgent(
	ctx context.Context,
	authenticated storeagent.AuthenticatedAgent,
	teamID string,
) (model.WorkspaceTeam, []TeamMemberView, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return model.WorkspaceTeam{}, nil, err
	}
	workspaceID := trim(authenticated.WorkspaceID)
	memberID := trim(authenticated.MemberID)
	teamID = trim(teamID)
	if workspaceID == "" || memberID == "" || teamID == "" {
		return model.WorkspaceTeam{}, nil, badRequest("authenticated Agent and teamId are required")
	}
	var count int
	if err := queryer.QueryRow(ctx, `
SELECT COUNT(*)
FROM workspace_team_members
WHERE workspace_id=$1 AND team_id=$2 AND actor_kind='agent' AND actor_id=$3 AND removed_at IS NULL
`, workspaceID, teamID, memberID).Scan(&count); err != nil {
		return model.WorkspaceTeam{}, nil, fmt.Errorf("authorize Agent Team read: %w", err)
	}
	if count != 1 {
		return model.WorkspaceTeam{}, nil, notFound("team not found")
	}
	loaded, err := loadTeam(ctx, queryer, workspaceID, teamID)
	if err != nil {
		return model.WorkspaceTeam{}, nil, err
	}
	if loaded.Status != StatusActive {
		return model.WorkspaceTeam{}, nil, notFound("team not found")
	}
	members, err := listTeamMemberViews(ctx, queryer, workspaceID, teamID)
	if err != nil {
		return model.WorkspaceTeam{}, nil, err
	}
	return loaded, members, nil
}