package serviceactor

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

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
)

// PrincipalAuthorization records a service grant to a AgentTeam principal such as a
// human, agent, or workspace.
type PrincipalAuthorization struct {
	ID                     string
	WorkspaceID            string
	ServiceDefinitionID    string
	ServiceInstallationID  string
	ServiceKind            string
	PrincipalKind          string
	PrincipalRef           string
	AccessRole             string
	Status                 string
	DisabledReason         string
	ScopesJSON             []byte
	AuthorizedByMemberID   string
	PolicyVersion          int64
	Metadata               []byte
	CreatedAt              time.Time
	UpdatedAt              time.Time
	DisabledAt             *time.Time
	RemovedAt              *time.Time
	ActiveAuthorizationKey string
}

// PrincipalAuthorizationUpsert describes a principal authorization write.
type PrincipalAuthorizationUpsert struct {
	ID                     string
	WorkspaceID            string
	ServiceDefinitionID    string
	ServiceInstallationID  string
	ServiceKind            string
	PrincipalKind          string
	PrincipalRef           string
	AccessRole             string
	Scopes                 []string
	AuthorizedByMemberID   string
	Metadata               []byte
	Now                    time.Time
	ActiveAuthorizationKey string
}

// PrincipalAuthorizationDisable describes a service principal authorization
// disable or opt-out write.
type PrincipalAuthorizationDisable struct {
	ID                     string
	WorkspaceID            string
	ServiceDefinitionID    string
	ServiceInstallationID  string
	ServiceKind            string
	PrincipalKind          string
	PrincipalRef           string
	AccessRole             string
	DisabledReason         string
	Scopes                 []string
	AuthorizedByMemberID   string
	Metadata               []byte
	Now                    time.Time
	ActiveAuthorizationKey string
}

// UpsertPrincipalAuthorization creates or updates an active principal authorization.
func (store *Store) UpsertPrincipalAuthorization(ctx context.Context, params PrincipalAuthorizationUpsert) (PrincipalAuthorization, error) {
	execer, err := execerFromStore(store)
	if err != nil {
		return PrincipalAuthorization{}, err
	}
	return upsertPrincipalAuthorization(ctx, execer, params)
}

// UpsertPrincipalAuthorization creates or updates an active principal authorization in the transaction.
func (store *TxStore) UpsertPrincipalAuthorization(ctx context.Context, params PrincipalAuthorizationUpsert) (PrincipalAuthorization, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return PrincipalAuthorization{}, err
	}
	return upsertPrincipalAuthorization(ctx, execer, params)
}

// DisablePrincipalAuthorization disables or creates an explicit opt-out service
// principal authorization.
func (store *Store) DisablePrincipalAuthorization(ctx context.Context, params PrincipalAuthorizationDisable) (PrincipalAuthorization, error) {
	execer, err := execerFromStore(store)
	if err != nil {
		return PrincipalAuthorization{}, err
	}
	return disablePrincipalAuthorization(ctx, execer, params)
}

// DisablePrincipalAuthorization disables or creates an explicit opt-out service
// principal authorization in the transaction.
func (store *TxStore) DisablePrincipalAuthorization(ctx context.Context, params PrincipalAuthorizationDisable) (PrincipalAuthorization, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return PrincipalAuthorization{}, err
	}
	return disablePrincipalAuthorization(ctx, execer, params)
}

// LoadActivePrincipalAuthorizationForUpdate locks an active principal authorization.
func (store *TxStore) LoadActivePrincipalAuthorizationForUpdate(ctx context.Context, workspaceID string, principalKind string, principalRef string, serviceInstallationID string) (PrincipalAuthorization, bool, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return PrincipalAuthorization{}, false, err
	}
	return loadActivePrincipalAuthorizationForUpdate(ctx, execer, workspaceID, principalKind, principalRef, serviceInstallationID)
}

func upsertPrincipalAuthorization(ctx context.Context, execer coredb.QueryExecer, params PrincipalAuthorizationUpsert) (PrincipalAuthorization, error) {
	workspaceID := trim(params.WorkspaceID)
	definitionID := trim(params.ServiceDefinitionID)
	installationID := trim(params.ServiceInstallationID)
	serviceKind := trim(params.ServiceKind)
	principalKind := trim(params.PrincipalKind)
	principalRef := trim(params.PrincipalRef)
	authorizedByMemberID := trim(params.AuthorizedByMemberID)
	if workspaceID == "" || definitionID == "" || installationID == "" || serviceKind == "" || principalKind == "" || principalRef == "" || authorizedByMemberID == "" {
		return PrincipalAuthorization{}, badRequest("workspaceId, serviceDefinitionId, serviceInstallationId, serviceKind, principal, and authorizedByMemberId are required")
	}
	accessRole, err := NormalizeAccessRole(params.AccessRole)
	if err != nil {
		return PrincipalAuthorization{}, err
	}
	scopesJSON, err := model.MarshalJSONObject("service principal authorization scopes", sortedUnique(params.Scopes))
	if err != nil {
		return PrincipalAuthorization{}, fmt.Errorf("encode service principal authorization scopes: %w", err)
	}
	now := params.Now.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	existing, found, err := loadActivePrincipalAuthorizationForUpdate(ctx, execer, workspaceID, principalKind, principalRef, installationID)
	if err != nil {
		return PrincipalAuthorization{}, err
	}
	if found {
		if _, err := execer.Exec(ctx, `
UPDATE service_principal_authorizations
SET status = 'active',
    disabled_reason = '',
    scopes_json = $2,
    authorized_by_member_id = $3,
    policy_version = policy_version + 1,
    metadata = $4,
    disabled_at = NULL,
    removed_at = NULL,
    updated_at = $5,
    active_authorization_key = $6,
    access_role = $7
WHERE id = $1
`, existing.ID, scopesJSON, authorizedByMemberID, params.Metadata, now, nullableString(params.ActiveAuthorizationKey), accessRole); err != nil {
			return PrincipalAuthorization{}, fmt.Errorf("update service principal authorization: %w", err)
		}
		return loadPrincipalAuthorizationByID(ctx, execer, existing.ID)
	}

	authorizationID := trim(params.ID)
	if authorizationID == "" {
		authorizationID, err = identifiers.NewEntityID("svcauth")
		if err != nil {
			return PrincipalAuthorization{}, fmt.Errorf("generate service principal authorization id: %w", err)
		}
	}
	if _, err := execer.Exec(ctx, `
INSERT INTO service_principal_authorizations (
    id, workspace_id, service_definition_id, service_installation_id,
    service_kind, principal_kind, principal_ref, access_role, status, disabled_reason,
    scopes_json, authorized_by_member_id, policy_version, metadata,
    created_at, updated_at, active_authorization_key
) VALUES (
    $1, $2, $3, $4,
    $5, $6, $7, $8, 'active', '',
    $9, $10, 1, $11,
    $12, $12, $13
)
`, authorizationID, workspaceID, definitionID, installationID, serviceKind, principalKind, principalRef, accessRole, scopesJSON, authorizedByMemberID, params.Metadata, now, nullableString(params.ActiveAuthorizationKey)); err != nil {
		return PrincipalAuthorization{}, fmt.Errorf("insert service principal authorization: %w", mapAuthorizationWriteError(err))
	}
	return loadPrincipalAuthorizationByID(ctx, execer, authorizationID)
}

// NormalizeAccessRole validates an access role, defaulting to use.
func NormalizeAccessRole(accessRole string) (string, error) {
	accessRole = trim(accessRole)
	if accessRole == "" {
		return AccessRoleUse, nil
	}
	switch accessRole {
	case AccessRoleUse, AccessRoleManage:
		return accessRole, nil
	default:
		return "", badRequest("service principal authorization access role is invalid")
	}
}

func disablePrincipalAuthorization(ctx context.Context, execer coredb.QueryExecer, params PrincipalAuthorizationDisable) (PrincipalAuthorization, error) {
	workspaceID := trim(params.WorkspaceID)
	definitionID := trim(params.ServiceDefinitionID)
	installationID := trim(params.ServiceInstallationID)
	serviceKind := trim(params.ServiceKind)
	principalKind := trim(params.PrincipalKind)
	principalRef := trim(params.PrincipalRef)
	authorizedByMemberID := trim(params.AuthorizedByMemberID)
	if workspaceID == "" || definitionID == "" || installationID == "" || serviceKind == "" || principalKind == "" || principalRef == "" || authorizedByMemberID == "" {
		return PrincipalAuthorization{}, badRequest("workspaceId, serviceDefinitionId, serviceInstallationId, serviceKind, principal, and authorizedByMemberId are required")
	}
	scopesJSON, err := model.MarshalJSONObject("service principal authorization scopes", sortedUnique(params.Scopes))
	if err != nil {
		return PrincipalAuthorization{}, fmt.Errorf("encode service principal authorization scopes: %w", err)
	}
	now := params.Now.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	existing, found, err := loadActivePrincipalAuthorizationForUpdate(ctx, execer, workspaceID, principalKind, principalRef, installationID)
	if err != nil {
		return PrincipalAuthorization{}, err
	}
	if found {
		if _, err := execer.Exec(ctx, `
UPDATE service_principal_authorizations
SET status = 'disabled',
    disabled_reason = $2,
    scopes_json = $3,
    authorized_by_member_id = $4,
    policy_version = policy_version + 1,
    metadata = $5,
    disabled_at = $6,
    removed_at = NULL,
    updated_at = $6,
    active_authorization_key = $7
WHERE id = $1
`, existing.ID, trim(params.DisabledReason), scopesJSON, authorizedByMemberID, params.Metadata, now, nullableString(params.ActiveAuthorizationKey)); err != nil {
			return PrincipalAuthorization{}, fmt.Errorf("disable service principal authorization: %w", err)
		}
		return loadPrincipalAuthorizationByID(ctx, execer, existing.ID)
	}

	authorizationID := trim(params.ID)
	if authorizationID == "" {
		authorizationID, err = identifiers.NewEntityID("svcauth")
		if err != nil {
			return PrincipalAuthorization{}, fmt.Errorf("generate service principal authorization id: %w", err)
		}
	}
	if _, err := execer.Exec(ctx, `
INSERT INTO service_principal_authorizations (
    id, workspace_id, service_definition_id, service_installation_id,
    service_kind, principal_kind, principal_ref, status, disabled_reason,
    scopes_json, authorized_by_member_id, policy_version, metadata,
    created_at, updated_at, disabled_at, active_authorization_key
) VALUES (
    $1, $2, $3, $4,
    $5, $6, $7, 'disabled', $8,
    $9, $10, 1, $11,
    $12, $12, $12, $13
)
`, authorizationID, workspaceID, definitionID, installationID, serviceKind, principalKind, principalRef, trim(params.DisabledReason), scopesJSON, authorizedByMemberID, params.Metadata, now, nullableString(params.ActiveAuthorizationKey)); err != nil {
		return PrincipalAuthorization{}, fmt.Errorf("insert disabled service principal authorization: %w", mapAuthorizationWriteError(err))
	}
	return loadPrincipalAuthorizationByID(ctx, execer, authorizationID)
}

func loadActivePrincipalAuthorizationForUpdate(ctx context.Context, queryer coredb.QueryRower, workspaceID string, principalKind string, principalRef string, serviceInstallationID string) (PrincipalAuthorization, bool, error) {
	row := queryer.QueryRow(ctx, authorizationSelectSQL()+`
WHERE workspace_id = $1
  AND principal_kind = $2
  AND principal_ref = $3
  AND service_installation_id = $4
LIMIT 1
FOR UPDATE
`, trim(workspaceID), trim(principalKind), trim(principalRef), trim(serviceInstallationID))
	authorization, err := scanPrincipalAuthorization(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return PrincipalAuthorization{}, false, nil
		}
		return PrincipalAuthorization{}, false, fmt.Errorf("load service principal authorization: %w", err)
	}
	return authorization, true, nil
}

func loadPrincipalAuthorizationByID(ctx context.Context, queryer coredb.QueryRower, authorizationID string) (PrincipalAuthorization, error) {
	row := queryer.QueryRow(ctx, authorizationSelectSQL()+` WHERE id = $1`, trim(authorizationID))
	authorization, err := scanPrincipalAuthorization(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return PrincipalAuthorization{}, notFound("service principal authorization not found")
		}
		return PrincipalAuthorization{}, fmt.Errorf("load service principal authorization: %w", err)
	}
	return authorization, nil
}

func authorizationSelectSQL() string {
	return `
SELECT id, workspace_id, service_definition_id, service_installation_id,
       service_kind, principal_kind, principal_ref, access_role, status, disabled_reason,
       scopes_json, authorized_by_member_id, policy_version, metadata,
       created_at, updated_at, disabled_at, removed_at, active_authorization_key
FROM service_principal_authorizations`
}

func scanPrincipalAuthorization(row coredb.Row) (PrincipalAuthorization, error) {
	var authorization PrincipalAuthorization
	var disabledAt sql.NullTime
	var removedAt sql.NullTime
	var activeKey sql.NullString
	if err := row.Scan(
		&authorization.ID,
		&authorization.WorkspaceID,
		&authorization.ServiceDefinitionID,
		&authorization.ServiceInstallationID,
		&authorization.ServiceKind,
		&authorization.PrincipalKind,
		&authorization.PrincipalRef,
		&authorization.AccessRole,
		&authorization.Status,
		&authorization.DisabledReason,
		&authorization.ScopesJSON,
		&authorization.AuthorizedByMemberID,
		&authorization.PolicyVersion,
		&authorization.Metadata,
		&authorization.CreatedAt,
		&authorization.UpdatedAt,
		&disabledAt,
		&removedAt,
		&activeKey,
	); err != nil {
		return PrincipalAuthorization{}, err
	}
	authorization.ID = trim(authorization.ID)
	authorization.WorkspaceID = trim(authorization.WorkspaceID)
	authorization.ServiceDefinitionID = trim(authorization.ServiceDefinitionID)
	authorization.ServiceInstallationID = trim(authorization.ServiceInstallationID)
	authorization.ServiceKind = trim(authorization.ServiceKind)
	authorization.PrincipalKind = trim(authorization.PrincipalKind)
	authorization.PrincipalRef = trim(authorization.PrincipalRef)
	authorization.AccessRole = trim(authorization.AccessRole)
	if authorization.AccessRole == "" {
		authorization.AccessRole = AccessRoleUse
	}
	authorization.Status = trim(authorization.Status)
	authorization.AuthorizedByMemberID = trim(authorization.AuthorizedByMemberID)
	authorization.DisabledAt = ptrFromNullTime(disabledAt)
	authorization.RemovedAt = ptrFromNullTime(removedAt)
	if activeKey.Valid {
		authorization.ActiveAuthorizationKey = trim(activeKey.String)
	}
	return authorization, nil
}

// ListInstallationPrincipalAuthorizations lists every authorization decision on
// one installation, active and disabled, for management surfaces.
func (store *Store) ListInstallationPrincipalAuthorizations(ctx context.Context, workspaceID string, serviceInstallationID string) ([]PrincipalAuthorization, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	return listInstallationPrincipalAuthorizations(ctx, queryer, workspaceID, serviceInstallationID)
}

// ListInstallationPrincipalAuthorizations lists every authorization decision on
// one installation in the transaction.
func (store *TxStore) ListInstallationPrincipalAuthorizations(ctx context.Context, workspaceID string, serviceInstallationID string) ([]PrincipalAuthorization, error) {
	queryer, err := queryerFromTxStore(store)
	if err != nil {
		return nil, err
	}
	return listInstallationPrincipalAuthorizations(ctx, queryer, workspaceID, serviceInstallationID)
}

func listInstallationPrincipalAuthorizations(ctx context.Context, queryer coredb.Queryer, workspaceID string, serviceInstallationID string) ([]PrincipalAuthorization, error) {
	workspaceID = trim(workspaceID)
	serviceInstallationID = trim(serviceInstallationID)
	if workspaceID == "" || serviceInstallationID == "" {
		return nil, badRequest("workspaceId and serviceInstallationId are required")
	}
	rows, err := queryer.Query(ctx, authorizationSelectSQL()+`
WHERE workspace_id = $1
  AND service_installation_id = $2
ORDER BY principal_kind ASC, principal_ref ASC, created_at ASC, id ASC
`, workspaceID, serviceInstallationID)
	if err != nil {
		return nil, fmt.Errorf("list installation principal authorizations: %w", err)
	}
	defer rows.Close()
	authorizations := make([]PrincipalAuthorization, 0)
	for rows.Next() {
		authorization, err := scanPrincipalAuthorization(rows)
		if err != nil {
			return nil, fmt.Errorf("scan installation principal authorization: %w", err)
		}
		authorizations = append(authorizations, authorization)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate installation principal authorizations: %w", err)
	}
	return authorizations, nil
}

// ResolveHumanInstallationAccess returns the strongest access a workspace
// member holds on an installation through explicit principal authorizations:
// the member's own human grant plus the workspace-wide grant. Personal
// ownership is resolved by the caller from the installation row.
func (store *Store) ResolveHumanInstallationAccess(ctx context.Context, workspaceID string, memberID string, serviceInstallationID string) (string, bool, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return "", false, err
	}
	return resolveHumanInstallationAccess(ctx, queryer, workspaceID, memberID, serviceInstallationID, false)
}

// ResolveHumanInstallationAccess is the transaction-bound access resolution.
func (store *TxStore) ResolveHumanInstallationAccess(ctx context.Context, workspaceID string, memberID string, serviceInstallationID string) (string, bool, error) {
	queryer, err := queryerFromTxStore(store)
	if err != nil {
		return "", false, err
	}
	return resolveHumanInstallationAccess(ctx, queryer, workspaceID, memberID, serviceInstallationID, false)
}

func resolveHumanInstallationAccess(ctx context.Context, queryer coredb.Queryer, workspaceID string, memberID string, serviceInstallationID string, forUpdate bool) (string, bool, error) {
	workspaceID = trim(workspaceID)
	memberID = trim(memberID)
	installationID := trim(serviceInstallationID)
	if workspaceID == "" || memberID == "" || installationID == "" {
		return "", false, badRequest("workspaceId, memberId, and serviceInstallationId are required")
	}
	lockClause := ""
	if forUpdate {
		lockClause = "\nFOR UPDATE"
	}
	rows, err := queryer.Query(ctx, `
SELECT access_role
FROM service_principal_authorizations
WHERE workspace_id = $1
  AND service_installation_id = $2
  AND status = 'active'
  AND removed_at IS NULL
  AND (
    (principal_kind = 'human' AND principal_ref = $3)
    OR (principal_kind = 'workspace' AND principal_ref = $1)
  )
`+lockClause, workspaceID, installationID, memberID)
	if err != nil {
		return "", false, fmt.Errorf("resolve human installation access: %w", err)
	}
	defer rows.Close()
	best := ""
	for rows.Next() {
		var accessRole string
		if err := rows.Scan(&accessRole); err != nil {
			return "", false, fmt.Errorf("scan human installation access: %w", err)
		}
		accessRole = trim(accessRole)
		if accessRole == "" {
			accessRole = AccessRoleUse
		}
		if accessRole == AccessRoleManage {
			best = AccessRoleManage
		} else if best == "" {
			best = AccessRoleUse
		}
	}
	if err := rows.Err(); err != nil {
		return "", false, fmt.Errorf("iterate human installation access: %w", err)
	}
	return best, best != "", nil
}

func mapAuthorizationWriteError(err error) error {
	if constraint, ok := coredb.UniqueViolationConstraint(err); ok {
		switch constraint {
		case "service_principal_authorizations_active_key":
			return conflict("Service principal authorization already exists.")
		}
	}
	return err
}

// TrimInstallationPrincipalAuthorizationScopes intersects every active
// principal authorization of one installation with the given scope set,
// disabling authorizations left with no scopes. It returns whether any Agent
// authorization changed so the caller can refresh flattened catalogs. The
// trim computes every new scope set in memory first and lands the changes in
// two set-based statements.
func (store *TxStore) TrimInstallationPrincipalAuthorizationScopes(
	ctx context.Context,
	workspaceID string,
	installationID string,
	activeScopes []string,
	now time.Time,
) (bool, error) {
	authorizations, err := store.ListInstallationPrincipalAuthorizations(ctx, workspaceID, installationID)
	if err != nil {
		return false, err
	}
	allowedSet := make(map[string]struct{}, len(activeScopes))
	for _, scope := range activeScopes {
		allowedSet[trim(scope)] = struct{}{}
	}
	agentChanged := false
	type narrowedAuthorization struct {
		id     string
		scopes []string
	}
	narrowed := make([]narrowedAuthorization, 0, len(authorizations))
	emptied := make([]string, 0, len(authorizations))
	for _, authorization := range authorizations {
		if authorization.Status != StatusActive {
			continue
		}
		var current []string
		if len(authorization.ScopesJSON) > 0 {
			if err := model.UnmarshalJSONObject("authorization scopes", authorization.ScopesJSON, &current); err != nil {
				return false, fmt.Errorf("decode authorization scopes for trim: %w", err)
			}
		}
		trimmed := make([]string, 0, len(current))
		seen := make(map[string]struct{}, len(current))
		changed := false
		for _, scope := range current {
			scoped := trim(scope)
			if scoped == "" {
				changed = true
				continue
			}
			if _, ok := allowedSet[scoped]; !ok {
				changed = true
				continue
			}
			if _, dup := seen[scoped]; dup {
				changed = true
				continue
			}
			seen[scoped] = struct{}{}
			trimmed = append(trimmed, scoped)
		}
		if !changed && len(trimmed) == len(current) {
			continue
		}
		if authorization.PrincipalKind == PrincipalKindAgent {
			agentChanged = true
		}
		if len(trimmed) == 0 {
			emptied = append(emptied, authorization.ID)
			continue
		}
		narrowed = append(narrowed, narrowedAuthorization{id: authorization.ID, scopes: trimmed})
	}
	if len(emptied) > 0 {
		placeholders := make([]string, 0, len(emptied))
		args := make([]any, 0, len(emptied)+1)
		args = append(args, now)
		for _, id := range emptied {
			args = append(args, id)
			placeholders = append(placeholders, fmt.Sprintf("$%d", len(args)))
		}
		if _, err := store.db.Exec(ctx, fmt.Sprintf(`
UPDATE service_principal_authorizations
SET status = 'disabled',
    disabled_reason = 'scope_narrowed',
    disabled_at = $1,
    updated_at = $1,
    active_authorization_key = NULL,
    policy_version = policy_version + 1
WHERE status = 'active'
  AND id IN (%s)
`, strings.Join(placeholders, ", ")), args...); err != nil {
			return false, fmt.Errorf("disable narrowed principal authorizations: %w", err)
		}
	}
	if len(narrowed) == 0 {
		return agentChanged, nil
	}
	setClauses2 := make([]string, 0, len(narrowed))
	args2 := make([]any, 0, len(narrowed)*3+1)
	for _, item := range narrowed {
		scopesJSON, err := model.MarshalJSONObject("narrowed authorization scopes", item.scopes)
		if err != nil {
			return false, err
		}
		setClauses2 = append(setClauses2, fmt.Sprintf("WHEN $%d THEN $%d", len(args2)+1, len(args2)+2))
		args2 = append(args2, item.id, scopesJSON)
	}
	nowIdx := len(args2) + 1
	args2 = append(args2, now)
	idPlaceholders := make([]string, 0, len(narrowed))
	for _, item := range narrowed {
		args2 = append(args2, item.id)
		idPlaceholders = append(idPlaceholders, fmt.Sprintf("$%d", len(args2)))
	}
	if _, err := store.db.Exec(ctx, fmt.Sprintf(`
UPDATE service_principal_authorizations
SET scopes_json = CASE id %s END,
    updated_at = $%d,
    policy_version = policy_version + 1
WHERE status = 'active'
  AND id IN (%s)
`, strings.Join(setClauses2, " "), nowIdx, strings.Join(idPlaceholders, ", ")), args2...); err != nil {
		return false, fmt.Errorf("narrow principal authorization scopes: %w", err)
	}
	return agentChanged, nil
}

// RequireInstallationManageInTx locks the installation row and verifies the
// member holds manage authority over a personal installation (owner or an
// active manage grant). It returns the installation's ownership kind.
func (store *TxStore) RequireInstallationManageInTx(
	ctx context.Context,
	workspaceID string,
	installationID string,
	memberID string,
) (string, error) {
	var ownershipKind, ownerMemberID string
	err := store.db.QueryRow(ctx, `
SELECT ownership_kind, COALESCE(owner_member_id, '')
FROM service_installations
WHERE workspace_id = $1
  AND id = $2
  AND service_kind = 'app'
LIMIT 1
FOR UPDATE
`, trim(workspaceID), trim(installationID)).Scan(&ownershipKind, &ownerMemberID)
	if err != nil {
		if coredb.IsNotFound(err) {
			return "", notFound("service installation not found")
		}
		return "", fmt.Errorf("lock installation for manage check: %w", err)
	}
	trimmedOwnership := trim(ownershipKind)
	if trimmedOwnership != OwnershipKindPersonal {
		return trimmedOwnership, nil
	}
	if trim(ownerMemberID) == trim(memberID) {
		return trimmedOwnership, nil
	}
	accessRole, granted, err := store.ResolveHumanInstallationAccessForUpdate(ctx, workspaceID, memberID, installationID)
	if err != nil {
		return "", err
	}
	if !granted || accessRole != AccessRoleManage {
		return "", notFound("service installation not found")
	}
	return trimmedOwnership, nil
}

// ResolveHumanInstallationAccessForUpdate is the row-locking variant of
// ResolveHumanInstallationAccess for write-transaction revalidation.
func (store *TxStore) ResolveHumanInstallationAccessForUpdate(ctx context.Context, workspaceID string, memberID string, installationID string) (string, bool, error) {
	queryer, err := queryerFromTxStore(store)
	if err != nil {
		return "", false, err
	}
	return resolveHumanInstallationAccess(ctx, queryer, workspaceID, memberID, installationID, true)
}

// IntersectScopes returns the candidate scopes that remain inside the
// allowed set, deduplicated in first-occurrence order.
func IntersectScopes(candidate []string, allowed []string) []string {
	allowedSet := make(map[string]struct{}, len(allowed))
	for _, scope := range allowed {
		allowedSet[trim(scope)] = struct{}{}
	}
	out := make([]string, 0, len(candidate))
	seen := make(map[string]struct{}, len(candidate))
	for _, scope := range candidate {
		scoped := trim(scope)
		if scoped == "" {
			continue
		}
		if _, ok := allowedSet[scoped]; !ok {
			continue
		}
		if _, dup := seen[scoped]; dup {
			continue
		}
		seen[scoped] = struct{}{}
		out = append(out, scoped)
	}
	return out
}

// NormalizeScopeSet trims, deduplicates, and sorts a scope set.
func NormalizeScopeSet(scopes []string) []string {
	out := trimSlice(scopes)
	sort.Strings(out)
	return out
}

// ScopeSetCovers reports whether granted covers every required scope.
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
}

// PrincipalAuthorizationKeyDigest renders the durable active-row key of one
// (parts...) authorization identity.
func PrincipalAuthorizationKeyDigest(parts ...string) string {
	trimmed := make([]string, 0, len(parts))
	for _, part := range parts {
		trimmed = append(trimmed, trim(part))
	}
	payload, err := model.MarshalJSONObject("service actor principal authorization key", trimmed)
	if err != nil {
		return ""
	}
	sum := sha256.Sum256(payload)
	return hex.EncodeToString(sum[:])
}

// ListAccessiblePersonalInstallations returns the subset of the given
// personal installations the member can use through active human or
// workspace grants.
func (store *Store) ListAccessiblePersonalInstallations(ctx context.Context, workspaceID string, memberID string, installationIDs []string) (map[string]struct{}, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	return listAccessiblePersonalInstallations(ctx, queryer, workspaceID, memberID, installationIDs)
}

func listAccessiblePersonalInstallations(ctx context.Context, queryer coredb.Queryer, workspaceID string, memberID string, installationIDs []string) (map[string]struct{}, error) {
	workspaceID = trim(workspaceID)
	memberID = trim(memberID)
	ids := trimSlice(installationIDs)
	if workspaceID == "" || memberID == "" || len(ids) == 0 {
		return map[string]struct{}{}, nil
	}
	cte, cteArgs, _, err := coredb.BuildStringValuesCTE("personal_installations", "installation_id", ids, 3)
	if err != nil {
		return nil, fmt.Errorf("build accessible personal installation CTE: %w", err)
	}
	args := append([]any{workspaceID, memberID}, cteArgs...)
	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s
SELECT requested.installation_id
FROM personal_installations requested
WHERE EXISTS (
    SELECT 1
    FROM service_principal_authorizations spa
    WHERE spa.workspace_id = $1
      AND spa.service_installation_id = requested.installation_id
      AND spa.status = 'active'
      AND spa.removed_at IS NULL
      AND (
        (spa.principal_kind = 'human' AND spa.principal_ref = $2)
        OR (spa.principal_kind = 'workspace' AND spa.principal_ref = $1)
      )
)
`, cte), args...)
	if err != nil {
		return nil, fmt.Errorf("list accessible personal installations: %w", err)
	}
	defer rows.Close()
	accessible := make(map[string]struct{}, len(ids))
	for rows.Next() {
		var installationID string
		if err := rows.Scan(&installationID); err != nil {
			return nil, fmt.Errorf("scan accessible personal installation: %w", err)
		}
		accessible[trim(installationID)] = struct{}{}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate accessible personal installations: %w", err)
	}
	return accessible, nil
}

// DisableMemberInstallationGrants disables every active human-principal
// installation grant held by one member, so a later re-invite cannot
// resurrect old authority.
func (store *TxStore) DisableMemberInstallationGrants(
	ctx context.Context,
	workspaceID string,
	memberID string,
	now time.Time,
) error {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return err
	}
	if _, err := execer.Exec(ctx, `
UPDATE service_principal_authorizations
SET status = 'disabled',
    disabled_reason = 'member_offboarded',
    disabled_at = $3,
    updated_at = $3,
    active_authorization_key = NULL,
    policy_version = policy_version + 1
WHERE workspace_id = $1
  AND principal_kind = 'human'
  AND principal_ref = $2
  AND status = 'active'
`, trim(workspaceID), trim(memberID), now); err != nil {
		return fmt.Errorf("disable offboarded member installation grants: %w", err)
	}
	return nil
}

// ResolveHumanInstallationsAccess returns every active human or workspace
// grant the member holds on the given installations in one query.
func (store *Store) ResolveHumanInstallationsAccess(ctx context.Context, workspaceID string, memberID string, installationIDs []string) ([]PrincipalAuthorization, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	workspaceID = trim(workspaceID)
	memberID = trim(memberID)
	ids := trimSlice(installationIDs)
	if workspaceID == "" || memberID == "" || len(ids) == 0 {
		return []PrincipalAuthorization{}, nil
	}
	cte, cteArgs, _, err := coredb.BuildStringValuesCTE("requested_installations", "installation_id", ids, 3)
	if err != nil {
		return nil, fmt.Errorf("build installations CTE: %w", err)
	}
	args := append([]any{workspaceID, memberID}, cteArgs...)
	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s
SELECT authorization.id, authorization.workspace_id,
       authorization.service_definition_id,
       authorization.service_installation_id, authorization.service_kind,
       authorization.principal_kind, authorization.principal_ref, authorization.access_role,
       authorization.status, authorization.disabled_reason,
       authorization.scopes_json, authorization.authorized_by_member_id,
       authorization.policy_version, authorization.metadata,
       authorization.created_at, authorization.updated_at,
       authorization.disabled_at, authorization.removed_at,
       authorization.active_authorization_key
FROM requested_installations requested
JOIN service_principal_authorizations authorization
  ON authorization.workspace_id = $1
 AND authorization.service_installation_id = requested.installation_id
 AND authorization.status = 'active'
 AND authorization.removed_at IS NULL
 AND (
   (authorization.principal_kind = 'human' AND authorization.principal_ref = $2)
   OR (authorization.principal_kind = 'workspace' AND authorization.principal_ref = $1)
 )
ORDER BY authorization.service_installation_id ASC, authorization.id ASC
`, cte), args...)
	if err != nil {
		return nil, fmt.Errorf("resolve human installations access: %w", err)
	}
	defer rows.Close()
	authorizations := make([]PrincipalAuthorization, 0)
	for rows.Next() {
		authorization, err := scanPrincipalAuthorization(rows)
		if err != nil {
			return nil, fmt.Errorf("scan human installations access: %w", err)
		}
		authorizations = append(authorizations, authorization)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate human installations access: %w", err)
	}
	return authorizations, nil
}

// RequireHumanInstallationUseInTx is the single transaction-only human-use
// authority: it locks the member row (must be an active human), locks the
// installation row, and resolves owner-or-grant access. Write paths (create,
// lifecycle, delivery reservation) must call this inside their transaction;
// plain snapshot reads are not authoritative under TiDB RR.
func (store *TxStore) RequireHumanInstallationUseInTx(ctx context.Context, workspaceID string, memberID string, installationID string) error {
	workspaceID = trim(workspaceID)
	memberID = trim(memberID)
	installationID = trim(installationID)
	if workspaceID == "" || memberID == "" || installationID == "" {
		return badRequest("workspaceId, memberId, and installationId are required")
	}
	var memberStatus, memberType string
	if err := store.db.QueryRow(ctx, `
SELECT type, status
FROM members
WHERE workspace_id = $1
  AND id = $2
LIMIT 1
FOR UPDATE
`, workspaceID, memberID).Scan(&memberType, &memberStatus); err != nil {
		if coredb.IsNotFound(err) {
			return notFound("app_installation_not_authorized")
		}
		return fmt.Errorf("lock human member for installation use: %w", err)
	}
	if memberType != "human" || memberStatus != StatusActive {
		return notFound("app_installation_not_authorized")
	}
	var ownershipKind, ownerMemberID string
	if err := store.db.QueryRow(ctx, `
SELECT ownership_kind, COALESCE(owner_member_id, '')
FROM service_installations
WHERE workspace_id = $1
  AND id = $2
  AND service_kind = 'app'
  AND status = 'active'
LIMIT 1
FOR UPDATE
`, workspaceID, installationID).Scan(&ownershipKind, &ownerMemberID); err != nil {
		if coredb.IsNotFound(err) {
			return notFound("app_installation_not_authorized")
		}
		return fmt.Errorf("lock installation for human use: %w", err)
	}
	if trim(ownershipKind) != OwnershipKindPersonal {
		return nil
	}
	if trim(ownerMemberID) == memberID {
		return nil
	}
	accessRole, granted, err := store.ResolveHumanInstallationAccessForUpdate(ctx, workspaceID, memberID, installationID)
	if err != nil {
		return err
	}
	if !granted || accessRole == "" {
		return notFound("app_installation_not_authorized")
	}
	return nil
}