package message

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

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

const (
	DefaultMessagePinListLimit = 50
	MaxMessagePinListLimit     = 100
)

type MessagePinCursor struct {
	PinnedAt time.Time
	ID       string
}

func (cursor MessagePinCursor) IsZero() bool {
	return cursor.PinnedAt.IsZero() && strings.TrimSpace(cursor.ID) == ""
}

type MessagePin struct {
	ID                 string
	WorkspaceID        string
	ScopeID            string
	MessageID          string
	PinnedByMemberID   string
	PinnedBy           AuthorProfile
	PinnedAt           time.Time
	Message            AppMessage
	ViewerState        MessagePinViewerState
	ViewerCapabilities MessagePinViewerCapabilities
}

type MessagePinViewerState struct{}

type MessagePinViewerCapabilities struct {
	CanJump             bool
	CanUnpinForEveryone bool
}

type MessagePinList struct {
	ScopeID                string
	ViewerMemberID         string
	Pins                   []MessagePin
	LatestPin              *MessagePin
	NextCursor             string
	PinRevision            string
	ViewerPinStateRevision string
}

type MessagePinState struct {
	IsPinned         bool
	PinID            string
	PinnedAt         time.Time
	PinnedByMemberID string
}

type MessagePinMutationResult struct {
	Changed   bool
	MessageID string
	PinID     string
	Pin       *MessagePin
	List      MessagePinList
}

type ScopeMessagePinRecord struct {
	ID               string
	WorkspaceID      string
	ScopeID          string
	MessageID        string
	PinnedByMemberID string
	PinnedAt         time.Time
	CreatedAt        time.Time
	UpdatedAt        time.Time
}

type ScopeMessagePinListRow struct {
	Pin      ScopeMessagePinRecord
	Message  ScopeMessageRecord
	ScanRank int
}

type ScopeMessagePinStateRow struct {
	MessageID        string
	PinID            string
	PinnedByMemberID string
	PinnedAt         time.Time
}

type ScopeMessagePinMutationResult struct {
	Changed bool
	Pin     ScopeMessagePinRecord
}

func (store *Store) ListMessagePinsForUser(ctx context.Context, userID, workspaceID, scopeID string, cursor MessagePinCursor, limit int) (MessagePinList, error) {
	var result MessagePinList
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var err error
		result, err = store.InTx(tx).ListMessagePinsForUser(ctx, userID, workspaceID, scopeID, cursor, limit)
		return err
	})
	if err != nil {
		return MessagePinList{}, err
	}
	return result, nil
}

func (store *Store) PinMessageForUser(ctx context.Context, userID, workspaceID, scopeID, messageID string) (MessagePinMutationResult, error) {
	var result MessagePinMutationResult
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var err error
		result, err = store.InTx(tx).PinMessageForUser(ctx, userID, workspaceID, scopeID, messageID)
		return err
	})
	if err != nil {
		return MessagePinMutationResult{}, err
	}
	return result, nil
}

func (store *Store) UnpinMessageForEveryoneForUser(ctx context.Context, userID, workspaceID, scopeID, messageID string) (MessagePinMutationResult, error) {
	var result MessagePinMutationResult
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var err error
		result, err = store.InTx(tx).UnpinMessageForEveryoneForUser(ctx, userID, workspaceID, scopeID, messageID)
		return err
	})
	if err != nil {
		return MessagePinMutationResult{}, err
	}
	return result, nil
}

func (txs TxStore) ListMessagePinsForUser(ctx context.Context, userID, workspaceID, scopeID string, cursor MessagePinCursor, limit int) (MessagePinList, error) {
	target, err := txs.resolveMessagePinTargetForUser(ctx, userID, workspaceID, scopeID, false)
	if err != nil {
		return MessagePinList{}, err
	}
	return txs.listMessagePinsForTarget(ctx, target, cursor, limit)
}

func (txs TxStore) PinMessageForUser(ctx context.Context, userID, workspaceID, scopeID, messageID string) (MessagePinMutationResult, error) {
	target, err := txs.resolveMessagePinTargetForUser(ctx, userID, workspaceID, scopeID, true)
	if err != nil {
		return MessagePinMutationResult{}, err
	}
	message, err := txs.loadMessageForPinMutation(ctx, target.WorkspaceID, target.ScopeID, messageID, true)
	if err != nil {
		return MessagePinMutationResult{}, err
	}
	if err := txs.requirePinMessageVisible(ctx, target, message.ID); err != nil {
		return MessagePinMutationResult{}, err
	}
	now := time.Now().UTC()
	pinID, err := identifiers.NewEntityID("pin")
	if err != nil {
		return MessagePinMutationResult{}, fmt.Errorf("generate message pin id: %w", err)
	}
	mutation, err := NewScopeRepositories(txs.tx).Pins.InsertActivePin(ctx, ScopeMessagePinRecord{
		ID:               pinID,
		WorkspaceID:      target.WorkspaceID,
		ScopeID:          target.ScopeID,
		MessageID:        message.ID,
		PinnedByMemberID: target.MemberID,
		PinnedAt:         now,
		CreatedAt:        now,
		UpdatedAt:        now,
	})
	if err != nil {
		return MessagePinMutationResult{}, err
	}
	list, err := txs.listMessagePinsForTarget(ctx, target, MessagePinCursor{}, DefaultMessagePinListLimit)
	if err != nil {
		return MessagePinMutationResult{}, err
	}
	return MessagePinMutationResult{
		Changed:   mutation.Changed,
		MessageID: message.ID,
		PinID:     mutation.Pin.ID,
		Pin:       findMessagePinInList(list.Pins, mutation.Pin.ID, message.ID),
		List:      list,
	}, nil
}

func (txs TxStore) UnpinMessageForEveryoneForUser(ctx context.Context, userID, workspaceID, scopeID, messageID string) (MessagePinMutationResult, error) {
	target, err := txs.resolveMessagePinTargetForUser(ctx, userID, workspaceID, scopeID, true)
	if err != nil {
		return MessagePinMutationResult{}, err
	}
	message, err := txs.loadMessageForPinMutation(ctx, target.WorkspaceID, target.ScopeID, messageID, true)
	if err != nil {
		return MessagePinMutationResult{}, err
	}
	if err := txs.requirePinMessageVisible(ctx, target, message.ID); err != nil {
		return MessagePinMutationResult{}, err
	}
	pins := NewScopeRepositories(txs.tx).Pins
	mutation, err := pins.DeleteActivePin(ctx, target.WorkspaceID, target.ScopeID, message.ID)
	if err != nil {
		return MessagePinMutationResult{}, err
	}
	list, err := txs.listMessagePinsForTarget(ctx, target, MessagePinCursor{}, DefaultMessagePinListLimit)
	if err != nil {
		return MessagePinMutationResult{}, err
	}
	return MessagePinMutationResult{
		Changed:   mutation.Changed,
		MessageID: message.ID,
		PinID:     mutation.Pin.ID,
		List:      list,
	}, nil
}

func (txs TxStore) resolveMessagePinTargetForUser(ctx context.Context, userID, workspaceID, scopeID string, requireWrite bool) (messagePinTarget, error) {
	if txs.tx == nil {
		return messagePinTarget{}, fmt.Errorf("message pin target resolver requires transaction")
	}
	userID = strings.TrimSpace(userID)
	workspaceID = strings.TrimSpace(workspaceID)
	scopeID = strings.TrimSpace(scopeID)
	if userID == "" || workspaceID == "" || scopeID == "" {
		return messagePinTarget{}, invalidArgument("message pin target requires user, workspace, and scope")
	}
	scope, err := NewScopeRepositories(txs.tx).Scopes.LoadScope(ctx, scopeID)
	if err != nil {
		return messagePinTarget{}, err
	}
	if strings.TrimSpace(scope.WorkspaceID) != workspaceID || strings.TrimSpace(scope.Status) != "active" || scope.ArchivedAt != nil {
		return messagePinTarget{}, ErrNotFound
	}
	scopeKind, err := scopeTargetKindForPin(scope.Type)
	if err != nil {
		return messagePinTarget{}, err
	}
	action := access.ActionRead
	operation := access.ScopeTargetOperationMessageRead
	if requireWrite {
		action = access.ActionPost
		operation = access.ScopeTargetOperationMessagePinWrite
	}

	var member access.HumanWorkspaceMember
	var basis []access.ScopePermissionBasis
	var parentScope access.ScopeRef
	switch scopeKind {
	case access.ScopeTargetKindChannel:
		channelAccess, accessErr := access.LoadAuthorizedHumanChannelAccess(ctx, txs.tx, userID, workspaceID, scopeID, access.HumanAction{Resource: access.ResourceChannel, Action: action})
		if accessErr != nil {
			return messagePinTarget{}, mapAccessError(accessErr)
		}
		member = channelAccess.WorkspaceMember
		basis = []access.ScopePermissionBasis{{
			Kind:           access.ScopePermissionBasisDirectMembership,
			Scope:          access.ScopeRef{ScopeID: scopeID, ScopeType: access.ScopeTargetKindChannel},
			DecisionSource: access.DecisionSourceChannelMembership,
			MemberID:       member.Member.ID,
			Reason:         "message_pin_target",
		}}
	case access.ScopeTargetKindDM:
		dmAccess, accessErr := access.LoadAuthorizedHumanDMAccess(ctx, txs.tx, userID, workspaceID, scopeID, access.HumanAction{Resource: access.ResourceDM, Action: action})
		if accessErr != nil {
			return messagePinTarget{}, mapAccessError(accessErr)
		}
		member = dmAccess.WorkspaceMember
		basis = []access.ScopePermissionBasis{{
			Kind:           access.ScopePermissionBasisDirectMembership,
			Scope:          access.ScopeRef{ScopeID: scopeID, ScopeType: access.ScopeTargetKindDM},
			DecisionSource: access.DecisionSourceDMParticipant,
			MemberID:       member.Member.ID,
			Reason:         "message_pin_target",
		}}
	case access.ScopeTargetKindThread:
		threadAccess, accessErr := access.LoadAuthorizedHumanThreadAccess(ctx, txs.tx, userID, workspaceID, scopeID, access.HumanAction{Resource: access.ResourceThread, Action: action})
		if accessErr != nil {
			return messagePinTarget{}, mapAccessError(accessErr)
		}
		member = threadAccess.WorkspaceMember
		parentKind := access.ScopeTargetKindChannel
		basisKind := access.ScopePermissionBasisParentMembership
		decisionSource := access.DecisionSourceThreadInherit
		parentScopeID := strings.TrimSpace(threadAccess.ParentChannelID)
		if threadAccess.ParentThreadType == domain.ThreadTypeDM {
			parentKind = access.ScopeTargetKindDM
			basisKind = access.ScopePermissionBasisParentDMParticipant
			decisionSource = access.DecisionSourceDMParticipant
			parentScopeID = strings.TrimSpace(scope.ParentScopeID)
		}
		parentScope = access.ScopeRef{ScopeID: parentScopeID, ScopeType: parentKind}
		basis = []access.ScopePermissionBasis{{
			Kind:           basisKind,
			Scope:          parentScope,
			DecisionSource: decisionSource,
			MemberID:       member.Member.ID,
			Reason:         "message_pin_target",
		}}
	case access.ScopeTargetKindServiceAccount:
		member, basis, err = txs.resolveServiceAccountPinAuthority(ctx, userID, workspaceID, scopeID, requireWrite)
		if err != nil {
			return messagePinTarget{}, err
		}
	}
	return buildMessagePinTarget(scope, scopeKind, operation, member, basis, parentScope, userID, workspaceID, scopeID, requireWrite)
}

func buildMessagePinTarget(
	scope ScopeRecord,
	scopeKind access.ScopeTargetKind,
	operation access.ScopeTargetOperation,
	member access.HumanWorkspaceMember,
	basis []access.ScopePermissionBasis,
	parentScope access.ScopeRef,
	userID, workspaceID, scopeID string,
	requireWrite bool,
) (messagePinTarget, error) {
	contract := access.ResolvedScopeTarget{
		WorkspaceID: workspaceID,
		Operation:   operation,
		Requested: access.ScopeTargetRef{
			Kind: scopeKind,
			Ref:  scopeID,
		},
		Subjects: []access.ScopeAuthoritySubject{{
			Kind:          access.ScopeAuthoritySubjectDurableAuthor,
			PrincipalType: access.PrincipalTypeUser,
			PrincipalID:   userID,
			MemberID:      member.Member.ID,
		}},
		StorageScope:    access.ScopeRef{ScopeID: scopeID, ScopeType: scopeKind},
		ReadScope:       access.ScopeRef{ScopeID: scopeID, ScopeType: scopeKind},
		ParentScope:     parentScope,
		PermissionBasis: basis,
	}
	if scopeKind == access.ScopeTargetKindThread {
		contract.RootMessageID = strings.TrimSpace(scope.RootMessageID)
		contract.RootMessageShort = strings.TrimSpace(scope.RootMessageShortID)
	}
	contract = contract.Normalized()
	if err := contract.Validate(); err != nil {
		return messagePinTarget{}, err
	}
	return messagePinTarget{
		WorkspaceID: workspaceID,
		ScopeID:     scopeID,
		ScopeType:   string(scopeKind),
		MemberID:    member.Member.ID,
		UserID:      userID,
		CanWrite:    requireWrite,
		Contract:    contract,
	}, nil
}

func (txs TxStore) resolveServiceAccountPinAuthority(ctx context.Context, userID, workspaceID, scopeID string, requireWrite bool) (access.HumanWorkspaceMember, []access.ScopePermissionBasis, error) {
	workspaceMember, err := access.LoadHumanWorkspaceMember(ctx, txs.tx, userID, workspaceID)
	if err != nil {
		return access.HumanWorkspaceMember{}, nil, mapAccessError(err)
	}
	membership, err := NewScopeRepositories(txs.tx).Memberships.LoadActive(ctx, scopeID, workspaceMember.Member.ID)
	if err != nil || strings.TrimSpace(membership.Visibility) != "public" {
		if err != nil && !errors.Is(err, ErrNotFound) {
			return access.HumanWorkspaceMember{}, nil, err
		}
		return access.HumanWorkspaceMember{}, nil, ErrForbidden
	}
	basis := []access.ScopePermissionBasis{{
		Kind:     access.ScopePermissionBasisDirectMembership,
		Scope:    access.ScopeRef{ScopeID: scopeID, ScopeType: access.ScopeTargetKindServiceAccount},
		MemberID: workspaceMember.Member.ID,
		Reason:   "service_account_message_pin_membership",
	}}
	if !requireWrite {
		return workspaceMember, basis, nil
	}
	var managementKind, managerAssignmentID, workspaceRole string
	var systemAdmin bool
	if err := txs.tx.QueryRow(ctx, `
SELECT account.management_kind, COALESCE(manager.id, ''),
       COALESCE(viewer_member.workspace_role, ''), viewer_user.is_admin
FROM service_accounts account
JOIN members viewer_member
  ON viewer_member.workspace_id = account.workspace_id
 AND viewer_member.id = $3
 AND viewer_member.user_id = $4
 AND viewer_member.type = 'human'
 AND viewer_member.status = 'active'
JOIN users viewer_user ON viewer_user.id = viewer_member.user_id
LEFT JOIN service_account_managers manager
  ON manager.workspace_id = account.workspace_id
 AND manager.service_account_id = account.id
 AND manager.member_id = viewer_member.id
 AND manager.status = 'active'
 AND manager.ended_at IS NULL
WHERE account.workspace_id = $1
  AND account.message_scope_id = $2
  AND (
    (account.management_kind = 'workspace'
      AND (manager.id IS NOT NULL OR viewer_member.workspace_role IN ('owner', 'admin')))
    OR (account.management_kind = 'platform' AND viewer_user.is_admin = TRUE)
  )
LIMIT 1
`, workspaceID, scopeID, workspaceMember.Member.ID, userID).Scan(
		&managementKind, &managerAssignmentID, &workspaceRole, &systemAdmin,
	); err != nil {
		if coredb.IsNotFound(err) {
			return access.HumanWorkspaceMember{}, nil, ErrForbidden
		}
		return access.HumanWorkspaceMember{}, nil, fmt.Errorf("check service account pin authority: %w", err)
	}
	if managementKind == "platform" && systemAdmin {
		return workspaceMember, []access.ScopePermissionBasis{{
			Kind:     access.ScopePermissionBasisSystem,
			Scope:    access.ScopeRef{ScopeID: scopeID, ScopeType: access.ScopeTargetKindServiceAccount},
			MemberID: workspaceMember.Member.ID,
			Reason:   "platform_service_account_system_admin",
		}}, nil
	}
	if access.HasWorkspaceAdminRole(domain.WorkspaceRole(workspaceRole)) {
		return workspaceMember, []access.ScopePermissionBasis{{
			Kind:           access.ScopePermissionBasisDirectMembership,
			Scope:          access.ScopeRef{ScopeID: scopeID, ScopeType: access.ScopeTargetKindServiceAccount},
			DecisionSource: access.DecisionSourceWorkspaceRole,
			MemberID:       workspaceMember.Member.ID,
			Reason:         "workspace_service_account_admin",
		}}, nil
	}
	return workspaceMember, []access.ScopePermissionBasis{{
		Kind:     access.ScopePermissionBasisServiceAccountManager,
		Scope:    access.ScopeRef{ScopeID: scopeID, ScopeType: access.ScopeTargetKindServiceAccount},
		MemberID: workspaceMember.Member.ID,
		GrantID:  strings.TrimSpace(managerAssignmentID),
		Reason:   "service_account_manager",
	}}, nil
}

func (txs TxStore) listMessagePinsForTarget(ctx context.Context, target messagePinTarget, cursor MessagePinCursor, limit int) (MessagePinList, error) {
	limit = normalizeMessagePinLimit(limit)
	canWrite, err := txs.canWriteMessagePinTarget(ctx, target.UserID, target.WorkspaceID, target.ScopeID)
	if err != nil {
		return MessagePinList{}, err
	}
	scanLimit := limit + 1
	rows, err := NewScopeRepositories(txs.tx).Pins.ListActivePins(ctx, target.WorkspaceID, target.ScopeID, target.MemberID, cursor, scanLimit)
	if err != nil {
		return MessagePinList{}, err
	}
	hasMore := len(rows) > limit
	if hasMore {
		rows = rows[:limit]
	}
	if len(rows) == 0 {
		return MessagePinList{ScopeID: target.ScopeID, ViewerMemberID: target.MemberID}, nil
	}
	visible, err := txs.visibleMessagePinRowsForTarget(ctx, target, rows)
	if err != nil {
		return MessagePinList{}, err
	}
	if len(visible.rows) == 0 {
		return messagePinListWithCursor(MessagePinList{ScopeID: target.ScopeID, ViewerMemberID: target.MemberID}, hasMore, rows), nil
	}
	pins, maxPinUpdatedAt, maxHiddenAt, err := txs.messagePinsFromVisibleRows(ctx, target, visible, canWrite)
	if err != nil {
		return MessagePinList{}, err
	}
	return messagePinListWithCursor(messagePinListFromPins(target, pins, maxPinUpdatedAt, maxHiddenAt), hasMore, rows), nil
}

type visibleMessagePinRows struct {
	rows []ScopeMessagePinListRow
	refs []VisibleMessageRef
}

func (txs TxStore) visibleMessagePinRowsForTarget(ctx context.Context, target messagePinTarget, rows []ScopeMessagePinListRow) (visibleMessagePinRows, error) {
	items := make([]SidecarReadBatchItem, 0, len(rows))
	for _, row := range rows {
		items = append(items, SidecarReadBatchItem{
			RequestID:      row.Pin.ID,
			MessageID:      row.Pin.MessageID,
			ViewerMemberID: target.MemberID,
		})
	}
	decisions, err := NewScopeAPIServiceForQueryer(txs.tx).AuthorizeSidecarReadBatch(ctx, SidecarReadBatchRequest{
		WorkspaceID: target.WorkspaceID,
		Kind:        SidecarKindPin,
		Items:       items,
	})
	if err != nil {
		return visibleMessagePinRows{}, err
	}
	visibleRows := make([]ScopeMessagePinListRow, 0, len(rows))
	visibleRefs := make([]VisibleMessageRef, 0, len(rows))
	for _, row := range rows {
		decision, ok := decisions[strings.TrimSpace(row.Pin.ID)]
		if !ok || !decision.Allowed {
			continue
		}
		if strings.TrimSpace(decision.ScopeID) != strings.TrimSpace(row.Message.ScopeID) {
			continue
		}
		visibleRows = append(visibleRows, row)
		visibleRefs = append(visibleRefs, VisibleMessageRef{
			Message: row.Message,
			Reason:  decision.Reason,
		})
	}
	return visibleMessagePinRows{rows: visibleRows, refs: visibleRefs}, nil
}

func (txs TxStore) messagePinsFromVisibleRows(ctx context.Context, target messagePinTarget, visible visibleMessagePinRows, canWrite bool) ([]MessagePin, time.Time, time.Time, error) {
	service := NewScopeAPIServiceForQueryer(txs.tx)
	messages, err := service.appMessagesFromVisibleRefs(ctx, target.WorkspaceID, visible.refs)
	if err != nil {
		return nil, time.Time{}, time.Time{}, err
	}
	if err := AttachResolvedMentionsForAppMessages(ctx, service.db, target.WorkspaceID, target.MemberID, messages); err != nil {
		return nil, time.Time{}, time.Time{}, err
	}
	if err := AttachMessagePinStateForAppMessages(ctx, service.db, target.WorkspaceID, target.MemberID, messages); err != nil {
		return nil, time.Time{}, time.Time{}, err
	}
	messagesByID := make(map[string]AppMessage, len(messages))
	for _, message := range messages {
		messagesByID[strings.TrimSpace(message.ID)] = message
	}
	pinnerIDs := make([]string, 0, len(visible.rows))
	for _, row := range visible.rows {
		pinnerIDs = append(pinnerIDs, row.Pin.PinnedByMemberID)
	}
	pinners, err := loadScopeMemberPresentations(ctx, txs.tx, target.WorkspaceID, pinnerIDs)
	if err != nil {
		return nil, time.Time{}, time.Time{}, err
	}

	pins := make([]MessagePin, 0, len(visible.rows))
	var maxPinUpdatedAt time.Time
	for _, row := range visible.rows {
		message, ok := messagesByID[strings.TrimSpace(row.Pin.MessageID)]
		if !ok {
			continue
		}
		if row.Pin.UpdatedAt.After(maxPinUpdatedAt) {
			maxPinUpdatedAt = row.Pin.UpdatedAt
		}
		pinner := pinners[strings.TrimSpace(row.Pin.PinnedByMemberID)].AuthorProfile().Normalize()
		pins = append(pins, MessagePin{
			ID:               row.Pin.ID,
			WorkspaceID:      row.Pin.WorkspaceID,
			ScopeID:          row.Pin.ScopeID,
			MessageID:        row.Pin.MessageID,
			PinnedByMemberID: row.Pin.PinnedByMemberID,
			PinnedBy:         pinner,
			PinnedAt:         row.Pin.PinnedAt,
			Message:          message,
			ViewerState:      MessagePinViewerState{},
			ViewerCapabilities: MessagePinViewerCapabilities{
				CanJump:             true,
				CanUnpinForEveryone: canWrite,
			},
		})
	}
	return pins, maxPinUpdatedAt, time.Time{}, nil
}

func messagePinListFromPins(target messagePinTarget, pins []MessagePin, maxPinUpdatedAt time.Time, _ time.Time) MessagePinList {
	list := MessagePinList{
		ScopeID:        target.ScopeID,
		ViewerMemberID: target.MemberID,
		Pins:           pins,
		PinRevision:    messagePinRevision("pins", maxPinUpdatedAt, len(pins)),
	}
	if len(pins) > 0 {
		list.LatestPin = cloneMessagePin(pins[0])
	}
	return list
}

func messagePinListWithCursor(list MessagePinList, hasMore bool, rows []ScopeMessagePinListRow) MessagePinList {
	if hasMore && len(rows) > 0 {
		list.NextCursor = encodeMessagePinCursor(rows[len(rows)-1].Pin)
	}
	return list
}

func (txs TxStore) canWriteMessagePinTarget(ctx context.Context, userID, workspaceID, scopeID string) (bool, error) {
	_, err := txs.resolveMessagePinTargetForUser(ctx, userID, workspaceID, scopeID, true)
	if err == nil {
		return true, nil
	}
	if errors.Is(err, ErrForbidden) || errors.Is(err, ErrNotFound) || errors.Is(err, access.ErrForbidden) || errors.Is(err, access.ErrNotFound) {
		return false, nil
	}
	return false, err
}

func (txs TxStore) loadMessageForPinMutation(ctx context.Context, workspaceID, scopeID, messageID string, forUpdate bool) (ScopeMessageRecord, error) {
	if txs.tx == nil {
		return ScopeMessageRecord{}, fmt.Errorf("message pin mutation requires transaction")
	}
	query := `
SELECT id, workspace_id, scope_id, seq, workspace_global_seq, author_member_id,
       author_type,
       COALESCE(NULLIF(notify_principal_member_id, ''), author_member_id),
       COALESCE(NULLIF(idempotency_subject_member_id, ''), author_member_id),
       COALESCE(client_message_id, ''), COALESCE(idempotency_payload_digest, ''),
       message_type, content, metadata, created_at, updated_at,
       edited_at, COALESCE(edited_by_member_id, ''), edit_revision
FROM scope_messages
WHERE workspace_id = $1
  AND scope_id = $2
  AND id = $3
`
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	var record ScopeMessageRecord
	var metadata []byte
	var editedAt sql.NullTime
	err := txs.tx.QueryRow(ctx, query, strings.TrimSpace(workspaceID), strings.TrimSpace(scopeID), strings.TrimSpace(messageID)).Scan(
		&record.ID,
		&record.WorkspaceID,
		&record.ScopeID,
		&record.Seq,
		&record.WorkspaceGlobalSeq,
		&record.AuthorMemberID,
		&record.AuthorType,
		&record.NotifyPrincipalMemberID,
		&record.IdempotencySubjectMemberID,
		&record.ClientMessageID,
		&record.IdempotencyPayloadDigest,
		&record.MessageType,
		&record.Content,
		&metadata,
		&record.CreatedAt,
		&record.UpdatedAt,
		&editedAt,
		&record.EditedByMemberID,
		&record.EditRevision,
	)
	if errorsIsNoRows(err) {
		return ScopeMessageRecord{}, ErrNotFound
	}
	if err != nil {
		return ScopeMessageRecord{}, fmt.Errorf("load message for pin mutation: %w", err)
	}
	record.EditedAt = nullableTimePtr(editedAt)
	record.Metadata = metadata
	return record, nil
}

func (txs TxStore) requirePinMessageVisible(ctx context.Context, target messagePinTarget, messageID string) error {
	decision, err := NewScopeAPIServiceForQueryer(txs.tx).AuthorizeSidecarRead(ctx, SidecarReadRequest{
		WorkspaceID:    target.WorkspaceID,
		MessageID:      strings.TrimSpace(messageID),
		ViewerMemberID: target.MemberID,
		ViewerType:     ScopeActorHuman,
		Kind:           SidecarKindPin,
	})
	if err != nil {
		return err
	}
	if !decision.Allowed || strings.TrimSpace(decision.ScopeID) != target.ScopeID {
		return ErrNotFound
	}
	return nil
}

func pinPreconditionMatches(pin ScopeMessagePinRecord, pinID string) bool {
	pinID = strings.TrimSpace(pinID)
	return pinID == "" || strings.TrimSpace(pin.ID) == pinID
}

func normalizeScopeMessagePinRecord(record ScopeMessagePinRecord) ScopeMessagePinRecord {
	now := time.Now().UTC()
	record.ID = strings.TrimSpace(record.ID)
	record.WorkspaceID = strings.TrimSpace(record.WorkspaceID)
	record.ScopeID = strings.TrimSpace(record.ScopeID)
	record.MessageID = strings.TrimSpace(record.MessageID)
	record.PinnedByMemberID = strings.TrimSpace(record.PinnedByMemberID)
	if record.PinnedAt.IsZero() {
		record.PinnedAt = now
	}
	record.PinnedAt = record.PinnedAt.UTC()
	if record.CreatedAt.IsZero() {
		record.CreatedAt = record.PinnedAt
	}
	record.CreatedAt = record.CreatedAt.UTC()
	if record.UpdatedAt.IsZero() {
		record.UpdatedAt = record.CreatedAt
	}
	record.UpdatedAt = record.UpdatedAt.UTC()
	return record
}

func normalizeMessagePinCursor(cursor MessagePinCursor) (MessagePinCursor, error) {
	cursor.ID = strings.TrimSpace(cursor.ID)
	if cursor.IsZero() {
		return MessagePinCursor{}, nil
	}
	if cursor.PinnedAt.IsZero() || cursor.ID == "" {
		return MessagePinCursor{}, badRequest("invalid message pin cursor")
	}
	cursor.PinnedAt = cursor.PinnedAt.UTC()
	return cursor, nil
}

func ParseMessagePinCursorToken(token string) (MessagePinCursor, error) {
	token = strings.TrimSpace(token)
	if token == "" {
		return MessagePinCursor{}, nil
	}
	pinnedAtRaw, id, ok := strings.Cut(token, "|")
	if !ok {
		return MessagePinCursor{}, badRequest("invalid message pin cursor")
	}
	pinnedAt, err := time.Parse(time.RFC3339Nano, pinnedAtRaw)
	if err != nil {
		return MessagePinCursor{}, badRequest("invalid message pin cursor")
	}
	return normalizeMessagePinCursor(MessagePinCursor{PinnedAt: pinnedAt, ID: id})
}

func normalizeMessagePinLimit(limit int) int {
	if limit <= 0 {
		return DefaultMessagePinListLimit
	}
	if limit > MaxMessagePinListLimit {
		return MaxMessagePinListLimit
	}
	return limit
}

func encodeMessagePinCursor(pin ScopeMessagePinRecord) string {
	if strings.TrimSpace(pin.ID) == "" || pin.PinnedAt.IsZero() {
		return ""
	}
	return pin.PinnedAt.UTC().Format(time.RFC3339Nano) + "|" + strings.TrimSpace(pin.ID)
}

func messagePinRevision(prefix string, at time.Time, count int) string {
	prefix = strings.TrimSpace(prefix)
	if prefix == "" {
		prefix = "message_pin"
	}
	if at.IsZero() && count == 0 {
		return prefix + ":0"
	}
	return fmt.Sprintf("%s:%d:%d", prefix, count, at.UTC().UnixNano())
}

func findMessagePinInList(pins []MessagePin, pinID string, messageID string) *MessagePin {
	pinID = strings.TrimSpace(pinID)
	messageID = strings.TrimSpace(messageID)
	for i := range pins {
		if (pinID != "" && strings.TrimSpace(pins[i].ID) == pinID) || (messageID != "" && strings.TrimSpace(pins[i].MessageID) == messageID) {
			return cloneMessagePin(pins[i])
		}
	}
	return nil
}

func cloneMessagePin(pin MessagePin) *MessagePin {
	cloned := pin
	return &cloned
}

func scopeTargetKindForPin(scopeType string) (access.ScopeTargetKind, error) {
	switch strings.TrimSpace(scopeType) {
	case "channel":
		return access.ScopeTargetKindChannel, nil
	case "dm":
		return access.ScopeTargetKindDM, nil
	case "thread":
		return access.ScopeTargetKindThread, nil
	case "service_account":
		return access.ScopeTargetKindServiceAccount, nil
	default:
		return "", ErrNotFound
	}
}

func scanScopeMessagePinListRows(rows coredb.Rows) ([]ScopeMessagePinListRow, error) {
	result := make([]ScopeMessagePinListRow, 0)
	for rows.Next() {
		var row ScopeMessagePinListRow
		var metadata []byte
		var editedAt sql.NullTime
		if err := rows.Scan(
			&row.Pin.ID,
			&row.Pin.WorkspaceID,
			&row.Pin.ScopeID,
			&row.Pin.MessageID,
			&row.Pin.PinnedByMemberID,
			&row.Pin.PinnedAt,
			&row.Pin.CreatedAt,
			&row.Pin.UpdatedAt,
			&row.Message.ID,
			&row.Message.WorkspaceID,
			&row.Message.ScopeID,
			&row.Message.Seq,
			&row.Message.WorkspaceGlobalSeq,
			&row.Message.AuthorMemberID,
			&row.Message.AuthorType,
			&row.Message.NotifyPrincipalMemberID,
			&row.Message.IdempotencySubjectMemberID,
			&row.Message.ClientMessageID,
			&row.Message.IdempotencyPayloadDigest,
			&row.Message.MessageType,
			&row.Message.Content,
			&metadata,
			&row.Message.CreatedAt,
			&row.Message.UpdatedAt,
			&editedAt,
			&row.Message.EditedByMemberID,
			&row.Message.EditRevision,
		); err != nil {
			return nil, fmt.Errorf("scan scope message pin list row: %w", err)
		}
		row.Message.EditedAt = nullableTimePtr(editedAt)
		row.Message.Metadata = metadata
		row.ScanRank = len(result)
		result = append(result, row)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate scope message pin list rows: %w", err)
	}
	return result, nil
}