package memory

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

	"github.com/tidbcloud/AgentTeam/internal/store/model"
	"github.com/tidbcloud/AgentTeam/internal/store/resource/activity"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
)

// Store persists managed Memory assets, grants, bindings, pins, and access
// outbox records. Authorization, lifecycle orchestration, repository content,
// and API projection remain in their service and API owners.
type Store struct{ db *gorm.DB }

// New constructs a managed Memory persistence store.
func New(db *gorm.DB) *Store { return &Store{db: db} }

// WithDB returns the same persistence owner bound to db, commonly a transaction.
func (s *Store) WithDB(db *gorm.DB) *Store { return &Store{db: db} }

// DB returns the underlying database handle for bounded owner-level composition.
func (s *Store) DB() *gorm.DB {
	if s == nil {
		return nil
	}
	return s.db
}

func (s *Store) ensureDB() error {
	if s == nil || s.db == nil {
		return fmt.Errorf("memory store is not configured")
	}
	return nil
}

func firstNonEmptyStoreString(values ...string) string {
	for _, value := range values {
		if strings.TrimSpace(value) != "" {
			return strings.TrimSpace(value)
		}
	}
	return ""
}

func firstNonZeroStoreTime(values ...time.Time) time.Time {
	for _, value := range values {
		if !value.IsZero() {
			return value
		}
	}
	return time.Time{}
}

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

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

// Transaction runs fn against a transaction-bound managed Memory store.
func (s *Store) Transaction(ctx context.Context, fn func(*Store) error) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	return s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error { return fn(s.WithDB(tx)) })
}

// MemoryCollectionPage is the bounded persistence result for a managed Memory
// collection query. It is not an API response DTO.
type MemoryCollectionPage struct {
	Items   []Memory
	Total   int64
	Page    int
	PerPage int
	HasMore bool
}

// MemoryCollectionOptions specifies the filters and pagination applied by the
// managed Memory collection query.
type MemoryCollectionOptions struct {
	WorkspaceID     string
	PrincipalID     string
	View            string
	Query           string
	IncludeInactive bool
	Page            int
	PerPage         int
}

const (
	latestMemoryCommitsAlias = "latest_memory_commits"
)

func (s *Store) memoryCollectionQuery(ctx context.Context, opts MemoryCollectionOptions) *gorm.DB {
	q := s.db.WithContext(ctx).Model(&Memory{}).Where("memories.deleted_at IS NULL")
	if !opts.IncludeInactive {
		q = q.Where("memories.is_active = ?", true)
	}
	if principalID := strings.TrimSpace(opts.PrincipalID); principalID != "" {
		q = q.Joins("LEFT JOIN memory_grants AS user_mem_grants ON user_mem_grants.workspace_id = ? AND user_mem_grants.memory_id = memories.id AND user_mem_grants.subject_type = ? AND user_mem_grants.subject_id = ?", strings.TrimSpace(opts.WorkspaceID), "user", principalID).
			Where("memories.created_by = ? OR user_mem_grants.id IS NOT NULL", principalID).
			Group("memories.id")
	}
	if strings.TrimSpace(opts.Query) != "" {
		pattern := "%" + strings.ToLower(strings.TrimSpace(opts.Query)) + "%"
		q = q.Where("LOWER(memories.name) LIKE ? OR LOWER(memories.description) LIKE ?", pattern, pattern)
	}
	switch strings.TrimSpace(opts.View) {
	case "", "all":
		return q
	case "pinned":
		return q.Joins("JOIN memory_pins ON memory_pins.workspace_id = ? AND memory_pins.memory_id = memories.id AND memory_pins.principal_id = ?", strings.TrimSpace(opts.WorkspaceID), strings.TrimSpace(opts.PrincipalID))
	default:
		return q
	}
}

func joinLatestMemoryCommits(q *gorm.DB, workspaceID string) *gorm.DB {
	latestCommitSubquery := "SELECT asset_id, occurred_at AS last_commit_at FROM resource_activity_latest_commits WHERE workspace_id = ? AND asset_type = ?"
	return q.
		Joins("LEFT JOIN ("+latestCommitSubquery+") AS "+latestMemoryCommitsAlias+" ON "+latestMemoryCommitsAlias+".asset_id = memories.id", strings.TrimSpace(workspaceID), activity.AssetTypeMemory)
}

// memoryListRowsQuery builds the SELECT that powers the memory collection list:
// it joins the latest projected commit per memory and applies the ordering that
// prefers commit time and falls back to metadata updated_at. It is shared by
// ListMemoryCollection and the only_full_group_by regression test so the test
// exercises the real query path rather than a duplicated copy.
func (s *Store) memoryListRowsQuery(ctx context.Context, opts MemoryCollectionOptions) *gorm.DB {
	q := joinLatestMemoryCommits(s.memoryCollectionQuery(ctx, opts), opts.WorkspaceID)
	switch strings.TrimSpace(opts.View) {
	case "pinned":
		q = q.Order("MAX(memory_pins.updated_at) DESC").Order("memories.name ASC")
	default:
		// latest_memory_commits is a derived table (LEFT JOIN subquery). MySQL's
		// ONLY_FULL_GROUP_BY mode cannot trace functional dependency through
		// derived tables, so a bare last_commit_at in ORDER BY is rejected even
		// though the join is effectively 1:1 per memory. Wrap it in MAX() to
		// return that single value (or NULL, which COALESCE falls back).
		q = q.Order("COALESCE(MAX(" + latestMemoryCommitsAlias + ".last_commit_at), memories.updated_at) DESC").Order("memories.name ASC")
	}
	return q
}

func (s *Store) decorateMemoryCollectionCommitTimes(ctx context.Context, workspaceID string, items []Memory) error {
	if len(items) == 0 {
		return nil
	}
	ids := make([]string, 0, len(items))
	seen := make(map[string]struct{}, len(items))
	for _, item := range items {
		id := strings.TrimSpace(item.ID)
		if id == "" {
			continue
		}
		if _, ok := seen[id]; ok {
			continue
		}
		seen[id] = struct{}{}
		ids = append(ids, id)
	}
	if len(ids) == 0 {
		return nil
	}
	latestByID, err := s.latestMemoryCommitTimes(ctx, workspaceID, ids)
	if err != nil {
		return fmt.Errorf("decorate memory commit times: %w", err)
	}
	for i := range items {
		if occurredAt, ok := latestByID[strings.TrimSpace(items[i].ID)]; ok {
			items[i].UpdatedAt = occurredAt
		}
	}
	return nil
}

func (s *Store) latestMemoryCommitTimes(ctx context.Context, workspaceID string, ids []string) (map[string]time.Time, error) {
	if len(ids) == 0 {
		return map[string]time.Time{}, nil
	}
	var rows []struct {
		AssetID    string
		OccurredAt time.Time
	}
	err := latestMemoryCommitRowsQuery(s.db.WithContext(ctx), workspaceID, ids).Scan(&rows).Error
	if err != nil {
		return nil, err
	}
	latestByID := make(map[string]time.Time, len(rows))
	for _, row := range rows {
		if !row.OccurredAt.IsZero() {
			latestByID[strings.TrimSpace(row.AssetID)] = row.OccurredAt.UTC()
		}
	}
	return latestByID, nil
}

func latestMemoryCommitRowsQuery(q *gorm.DB, workspaceID string, ids []string) *gorm.DB {
	return q.
		Model(&activity.ResourceActivityLatestCommit{}).
		Select("asset_id, occurred_at").
		Where("workspace_id = ? AND asset_type = ? AND asset_id IN ?", strings.TrimSpace(workspaceID), activity.AssetTypeMemory, ids)
}

// LatestMemoryCommitTimesForBranches decorates only explicitly requested
// legacy Memory IDs from the one-row-per-asset latest projection. Joining the
// referenced event preserves current-branch validation without scanning the
// append-only activity history. A latest event on another branch intentionally
// yields no timestamp rather than searching older history.
type latestMemoryCommitTimeRow struct {
	AssetID     string
	OccurredAt  time.Time
	DetailsJSON string
}

func latestMemoryCommitTimesForBranchesQuery(q *gorm.DB, workspaceID string, ids []string) *gorm.DB {
	return q.Raw(`
SELECT latest.asset_id, latest.occurred_at, events.details_json
FROM resource_activity_latest_commits AS latest
JOIN resource_activity_events AS events
  ON events.id = latest.event_id
 AND events.workspace_id = latest.workspace_id
 AND events.asset_type = latest.asset_type
 AND events.asset_id = latest.asset_id
 AND events.event_type = ?
WHERE latest.workspace_id = ? AND latest.asset_type = ? AND latest.asset_id IN ?`,
		activity.EventTypeCommit, strings.TrimSpace(workspaceID), activity.AssetTypeMemory, ids)
}

func (s *Store) LatestMemoryCommitTimesForBranches(ctx context.Context, workspaceID string, branchesByID map[string]string) (map[string]time.Time, error) {
	if len(branchesByID) == 0 {
		return map[string]time.Time{}, nil
	}
	ids := make([]string, 0, len(branchesByID))
	for id := range branchesByID {
		if trimmed := strings.TrimSpace(id); trimmed != "" {
			ids = append(ids, trimmed)
		}
	}
	if len(ids) == 0 {
		return map[string]time.Time{}, nil
	}
	var rows []latestMemoryCommitTimeRow
	err := latestMemoryCommitTimesForBranchesQuery(s.db.WithContext(ctx), workspaceID, ids).Scan(&rows).Error
	if err != nil {
		return nil, err
	}
	latestByID := make(map[string]time.Time, len(rows))
	for _, row := range rows {
		id := strings.TrimSpace(row.AssetID)
		if id == "" || row.OccurredAt.IsZero() {
			continue
		}
		if !memoryCommitEventMatchesBranch(row.DetailsJSON, branchesByID[id]) {
			continue
		}
		latestByID[id] = row.OccurredAt.UTC()
	}
	return latestByID, nil
}

func memoryCommitEventMatchesBranch(detailsJSON string, branch string) bool {
	branch = strings.TrimSpace(branch)
	if branch == "" {
		return true
	}
	var details model.ResourceMemoryCommitDetailsJSON
	if err := model.UnmarshalJSONObject("resource Memory commit details", []byte(strings.TrimSpace(detailsJSON)), &details); err != nil {
		return false
	}
	return memoryCommitRefMatchesBranch(details.Ref, branch)
}

func memoryCommitRefMatchesBranch(ref string, branch string) bool {
	ref = strings.TrimSpace(ref)
	branch = strings.TrimSpace(branch)
	if branch == "" {
		return true
	}
	if ref == "" {
		return false
	}
	if ref == branch {
		return true
	}
	if strings.TrimPrefix(ref, "refs/heads/") == branch {
		return true
	}
	return ref == "refs/heads/"+branch
}

func (s *Store) ListMemoryCollection(ctx context.Context, opts MemoryCollectionOptions) (MemoryCollectionPage, error) {
	if err := s.ensureDB(); err != nil {
		return MemoryCollectionPage{}, err
	}
	if opts.Page <= 0 {
		opts.Page = 1
	}
	if opts.PerPage <= 0 {
		opts.PerPage = 50
	}
	offset := (opts.Page - 1) * opts.PerPage
	countQuery := s.memoryCollectionQuery(ctx, opts)
	var total int64
	if err := countQuery.Count(&total).Error; err != nil {
		return MemoryCollectionPage{}, fmt.Errorf("count memories: %w", err)
	}

	var items []Memory
	if err := s.memoryListRowsQuery(ctx, opts).Limit(opts.PerPage).Offset(offset).Find(&items).Error; err != nil {
		return MemoryCollectionPage{}, fmt.Errorf("list memories: %w", err)
	}
	if err := s.decorateMemoryCollectionCommitTimes(ctx, strings.TrimSpace(opts.WorkspaceID), items); err != nil {
		return MemoryCollectionPage{}, err
	}
	if err := s.decorateMemoryPins(ctx, strings.TrimSpace(opts.WorkspaceID), strings.TrimSpace(opts.PrincipalID), items); err != nil {
		return MemoryCollectionPage{}, err
	}
	return MemoryCollectionPage{Items: items, Total: total, Page: opts.Page, PerPage: opts.PerPage, HasMore: int64(offset+len(items)) < total}, nil
}

func (s *Store) CountWorkspaceMemories(ctx context.Context, workspaceID string, createdBy string) (int64, error) {
	if err := s.ensureDB(); err != nil {
		return 0, err
	}
	var total int64
	query := s.db.WithContext(ctx).Model(&Memory{}).Where("deleted_at IS NULL")
	if owner := strings.TrimSpace(createdBy); owner != "" {
		query = query.Where("created_by = ?", owner)
	}
	if err := query.Count(&total).Error; err != nil {
		return 0, fmt.Errorf("count workspace memories: %w", err)
	}
	return total, nil
}

// ListVisibleMemories returns the complete set of Memory resources visible to
// a principal. Activity APIs use this as an explicit prefilter before querying
// resource_activity_events, preventing event rows from becoming the authority
// for access control.
func (s *Store) ListVisibleMemories(ctx context.Context, workspaceID, principalID string, includeInactive bool) ([]Memory, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	q := s.memoryCollectionQuery(ctx, MemoryCollectionOptions{WorkspaceID: workspaceID, PrincipalID: principalID, IncludeInactive: includeInactive})
	var items []Memory
	if err := q.Order("memories.id ASC").Find(&items).Error; err != nil {
		return nil, fmt.Errorf("list visible memories: %w", err)
	}
	return items, nil
}

func (s *Store) CreateMemory(ctx context.Context, m Memory) (Memory, error) {
	if err := s.ensureDB(); err != nil {
		return Memory{}, err
	}
	if err := s.db.WithContext(ctx).Create(&m).Error; err != nil {
		return Memory{}, fmt.Errorf("create memory: %w", err)
	}
	return m, nil
}

func (s *Store) GetMemory(ctx context.Context, workspaceID, id string) (Memory, error) {
	if err := s.ensureDB(); err != nil {
		return Memory{}, err
	}
	var m Memory
	err := s.db.WithContext(ctx).Where("id = ? AND deleted_at IS NULL", strings.TrimSpace(id)).First(&m).Error
	if err != nil {
		return Memory{}, err
	}
	return m, nil
}

func (s *Store) UpdateMemory(ctx context.Context, m Memory) (Memory, error) {
	if err := s.ensureDB(); err != nil {
		return Memory{}, err
	}
	err := s.db.WithContext(ctx).Model(&Memory{}).Where("id = ?", m.ID).Updates(map[string]any{
		"slug":        m.Slug,
		"name":        m.Name,
		"description": m.Description,
		"is_active":   m.IsActive,
		"updated_by":  m.UpdatedBy,
		"updated_at":  m.UpdatedAt,
		"deleted_at":  m.DeletedAt,
	}).Error
	if err != nil {
		return Memory{}, fmt.Errorf("update memory: %w", err)
	}
	if m.DeletedAt != nil {
		return m, nil
	}
	return s.GetMemory(ctx, "", m.ID)
}

func (s *Store) DeleteMemory(ctx context.Context, workspaceID, memoryID string) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	workspaceID = strings.TrimSpace(workspaceID)
	memoryID = strings.TrimSpace(memoryID)
	return s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		if err := tx.Where("workspace_id = ? AND memory_id = ?", workspaceID, memoryID).Delete(&MemoryContentCheckpoint{}).Error; err != nil {
			return fmt.Errorf("delete memory content checkpoints: %w", err)
		}
		if err := tx.Where("memory_id = ?", memoryID).Delete(&MemoryPin{}).Error; err != nil {
			return fmt.Errorf("delete memory pins: %w", err)
		}
		if err := tx.Where("memory_id = ?", memoryID).Delete(&MemoryGrant{}).Error; err != nil {
			return fmt.Errorf("delete memory grants: %w", err)
		}
		result := tx.Where("id = ?", memoryID).Delete(&Memory{})
		if result.Error != nil {
			return fmt.Errorf("delete memory: %w", result.Error)
		}
		if result.RowsAffected == 0 {
			return gorm.ErrRecordNotFound
		}
		return nil
	})
}

func (s *Store) UpsertMemoryGrant(ctx context.Context, grant MemoryGrant) (MemoryGrant, error) {
	if err := s.ensureDB(); err != nil {
		return MemoryGrant{}, err
	}
	err := s.db.WithContext(ctx).Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "workspace_id"}, {Name: "memory_id"}, {Name: "subject_type"}, {Name: "subject_id"}},
		DoUpdates: clause.AssignmentColumns([]string{"role", "updated_by", "updated_at"}),
	}).Create(&grant).Error
	if err != nil {
		return MemoryGrant{}, fmt.Errorf("upsert memory grant: %w", err)
	}
	return s.GetMemoryGrant(ctx, grant.WorkspaceID, grant.MemoryID, grant.SubjectType, grant.SubjectID)
}

func (s *Store) ListMemoryGrants(ctx context.Context, workspaceID, memoryID string) ([]MemoryGrant, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	var grants []MemoryGrant
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND memory_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(memoryID)).
		Order("subject_type ASC").Order("subject_id ASC").
		Find(&grants).Error; err != nil {
		return nil, fmt.Errorf("list memory grants: %w", err)
	}
	return grants, nil
}

func (s *Store) GetMemoryGrant(ctx context.Context, workspaceID, memoryID, subjectType, subjectID string) (MemoryGrant, error) {
	if err := s.ensureDB(); err != nil {
		return MemoryGrant{}, err
	}
	var grant MemoryGrant
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND memory_id = ? AND subject_type = ? AND subject_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(memoryID), strings.TrimSpace(subjectType), strings.TrimSpace(subjectID)).
		First(&grant).Error
	return grant, err
}

func (s *Store) GetMemoryGrantByID(ctx context.Context, workspaceID, memoryID, grantID string) (MemoryGrant, error) {
	if err := s.ensureDB(); err != nil {
		return MemoryGrant{}, err
	}
	var grant MemoryGrant
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND memory_id = ? AND id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(memoryID), strings.TrimSpace(grantID)).
		First(&grant).Error
	return grant, err
}

func (s *Store) DeleteMemoryGrantByID(ctx context.Context, workspaceID, memoryID, grantID string) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	result := s.db.WithContext(ctx).
		Where("workspace_id = ? AND memory_id = ? AND id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(memoryID), strings.TrimSpace(grantID)).
		Delete(&MemoryGrant{})
	if result.Error != nil {
		return fmt.Errorf("delete memory grant: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

// AgentAccessibleMemory is a store projection of one agent's managed Memory
// binding and latest matching-branch sync time. It is not an API DTO.
type AgentAccessibleMemory struct {
	Memory     Memory
	Binding    MemoryAgentBinding
	LastSyncAt time.Time
}

func (s *Store) UpsertMemoryAgentBinding(ctx context.Context, binding MemoryAgentBinding) (MemoryAgentBinding, error) {
	if err := s.ensureDB(); err != nil {
		return MemoryAgentBinding{}, err
	}
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		return s.WithDB(tx).applyAgentMemoryBindingToMemory(ctx, binding)
	})
	if err != nil {
		return MemoryAgentBinding{}, err
	}
	return s.GetMemoryAgentBinding(ctx, binding.WorkspaceID, binding.AgentID)
}

func (s *Store) applyAgentMemoryBindingToMemory(ctx context.Context, binding MemoryAgentBinding) error {
	branch := strings.TrimSpace(binding.Branch)
	if branch == "" {
		branch = "main"
	}
	var target Memory
	if err := s.db.WithContext(ctx).
		Where("id = ? AND deleted_at IS NULL", strings.TrimSpace(binding.MemoryID)).
		First(&target).Error; err != nil {
		return fmt.Errorf("get memory for agent binding: %w", err)
	}
	if currentAgentID := stringValueForStore(target.AgentID); currentAgentID != "" && currentAgentID != strings.TrimSpace(binding.AgentID) {
		return fmt.Errorf("%w: memory already belongs to another agent", ErrMemoryAgentBindingConflict)
	}
	updates := map[string]any{
		"agent_id":                        stringPtrForStore(binding.AgentID),
		"branch":                          branch,
		"import_existing_local_content":   binding.ImportExistingLocalContent,
		"replace_local_content_on_switch": binding.ReplaceLocalContentOnSwitch,
		"updated_by":                      strings.TrimSpace(binding.UpdatedBy),
		"updated_at":                      firstNonZeroStoreTime(binding.UpdatedAt, time.Now().UTC()),
	}
	if updates["updated_by"] == "" {
		delete(updates, "updated_by")
	}
	if agentID := strings.TrimSpace(binding.AgentID); agentID != "" {
		if err := s.db.WithContext(ctx).
			Model(&Memory{}).
			Where("agent_id = ? AND id <> ?", agentID, strings.TrimSpace(binding.MemoryID)).
			Update("agent_id", nil).Error; err != nil {
			return fmt.Errorf("clear previous agent memory ownership: %w", err)
		}
	}
	if err := s.db.WithContext(ctx).
		Model(&Memory{}).
		Where("id = ? AND deleted_at IS NULL", strings.TrimSpace(binding.MemoryID)).
		Updates(updates).Error; err != nil {
		return fmt.Errorf("apply agent memory binding to memory: %w", err)
	}
	return nil
}

func (s *Store) GetMemoryAgentBinding(ctx context.Context, workspaceID, agentID string) (MemoryAgentBinding, error) {
	if err := s.ensureDB(); err != nil {
		return MemoryAgentBinding{}, err
	}
	var memory Memory
	err := s.db.WithContext(ctx).
		Where("agent_id = ? AND deleted_at IS NULL", strings.TrimSpace(agentID)).
		First(&memory).Error
	if err != nil {
		return MemoryAgentBinding{}, err
	}
	return memoryAgentBindingFromMemory(strings.TrimSpace(workspaceID), memory), nil
}

func (s *Store) DeleteMemoryAgentBinding(ctx context.Context, workspaceID, agentID string) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	agentID = strings.TrimSpace(agentID)
	return s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var memory Memory
		if err := tx.Where("agent_id = ? AND deleted_at IS NULL", agentID).First(&memory).Error; err != nil {
			return err
		}
		if err := tx.Model(&Memory{}).
			Where("id = ? AND agent_id = ?", memory.ID, agentID).
			Updates(map[string]any{
				"agent_id":                        nil,
				"branch":                          "main",
				"import_existing_local_content":   false,
				"replace_local_content_on_switch": false,
				"updated_by":                      strings.TrimSpace(memory.UpdatedBy),
				"updated_at":                      time.Now().UTC(),
			}).Error; err != nil {
			return fmt.Errorf("clear memory agent ownership: %w", err)
		}
		return nil
	})
}

func (s *Store) ListMemoryAgentBindings(ctx context.Context, workspaceID, memoryID string) ([]MemoryAgentBinding, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	var memory Memory
	if err := s.db.WithContext(ctx).
		Where("id = ? AND agent_id IS NOT NULL AND deleted_at IS NULL", strings.TrimSpace(memoryID)).
		First(&memory).Error; err != nil {
		if errors.Is(err, gorm.ErrRecordNotFound) {
			return []MemoryAgentBinding{}, nil
		}
		return nil, fmt.Errorf("list memory agent bindings: %w", err)
	}
	return []MemoryAgentBinding{memoryAgentBindingFromMemory(strings.TrimSpace(workspaceID), memory)}, nil
}

func (s *Store) GetAgentAccessibleMemory(ctx context.Context, workspaceID, agentID string, includeInactive bool) (*AgentAccessibleMemory, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	rowsByAgent, err := s.ListAgentAccessibleMemories(ctx, workspaceID, []string{agentID}, includeInactive)
	if err != nil {
		return nil, err
	}
	if row, ok := rowsByAgent[strings.TrimSpace(agentID)]; ok {
		return &row, nil
	}
	return nil, fmt.Errorf("get agent accessible memory: %w", gorm.ErrRecordNotFound)
}

func (s *Store) ListAgentAccessibleMemories(ctx context.Context, workspaceID string, agentIDs []string, includeInactive bool) (map[string]AgentAccessibleMemory, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	ids := make([]string, 0, len(agentIDs))
	seen := make(map[string]struct{}, len(agentIDs))
	for _, id := range agentIDs {
		id = strings.TrimSpace(id)
		if id == "" {
			continue
		}
		if _, ok := seen[id]; ok {
			continue
		}
		seen[id] = struct{}{}
		ids = append(ids, id)
	}
	out := make(map[string]AgentAccessibleMemory, len(ids))
	if len(ids) == 0 {
		return out, nil
	}
	q := s.db.WithContext(ctx).
		Model(&Memory{}).
		Where("agent_id IN ? AND deleted_at IS NULL", ids).
		Order("agent_id ASC")
	if !includeInactive {
		q = q.Where("is_active = ?", true)
	}
	var rows []Memory
	if err := q.Find(&rows).Error; err != nil {
		return nil, fmt.Errorf("list agent accessible memories: %w", err)
	}
	for _, r := range rows {
		agentID := stringValueForStore(r.AgentID)
		m := Memory{
			ID:                          r.ID,
			AgentID:                     r.AgentID,
			Slug:                        r.Slug,
			Name:                        r.Name,
			Description:                 r.Description,
			StorageScope:                r.StorageScope,
			StorageOwnerLogin:           r.StorageOwnerLogin,
			IsActive:                    r.IsActive,
			Branch:                      r.Branch,
			ContentID:                   r.ContentID,
			ActiveContentVersionID:      r.ActiveContentVersionID,
			ContentGeneration:           r.ContentGeneration,
			ContentHistorySequence:      r.ContentHistorySequence,
			ActiveReplicaID:             r.ActiveReplicaID,
			AcknowledgedSequence:        r.AcknowledgedSequence,
			ImportExistingLocalContent:  r.ImportExistingLocalContent,
			ReplaceLocalContentOnSwitch: r.ReplaceLocalContentOnSwitch,
			CreatedBy:                   r.CreatedBy,
			UpdatedBy:                   r.UpdatedBy,
			CreatedAt:                   r.CreatedAt,
			UpdatedAt:                   r.UpdatedAt,
		}
		out[agentID] = AgentAccessibleMemory{Memory: m, Binding: memoryAgentBindingFromMemory(strings.TrimSpace(workspaceID), r)}
	}
	return out, nil
}

func memoryAgentBindingFromMemory(workspaceID string, memory Memory) MemoryAgentBinding {
	agentID := stringValueForStore(memory.AgentID)
	return MemoryAgentBinding{
		ID:                          memory.ID,
		WorkspaceID:                 strings.TrimSpace(workspaceID),
		AgentID:                     agentID,
		MemoryID:                    memory.ID,
		Branch:                      memory.Branch,
		ImportExistingLocalContent:  memory.ImportExistingLocalContent,
		ReplaceLocalContentOnSwitch: memory.ReplaceLocalContentOnSwitch,
		CreatedBy:                   memory.CreatedBy,
		UpdatedBy:                   memory.UpdatedBy,
		CreatedAt:                   memory.CreatedAt,
		UpdatedAt:                   memory.UpdatedAt,
	}
}

func (s *Store) UpsertMemoryPin(ctx context.Context, pin MemoryPin) (MemoryPin, error) {
	if err := s.ensureDB(); err != nil {
		return MemoryPin{}, err
	}
	err := s.db.WithContext(ctx).Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "workspace_id"}, {Name: "principal_id"}, {Name: "memory_id"}},
		DoUpdates: clause.AssignmentColumns([]string{"updated_at"}),
	}).Create(&pin).Error
	if err != nil {
		return MemoryPin{}, fmt.Errorf("upsert memory pin: %w", err)
	}
	return pin, nil
}

func (s *Store) DeleteMemoryPin(ctx context.Context, workspaceID, principalID, memoryID string) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	result := s.db.WithContext(ctx).Where("workspace_id = ? AND principal_id = ? AND memory_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(principalID), strings.TrimSpace(memoryID)).Delete(&MemoryPin{})
	if result.Error != nil {
		return fmt.Errorf("delete memory pin: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (s *Store) DecorateMemoryPin(ctx context.Context, workspaceID, principalID string, m *Memory) error {
	if m == nil || strings.TrimSpace(principalID) == "" {
		return nil
	}
	var pin MemoryPin
	err := s.db.WithContext(ctx).Where("workspace_id = ? AND principal_id = ? AND memory_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(principalID), strings.TrimSpace(m.ID)).First(&pin).Error
	if err != nil {
		if err == gorm.ErrRecordNotFound {
			return nil
		}
		return fmt.Errorf("load memory pin: %w", err)
	}
	m.Pinned = true
	pinnedAt := pin.UpdatedAt
	m.PinnedAt = &pinnedAt
	return nil
}

func (s *Store) decorateMemoryPins(ctx context.Context, workspaceID, principalID string, items []Memory) error {
	if len(items) == 0 || strings.TrimSpace(principalID) == "" {
		return nil
	}
	ids := make([]string, 0, len(items))
	for _, m := range items {
		if strings.TrimSpace(m.ID) != "" {
			ids = append(ids, strings.TrimSpace(m.ID))
		}
	}
	if len(ids) == 0 {
		return nil
	}
	var pins []MemoryPin
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND principal_id = ? AND memory_id IN ?", strings.TrimSpace(workspaceID), strings.TrimSpace(principalID), ids).
		Find(&pins).Error; err != nil {
		return fmt.Errorf("load memory pins: %w", err)
	}
	pinByID := make(map[string]MemoryPin, len(pins))
	for _, pin := range pins {
		pinByID[pin.MemoryID] = pin
	}
	for i := range items {
		if pin, ok := pinByID[items[i].ID]; ok {
			items[i].Pinned = true
			pinnedAt := pin.UpdatedAt
			items[i].PinnedAt = &pinnedAt
		}
	}
	return nil
}