package app

import (
	"context"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/core/secrets"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/access"
	"github.com/tidbcloud/AgentTeam/internal/store/agent"
	"github.com/tidbcloud/AgentTeam/internal/store/app/runtime"
	"github.com/tidbcloud/AgentTeam/internal/store/plan"
	"github.com/tidbcloud/AgentTeam/internal/store/team"
)

type appMachineListScope string

const (
	appMachineListScopeVisible appMachineListScope = "visible"
	appMachineListScopeOwn     appMachineListScope = "own"
	appMachineListScopeAll     appMachineListScope = "all"
)

func (store *Store) listMachinesForWorkspace(ctx context.Context, userID string, workspaceID string, scope appMachineListScope) ([]Machine, error) {
	machines, _, err := store.queryMachinesForWorkspace(ctx, userID, workspaceID, scope, 0, machineListCursor{})
	return machines, err
}

func (store *Store) listMachinesForWorkspacePage(ctx context.Context, userID string, workspaceID string, scope appMachineListScope, options MachineListOptions) (MachineListPage, error) {
	limit := normalizeMachineListLimit(options.Limit)
	cursor, err := decodeMachineListCursor(options.Cursor)
	if err != nil {
		return MachineListPage{}, err
	}
	machines, hasMore, err := store.queryMachinesForWorkspace(ctx, userID, workspaceID, scope, limit+1, cursor)
	if err != nil {
		return MachineListPage{}, err
	}
	nextCursor := ""
	if hasMore {
		machines = machines[:limit]
		lastMachine := machines[len(machines)-1]
		nextCursor, err = encodeMachineListCursor(machineListCursor{
			CreatedAt: lastMachine.CreatedAt,
			ID:        lastMachine.ID,
		})
		if err != nil {
			return MachineListPage{}, err
		}
	}
	return MachineListPage{
		Machines:   machines,
		HasMore:    hasMore,
		NextCursor: nextCursor,
	}, nil
}

func (store *Store) queryMachinesForWorkspace(ctx context.Context, userID string, workspaceID string, scope appMachineListScope, fetchLimit int, cursor machineListCursor) ([]Machine, bool, error) {
	workspaceMember, err := store.loadRuntimeWorkspaceMember(ctx, store.db, userID, workspaceID)
	if err != nil {
		return nil, false, err
	}
	isAdmin := access.HasWorkspaceAdminRole(domain.WorkspaceRole(workspaceMember.WorkspaceRole))

	query := `
SELECT id, name, created_by_user_id, LEFT(api_key, 20) AS api_key_prefix, hostname, os, daemon_version, created_at
FROM machines
WHERE workspace_id = $1
  AND status = 'active'`
	args := []any{workspaceID}
	switch scope {
	case appMachineListScopeOwn:
		query += ` AND created_by_user_id = $2`
		args = append(args, userID)
	case appMachineListScopeAll:
		if !isAdmin {
			return nil, false, ErrForbidden
		}
	case appMachineListScopeVisible:
		if !isAdmin {
			query += ` AND created_by_user_id = $2`
			args = append(args, userID)
		}
	default:
		return nil, false, badRequest("invalid machine list scope")
	}
	if cursor != (machineListCursor{}) {
		createdAtArg := len(args) + 1
		idArg := createdAtArg + 1
		query += fmt.Sprintf(` AND (created_at > $%d OR (created_at = $%d AND id > $%d))`, createdAtArg, createdAtArg, idArg)
		args = append(args, cursor.CreatedAt, cursor.ID)
	}
	query += `
ORDER BY created_at ASC, id ASC`
	if fetchLimit > 0 {
		limitArg := len(args) + 1
		query += fmt.Sprintf(`
LIMIT $%d`, limitArg)
		args = append(args, fetchLimit)
	}

	rows, err := store.db.Query(ctx, query, args...)
	if err != nil {
		return nil, false, fmt.Errorf("list machines: %w", err)
	}
	defer rows.Close()

	machines := make([]Machine, 0)
	machineIDs := make([]string, 0)
	for rows.Next() {
		var machine Machine
		var createdByUserID string
		if err := rows.Scan(&machine.ID, &machine.Name, &createdByUserID, &machine.APIKeyPrefix, &machine.Hostname, &machine.OS, &machine.DaemonVersion, &machine.CreatedAt); err != nil {
			return nil, false, fmt.Errorf("scan machine: %w", err)
		}
		machine.APIKeyPrefix = apiKeyPrefix(machine.APIKeyPrefix)
		machine.CreatedByUserID = createdByUserID
		machine.CanManage = strings.TrimSpace(createdByUserID) == strings.TrimSpace(userID)
		machine.Status = "offline"
		machines = append(machines, machine)
		machineIDs = append(machineIDs, machine.ID)
	}
	if err := rows.Err(); err != nil {
		return nil, false, fmt.Errorf("iterate machines: %w", err)
	}
	hasMore := fetchLimit > 0 && len(machines) == fetchLimit
	if hasMore {
		machines = machines[:fetchLimit-1]
		machineIDs = machineIDs[:fetchLimit-1]
	}
	capabilityMap, defaultsMap, err := runtime.LoadMachineRuntimeCapabilities(ctx, store.db, machineIDs)
	if err != nil {
		return nil, false, err
	}
	for index := range machines {
		machines[index].Runtimes = capabilityMap[machines[index].ID]
		machines[index].RuntimeDefaults = defaultsMap[machines[index].ID]
	}
	return machines, hasMore, nil
}

func (store *Store) ListMachinesForWorkspace(ctx context.Context, userID string, workspaceID string) ([]Machine, error) {
	return store.listMachinesForWorkspace(ctx, userID, workspaceID, appMachineListScopeVisible)
}

func (store *Store) ListMachinesForWorkspacePage(ctx context.Context, userID string, workspaceID string, options MachineListOptions) (MachineListPage, error) {
	return store.listMachinesForWorkspacePage(ctx, userID, workspaceID, appMachineListScopeVisible, options)
}

func (store *Store) ListOwnMachinesForWorkspace(ctx context.Context, userID string, workspaceID string) ([]Machine, error) {
	return store.listMachinesForWorkspace(ctx, userID, workspaceID, appMachineListScopeOwn)
}

func (store *Store) ListOwnMachinesForWorkspacePage(ctx context.Context, userID string, workspaceID string, options MachineListOptions) (MachineListPage, error) {
	return store.listMachinesForWorkspacePage(ctx, userID, workspaceID, appMachineListScopeOwn, options)
}

func (store *Store) ListAllMachinesForWorkspace(ctx context.Context, userID string, workspaceID string) ([]Machine, error) {
	return store.listMachinesForWorkspace(ctx, userID, workspaceID, appMachineListScopeAll)
}

func (store *Store) ListAllMachinesForWorkspacePage(ctx context.Context, userID string, workspaceID string, options MachineListOptions) (MachineListPage, error) {
	return store.listMachinesForWorkspacePage(ctx, userID, workspaceID, appMachineListScopeAll, options)
}

func (store *Store) CreateMachineForWorkspace(ctx context.Context, userID string, workspaceID string, name string) (Machine, string, error) {
	trimmedName := strings.TrimSpace(name)
	if trimmedName == "" {
		return Machine{}, "", badRequest("name is required")
	}

	machineID, err := identifiers.NewEntityID("mac")
	if err != nil {
		return Machine{}, "", fmt.Errorf("generate machine id: %w", err)
	}
	apiKey, err := secrets.NewToken(32)
	if err != nil {
		return Machine{}, "", fmt.Errorf("generate machine api key: %w", err)
	}

	now := time.Now().UTC()
	tx, err := store.db.BeginTx(ctx, coredb.TxOptions{})
	if err != nil {
		return Machine{}, "", fmt.Errorf("begin create machine tx: %w", err)
	}
	defer rollback(tx, ctx)

	if _, err := store.loadRuntimeWorkspaceMemberForUpdate(ctx, tx, userID, workspaceID); err != nil {
		return Machine{}, "", err
	}
	if err := store.checkWorkspaceQuotaInTx(ctx, tx, workspaceID, plan.MetricMachines, 1); err != nil {
		return Machine{}, "", err
	}
	if _, err := tx.Exec(ctx, `
INSERT INTO machines (id, workspace_id, created_by_user_id, name, api_key, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5, $6, $6)
`, machineID, workspaceID, userID, trimmedName, apiKey, now); err != nil {
		return Machine{}, "", fmt.Errorf("insert machine: %w", err)
	}
	if err := tx.Commit(ctx); err != nil {
		return Machine{}, "", fmt.Errorf("commit create machine: %w", err)
	}

	return Machine{
		ID:              machineID,
		Name:            trimmedName,
		Status:          "offline",
		APIKeyPrefix:    apiKeyPrefix(apiKey),
		CanManage:       true,
		Runtimes:        []string{},
		RuntimeDefaults: map[string]runtime.RuntimeDefaults{},
		CreatedAt:       now,
	}, apiKey, nil
}

func (store *Store) RenameMachineForWorkspace(ctx context.Context, userID string, workspaceID string, machineID string, name string) error {
	if _, err := store.requireMachineManagement(ctx, userID, workspaceID, machineID); err != nil {
		return err
	}
	trimmedName := strings.TrimSpace(name)
	if trimmedName == "" {
		return badRequest("name is required")
	}
	commandTag, err := store.db.Exec(ctx, `
UPDATE machines
SET name = $3, updated_at = NOW()
WHERE workspace_id = $1 AND id = $2 AND status = 'active'
`, workspaceID, machineID, trimmedName)
	if err != nil {
		return fmt.Errorf("rename machine: %w", err)
	}
	if commandTag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

func (store *Store) DeleteMachineForWorkspace(ctx context.Context, userID string, workspaceID string, machineID string) error {
	if _, err := store.requireMachineManagement(ctx, userID, workspaceID, machineID); err != nil {
		return err
	}

	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		commandTag, err := tx.Exec(ctx, `
UPDATE machines
SET status = 'revoked',
    api_key = CONCAT('revoked-', id, '-', api_key),
    updated_at = NOW()
WHERE workspace_id = $1 AND id = $2 AND status = 'active'
`, workspaceID, machineID)
		if err != nil {
			return fmt.Errorf("revoke machine %s in workspace %s: %w", strings.TrimSpace(machineID), strings.TrimSpace(workspaceID), err)
		}
		if commandTag.RowsAffected() == 0 {
			return ErrNotFound
		}

		if _, err := tx.Exec(ctx, `
DELETE FROM machine_runtime_capabilities
WHERE machine_id = $1
`, machineID); err != nil {
			return fmt.Errorf("delete runtime capabilities for machine %s: %w", strings.TrimSpace(machineID), err)
		}
		return nil
	})
	if err != nil {
		if coredb.IsForeignKeyViolation(err) {
			return ErrConflict
		}
		return err
	}
	return nil
}

func (store *Store) EvictMachineForWorkspace(ctx context.Context, userID string, workspaceID string, machineID string) ([]string, error) {
	if _, err := store.requireWorkspaceAdmin(ctx, userID, workspaceID); err != nil {
		return nil, err
	}

	var agentIDs []string
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var err error
		agentIDs, err = store.evictMachineInTx(ctx, tx, workspaceID, machineID)
		return err
	}); err != nil {
		return nil, err
	}
	return agentIDs, nil
}

type machineEvictionAgent struct {
	agentID  string
	memberID string
}

func (store *Store) evictMachineInTx(ctx context.Context, tx coredb.Tx, workspaceID string, machineID string) ([]string, error) {
	// Machine lifecycle operations use machine -> Agent -> agent member as
	// their canonical lock order. Invocation acceptance uses the same order,
	// so fence the machine before touching any of its Agent/member rows.
	var lockedMachineID string
	if err := tx.QueryRow(ctx, `
SELECT id
FROM machines
WHERE workspace_id = $1 AND id = $2 AND status = 'active'
FOR UPDATE
`, workspaceID, machineID).Scan(&lockedMachineID); err != nil {
		if coredb.IsNotFound(err) {
			return nil, ErrNotFound
		}
		return nil, fmt.Errorf("lock machine for eviction: %w", err)
	}

	// Lock every Agent deterministically before any agent-member projection
	// mutation. This also prevents a concurrent per-Agent lifecycle operation
	// from observing only part of the eviction.
	rows, err := tx.Query(ctx, `
SELECT id, member_id FROM agents WHERE workspace_id = $1 AND machine_id = $2
ORDER BY id
FOR UPDATE
`, workspaceID, machineID)
	if err != nil {
		return nil, fmt.Errorf("list agents for eviction: %w", err)
	}
	defer rows.Close()
	var agents []machineEvictionAgent
	for rows.Next() {
		var agent machineEvictionAgent
		if err := rows.Scan(&agent.agentID, &agent.memberID); err != nil {
			return nil, fmt.Errorf("scan agent id: %w", err)
		}
		agents = append(agents, agent)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate agent ids: %w", err)
	}

	// Clean up each agent's canonical scope projections and member-local DM
	// Inbox rows before deleting the agent identities.
	now := time.Now().UTC()
	for _, agent := range agents {
		if err := store.markMemberScopeProjectionsRemoved(ctx, tx, agent.memberID, now); err != nil {
			return nil, err
		}
		if err := deleteAgentDMInboxProjections(ctx, tx, workspaceID, agent.memberID); err != nil {
			return nil, err
		}
	}

	if len(agents) > 0 {
		if _, err := tx.Exec(ctx, `
DELETE FROM agents WHERE workspace_id = $1 AND machine_id = $2
		`, workspaceID, machineID); err != nil {
			return nil, fmt.Errorf("evict machine agents: %w", err)
		}
	}

	commandTag, err := tx.Exec(ctx, `
DELETE FROM machines WHERE workspace_id = $1 AND id = $2 AND status = 'active'
`, workspaceID, machineID)
	if err != nil {
		return nil, fmt.Errorf("evict machine: %w", err)
	}
	if commandTag.RowsAffected() == 0 {
		return nil, ErrNotFound
	}

	agentIDs := make([]string, len(agents))
	for index, agent := range agents {
		agentIDs[index] = agent.agentID
	}
	return agentIDs, nil
}

func apiKeyPrefix(apiKey string) string {
	trimmed := strings.TrimSpace(apiKey)
	if len(trimmed) <= 20 {
		return trimmed
	}
	return trimmed[:20]
}

func (store *Store) applyAppAgentLLMViewFields(appAgent *agent.AppAgent, llmMode string, baseURL string, apiKeyCiphertext string, headersJSON []byte, wireAPI string) {
	llm, err := agent.DecodeStoredAgentLLMColumnsWithFallback(store.appCredentialStorageKey, store.legacyAppCredentialStorageKeys, llmMode, baseURL, apiKeyCiphertext, headersJSON, wireAPI)
	if err != nil {
		appAgent.LLMMode = agent.NormalizeAgentLLMMode(llmMode)
		appAgent.BaseURL = strings.TrimSpace(baseURL)
		appAgent.Headers = decodeAgentHeadersForUnreadableConfig(headersJSON)
		appAgent.WireAPI = strings.TrimSpace(wireAPI)
		appAgent.APIKeyConfigured = strings.TrimSpace(apiKeyCiphertext) != ""
		appAgent.LLMConfigError = agent.AgentLLMConfigErrorUnreadable
		return
	}
	appAgent.LLMMode, appAgent.BaseURL, appAgent.Headers, appAgent.WireAPI, appAgent.APIKeyConfigured = agent.AgentLLMViewFieldsFromStored(llm)
}

func (store *Store) applyAgentViewLLMFields(agentView *agent.AgentView, llmMode string, baseURL string, apiKeyCiphertext string, headersJSON []byte, wireAPI string) {
	llm, err := agent.DecodeStoredAgentLLMColumnsWithFallback(store.appCredentialStorageKey, store.legacyAppCredentialStorageKeys, llmMode, baseURL, apiKeyCiphertext, headersJSON, wireAPI)
	if err != nil {
		agentView.LLMMode = agent.NormalizeAgentLLMMode(llmMode)
		agentView.BaseURL = strings.TrimSpace(baseURL)
		agentView.APIKey = ""
		agentView.Headers = decodeAgentHeadersForUnreadableConfig(headersJSON)
		agentView.WireAPI = strings.TrimSpace(wireAPI)
		agentView.LLMConfigError = agent.AgentLLMConfigErrorUnreadable
		return
	}
	agentView.LLMMode, agentView.BaseURL, agentView.APIKey, agentView.Headers, agentView.WireAPI = agent.AgentLLMFieldsFromStored(llm)
}

func decodeAgentHeadersForUnreadableConfig(headersJSON []byte) map[string]string {
	headers, err := agent.DecodeAgentHeaders(headersJSON)
	if err != nil {
		return map[string]string{}
	}
	return headers
}

func (store *Store) ListAgentsForWorkspace(ctx context.Context, userID string, workspaceID string) ([]agent.AppAgent, error) {
	return store.ListAgentsForWorkspaceWithOptions(ctx, userID, workspaceID, agent.AppAgentListOptions{})
}

func (store *Store) ListOwnedAgentsForWorkspace(ctx context.Context, userID string, workspaceID string) ([]agent.AppAgent, error) {
	return store.ListAgentsForWorkspaceWithOptions(ctx, userID, workspaceID, agent.AppAgentListOptions{OwnedOnly: true})
}

// ListAgentsForWorkspaceWithOptions returns workspace agents with optional
// roster hydration controls.
func (store *Store) ListAgentsForWorkspaceWithOptions(ctx context.Context, userID string, workspaceID string, options agent.AppAgentListOptions) ([]agent.AppAgent, error) {
	var workspaceMember access.HumanWorkspaceMember
	if channelID := strings.TrimSpace(options.ChannelID); channelID != "" {
		channelAccess, err := store.loadAuthorizedHumanChannelAccess(ctx, store.db, userID, workspaceID, channelID, channelReadAction)
		if err != nil {
			return nil, err
		}
		workspaceMember = channelAccess.WorkspaceMember
	} else {
		var err error
		workspaceMember, err = store.loadRuntimeWorkspaceMember(ctx, store.db, userID, workspaceID)
		if err != nil {
			return nil, err
		}
	}
	return store.listAgentsForWorkspaceMember(ctx, userID, workspaceID, workspaceMember, options)
}

func (store *Store) listAgentsForWorkspaceMember(ctx context.Context, userID string, workspaceID string, workspaceMember access.HumanWorkspaceMember, options agent.AppAgentListOptions) ([]agent.AppAgent, error) {
	canDisableAll := access.HasWorkspaceAdminRole(domain.WorkspaceRole(workspaceMember.WorkspaceRole))

	ownedFilter := ""
	args := []any{workspaceID, workspaceMember.Member.ID}
	nextParam := 3
	channelFilter := ""
	if trimmedChannelID := strings.TrimSpace(options.ChannelID); trimmedChannelID != "" {
		channelFilter = fmt.Sprintf(`  AND EXISTS (
	      SELECT 1
	      FROM scope_memberships channel_member
	      WHERE channel_member.scope_id = $%d
	        AND channel_member.member_id = a.member_id
	        AND channel_member.removed_at IS NULL
	        AND channel_member.archived_at IS NULL
	        AND channel_member.effective_to_seq IS NULL
	        AND (channel_member.visibility = 'public'
	             OR (channel_member.visibility = 'private' AND channel_member.owner_member_id = $2))
	  )
`, nextParam)
		args = append(args, trimmedChannelID)
		nextParam++
	}
	if options.OwnedOnly {
		ownedFilter = fmt.Sprintf("  AND mac.created_by_user_id = $%d\n", nextParam)
		args = append(args, strings.TrimSpace(userID))
		nextParam++
	}
	limitClause := ""
	if options.Limit > 0 {
		limitClause = fmt.Sprintf("LIMIT $%d", nextParam)
		args = append(args, options.Limit)
	}
	sourceClause := `FROM agents a
JOIN members m ON m.id = a.member_id
JOIN machines mac ON mac.workspace_id = a.workspace_id AND mac.id = a.machine_id
WHERE a.workspace_id = $1
  AND a.status <> 'archived'
  AND mac.status = 'active'
` + channelFilter + ownedFilter
	if options.Limit > 0 {
		sourceClause = fmt.Sprintf(`FROM (
	    SELECT a.id
	    FROM agents a
	    JOIN members m ON m.id = a.member_id
	    JOIN machines mac ON mac.workspace_id = a.workspace_id AND mac.id = a.machine_id
	    WHERE a.workspace_id = $1
	      AND a.status <> 'archived'
	      AND mac.status = 'active'
%s%s    ORDER BY m.display_name ASC, a.id ASC
	    %s
	) candidate_agents
	JOIN agents a ON a.id = candidate_agents.id
JOIN members m ON m.id = a.member_id
JOIN machines mac ON mac.workspace_id = a.workspace_id AND mac.id = a.machine_id
`, channelFilter, ownedFilter, limitClause)
		limitClause = ""
	}

	rows, err := store.db.Query(ctx, fmt.Sprintf(`
SELECT a.id, a.name, m.id, m.display_name, a.avatar_url, a.description, a.runtime, a.permission_mode, a.model, a.reasoning_effort,
       a.llm_mode, a.llm_base_url, a.llm_api_key_ciphertext, a.llm_headers_json, a.llm_wire_api, a.machine_id,
       'offline' AS machine_status,
       COALESCE(a.model_profile_id, ''),
       a.source_kind, a.source_ref,
       mac.created_by_user_id,
       ($2 <> '' AND FALSE) AS can_review_shared,
       a.auto_wake_enabled, 'inactive', 'offline', '',
       NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
       a.created_at
%s
ORDER BY m.display_name ASC, a.id ASC
%s
`, sourceClause, limitClause), args...)
	if err != nil {
		return nil, fmt.Errorf("list agents: %w", err)
	}
	defer rows.Close()

	agents := make([]agent.AppAgent, 0)
	for rows.Next() {
		var appAgent agent.AppAgent
		var createdByUserID string
		var canReviewShared bool
		var currentInstanceID *string
		var currentInstanceMachineID *string
		var currentInstanceSessionID *string
		var currentInstanceStatus *string
		var currentInstanceActivity *string
		var currentInstanceActivityDetail *string
		var currentInstanceSourceKind *string
		var currentInstanceSourceRef *string
		var currentInstanceStartedAt *time.Time
		var currentInstanceLastSeenAt *time.Time
		var llmMode string
		var baseURL string
		var apiKeyCiphertext string
		var headersJSON []byte
		var wireAPI string
		if err := rows.Scan(
			&appAgent.ID,
			&appAgent.Name,
			&appAgent.MemberID,
			&appAgent.DisplayName,
			&appAgent.AvatarURL,
			&appAgent.Description,
			&appAgent.Runtime,
			&appAgent.PermissionMode,
			&appAgent.Model,
			&appAgent.ReasoningEffort,
			&llmMode,
			&baseURL,
			&apiKeyCiphertext,
			&headersJSON,
			&wireAPI,
			&appAgent.MachineID,
			&appAgent.MachineStatus,
			&appAgent.ModelProfileID,
			&appAgent.SourceKind,
			&appAgent.SourceRef,
			&createdByUserID,
			&canReviewShared,
			&appAgent.AutoWakeEnabled,
			&appAgent.Status,
			&appAgent.Activity,
			&appAgent.ActivityDetail,
			&currentInstanceID,
			&currentInstanceMachineID,
			&currentInstanceSessionID,
			&currentInstanceStatus,
			&currentInstanceActivity,
			&currentInstanceActivityDetail,
			&currentInstanceSourceKind,
			&currentInstanceSourceRef,
			&currentInstanceStartedAt,
			&currentInstanceLastSeenAt,
			&appAgent.CreatedAt,
		); err != nil {
			return nil, fmt.Errorf("scan agent: %w", err)
		}
		appAgent.ReasoningEffort = agent.NormalizeReasoningEffortForRuntime(
			appAgent.Runtime,
			appAgent.ReasoningEffort,
		)
		appAgent.PermissionMode = agent.DefaultAgentPermissionMode(appAgent.PermissionMode)
		appAgent.CurrentInstance = buildAppAgentInstance(
			currentInstanceID,
			currentInstanceMachineID,
			currentInstanceSessionID,
			currentInstanceStatus,
			currentInstanceActivity,
			currentInstanceActivityDetail,
			currentInstanceSourceKind,
			currentInstanceSourceRef,
			currentInstanceStartedAt,
			currentInstanceLastSeenAt,
		)
		appAgent.OwnerUserID = strings.TrimSpace(createdByUserID)
		appAgent.CanManage = strings.TrimSpace(createdByUserID) == strings.TrimSpace(userID)
		appAgent.CanReview = appAgent.CanManage || canReviewShared
		appAgent.CanDisable = canDisableAll || appAgent.CanManage
		if appAgent.CanManage {
			store.applyAppAgentLLMViewFields(&appAgent, llmMode, baseURL, apiKeyCiphertext, headersJSON, wireAPI)
		} else {
			appAgent.ModelProfileID = ""
		}
		agents = append(agents, appAgent)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate agents: %w", err)
	}

	agentIDs := make([]string, 0, len(agents))
	for _, appAgent := range agents {
		agentIDs = append(agentIDs, appAgent.ID)
	}
	roleBindings, err := store.loadAgentRoleBindingSummaries(ctx, workspaceID, agentIDs)
	if err != nil {
		return nil, err
	}
	for i := range agents {
		agents[i].CurrentRoleBinding = roleBindings[agents[i].ID]
	}

	if !options.IncludeRuntimeFailures {
		return agents, nil
	}

	latestRuntimeFailures, err := store.AgentOps().LoadLatestAgentRuntimeFailureSummaries(ctx, workspaceID, agentIDs)
	if err != nil {
		return nil, err
	}
	for i := range agents {
		summary, ok := latestRuntimeFailures[agents[i].ID]
		if !ok {
			continue
		}
		summaryCopy := summary
		agents[i].LatestRuntimeFailure = &summaryCopy
	}
	return agents, nil
}

func (store *Store) ListAgentsForMachineWorkspace(ctx context.Context, userID string, workspaceID string, machineID string) ([]agent.AppAgent, error) {
	if _, err := store.loadRuntimeWorkspaceMember(ctx, store.db, userID, workspaceID); err != nil {
		return nil, err
	}

	rows, err := store.db.Query(ctx, `
SELECT a.id, a.name, m.id, m.display_name, a.permission_mode,
       a.runtime, a.model, a.reasoning_effort,
       a.auto_wake_enabled, 'inactive', 'offline', '', a.created_at
FROM agents a
JOIN members m ON m.id = a.member_id
JOIN machines mac ON mac.workspace_id = a.workspace_id AND mac.id = a.machine_id
WHERE a.workspace_id = $1
  AND a.machine_id = $2
  AND a.status <> 'archived'
  AND mac.status = 'active'
  AND mac.created_by_user_id = $3
ORDER BY m.display_name ASC, a.id ASC
`, strings.TrimSpace(workspaceID), strings.TrimSpace(machineID), strings.TrimSpace(userID))
	if err != nil {
		return nil, fmt.Errorf("list machine workspace agents: %w", err)
	}
	defer rows.Close()

	agents := make([]agent.AppAgent, 0)
	for rows.Next() {
		var appAgent agent.AppAgent
		if err := rows.Scan(
			&appAgent.ID,
			&appAgent.Name,
			&appAgent.MemberID,
			&appAgent.DisplayName,
			&appAgent.PermissionMode,
			&appAgent.Runtime,
			&appAgent.Model,
			&appAgent.ReasoningEffort,
			&appAgent.AutoWakeEnabled,
			&appAgent.Status,
			&appAgent.Activity,
			&appAgent.ActivityDetail,
			&appAgent.CreatedAt,
		); err != nil {
			return nil, fmt.Errorf("scan machine workspace agent: %w", err)
		}
		appAgent.ReasoningEffort = agent.NormalizeReasoningEffortForRuntime(
			appAgent.Runtime,
			appAgent.ReasoningEffort,
		)
		appAgent.PermissionMode = agent.DefaultAgentPermissionMode(appAgent.PermissionMode)
		appAgent.MachineID = strings.TrimSpace(machineID)
		appAgent.OwnerUserID = strings.TrimSpace(userID)
		appAgent.CanManage = true
		appAgent.CanReview = true
		appAgent.CanDisable = true
		agents = append(agents, appAgent)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate machine workspace agents: %w", err)
	}
	return agents, nil
}

func (store *Store) AgentForWorkspace(ctx context.Context, userID string, workspaceID string, agentID string, options ...agent.AppAgentDetailOptions) (agent.AppAgent, error) {
	includeRuntimeFailure := true
	if len(options) > 0 {
		includeRuntimeFailure = options[0].IncludeRuntimeFailure
	}
	return store.agentForWorkspace(ctx, userID, workspaceID, agentID, includeRuntimeFailure)
}

func buildAppAgentInstance(
	instanceID *string,
	machineID *string,
	sessionID *string,
	status *string,
	activity *string,
	activityDetail *string,
	sourceKind *string,
	sourceRef *string,
	startedAt *time.Time,
	lastSeenAt *time.Time,
) *agent.AppAgentInstance {
	if instanceID == nil || startedAt == nil || lastSeenAt == nil {
		return nil
	}
	return &agent.AppAgentInstance{
		ID:             strings.TrimSpace(*instanceID),
		MachineID:      stringPointerValue(machineID),
		SessionID:      stringPointerValue(sessionID),
		Status:         stringPointerValue(status),
		Activity:       stringPointerValue(activity),
		ActivityDetail: stringPointerValue(activityDetail),
		SourceKind:     stringPointerValue(sourceKind),
		SourceRef:      stringPointerValue(sourceRef),
		StartedAt:      *startedAt,
		LastSeenAt:     *lastSeenAt,
	}
}

func stringPointerValue(value *string) string {
	if value == nil {
		return ""
	}
	return strings.TrimSpace(*value)
}

func (store *Store) SetAgentAutoWakeForUser(ctx context.Context, userID string, workspaceID string, agentID string, enabled bool) (agent.AgentView, error) {
	if enabled {
		if _, err := store.requireAgentOperation(ctx, userID, workspaceID, agentID); err != nil {
			return agent.AgentView{}, err
		}
	} else {
		if _, err := store.requireAgentDisable(ctx, userID, workspaceID, agentID); err != nil {
			return agent.AgentView{}, err
		}
	}

	if _, err := store.db.Exec(ctx, `
UPDATE agents
SET auto_wake_enabled = $3,
    auth_token_hash = CASE WHEN $3 THEN auth_token_hash ELSE NULL END,
    updated_at = NOW()
WHERE workspace_id = $1 AND id = $2 AND status <> 'archived'
`, workspaceID, agentID, enabled); err != nil {
		return agent.AgentView{}, fmt.Errorf("update agent auto wake: %w", err)
	}
	return store.agentViewByID(ctx, store.db, workspaceID, agentID)
}

func (store *Store) ClearAgentSessionForWorkspace(ctx context.Context, userID string, workspaceID string, agentID string) error {
	if _, err := store.requireAgentOperation(ctx, userID, workspaceID, agentID); err != nil {
		return err
	}
	commandTag, err := store.db.Exec(ctx, `
UPDATE agents
SET session_id = '',
    auth_token_hash = NULL,
    updated_at = NOW()
WHERE workspace_id = $1 AND id = $2 AND status <> 'archived'
`, workspaceID, agentID)
	if err != nil {
		return fmt.Errorf("clear agent session: %w", err)
	}
	if commandTag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

func (store *Store) GetAgentWorkspaceReaderForUser(ctx context.Context, userID string, workspaceID string, agentID string) (agent.AgentView, error) {
	if _, err := store.requireAgentWorkspaceRead(ctx, userID, workspaceID, agentID); err != nil {
		return agent.AgentView{}, err
	}
	return store.agentViewByID(ctx, store.db, workspaceID, agentID)
}

func (store *Store) agentViewByID(ctx context.Context, queryer interface {
	QueryRow(context.Context, string, ...any) coredb.Row
}, workspaceID string, agentID string) (agent.AgentView, error) {
	row := queryer.QueryRow(ctx, `
SELECT a.id, a.member_id, m.display_name, a.description, a.runtime, a.permission_mode, a.model, a.reasoning_effort,
       a.llm_mode, a.llm_base_url, a.llm_api_key_ciphertext, a.llm_headers_json, a.llm_wire_api, a.machine_id,
       COALESCE(a.model_profile_id, ''),
       a.source_kind, a.source_ref,
       a.auto_wake_enabled, 'inactive', 'offline', '', ''
FROM agents a
JOIN members m ON m.id = a.member_id
JOIN machines mac ON mac.workspace_id = a.workspace_id AND mac.id = a.machine_id
WHERE a.workspace_id = $1 AND a.id = $2
  AND a.status <> 'archived'
  AND mac.status = 'active'
`, workspaceID, agentID)

	var agentView agent.AgentView
	var llmMode string
	var baseURL string
	var apiKeyCiphertext string
	var headersJSON []byte
	var wireAPI string
	if err := row.Scan(&agentView.AgentID, &agentView.MemberID, &agentView.DisplayName, &agentView.Description, &agentView.Runtime, &agentView.PermissionMode, &agentView.Model, &agentView.ReasoningEffort, &llmMode, &baseURL, &apiKeyCiphertext, &headersJSON, &wireAPI, &agentView.MachineID, &agentView.ModelProfileID, &agentView.SourceKind, &agentView.SourceRef, &agentView.AutoWakeEnabled, &agentView.Status, &agentView.Activity, &agentView.ActivityDetail, &agentView.SessionID); err != nil {
		if coredb.IsNotFound(err) {
			return agent.AgentView{}, ErrNotFound
		}
		return agent.AgentView{}, fmt.Errorf("load agent: %w", err)
	}
	agentView.ReasoningEffort = agent.NormalizeReasoningEffortForRuntime(
		agentView.Runtime,
		agentView.ReasoningEffort,
	)
	agentView.PermissionMode = agent.DefaultAgentPermissionMode(agentView.PermissionMode)
	store.applyAgentViewLLMFields(&agentView, llmMode, baseURL, apiKeyCiphertext, headersJSON, wireAPI)
	latestRuntimeFailure, err := store.AgentOps().LoadLatestAgentRuntimeFailureSummary(ctx, workspaceID, agentID)
	if err != nil {
		return agent.AgentView{}, err
	}
	agentView.LatestRuntimeFailure = latestRuntimeFailure
	return agentView, nil
}

func (store *Store) DeleteAgentForWorkspace(ctx context.Context, userID string, workspaceID string, agentID string) (string, error) {
	if _, err := store.requireAgentOperation(ctx, userID, workspaceID, agentID); err != nil {
		return "", err
	}

	var txResult string
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {

		row := tx.QueryRow(ctx, `
		SELECT member_id, machine_id
		FROM agents
		WHERE workspace_id = $1 AND id = $2
		FOR UPDATE
		`, workspaceID, agentID)
		var memberID string
		var machineID string
		if err := row.Scan(&memberID, &machineID); err != nil {
			if coredb.IsNotFound(err) {
				return ErrNotFound
			}
			return fmt.Errorf("load agent for delete: %w", err)
		}

		if err := store.deleteWorkspaceAgentInTx(ctx, tx, workspaceID, agentID); err != nil {
			return err
		}

		txResult = machineID
		return nil
	}); err != nil {
		return "", err
	}
	return txResult, nil
}

func (store *Store) deleteWorkspaceAgentInTx(ctx context.Context, tx coredb.Tx, workspaceID string, agentID string) error {
	row := tx.QueryRow(ctx, `
SELECT member_id
FROM agents
WHERE workspace_id = $1 AND id = $2
FOR UPDATE
`, workspaceID, agentID)

	var memberID string
	if err := row.Scan(&memberID); err != nil {
		if coredb.IsNotFound(err) {
			return ErrNotFound
		}
		return fmt.Errorf("load agent for delete: %w", err)
	}

	now := time.Now().UTC()
	if err := store.markMemberScopeProjectionsRemoved(ctx, tx, memberID, now); err != nil {
		return err
	}
	if err := deleteAgentDMInboxProjections(ctx, tx, workspaceID, memberID); err != nil {
		return err
	}
	if err := team.New(store.db).InTx(tx).RemoveActiveAgentMemberships(ctx, workspaceID, memberID, now); err != nil {
		return fmt.Errorf("soft-remove team memberships for agent %s member %s in workspace %s: %w", strings.TrimSpace(agentID), strings.TrimSpace(memberID), strings.TrimSpace(workspaceID), err)
	}
	if _, err := tx.Exec(ctx, `
UPDATE members
SET agent_display_name_key = NULL,
    updated_at = $2
WHERE id = $1
`, memberID, now); err != nil {
		return fmt.Errorf("release deleted agent display name key: %w", err)
	}
	if err := deleteKnowledgeBaseAgentGrantsForAgentIDs(ctx, tx, workspaceID, []string{agentID}); err != nil {
		return err
	}
	if err := deleteMemoryAgentBindingsForAgentIDs(ctx, tx, workspaceID, []string{agentID}); err != nil {
		return err
	}
	if err := deleteSkillAgentBindingsForAgentIDs(ctx, tx, workspaceID, []string{agentID}); err != nil {
		return err
	}
	if _, err := tx.Exec(ctx, `DELETE FROM agents WHERE workspace_id = $1 AND id = $2`, workspaceID, agentID); err != nil {
		return fmt.Errorf("delete agent: %w", err)
	}
	return nil
}

func (store *Store) DeleteWorkspaceAgentInTx(ctx context.Context, tx coredb.Tx, workspaceID string, agentID string) error {
	return store.deleteWorkspaceAgentInTx(ctx, tx, workspaceID, agentID)
}