package app

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/app/capabilities"
	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/access"
	installationstore "github.com/tidbcloud/AgentTeam/internal/store/app/installation"
	"github.com/tidbcloud/AgentTeam/internal/store/app/registry"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
	"github.com/tidbcloud/AgentTeam/internal/store/serviceactor"
	"github.com/tidbcloud/AgentTeam/internal/store/storeerr"
)

const (
	AgentAppAuthorizationStatusActive   = "active"
	AgentAppAuthorizationStatusDisabled = "disabled"

	serviceActorPrincipalKindAgent = "agent"
)

type AgentAppAuthorizationRecord struct {
	ID                   string
	WorkspaceID          string
	AgentID              string
	AgentMemberID        string
	AppID                string
	AppSlug              string
	AppInstallationID    string
	AppMemberID          string
	Status               string
	DisabledReason       string
	Scopes               []string
	AuthorizedByMemberID string
	PolicyVersion        int64
	Metadata             []byte
	CreatedAt            time.Time
	UpdatedAt            time.Time
	DisabledAt           *time.Time
	RemovedAt            *time.Time
	MCPTools             []string
}

type UpsertAgentAppAuthorizationParams struct {
	WorkspaceID          string
	AgentID              string
	AppInstallationID    string
	AuthorizedByMemberID string
	Scopes               []string
	Metadata             map[string]any
	// OperatorUserID, when set, is re-verified inside the write transaction
	// to still manage the agent before the authorization is written.
	OperatorUserID string
}

type AgentAppScopedTokenResult struct {
	Token         string
	Authorization AgentAppAuthorizationRecord
	ExpiresAt     time.Time
}

func (store *Store) UpsertAgentAppAuthorization(ctx context.Context, params UpsertAgentAppAuthorizationParams) (AgentAppAuthorizationRecord, error) {
	workspaceID := strings.TrimSpace(params.WorkspaceID)
	agentID := strings.TrimSpace(params.AgentID)
	installationID := strings.TrimSpace(params.AppInstallationID)
	authorizedByMemberID := strings.TrimSpace(params.AuthorizedByMemberID)
	if workspaceID == "" || agentID == "" || installationID == "" || authorizedByMemberID == "" {
		return AgentAppAuthorizationRecord{}, badRequest("workspaceId, agentId, appInstallationId, and authorizedByMemberId are required")
	}
	metadataJSON, err := marshalServiceEventSubscriptionJSONMap(params.Metadata)
	if err != nil {
		return AgentAppAuthorizationRecord{}, err
	}
	if err := store.ensureServiceActorAppInstallationFresh(ctx, workspaceID, installationID); err != nil {
		return AgentAppAuthorizationRecord{}, err
	}

	var record AgentAppAuthorizationRecord
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		installation, found, err := loadAgentAuthorizationInstallation(ctx, tx, workspaceID, installationID)
		if err != nil {
			return err
		}
		if !found || !installation.Enabled {
			return storeerr.BadRequest("app installation is not enabled")
		}
		if !installation.Capability.Supported {
			return storeerr.BadRequest("app does not expose an agent MCP")
		}
		if err := requireAgentInWorkspace(ctx, tx, workspaceID, agentID); err != nil {
			return err
		}
		if err := requireMemberInWorkspace(ctx, tx, workspaceID, authorizedByMemberID); err != nil {
			return err
		}

		scopes := normalizeScopeSet(params.Scopes)
		if len(scopes) == 0 {
			scopes = installation.Capability.Scopes
		}
		if !scopeSetCovers(installation.Capability.Scopes, scopes) {
			return storeerr.Forbidden("token_scope_denied")
		}
		// Canonical mutation order: Definition mutation lock, then the
		// authorizer's member row and agent-management authority, then the
		// installation row, then the authorization write.
		if err := installationstore.LockAppInstallationMutationScope(ctx, tx, installation.AppID); err != nil {
			return err
		}
		// Current-read revalidation: the pre-lock projection may be a stale
		// RR snapshot; reload it under the definition lock and re-run the
		// full admission (enabled, capability support, and scope coverage)
		// against the current capability instead of the pre-lock one.
		lockedInstallation, found, err := loadAgentAuthorizationInstallationForUpdate(ctx, tx, workspaceID, installationID)
		if err != nil {
			return err
		}
		if !found || !lockedInstallation.Enabled {
			return storeerr.Forbidden("app_not_authorized_for_agent")
		}
		installation = lockedInstallation
		if !installation.Capability.Supported {
			return storeerr.Forbidden("app_not_authorized_for_agent")
		}
		if params.Scopes == nil {
			scopes = installation.Capability.Scopes
		}
		if !scopeSetCovers(installation.Capability.Scopes, scopes) {
			return storeerr.Forbidden("token_scope_denied")
		}
		// The authorizer must currently be an active human member: an
		// offboarded or non-human principal can never write authorizations.
		if err := access.RequireActiveHumanMember(ctx, tx, workspaceID, authorizedByMemberID, true); err != nil {
			if errors.Is(err, access.ErrNotFound) {
				return badRequest("authorizer is not a workspace member")
			}
			if errors.Is(err, access.ErrForbidden) {
				return badRequest("authorizer must be an active human member")
			}
			return err
		}
		// When routed through a human operator, re-verify the agent-operation
		// relationship (the operator still manages this agent) inside the
		// write transaction, closing the revoke-race window of the early
		// requireAgentOperation check.
		if operatorUserID := strings.TrimSpace(params.OperatorUserID); operatorUserID != "" {
			if _, err := store.requireAgentOperationWithQueryer(ctx, tx, operatorUserID, workspaceID, agentID); err != nil {
				return err
			}
		}
		// A personal installation only accepts grants from its owner or a
		// manage grantee, verified under the write transaction's locks so a
		// concurrent ACL revoke cannot race the authorization write.
		ownershipKind, err := store.serviceActorStore().InTx(tx).RequireInstallationManageInTx(ctx, workspaceID, installation.ID, authorizedByMemberID)
		if err != nil {
			return err
		}
		// Personal-installation agent grants can never exceed the
		// installation's active scope set: the capability-derived default is
		// intersected down so a narrowed personal installation cannot be
		// re-widened through this legacy entry. Workspace installations keep
		// the capability-derived scope contract.
		if ownershipKind == serviceactor.OwnershipKindPersonal {
			grantsByInstallation, grantsErr := store.serviceActorStore().InTx(tx).ListActiveGrantScopesByInstallationIDsForUpdate(ctx, []string{installation.ID})
			if grantsErr != nil {
				return grantsErr
			}
			scopes = serviceactor.IntersectScopes(scopes, normalizeScopeSet(grantsByInstallation[installation.ID]))
			if len(scopes) == 0 {
				return storeerr.Forbidden("token_scope_denied")
			}
		}
		now := time.Now().UTC()
		authorization, err := store.serviceActorStore().InTx(tx).UpsertPrincipalAuthorization(ctx, serviceactor.PrincipalAuthorizationUpsert{
			WorkspaceID:            workspaceID,
			ServiceDefinitionID:    installation.AppID,
			ServiceInstallationID:  installation.ID,
			ServiceKind:            serviceactor.KindApp,
			PrincipalKind:          serviceActorPrincipalKindAgent,
			PrincipalRef:           agentID,
			Scopes:                 scopes,
			AuthorizedByMemberID:   authorizedByMemberID,
			Metadata:               metadataJSON,
			Now:                    now,
			ActiveAuthorizationKey: serviceActorPrincipalAuthorizationKey(workspaceID, agentID, installationID),
		})
		if err != nil {
			return err
		}
		record, err = loadAgentAppAuthorizationByID(ctx, tx, authorization.ID)
		if err != nil {
			return err
		}
		// Always let the same-transaction Catalog projector compare the complete
		// effective surface (explicit authz + default-allow) against the durable
		// digest. A local had-active gate is incorrect for default-allow: an
		// explicit enable can be an effective no-op, while a disabled opt-out can
		// be a real revoke despite there being no active row before the mutation.
		return store.RunAgentAppCatalogChangeHook(ctx, tx, authorization.WorkspaceID, authorization.PrincipalRef, now)
	}); err != nil {
		return AgentAppAuthorizationRecord{}, err
	}
	return record, nil
}

func (store *Store) DisableAgentAppAuthorization(ctx context.Context, workspaceID string, agentID string, appInstallationID string, reason string) (AgentAppAuthorizationRecord, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedAgentID := strings.TrimSpace(agentID)
	trimmedInstallationID := strings.TrimSpace(appInstallationID)
	if trimmedWorkspaceID == "" || trimmedAgentID == "" || trimmedInstallationID == "" {
		return AgentAppAuthorizationRecord{}, badRequest("workspaceId, agentId, and appInstallationId are required")
	}
	if err := store.ensureServiceActorAppInstallationFresh(ctx, trimmedWorkspaceID, trimmedInstallationID); err != nil {
		return AgentAppAuthorizationRecord{}, err
	}

	var record AgentAppAuthorizationRecord
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		installation, installationFound, err := loadAgentAuthorizationInstallation(ctx, tx, trimmedWorkspaceID, trimmedInstallationID)
		if err != nil {
			return err
		}
		if !installationFound || !installation.Enabled || !installation.Capability.Supported {
			return storeerr.ErrNotFound
		}
		authorizedByMemberID, err := loadDefaultAgentAuthorizerMemberID(ctx, tx, trimmedWorkspaceID, trimmedAgentID)
		if err != nil {
			return err
		}
		// Personal installations have no default-allow to opt out of: a
		// disable is only meaningful when this agent already holds an
		// explicit grant. Without one, fail closed instead of creating a
		// new disabled row (and a view into someone else's installation).
		if installation.OwnershipKind == serviceactor.OwnershipKindPersonal {
			existing, found, err := store.serviceActorStore().InTx(tx).LoadActivePrincipalAuthorizationForUpdate(ctx, trimmedWorkspaceID, serviceActorPrincipalKindAgent, trimmedAgentID, trimmedInstallationID)
			if err != nil {
				return err
			}
			if !found {
				return storeerr.ErrNotFound
			}
			_ = existing
		}
		metadataJSON, err := marshalServiceEventSubscriptionJSONMap(map[string]any{"source": "default_allow_opt_out"})
		if err != nil {
			return err
		}
		now := time.Now().UTC()
		authorization, err := store.serviceActorStore().InTx(tx).DisablePrincipalAuthorization(ctx, serviceactor.PrincipalAuthorizationDisable{
			WorkspaceID:            trimmedWorkspaceID,
			ServiceDefinitionID:    installation.AppID,
			ServiceInstallationID:  installation.ID,
			ServiceKind:            serviceactor.KindApp,
			PrincipalKind:          serviceActorPrincipalKindAgent,
			PrincipalRef:           trimmedAgentID,
			DisabledReason:         strings.TrimSpace(reason),
			Scopes:                 installation.Capability.Scopes,
			AuthorizedByMemberID:   authorizedByMemberID,
			Metadata:               metadataJSON,
			Now:                    now,
			ActiveAuthorizationKey: serviceActorPrincipalAuthorizationKey(trimmedWorkspaceID, trimmedAgentID, trimmedInstallationID),
		})
		if err != nil {
			return err
		}
		record, err = loadAgentAppAuthorizationByID(ctx, tx, authorization.ID)
		if err != nil {
			return err
		}
		// The Catalog projector is the single no-op authority; see the enable
		// path above for why an authorization-row-local gate is insufficient.
		return store.RunAgentAppCatalogChangeHook(ctx, tx, authorization.WorkspaceID, authorization.PrincipalRef, now)
	}); err != nil {
		return AgentAppAuthorizationRecord{}, err
	}
	return record, nil
}

func (store *Store) ListActiveAgentAppAuthorizations(ctx context.Context, workspaceID string, agentID string) ([]AgentAppAuthorizationRecord, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedAgentID := strings.TrimSpace(agentID)
	if trimmedWorkspaceID == "" || trimmedAgentID == "" {
		return nil, badRequest("workspaceId and agentId are required")
	}
	if err := store.ensureServiceActorAppWorkspaceFresh(ctx, trimmedWorkspaceID); err != nil {
		return nil, err
	}
	rows, err := store.db.Query(ctx, `
SELECT ag.id, ag.member_id, COALESCE(agent_owner_member.id, ''),
       si.id, sd.id, sd.slug, si.workspace_id, sm.member_id, si.status = 'active',
       si.ownership_kind,
       EXISTS (
           SELECT 1
           FROM service_connections sc
           WHERE sc.service_installation_id = si.id
             AND sc.status = 'active'
       ) AS has_enabled_connection,
       COALESCE(mcp.default_enabled, FALSE), COALESCE(subscription_manage.default_enabled, FALSE),
       COALESCE(mcp.config, '{}'),
       COALESCE(spa.id, ''), COALESCE(spa.status, ''), COALESCE(spa.disabled_reason, ''),
       COALESCE(spa.scopes_json, '[]'), COALESCE(spa.authorized_by_member_id, ''),
       COALESCE(spa.policy_version, 0), spa.metadata, spa.created_at, spa.updated_at,
       spa.disabled_at, spa.removed_at
FROM service_installations si
JOIN service_definitions sd ON sd.id = si.service_definition_id AND sd.service_kind = si.service_kind
JOIN service_members sm ON sm.service_installation_id = si.id
JOIN agents ag ON ag.workspace_id = si.workspace_id AND ag.id = $2
LEFT JOIN machines agent_machine
  ON agent_machine.workspace_id = ag.workspace_id
 AND agent_machine.id = ag.machine_id
LEFT JOIN members agent_owner_member
  ON agent_owner_member.workspace_id = ag.workspace_id
 AND agent_owner_member.user_id = agent_machine.created_by_user_id
 AND agent_owner_member.type = 'human'
 AND agent_owner_member.status = 'active'
LEFT JOIN service_capabilities mcp
  ON mcp.service_definition_id = sd.id
 AND mcp.capability_key = 'mcp.tools'
LEFT JOIN service_capabilities subscription_manage
  ON subscription_manage.service_definition_id = sd.id
 AND subscription_manage.capability_key = 'subscriptions.manage'
LEFT JOIN service_principal_authorizations spa
  ON spa.workspace_id = si.workspace_id
 AND spa.principal_kind = 'agent'
 AND spa.principal_ref = ag.id
 AND spa.service_installation_id = si.id
WHERE si.workspace_id = $1
  AND si.service_kind = 'app'
  AND ag.status <> 'archived'
  AND COALESCE(agent_machine.status, '') = 'active'
  AND si.status = 'active'
ORDER BY sd.slug ASC, si.id ASC
`, trimmedWorkspaceID, trimmedAgentID)
	if err != nil {
		return nil, fmt.Errorf("list active agent app authorizations: %w", err)
	}
	defer rows.Close()

	records := make([]AgentAppAuthorizationRecord, 0)
	for rows.Next() {
		policy, err := scanAgentAppAuthorizationPolicyRow(rows)
		if err != nil {
			return nil, err
		}
		record, ok := policy.listableAuthorizationRecord()
		if !ok {
			continue
		}
		records = append(records, record)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate agent app authorizations: %w", err)
	}
	return records, nil
}

func (store *Store) CheckAgentAppAuthorization(ctx context.Context, workspaceID string, agentID string, appInstallationID string, requiredScopes []string) (AgentAppAuthorizationRecord, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedAgentID := strings.TrimSpace(agentID)
	trimmedInstallationID := strings.TrimSpace(appInstallationID)
	if trimmedWorkspaceID == "" || trimmedAgentID == "" || trimmedInstallationID == "" {
		return AgentAppAuthorizationRecord{}, badRequest("workspaceId, agentId, and appInstallationId are required")
	}
	if err := store.ensureServiceActorAppInstallationFresh(ctx, trimmedWorkspaceID, trimmedInstallationID); err != nil {
		return AgentAppAuthorizationRecord{}, err
	}
	return checkAgentAppAuthorization(ctx, store.db, workspaceID, agentID, appInstallationID, requiredScopes)
}

func (store *Store) IssueAgentAppScopedToken(ctx context.Context, workspaceID string, agentID string, appInstallationID string, sessionID string, ttl time.Duration) (AgentAppScopedTokenResult, error) {
	if store.appCapabilitySigner == nil {
		return AgentAppScopedTokenResult{}, fmt.Errorf("app capability signer is not configured")
	}
	authorization, err := store.CheckAgentAppAuthorization(ctx, workspaceID, agentID, appInstallationID, nil)
	if err != nil {
		return AgentAppScopedTokenResult{}, err
	}
	now := time.Now().UTC()
	tokenID, err := identifiers.NewEntityID("aatok")
	if err != nil {
		return AgentAppScopedTokenResult{}, fmt.Errorf("generate agent app scoped token id: %w", err)
	}
	normalizedTTL := ttl
	switch {
	case normalizedTTL <= 0:
		normalizedTTL = time.Hour
	case normalizedTTL > maxAppCapabilityTTL:
		normalizedTTL = maxAppCapabilityTTL
	}
	expiresAt := now.Add(normalizedTTL)
	token, err := store.appCapabilitySigner.Sign(capabilities.Claims{
		Subject:            authorization.AgentID,
		AppID:              authorization.AppID,
		AppInstallationID:  authorization.AppInstallationID,
		WorkspaceID:        authorization.WorkspaceID,
		MemberID:           authorization.AppMemberID,
		AgentID:            authorization.AgentID,
		AgentMemberID:      authorization.AgentMemberID,
		AuthorizerMemberID: authorization.AuthorizedByMemberID,
		SessionID:          strings.TrimSpace(sessionID),
		PolicyVersion:      authorization.PolicyVersion,
		Scopes:             authorization.Scopes,
		TokenID:            tokenID,
		IssuedAtUnix:       now.Unix(),
		ExpiresAtUnix:      expiresAt.Unix(),
	})
	if err != nil {
		return AgentAppScopedTokenResult{}, err
	}
	return AgentAppScopedTokenResult{Token: token, Authorization: authorization, ExpiresAt: expiresAt}, nil
}

func (store *Store) AuthenticateAgentAppScopedToken(ctx context.Context, workspaceID string, agentID string, agentMemberID string, provider string, bearerToken string, requiredScopes []string) (AgentAppAuthorizationRecord, error) {
	if store.appCapabilitySigner == nil {
		return AgentAppAuthorizationRecord{}, fmt.Errorf("app capability signer is not configured")
	}
	claims, err := store.appCapabilitySigner.Verify(strings.TrimSpace(bearerToken))
	if err != nil {
		if errors.Is(err, capabilities.ErrExpiredToken) {
			return AgentAppAuthorizationRecord{}, storeerr.Forbidden("token_expired")
		}
		if errors.Is(err, capabilities.ErrInvalidToken) {
			return AgentAppAuthorizationRecord{}, storeerr.Forbidden("token_policy_revoked")
		}
		return AgentAppAuthorizationRecord{}, fmt.Errorf("verify agent app scoped token: %w", err)
	}
	expectedAppID, err := providerAppID(provider)
	if err != nil {
		return AgentAppAuthorizationRecord{}, err
	}
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedAgentID := strings.TrimSpace(agentID)
	trimmedAgentMemberID := strings.TrimSpace(agentMemberID)
	if strings.TrimSpace(claims.WorkspaceID) != trimmedWorkspaceID ||
		strings.TrimSpace(claims.Subject) != trimmedAgentID ||
		strings.TrimSpace(claims.AgentID) != trimmedAgentID ||
		strings.TrimSpace(claims.AgentMemberID) != trimmedAgentMemberID ||
		strings.TrimSpace(claims.AppID) != expectedAppID ||
		strings.TrimSpace(claims.AppInstallationID) == "" {
		return AgentAppAuthorizationRecord{}, storeerr.Forbidden("token_policy_revoked")
	}
	if !scopeSetCovers(claims.Scopes, requiredScopes) {
		return AgentAppAuthorizationRecord{}, storeerr.Forbidden("token_scope_denied")
	}
	authorization, err := store.CheckAgentAppAuthorization(ctx, trimmedWorkspaceID, trimmedAgentID, claims.AppInstallationID, requiredScopes)
	if err != nil {
		return AgentAppAuthorizationRecord{}, err
	}
	if authorization.AgentMemberID != trimmedAgentMemberID ||
		authorization.AppID != expectedAppID ||
		authorization.AppInstallationID != strings.TrimSpace(claims.AppInstallationID) ||
		authorization.AppMemberID != strings.TrimSpace(claims.MemberID) ||
		authorization.PolicyVersion != claims.PolicyVersion {
		return AgentAppAuthorizationRecord{}, storeerr.Forbidden("token_policy_revoked")
	}
	return authorization, nil
}

func checkAgentAppAuthorization(ctx context.Context, queryer coredb.QueryRower, workspaceID string, agentID string, appInstallationID string, requiredScopes []string) (AgentAppAuthorizationRecord, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedAgentID := strings.TrimSpace(agentID)
	trimmedInstallationID := strings.TrimSpace(appInstallationID)
	if trimmedWorkspaceID == "" || trimmedAgentID == "" || trimmedInstallationID == "" {
		return AgentAppAuthorizationRecord{}, badRequest("workspaceId, agentId, and appInstallationId are required")
	}
	policy, found, err := loadAgentAppAuthorizationPolicy(ctx, queryer, trimmedWorkspaceID, trimmedAgentID, trimmedInstallationID)
	if err != nil {
		return AgentAppAuthorizationRecord{}, err
	}
	if !found {
		return AgentAppAuthorizationRecord{}, storeerr.Forbidden("app_not_authorized_for_agent")
	}
	record, err := policy.authorizationRecord(requiredScopes)
	if err != nil {
		return AgentAppAuthorizationRecord{}, err
	}
	return record, nil
}

type agentAppAuthorizationPolicyRow struct {
	AgentID                   string
	AgentMemberID             string
	AgentOwnerMemberID        string
	AppInstallationID         string
	AppID                     string
	AppSlug                   string
	WorkspaceID               string
	AppMemberID               string
	InstallationEnabled       bool
	InstallationOwnershipKind string
	HasEnabledConnection      bool
	HasMCPTools               bool
	HasSubscriptions          bool
	MCPCapabilityConfig       []byte
	AuthorizationID           string
	Status                    string
	DisabledReason            string
	Scopes                    []string
	AuthorizedByMemberID      string
	PolicyVersion             int64
	Metadata                  []byte
	CreatedAt                 time.Time
	UpdatedAt                 time.Time
	DisabledAt                *time.Time
	RemovedAt                 *time.Time
}

func (row agentAppAuthorizationPolicyRow) listableAuthorizationRecord() (AgentAppAuthorizationRecord, bool) {
	record, err := row.authorizationRecord(nil)
	if err != nil {
		return AgentAppAuthorizationRecord{}, false
	}
	return record, true
}

func (row agentAppAuthorizationPolicyRow) authorizationRecord(requiredScopes []string) (AgentAppAuthorizationRecord, error) {
	capability := row.capabilitySet()
	if !capability.Supported {
		return AgentAppAuthorizationRecord{}, storeerr.Forbidden("app_not_authorized_for_agent")
	}
	if !row.InstallationEnabled {
		return AgentAppAuthorizationRecord{}, storeerr.Forbidden("app_not_authorized_for_agent")
	}
	if row.RemovedAt != nil {
		return AgentAppAuthorizationRecord{}, storeerr.Forbidden("app_not_authorized_for_agent")
	}
	if row.AuthorizationID != "" {
		if row.Status != AgentAppAuthorizationStatusActive {
			return AgentAppAuthorizationRecord{}, storeerr.Forbidden("app_not_authorized_for_agent")
		}
		if !row.HasEnabledConnection {
			return AgentAppAuthorizationRecord{}, storeerr.Forbidden("app_connection_not_configured")
		}
		record := row.explicitAuthorizationRecord()
		if !scopeSetCovers(record.Scopes, requiredScopes) {
			return AgentAppAuthorizationRecord{}, storeerr.Forbidden("token_scope_denied")
		}
		return record, nil
	}
	if agentAppAuthorizationRequiresExplicit(capability) {
		return AgentAppAuthorizationRecord{}, storeerr.Forbidden(
			"app_not_authorized_for_agent",
		)
	}
	if strings.TrimSpace(row.InstallationOwnershipKind) == serviceactor.OwnershipKindPersonal {
		// Personal installations never ride the legacy default-allow path:
		// only an explicit Agent grant (handled above) exposes them.
		return AgentAppAuthorizationRecord{}, storeerr.Forbidden(
			"app_not_authorized_for_agent",
		)
	}

	scopes := capability.Scopes
	if !row.HasEnabledConnection {
		return AgentAppAuthorizationRecord{}, storeerr.Forbidden("app_connection_not_configured")
	}
	if !scopeSetCovers(scopes, requiredScopes) {
		return AgentAppAuthorizationRecord{}, storeerr.Forbidden("token_scope_denied")
	}
	return AgentAppAuthorizationRecord{
		WorkspaceID:          row.WorkspaceID,
		AgentID:              row.AgentID,
		AgentMemberID:        row.AgentMemberID,
		AppID:                row.AppID,
		AppSlug:              row.AppSlug,
		AppInstallationID:    row.AppInstallationID,
		AppMemberID:          row.AppMemberID,
		Status:               AgentAppAuthorizationStatusActive,
		Scopes:               scopes,
		AuthorizedByMemberID: row.defaultAuthorizerMemberID(),
		PolicyVersion:        1,
		MCPTools:             capability.Tools,
	}, nil
}

func (row agentAppAuthorizationPolicyRow) defaultAuthorizerMemberID() string {
	if strings.TrimSpace(row.AgentOwnerMemberID) != "" {
		return strings.TrimSpace(row.AgentOwnerMemberID)
	}
	return row.AgentMemberID
}

func (row agentAppAuthorizationPolicyRow) explicitAuthorizationRecord() AgentAppAuthorizationRecord {
	capability := row.capabilitySet()
	scopes := row.Scopes
	if len(scopes) == 0 {
		scopes = capability.Scopes
	}
	return AgentAppAuthorizationRecord{
		ID:                   row.AuthorizationID,
		WorkspaceID:          row.WorkspaceID,
		AgentID:              row.AgentID,
		AgentMemberID:        row.AgentMemberID,
		AppID:                row.AppID,
		AppSlug:              row.AppSlug,
		AppInstallationID:    row.AppInstallationID,
		AppMemberID:          row.AppMemberID,
		Status:               row.Status,
		DisabledReason:       row.DisabledReason,
		Scopes:               scopes,
		AuthorizedByMemberID: row.AuthorizedByMemberID,
		PolicyVersion:        row.PolicyVersion,
		Metadata:             row.Metadata,
		CreatedAt:            row.CreatedAt,
		UpdatedAt:            row.UpdatedAt,
		DisabledAt:           row.DisabledAt,
		RemovedAt:            row.RemovedAt,
		MCPTools:             capability.Tools,
	}
}

func (row agentAppAuthorizationPolicyRow) capabilitySet() agentAppCapabilitySet {
	return agentAppCapabilitySetForService(row.AppSlug, row.HasMCPTools, row.HasSubscriptions, row.MCPCapabilityConfig)
}

func loadAgentAppAuthorizationPolicy(ctx context.Context, queryer coredb.QueryRower, workspaceID string, agentID string, installationID string) (agentAppAuthorizationPolicyRow, bool, error) {
	row := queryer.QueryRow(ctx, `
SELECT ag.id, ag.member_id, COALESCE(agent_owner_member.id, ''),
       si.id, sd.id, sd.slug, si.workspace_id, sm.member_id, si.status = 'active',
       si.ownership_kind,
       EXISTS (
           SELECT 1
           FROM service_connections sc
           WHERE sc.service_installation_id = si.id
             AND sc.status = 'active'
       ) AS has_enabled_connection,
       COALESCE(mcp.default_enabled, FALSE), COALESCE(subscription_manage.default_enabled, FALSE),
       COALESCE(mcp.config, '{}'),
       COALESCE(spa.id, ''), COALESCE(spa.status, ''), COALESCE(spa.disabled_reason, ''),
       COALESCE(spa.scopes_json, '[]'), COALESCE(spa.authorized_by_member_id, ''),
       COALESCE(spa.policy_version, 0), spa.metadata, spa.created_at, spa.updated_at,
       spa.disabled_at, spa.removed_at
FROM service_installations si
JOIN service_definitions sd ON sd.id = si.service_definition_id AND sd.service_kind = si.service_kind
JOIN service_members sm ON sm.service_installation_id = si.id
JOIN agents ag ON ag.workspace_id = si.workspace_id AND ag.id = $2
LEFT JOIN machines agent_machine
  ON agent_machine.workspace_id = ag.workspace_id
 AND agent_machine.id = ag.machine_id
LEFT JOIN members agent_owner_member
  ON agent_owner_member.workspace_id = ag.workspace_id
 AND agent_owner_member.user_id = agent_machine.created_by_user_id
 AND agent_owner_member.type = 'human'
 AND agent_owner_member.status = 'active'
LEFT JOIN service_capabilities mcp
  ON mcp.service_definition_id = sd.id
 AND mcp.capability_key = 'mcp.tools'
LEFT JOIN service_capabilities subscription_manage
  ON subscription_manage.service_definition_id = sd.id
 AND subscription_manage.capability_key = 'subscriptions.manage'
LEFT JOIN service_principal_authorizations spa
  ON spa.workspace_id = si.workspace_id
 AND spa.principal_kind = 'agent'
 AND spa.principal_ref = ag.id
 AND spa.service_installation_id = si.id
WHERE si.workspace_id = $1
	  AND si.id = $3
	  AND si.service_kind = 'app'
	  AND ag.status <> 'archived'
  AND COALESCE(agent_machine.status, '') = 'active'
LIMIT 1
`, workspaceID, agentID, installationID)
	policy, err := scanAgentAppAuthorizationPolicyRow(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return agentAppAuthorizationPolicyRow{}, false, nil
		}
		return agentAppAuthorizationPolicyRow{}, false, err
	}
	if !registry.IsProductApp(policy.AppID, policy.AppSlug) {
		return agentAppAuthorizationPolicyRow{}, false, nil
	}
	return policy, true, nil
}

func scanAgentAppAuthorizationPolicyRow(row coredb.Row) (agentAppAuthorizationPolicyRow, error) {
	var policy agentAppAuthorizationPolicyRow
	var scopesJSON string
	var mcpConfigJSON string
	var metadata sql.NullString
	var createdAt, updatedAt, disabledAt, removedAt sql.NullTime
	if err := row.Scan(
		&policy.AgentID,
		&policy.AgentMemberID,
		&policy.AgentOwnerMemberID,
		&policy.AppInstallationID,
		&policy.AppID,
		&policy.AppSlug,
		&policy.WorkspaceID,
		&policy.AppMemberID,
		&policy.InstallationEnabled,
		&policy.InstallationOwnershipKind,
		&policy.HasEnabledConnection,
		&policy.HasMCPTools,
		&policy.HasSubscriptions,
		&mcpConfigJSON,
		&policy.AuthorizationID,
		&policy.Status,
		&policy.DisabledReason,
		&scopesJSON,
		&policy.AuthorizedByMemberID,
		&policy.PolicyVersion,
		&metadata,
		&createdAt,
		&updatedAt,
		&disabledAt,
		&removedAt,
	); err != nil {
		return agentAppAuthorizationPolicyRow{}, err
	}
	policy.MCPCapabilityConfig = []byte(mcpConfigJSON)
	policy.Scopes = decodeAgentAppAuthorizationScopes([]byte(scopesJSON))
	if metadata.Valid {
		policy.Metadata = []byte(metadata.String)
	}
	if createdAt.Valid {
		policy.CreatedAt = createdAt.Time
	}
	if updatedAt.Valid {
		policy.UpdatedAt = updatedAt.Time
	}
	if disabledAt.Valid {
		policy.DisabledAt = &disabledAt.Time
	}
	if removedAt.Valid {
		policy.RemovedAt = &removedAt.Time
	}
	return policy, nil
}

func loadDefaultAgentAuthorizerMemberID(ctx context.Context, queryer coredb.QueryRower, workspaceID string, agentID string) (string, error) {
	row := queryer.QueryRow(ctx, `
SELECT COALESCE(agent_owner_member.id, ag.member_id)
FROM agents ag
LEFT JOIN machines agent_machine
  ON agent_machine.workspace_id = ag.workspace_id
 AND agent_machine.id = ag.machine_id
LEFT JOIN members agent_owner_member
  ON agent_owner_member.workspace_id = ag.workspace_id
 AND agent_owner_member.user_id = agent_machine.created_by_user_id
 AND agent_owner_member.type = 'human'
 AND agent_owner_member.status = 'active'
WHERE ag.workspace_id = $1
  AND ag.id = $2
  AND ag.status <> 'archived'
  AND COALESCE(agent_machine.status, '') = 'active'
LIMIT 1
`, workspaceID, agentID)
	var memberID string
	if err := row.Scan(&memberID); err != nil {
		if coredb.IsNotFound(err) {
			return "", storeerr.ErrNotFound
		}
		return "", fmt.Errorf("load default agent app authorizer: %w", err)
	}
	return memberID, nil
}

type agentAuthorizationInstallation struct {
	ID            string
	AppID         string
	AppSlug       string
	WorkspaceID   string
	MemberID      string
	Enabled       bool
	OwnershipKind string
	Capability    agentAppCapabilitySet
}

func loadAgentAuthorizationInstallationForUpdate(ctx context.Context, queryer coredb.QueryRower, workspaceID string, installationID string) (agentAuthorizationInstallation, bool, error) {
	projection, found, err := loadAgentAuthorizationInstallation(ctx, queryerForUpdate{queryer}, workspaceID, installationID)
	return projection, found, err
}

type queryerForUpdate struct {
	inner coredb.QueryRower
}

func (adapter queryerForUpdate) QueryRow(ctx context.Context, query string, args ...any) coredb.Row {
	return adapter.inner.QueryRow(ctx, query+"\nFOR UPDATE", args...)
}

func loadAgentAuthorizationInstallation(ctx context.Context, queryer coredb.QueryRower, workspaceID string, installationID string) (agentAuthorizationInstallation, bool, error) {
	row := queryer.QueryRow(ctx, `
SELECT si.id, sd.id, sd.slug, si.workspace_id, sm.member_id, si.status = 'active',
       COALESCE(si.ownership_kind, 'workspace'),
       COALESCE(mcp.default_enabled, FALSE), COALESCE(subscription_manage.default_enabled, FALSE),
       COALESCE(mcp.config, '{}')
FROM service_installations si
JOIN service_definitions sd
  ON sd.id = si.service_definition_id
 AND sd.service_kind = si.service_kind
JOIN service_members sm
  ON sm.service_installation_id = si.id
LEFT JOIN service_capabilities mcp
  ON mcp.service_definition_id = sd.id
 AND mcp.capability_key = 'mcp.tools'
LEFT JOIN service_capabilities subscription_manage
  ON subscription_manage.service_definition_id = sd.id
 AND subscription_manage.capability_key = 'subscriptions.manage'
WHERE si.workspace_id = $1
  AND si.id = $2
  AND si.service_kind = 'app'
`, workspaceID, installationID)
	var installation agentAuthorizationInstallation
	var hasMCPTools, hasSubscriptions bool
	var mcpConfigJSON string
	if err := row.Scan(&installation.ID, &installation.AppID, &installation.AppSlug, &installation.WorkspaceID, &installation.MemberID, &installation.Enabled, &installation.OwnershipKind, &hasMCPTools, &hasSubscriptions, &mcpConfigJSON); err != nil {
		if coredb.IsNotFound(err) {
			return agentAuthorizationInstallation{}, false, nil
		}
		return agentAuthorizationInstallation{}, false, fmt.Errorf("load service installation for agent authorization: %w", err)
	}
	installation.Capability = agentAppCapabilitySetForService(installation.AppSlug, hasMCPTools, hasSubscriptions, []byte(mcpConfigJSON))
	return installation, true, nil
}

func requireAgentInWorkspace(ctx context.Context, queryer coredb.QueryRower, workspaceID string, agentID string) error {
	row := queryer.QueryRow(ctx, `
SELECT 1
FROM agents ag
JOIN machines mac
  ON mac.workspace_id = ag.workspace_id
 AND mac.id = ag.machine_id
WHERE ag.workspace_id = $1
  AND ag.id = $2
  AND ag.status <> 'archived'
  AND mac.status = 'active'
`, workspaceID, agentID)
	var marker int
	if err := row.Scan(&marker); err != nil {
		if coredb.IsNotFound(err) {
			return storeerr.ErrNotFound
		}
		return fmt.Errorf("load agent for app authorization: %w", err)
	}
	return nil
}

func requireMemberInWorkspace(ctx context.Context, queryer coredb.QueryRower, workspaceID string, memberID string) error {
	row := queryer.QueryRow(ctx, `SELECT 1 FROM members WHERE workspace_id = $1 AND id = $2`, workspaceID, memberID)
	var marker int
	if err := row.Scan(&marker); err != nil {
		if coredb.IsNotFound(err) {
			return storeerr.ErrNotFound
		}
		return fmt.Errorf("load member for app authorization: %w", err)
	}
	return nil
}

func loadAgentAppAuthorizationByID(ctx context.Context, queryer coredb.QueryRower, authorizationID string) (AgentAppAuthorizationRecord, error) {
	row := queryer.QueryRow(ctx, `
SELECT ag.id, ag.member_id, COALESCE(agent_owner_member.id, ''),
       si.id, sd.id, sd.slug, si.workspace_id, sm.member_id, si.status = 'active',
       si.ownership_kind,
       EXISTS (
           SELECT 1
           FROM service_connections sc
           WHERE sc.service_installation_id = si.id
             AND sc.status = 'active'
       ) AS has_enabled_connection,
       COALESCE(mcp.default_enabled, FALSE), COALESCE(subscription_manage.default_enabled, FALSE),
       COALESCE(mcp.config, '{}'),
       COALESCE(spa.id, ''), COALESCE(spa.status, ''), COALESCE(spa.disabled_reason, ''),
       COALESCE(spa.scopes_json, '[]'), COALESCE(spa.authorized_by_member_id, ''),
       COALESCE(spa.policy_version, 0), spa.metadata, spa.created_at, spa.updated_at,
       spa.disabled_at, spa.removed_at
FROM service_principal_authorizations spa
JOIN agents ag ON ag.id = spa.principal_ref AND ag.workspace_id = spa.workspace_id
JOIN machines mac ON mac.workspace_id = ag.workspace_id AND mac.id = ag.machine_id
JOIN service_installations si ON si.id = spa.service_installation_id
JOIN service_definitions sd ON sd.id = spa.service_definition_id AND sd.service_kind = spa.service_kind
JOIN service_members sm ON sm.service_installation_id = si.id
LEFT JOIN members agent_owner_member
  ON agent_owner_member.workspace_id = ag.workspace_id
 AND agent_owner_member.user_id = mac.created_by_user_id
 AND agent_owner_member.type = 'human'
 AND agent_owner_member.status = 'active'
LEFT JOIN service_capabilities mcp
  ON mcp.service_definition_id = sd.id
 AND mcp.capability_key = 'mcp.tools'
LEFT JOIN service_capabilities subscription_manage
  ON subscription_manage.service_definition_id = sd.id
 AND subscription_manage.capability_key = 'subscriptions.manage'
WHERE spa.id = $1
  AND spa.service_kind = 'app'
  AND spa.principal_kind = 'agent'
  AND ag.status <> 'archived'
  AND mac.status = 'active'
`, authorizationID)
	policy, err := scanAgentAppAuthorizationPolicyRow(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return AgentAppAuthorizationRecord{}, storeerr.ErrNotFound
		}
		return AgentAppAuthorizationRecord{}, err
	}
	return policy.explicitAuthorizationRecord(), nil
}

func marshalAgentAppAuthorizationScopes(scopes []string) ([]byte, error) {
	normalized := normalizeScopeSet(scopes)
	payload, err := model.MarshalJSONObject("agent app authorization scopes", normalized)
	if err != nil {
		return nil, badRequest("scopes must be a JSON array")
	}
	return payload, nil
}

func decodeAgentAppAuthorizationScopes(payload []byte) []string {
	var scopes []string
	if err := model.UnmarshalJSONObject("agent app authorization scopes", payload, &scopes); err != nil {
		return nil
	}
	return normalizeScopeSet(scopes)
}

func normalizeScopeSet(scopes []string) []string {
	seen := make(map[string]struct{}, len(scopes))
	normalized := make([]string, 0, len(scopes))
	for _, scope := range scopes {
		trimmed := normalizeServiceEventAuthorizationScope(scope)
		if trimmed == "" {
			continue
		}
		if _, ok := seen[trimmed]; ok {
			continue
		}
		seen[trimmed] = struct{}{}
		normalized = append(normalized, trimmed)
	}
	return normalized
}

func scopeSetCovers(granted []string, required []string) bool {
	if len(required) == 0 {
		return true
	}
	allowed := make(map[string]struct{}, len(granted))
	for _, scope := range normalizeScopeSet(granted) {
		allowed[scope] = struct{}{}
	}
	for _, scope := range normalizeScopeSet(required) {
		if _, ok := allowed[scope]; !ok {
			return false
		}
	}
	return true
}

func serviceActorPrincipalAuthorizationKey(parts ...string) string {
	trimmed := make([]string, 0, len(parts))
	for _, part := range parts {
		trimmed = append(trimmed, strings.TrimSpace(part))
	}
	payload, _ := model.MarshalJSONObject("service actor principal authorization key", trimmed)
	sum := sha256.Sum256(payload)
	return hex.EncodeToString(sum[:])
}