package kb

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

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

// Store owns MeshX KB persistence.
type Store struct {
	db            *gorm.DB
	inTransaction bool
}

func New(db *gorm.DB) *Store               { return &Store{db: db} }
func (s *Store) WithDB(db *gorm.DB) *Store { return &Store{db: db, inTransaction: true} }
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("kb store is not configured")
	}
	return nil
}

func applyWorkspaceScope(query *gorm.DB, column string, scopes [][]string) *gorm.DB {
	if len(scopes) == 0 || scopes[0] == nil {
		return query
	}
	workspaceIDs := uniqueTrimmedStrings(scopes[0])
	if len(workspaceIDs) == 0 {
		return query.Where("1 = 0")
	}
	return query.Where(column+" IN ?", workspaceIDs)
}

func uniqueTrimmedStrings(values []string) []string {
	out := make([]string, 0, len(values))
	seen := make(map[string]struct{}, len(values))
	for _, value := range values {
		trimmed := strings.TrimSpace(value)
		if trimmed == "" {
			continue
		}
		if _, ok := seen[trimmed]; ok {
			continue
		}
		seen[trimmed] = struct{}{}
		out = append(out, trimmed)
	}
	return out
}

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)) })
}

// LockSource serializes source-scoped control-plane mutations. Callers must
// use it only from inside Store.Transaction and keep the locked work bounded.
func (s *Store) LockSource(ctx context.Context, workspaceID, kbID, sourceID string) (Source, error) {
	if err := s.ensureDB(); err != nil {
		return Source{}, err
	}
	var source Source
	if err := s.db.WithContext(ctx).
		Clauses(clause.Locking{Strength: "UPDATE"}).
		Where("workspace_id = ? AND knowledge_base_id = ? AND id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(kbID), strings.TrimSpace(sourceID)).
		First(&source).Error; err != nil {
		return Source{}, fmt.Errorf("lock kb source: %w", err)
	}
	return source, nil
}

func (s *Store) inTransactionStore() bool {
	return s != nil && s.inTransaction
}

type KnowledgeBaseCollectionPage struct {
	Items       []KnowledgeBase
	GroupCounts []KnowledgeBaseGroupCount
	Total       int64
	Page        int
	PerPage     int
	HasMore     bool
}

type KnowledgeBaseGroupCount struct {
	GroupID          string
	PublicationState string
	Count            int64
}

type KnowledgeBaseCollectionOptions struct {
	WorkspaceID                 string
	PrincipalID                 string
	BypassPrincipalAccessFilter bool
	BypassDraftVisibilityFilter bool
	View                        string
	Query                       string
	TagIDs                      []string
	IncludeInactive             bool
	Page                        int
	PerPage                     int
}

const (
	latestKBSourceCommitsAlias  = "latest_kb_source_commits"
	latestKnowledgeCommitsAlias = "latest_knowledge_base_commits"
)

func (s *Store) knowledgeBaseCollectionQuery(ctx context.Context, opts KnowledgeBaseCollectionOptions) *gorm.DB {
	q := s.db.WithContext(ctx).Model(&KnowledgeBase{}).Where("knowledge_bases.workspace_id = ?", strings.TrimSpace(opts.WorkspaceID))
	q = q.Where("knowledge_bases.deleted_at IS NULL")
	if !opts.IncludeInactive {
		q = q.Where("knowledge_bases.is_active = ?", true)
	}
	if strings.TrimSpace(opts.Query) != "" {
		pattern := "%" + strings.ToLower(strings.TrimSpace(opts.Query)) + "%"
		q = q.Where(`(
			LOWER(knowledge_bases.name) LIKE ?
			OR LOWER(knowledge_bases.description) LIKE ?
			OR EXISTS (
				SELECT 1
				FROM knowledge_base_tag_assignments
				JOIN knowledge_base_tags ON knowledge_base_tags.workspace_id = knowledge_base_tag_assignments.workspace_id
					AND knowledge_base_tags.id = knowledge_base_tag_assignments.tag_id
				WHERE knowledge_base_tag_assignments.workspace_id = knowledge_bases.workspace_id
					AND knowledge_base_tag_assignments.knowledge_base_id = knowledge_bases.id
					AND LOWER(knowledge_base_tags.name) LIKE ?
			)
		)`, pattern, pattern, pattern)
	}
	for _, tagID := range uniqueTrimmedStrings(opts.TagIDs) {
		q = q.Where(`EXISTS (
			SELECT 1 FROM knowledge_base_tag_assignments
			WHERE knowledge_base_tag_assignments.workspace_id = knowledge_bases.workspace_id
				AND knowledge_base_tag_assignments.knowledge_base_id = knowledge_bases.id
				AND knowledge_base_tag_assignments.tag_id = ?
		)`, tagID)
	}
	if principalID := strings.TrimSpace(opts.PrincipalID); principalID != "" {
		draftVisibilitySQL := `(
			knowledge_bases.created_by = ?
			OR EXISTS (
				SELECT 1 FROM knowledge_base_grants AS draft_collaborator_grants
				WHERE draft_collaborator_grants.workspace_id = knowledge_bases.workspace_id
					AND draft_collaborator_grants.knowledge_base_id = knowledge_bases.id
					AND draft_collaborator_grants.subject_type = ?
					AND draft_collaborator_grants.subject_id = ?
					AND draft_collaborator_grants.role IN ?
			)
		)`
		if opts.BypassDraftVisibilityFilter {
			draftVisibilitySQL = "1 = 1"
		}
		if opts.BypassPrincipalAccessFilter {
			q = q.Where(`(
				knowledge_bases.publication_state <> ?
				OR (`+"knowledge_bases.publication_state = ? AND "+draftVisibilitySQL+`)
			)`, append([]any{"draft", "draft"}, draftVisibilityArgs(principalID, opts.BypassDraftVisibilityFilter)...)...)
		} else {
			principalVisibilitySQL := `(
				knowledge_bases.created_by = ?
				OR EXISTS (
					SELECT 1 FROM knowledge_base_grants
					WHERE knowledge_base_grants.workspace_id = knowledge_bases.workspace_id
						AND knowledge_base_grants.knowledge_base_id = knowledge_bases.id
						AND knowledge_base_grants.subject_type = ?
						AND knowledge_base_grants.subject_id = ?
						AND knowledge_base_grants.role <> ?
				)
				OR NOT EXISTS (
						SELECT 1 FROM knowledge_base_grants
						WHERE knowledge_base_grants.workspace_id = knowledge_bases.workspace_id
							AND knowledge_base_grants.knowledge_base_id = knowledge_bases.id
							AND knowledge_base_grants.subject_type = ?
							AND knowledge_base_grants.subject_id = ?
				)
			)`
			args := []any{"draft", principalID, "user", principalID, "none", "user", principalID, "draft"}
			args = append(args, draftVisibilityArgs(principalID, opts.BypassDraftVisibilityFilter)...)
			q = q.Where(`(
				(knowledge_bases.publication_state <> ? AND `+principalVisibilitySQL+`)
				OR (knowledge_bases.publication_state = ? AND `+draftVisibilitySQL+`)
			)`, args...)
		}
	}
	switch strings.TrimSpace(opts.View) {
	case "", "all":
		return q
	case "official", "draft":
		return q.Where("knowledge_bases.publication_state = ?", strings.TrimSpace(opts.View))
	case "owned":
		return q.Where("knowledge_bases.created_by = ?", strings.TrimSpace(opts.PrincipalID))
	case "my_knowledge_bases":
		return q.Where("knowledge_bases.created_by = ? AND knowledge_bases.publication_state <> ?", strings.TrimSpace(opts.PrincipalID), "draft")
	case "my_drafts":
		return q.Where("knowledge_bases.created_by = ? AND knowledge_bases.publication_state = ?", strings.TrimSpace(opts.PrincipalID), "draft")
	case "pinned":
		return q.Joins("JOIN knowledge_base_pins ON knowledge_base_pins.workspace_id = knowledge_bases.workspace_id AND knowledge_base_pins.knowledge_base_id = knowledge_bases.id AND knowledge_base_pins.principal_id = ?", strings.TrimSpace(opts.PrincipalID))
	default:
		return q
	}
}

func draftVisibilityArgs(principalID string, bypass bool) []any {
	if bypass {
		return nil
	}
	return []any{principalID, "user", principalID, []string{"owner", "manage", "edit"}}
}

func joinLatestKnowledgeBaseCommits(q *gorm.DB, workspaceID string) *gorm.DB {
	latestCommitSubquery := `SELECT knowledge_base_sources.workspace_id, knowledge_base_sources.knowledge_base_id, MAX(resource_activity_latest_commits.occurred_at) AS last_commit_at
FROM knowledge_base_sources
JOIN resource_activity_latest_commits ON resource_activity_latest_commits.workspace_id = knowledge_base_sources.workspace_id
	AND resource_activity_latest_commits.asset_id = knowledge_base_sources.id
	AND resource_activity_latest_commits.asset_type = ?
WHERE knowledge_base_sources.workspace_id = ?
	AND knowledge_base_sources.deleted_at IS NULL
GROUP BY knowledge_base_sources.workspace_id, knowledge_base_sources.knowledge_base_id`
	return q.Joins("LEFT JOIN ("+latestCommitSubquery+") AS "+latestKnowledgeCommitsAlias+" ON "+latestKnowledgeCommitsAlias+".workspace_id = knowledge_bases.workspace_id AND "+latestKnowledgeCommitsAlias+".knowledge_base_id = knowledge_bases.id", activity.AssetTypeKB, strings.TrimSpace(workspaceID))
}

func (s *Store) knowledgeBaseListRowsQuery(ctx context.Context, opts KnowledgeBaseCollectionOptions) *gorm.DB {
	q := s.knowledgeBaseCollectionQuery(ctx, opts)
	if strings.TrimSpace(opts.View) == "pinned" {
		return q.Order("knowledge_base_pins.updated_at DESC").Order("knowledge_bases.name ASC")
	}
	q = joinLatestKnowledgeBaseCommits(q, opts.WorkspaceID)
	if view := strings.TrimSpace(opts.View); view == "" || view == "all" {
		q = q.Order("CASE knowledge_bases.publication_state WHEN 'official' THEN 0 ELSE 1 END ASC")
	}
	return q.
		Order("COALESCE(" + latestKnowledgeCommitsAlias + ".last_commit_at, knowledge_bases.updated_at) DESC").
		Order("knowledge_bases.name ASC")
}

func (s *Store) decorateKnowledgeBaseCollectionCommitTimes(ctx context.Context, workspaceID string, bases []KnowledgeBase) error {
	if len(bases) == 0 {
		return nil
	}
	ids := make([]string, 0, len(bases))
	seen := make(map[string]struct{}, len(bases))
	for _, base := range bases {
		id := strings.TrimSpace(base.ID)
		if id == "" {
			continue
		}
		if _, ok := seen[id]; ok {
			continue
		}
		seen[id] = struct{}{}
		ids = append(ids, id)
	}
	if len(ids) == 0 {
		return nil
	}
	var rows []struct {
		KnowledgeBaseID string
		OccurredAt      time.Time
	}
	err := s.db.WithContext(ctx).Raw(`
SELECT knowledge_base_sources.knowledge_base_id, resource_activity_latest_commits.occurred_at
FROM knowledge_base_sources
JOIN resource_activity_latest_commits ON resource_activity_latest_commits.workspace_id = knowledge_base_sources.workspace_id
	AND resource_activity_latest_commits.asset_id = knowledge_base_sources.id
	AND resource_activity_latest_commits.asset_type = ?
WHERE knowledge_base_sources.workspace_id = ?
	AND knowledge_base_sources.deleted_at IS NULL
	AND knowledge_base_sources.knowledge_base_id IN ?
ORDER BY knowledge_base_sources.knowledge_base_id, resource_activity_latest_commits.occurred_at DESC, resource_activity_latest_commits.event_id DESC`, activity.AssetTypeKB, strings.TrimSpace(workspaceID), ids).Scan(&rows).Error
	if err != nil {
		return fmt.Errorf("decorate knowledge base commit times: %w", err)
	}
	latestByID := make(map[string]time.Time, len(rows))
	for _, row := range rows {
		id := strings.TrimSpace(row.KnowledgeBaseID)
		if id == "" || !latestByID[id].IsZero() || row.OccurredAt.IsZero() {
			continue
		}
		latestByID[id] = row.OccurredAt.UTC()
	}
	for i := range bases {
		if occurredAt, ok := latestByID[strings.TrimSpace(bases[i].ID)]; ok {
			bases[i].UpdatedAt = occurredAt
		}
	}
	return nil
}

func (s *Store) ListKnowledgeBaseCollection(ctx context.Context, opts KnowledgeBaseCollectionOptions) (KnowledgeBaseCollectionPage, error) {
	if err := s.ensureDB(); err != nil {
		return KnowledgeBaseCollectionPage{}, err
	}
	if opts.Page <= 0 {
		opts.Page = 1
	}
	if opts.PerPage <= 0 {
		opts.PerPage = 50
	}
	offset := (opts.Page - 1) * opts.PerPage
	countQuery := s.knowledgeBaseCollectionQuery(ctx, opts)
	var total int64
	if err := countQuery.Count(&total).Error; err != nil {
		return KnowledgeBaseCollectionPage{}, fmt.Errorf("count knowledge bases: %w", err)
	}
	var groupCounts []KnowledgeBaseGroupCount
	if err := s.knowledgeBaseCollectionQuery(ctx, opts).
		Select("knowledge_bases.group_id AS group_id, knowledge_bases.publication_state AS publication_state, COUNT(knowledge_bases.id) AS count").
		Group("knowledge_bases.group_id, knowledge_bases.publication_state").
		Scan(&groupCounts).Error; err != nil {
		return KnowledgeBaseCollectionPage{}, fmt.Errorf("count knowledge bases by publication and group: %w", err)
	}

	var bases []KnowledgeBase
	if err := s.knowledgeBaseListRowsQuery(ctx, opts).Limit(opts.PerPage).Offset(offset).Find(&bases).Error; err != nil {
		return KnowledgeBaseCollectionPage{}, fmt.Errorf("list knowledge bases: %w", err)
	}
	if err := s.decorateKnowledgeBaseCollectionCommitTimes(ctx, strings.TrimSpace(opts.WorkspaceID), bases); err != nil {
		return KnowledgeBaseCollectionPage{}, err
	}
	if err := s.decorateKnowledgeBasePins(ctx, strings.TrimSpace(opts.WorkspaceID), strings.TrimSpace(opts.PrincipalID), bases); err != nil {
		return KnowledgeBaseCollectionPage{}, err
	}
	return KnowledgeBaseCollectionPage{Items: bases, GroupCounts: groupCounts, Total: total, Page: opts.Page, PerPage: opts.PerPage, HasMore: int64(offset+len(bases)) < total}, nil
}

func (s *Store) ListKnowledgeBases(ctx context.Context, workspaceID, query string, includeInactive bool, page, perPage int) ([]KnowledgeBase, error) {
	pageResult, err := s.ListKnowledgeBaseCollection(ctx, KnowledgeBaseCollectionOptions{WorkspaceID: workspaceID, View: "all", Query: query, IncludeInactive: includeInactive, Page: page, PerPage: perPage})
	if err != nil {
		return nil, err
	}
	return pageResult.Items, nil
}

// ListOfficialKnowledgeBases is the database-filtered default retrieval scope.
// Explicitly selected Drafts use ListKnowledgeBasesByIDs instead.
func (s *Store) ListOfficialKnowledgeBases(ctx context.Context, workspaceID string, page, perPage int) ([]KnowledgeBase, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	if page <= 0 {
		page = 1
	}
	if perPage <= 0 {
		perPage = 50
	}
	var bases []KnowledgeBase
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND publication_state = ? AND is_active = ? AND deleted_at IS NULL", strings.TrimSpace(workspaceID), "official", true).
		Order("updated_at DESC, id ASC").
		Limit(perPage).
		Offset((page - 1) * perPage).
		Find(&bases).Error; err != nil {
		return nil, fmt.Errorf("list official knowledge bases: %w", err)
	}
	return bases, nil
}

// ListKnowledgeBasesByIDs loads only the explicitly requested workspace KBs.
// Callers that need display decoration should use ListKnowledgeBaseCollection.
func (s *Store) ListKnowledgeBasesByIDs(ctx context.Context, workspaceID string, knowledgeBaseIDs []string) ([]KnowledgeBase, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	ids := uniqueTrimmedStrings(knowledgeBaseIDs)
	if len(ids) == 0 {
		return []KnowledgeBase{}, nil
	}
	var bases []KnowledgeBase
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND id IN ?", strings.TrimSpace(workspaceID), ids).
		Find(&bases).Error; err != nil {
		return nil, fmt.Errorf("list knowledge bases by IDs: %w", err)
	}
	return bases, nil
}

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

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

func (s *Store) DecorateKnowledgeBasePin(ctx context.Context, workspaceID, principalID string, base *KnowledgeBase) error {
	if base == nil || strings.TrimSpace(principalID) == "" {
		return nil
	}
	pin, err := s.GetKnowledgeBasePin(ctx, workspaceID, principalID, base.ID)
	if err != nil {
		if err == gorm.ErrRecordNotFound {
			return nil
		}
		return fmt.Errorf("load knowledge base pin: %w", err)
	}
	base.Pinned = true
	pinnedAt := pin.UpdatedAt
	base.PinnedAt = &pinnedAt
	return nil
}

func (s *Store) GetKnowledgeBasePin(ctx context.Context, workspaceID, principalID, kbID string) (KnowledgeBasePin, error) {
	if err := s.ensureDB(); err != nil {
		return KnowledgeBasePin{}, err
	}
	var pin KnowledgeBasePin
	err := s.db.WithContext(ctx).Where("workspace_id = ? AND principal_id = ? AND knowledge_base_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(principalID), strings.TrimSpace(kbID)).First(&pin).Error
	if err != nil {
		return KnowledgeBasePin{}, err
	}
	return pin, nil
}

func (s *Store) decorateKnowledgeBasePins(ctx context.Context, workspaceID, principalID string, bases []KnowledgeBase) error {
	if len(bases) == 0 || strings.TrimSpace(principalID) == "" {
		return nil
	}
	ids := make([]string, 0, len(bases))
	for _, base := range bases {
		if strings.TrimSpace(base.ID) != "" {
			ids = append(ids, strings.TrimSpace(base.ID))
		}
	}
	if len(ids) == 0 {
		return nil
	}
	var pins []KnowledgeBasePin
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND principal_id = ? AND knowledge_base_id IN ?", strings.TrimSpace(workspaceID), strings.TrimSpace(principalID), ids).
		Find(&pins).Error; err != nil {
		return fmt.Errorf("load knowledge base pins: %w", err)
	}
	pinByID := make(map[string]KnowledgeBasePin, len(pins))
	for _, pin := range pins {
		pinByID[pin.KnowledgeBaseID] = pin
	}
	for index := range bases {
		if pin, ok := pinByID[bases[index].ID]; ok {
			bases[index].Pinned = true
			pinnedAt := pin.UpdatedAt
			bases[index].PinnedAt = &pinnedAt
		}
	}
	return nil
}

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

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

func (s *Store) UpdateKnowledgeBase(ctx context.Context, base KnowledgeBase) (KnowledgeBase, error) {
	if err := s.ensureDB(); err != nil {
		return KnowledgeBase{}, err
	}
	err := s.db.WithContext(ctx).Model(&KnowledgeBase{}).Where("workspace_id = ? AND id = ?", base.WorkspaceID, base.ID).Updates(map[string]any{
		"slug":                              base.Slug,
		"group_id":                          base.GroupID,
		"name":                              base.Name,
		"description":                       base.Description,
		"default_workspace_member_role":     base.DefaultWorkspaceMemberRole,
		"default_repository_access_profile": base.DefaultRepositoryAccessProfile,
		"allow_knowledge_qa_without_source_access": base.AllowKnowledgeQAWithoutSourceAccess,
		"publication_state":                        base.PublicationState,
		"working_revision":                         base.WorkingRevision,
		"published_revision":                       base.PublishedRevision,
		"published_at":                             base.PublishedAt,
		"is_active":                                base.IsActive,
		"retrieval_policy":                         base.RetrievalPolicy,
		"source_collection_state":                  base.SourceCollectionState,
		"last_accessed_at":                         base.LastAccessedAt,
		"updated_by":                               base.UpdatedBy,
		"updated_at":                               base.UpdatedAt,
		"deleted_at":                               base.DeletedAt,
	}).Error
	if err != nil {
		return KnowledgeBase{}, fmt.Errorf("update knowledge base: %w", err)
	}
	return s.GetKnowledgeBase(ctx, base.WorkspaceID, base.ID)
}

func joinLatestKBSourceCommits(q *gorm.DB, workspaceID string) *gorm.DB {
	latestCommitSubquery := "SELECT workspace_id, 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 "+latestKBSourceCommitsAlias+" ON "+latestKBSourceCommitsAlias+".workspace_id = knowledge_base_sources.workspace_id AND "+latestKBSourceCommitsAlias+".asset_id = knowledge_base_sources.id", strings.TrimSpace(workspaceID), activity.AssetTypeKB)
}

func orderKBSourceRows(q *gorm.DB, includeKnowledgeBaseOrder bool) *gorm.DB {
	if includeKnowledgeBaseOrder {
		q = q.Order("knowledge_base_sources.knowledge_base_id ASC")
	}
	return q.Order("COALESCE(" + latestKBSourceCommitsAlias + ".last_commit_at, knowledge_base_sources.updated_at) DESC").
		Order("knowledge_base_sources.name ASC")
}

func (s *Store) decorateSourceCommitTimes(ctx context.Context, workspaceID string, sources []Source) error {
	if len(sources) == 0 {
		return nil
	}
	ids := make([]string, 0, len(sources))
	seen := make(map[string]struct{}, len(sources))
	for _, source := range sources {
		id := strings.TrimSpace(source.ID)
		if id == "" {
			continue
		}
		if _, ok := seen[id]; ok {
			continue
		}
		seen[id] = struct{}{}
		ids = append(ids, id)
	}
	if len(ids) == 0 {
		return nil
	}
	var rows []struct {
		AssetID    string
		OccurredAt time.Time
	}
	err := s.db.WithContext(ctx).
		Model(&activity.ResourceActivityLatestCommit{}).
		Select("asset_id, occurred_at").
		Where("workspace_id = ? AND asset_type = ? AND asset_id IN ?", strings.TrimSpace(workspaceID), activity.AssetTypeKB, ids).
		Scan(&rows).Error
	if err != nil {
		return fmt.Errorf("decorate kb source commit times: %w", 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()
		}
	}
	for i := range sources {
		if occurredAt, ok := latestByID[strings.TrimSpace(sources[i].ID)]; ok {
			sources[i].UpdatedAt = occurredAt
		}
	}
	return nil
}

func (s *Store) ListSources(ctx context.Context, workspaceID, kbID string, page, perPage int) ([]Source, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	offset := (page - 1) * perPage
	var sources []Source
	q := s.db.WithContext(ctx).Model(&Source{}).Where("knowledge_base_sources.workspace_id = ? AND knowledge_base_sources.knowledge_base_id = ? AND knowledge_base_sources.deleted_at IS NULL", strings.TrimSpace(workspaceID), strings.TrimSpace(kbID))
	q = orderKBSourceRows(joinLatestKBSourceCommits(q, workspaceID), false)
	err := q.Limit(perPage).Offset(offset).Find(&sources).Error
	if err != nil {
		return nil, fmt.Errorf("list kb sources: %w", err)
	}
	if err := s.decorateSourceCommitTimes(ctx, strings.TrimSpace(workspaceID), sources); err != nil {
		return nil, err
	}
	return sources, nil
}

func (s *Store) ListSourcesForKnowledgeBases(ctx context.Context, workspaceID string, kbIDs []string) ([]Source, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	ids := uniqueTrimmedStrings(kbIDs)
	if len(ids) == 0 {
		return []Source{}, nil
	}
	var sources []Source
	q := s.db.WithContext(ctx).Model(&Source{}).
		Where("knowledge_base_sources.workspace_id = ? AND knowledge_base_sources.knowledge_base_id IN ? AND knowledge_base_sources.deleted_at IS NULL", strings.TrimSpace(workspaceID), ids)
	q = orderKBSourceRows(joinLatestKBSourceCommits(q, workspaceID), true)
	err := q.Find(&sources).Error
	if err != nil {
		return nil, fmt.Errorf("list kb sources for knowledge bases: %w", err)
	}
	if err := s.decorateSourceCommitTimes(ctx, strings.TrimSpace(workspaceID), sources); err != nil {
		return nil, err
	}
	return sources, nil
}

// ListActivitySourcesForKnowledgeBases loads source metadata needed to refresh
// activity without computing display-only commit timestamps.
func (s *Store) ListActivitySourcesForKnowledgeBases(ctx context.Context, workspaceID string, kbIDs []string) ([]Source, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	ids := uniqueTrimmedStrings(kbIDs)
	if len(ids) == 0 {
		return []Source{}, nil
	}
	var sources []Source
	if err := s.activitySourceRowsQuery(ctx, workspaceID, ids).Find(&sources).Error; err != nil {
		return nil, fmt.Errorf("list kb activity sources: %w", err)
	}
	return sources, nil
}

func (s *Store) activitySourceRowsQuery(ctx context.Context, workspaceID string, kbIDs []string) *gorm.DB {
	return s.db.WithContext(ctx).Model(&Source{}).
		Where("knowledge_base_sources.workspace_id = ? AND knowledge_base_sources.knowledge_base_id IN ? AND knowledge_base_sources.deleted_at IS NULL", strings.TrimSpace(workspaceID), kbIDs).
		Order("knowledge_base_sources.knowledge_base_id ASC").
		Order("knowledge_base_sources.updated_at DESC").
		Order("knowledge_base_sources.name ASC")
}

// ListSourcesByIDs loads only the explicitly requested, non-deleted workspace
// sources without hydrating unrelated sources from their Knowledge Bases.
func (s *Store) ListSourcesByIDs(ctx context.Context, workspaceID string, sourceIDs []string) ([]Source, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	ids := uniqueTrimmedStrings(sourceIDs)
	if len(ids) == 0 {
		return []Source{}, nil
	}
	var sources []Source
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND id IN ? AND deleted_at IS NULL", strings.TrimSpace(workspaceID), ids).
		Find(&sources).Error; err != nil {
		return nil, fmt.Errorf("list kb sources by IDs: %w", err)
	}
	return sources, nil
}

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

func (s *Store) UpdateSource(ctx context.Context, source Source) (Source, error) {
	if err := s.ensureDB(); err != nil {
		return Source{}, err
	}
	err := s.db.WithContext(ctx).Model(&Source{}).
		Where("workspace_id = ? AND knowledge_base_id = ? AND id = ?", strings.TrimSpace(source.WorkspaceID), strings.TrimSpace(source.KnowledgeBaseID), strings.TrimSpace(source.ID)).
		Updates(map[string]any{
			"source_type":      source.SourceType,
			"status":           source.Status,
			"name":             source.Name,
			"description":      source.Description,
			"default_ref":      source.DefaultRef,
			"indexing_mode":    source.IndexingMode,
			"sync_status":      source.SyncStatus,
			"index_status":     source.IndexStatus,
			"analysis_status":  source.AnalysisStatus,
			"freshness_status": source.FreshnessStatus,
			"config_json":      source.Config,
			"last_error":       source.LastError,
			"last_sync_at":     source.LastSyncAt,
			"last_index_at":    source.LastIndexAt,
			"last_analysis_at": source.LastAnalysisAt,
			"updated_by":       source.UpdatedBy,
			"updated_at":       source.UpdatedAt,
			"deleted_at":       source.DeletedAt,
		}).Error
	if err != nil {
		return Source{}, fmt.Errorf("update kb source: %w", err)
	}
	return s.GetSource(ctx, source.WorkspaceID, source.KnowledgeBaseID, source.ID)
}

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

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

func (s *Store) DeleteSource(ctx context.Context, workspaceID, kbID, sourceID string) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	result := s.db.WithContext(ctx).
		Where("workspace_id = ? AND knowledge_base_id = ? AND id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(kbID), strings.TrimSpace(sourceID)).
		Delete(&Source{})
	if result.Error != nil {
		return fmt.Errorf("delete kb source: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (s *Store) CreateSourceMirror(ctx context.Context, mirror SourceMirror) (SourceMirror, error) {
	if err := s.ensureDB(); err != nil {
		return SourceMirror{}, err
	}
	normalized, err := ValidateSourceMirrorUpstreamType(mirror.UpstreamType)
	if err != nil {
		return SourceMirror{}, err
	}
	mirror.UpstreamType = normalized
	if mirror.SyncStatus == "" {
		if mirror.Enabled {
			mirror.SyncStatus = SourceMirrorSyncStatusIdle
		} else {
			mirror.SyncStatus = SourceMirrorSyncStatusDisabled
		}
	}
	if err := s.db.WithContext(ctx).Create(&mirror).Error; err != nil {
		return SourceMirror{}, fmt.Errorf("create kb source mirror: %w", err)
	}
	return mirror, nil
}

func (s *Store) UpsertSourceMirror(ctx context.Context, mirror SourceMirror) (SourceMirror, error) {
	if err := s.ensureDB(); err != nil {
		return SourceMirror{}, err
	}
	normalized, err := ValidateSourceMirrorUpstreamType(mirror.UpstreamType)
	if err != nil {
		return SourceMirror{}, err
	}
	mirror.UpstreamType = normalized
	if mirror.SyncStatus == "" {
		if mirror.Enabled {
			mirror.SyncStatus = SourceMirrorSyncStatusIdle
		} else {
			mirror.SyncStatus = SourceMirrorSyncStatusDisabled
		}
	}
	err = s.db.WithContext(ctx).Clauses(clause.OnConflict{
		Columns: []clause.Column{{Name: "source_id"}},
		DoUpdates: clause.AssignmentColumns([]string{
			"workspace_id",
			"knowledge_base_id",
			"upstream_type",
			"remote_url",
			"credential_ref",
			"default_ref",
			"interval_seconds",
			"enabled",
			"sync_status",
			"last_sync_at",
			"last_success_at",
			"next_sync_at",
			"last_error",
			"last_mirrored_commit",
			"updated_by",
			"updated_at",
		}),
	}).Create(&mirror).Error
	if err != nil {
		return SourceMirror{}, fmt.Errorf("upsert kb source mirror: %w", err)
	}
	return s.GetSourceMirror(ctx, mirror.WorkspaceID, mirror.KnowledgeBaseID, mirror.SourceID)
}

func (s *Store) GetSourceMirror(ctx context.Context, workspaceID, kbID, sourceID string) (SourceMirror, error) {
	if err := s.ensureDB(); err != nil {
		return SourceMirror{}, err
	}
	var mirror SourceMirror
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND knowledge_base_id = ? AND source_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(kbID), strings.TrimSpace(sourceID)).
		First(&mirror).Error
	if err != nil {
		return SourceMirror{}, err
	}
	return mirror, nil
}

// ListSourceMirrorsBySourceIDs loads the requested workspace-scoped mirrors in
// one query so authorization cost does not grow into one database round trip
// per source.
func (s *Store) ListSourceMirrorsBySourceIDs(ctx context.Context, workspaceID string, sourceIDs []string) ([]SourceMirror, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	sourceIDs = uniqueTrimmedStrings(sourceIDs)
	if len(sourceIDs) == 0 {
		return []SourceMirror{}, nil
	}
	var mirrors []SourceMirror
	if err := s.db.WithContext(ctx).
		Select("source_id", "workspace_id", "knowledge_base_id", "enabled", "sync_status", "last_error", "last_success_at", "last_mirrored_commit", "updated_at").
		Where("workspace_id = ? AND source_id IN ?", strings.TrimSpace(workspaceID), sourceIDs).
		Order("source_id ASC").
		Find(&mirrors).Error; err != nil {
		return nil, fmt.Errorf("list kb source mirrors by source IDs: %w", err)
	}
	return mirrors, nil
}

func (s *Store) UpdateSourceMirror(ctx context.Context, mirror SourceMirror) (SourceMirror, error) {
	if err := s.ensureDB(); err != nil {
		return SourceMirror{}, err
	}
	normalized, err := ValidateSourceMirrorUpstreamType(mirror.UpstreamType)
	if err != nil {
		return SourceMirror{}, err
	}
	err = s.db.WithContext(ctx).Model(&SourceMirror{}).
		Where("workspace_id = ? AND knowledge_base_id = ? AND source_id = ?", strings.TrimSpace(mirror.WorkspaceID), strings.TrimSpace(mirror.KnowledgeBaseID), strings.TrimSpace(mirror.SourceID)).
		Updates(map[string]any{
			"upstream_type":        normalized,
			"remote_url":           mirror.RemoteURL,
			"credential_ref":       mirror.CredentialRef,
			"default_ref":          mirror.DefaultRef,
			"default_ref_explicit": mirror.DefaultRefExplicit,
			"interval_seconds":     mirror.IntervalSeconds,
			"enabled":              mirror.Enabled,
			"sync_status":          mirror.SyncStatus,
			"last_sync_at":         mirror.LastSyncAt,
			"last_success_at":      mirror.LastSuccessAt,
			"next_sync_at":         mirror.NextSyncAt,
			"last_error":           mirror.LastError,
			"last_mirrored_commit": mirror.LastMirroredCommit,
			"updated_by":           mirror.UpdatedBy,
			"updated_at":           mirror.UpdatedAt,
		}).Error
	if err != nil {
		return SourceMirror{}, fmt.Errorf("update kb source mirror: %w", err)
	}
	return s.GetSourceMirror(ctx, mirror.WorkspaceID, mirror.KnowledgeBaseID, mirror.SourceID)
}

func (s *Store) DeleteSourceMirror(ctx context.Context, workspaceID, kbID, sourceID string) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	result := s.db.WithContext(ctx).
		Where("workspace_id = ? AND knowledge_base_id = ? AND source_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(kbID), strings.TrimSpace(sourceID)).
		Delete(&SourceMirror{})
	if result.Error != nil {
		return fmt.Errorf("delete kb source mirror: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (s *Store) ListDueSourceMirrors(ctx context.Context, now time.Time, limit int, workspaceScope ...[]string) ([]SourceMirror, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	if limit <= 0 {
		limit = 10
	}
	var mirrors []SourceMirror
	query := s.db.WithContext(ctx).
		Where("enabled = ? AND interval_seconds > ? AND (next_sync_at IS NULL OR next_sync_at <= ?)", true, 0, now).
		Where("sync_status NOT IN ?", []string{SourceMirrorSyncStatusQueued, SourceMirrorSyncStatusRunning, SourceMirrorSyncStatusDisabled})
	query = applyWorkspaceScope(query, "workspace_id", workspaceScope)
	err := query.
		Order("COALESCE(next_sync_at, created_at) ASC").
		Limit(limit).
		Find(&mirrors).Error
	if err != nil {
		return nil, fmt.Errorf("list due kb source mirrors: %w", err)
	}
	return mirrors, nil
}

func (s *Store) CreateAnalysisRun(ctx context.Context, run AnalysisRun) (AnalysisRun, error) {
	if err := s.ensureDB(); err != nil {
		return AnalysisRun{}, err
	}
	if err := s.db.WithContext(ctx).Create(&run).Error; err != nil {
		return AnalysisRun{}, fmt.Errorf("create kb analysis run: %w", err)
	}
	return run, nil
}

func (s *Store) UpdateAnalysisRun(ctx context.Context, run AnalysisRun) (AnalysisRun, error) {
	if err := s.ensureDB(); err != nil {
		return AnalysisRun{}, err
	}
	err := s.db.WithContext(ctx).Model(&AnalysisRun{}).Where("workspace_id = ? AND id = ?", strings.TrimSpace(run.WorkspaceID), strings.TrimSpace(run.ID)).Updates(map[string]any{
		"trigger": run.Trigger, "status": run.Status, "input_kind": run.InputKind, "input_source_commit_sha": run.InputSourceCommitSHA, "output_repo_role": run.OutputRepoRole, "output_commit_sha": run.OutputCommitSHA, "published_wiki_revision": run.PublishedWikiRevision, "progress_json": run.Progress, "error": run.Error, "started_at": run.StartedAt, "completed_at": run.CompletedAt, "updated_at": run.UpdatedAt,
	}).Error
	if err != nil {
		return AnalysisRun{}, fmt.Errorf("update kb analysis run: %w", err)
	}
	var loaded AnalysisRun
	if err := s.db.WithContext(ctx).Where("workspace_id = ? AND id = ?", strings.TrimSpace(run.WorkspaceID), strings.TrimSpace(run.ID)).First(&loaded).Error; err != nil {
		return AnalysisRun{}, err
	}
	return loaded, nil
}

func (s *Store) ListAnalysisRuns(ctx context.Context, workspaceID, kbID, sourceID string, page, perPage int) ([]AnalysisRun, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	if page <= 0 {
		page = 1
	}
	if perPage <= 0 {
		perPage = 20
	}
	q := s.db.WithContext(ctx).Where("workspace_id = ? AND knowledge_base_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(kbID))
	if strings.TrimSpace(sourceID) != "" {
		q = q.Where("source_id = ?", strings.TrimSpace(sourceID))
	}
	var rows []AnalysisRun
	if err := q.Order("created_at DESC").Limit(perPage).Offset((page - 1) * perPage).Find(&rows).Error; err != nil {
		return nil, fmt.Errorf("list kb analysis runs: %w", err)
	}
	return rows, nil
}

func (s *Store) CreateSourceRevision(ctx context.Context, revision SourceRevision) (SourceRevision, error) {
	if err := s.ensureDB(); err != nil {
		return SourceRevision{}, err
	}
	if revision.IsCurrent {
		if err := s.db.WithContext(ctx).Model(&SourceRevision{}).Where("workspace_id = ? AND knowledge_base_id = ? AND source_id = ? AND is_current = ?", strings.TrimSpace(revision.WorkspaceID), strings.TrimSpace(revision.KnowledgeBaseID), strings.TrimSpace(revision.SourceID), true).Update("is_current", false).Error; err != nil {
			return SourceRevision{}, fmt.Errorf("retire current kb source revisions: %w", err)
		}
	}
	if err := s.db.WithContext(ctx).Create(&revision).Error; err != nil {
		return SourceRevision{}, fmt.Errorf("create kb source revision: %w", err)
	}
	return revision, nil
}

type AdvanceSourceRevisionParams struct {
	Revision                       SourceRevision
	ExpectedCurrentSourceCommitSHA string
	EnforceExpectedCurrent         bool
}

// AdvanceSourceRevision publishes one immutable source commit as current. The
// source row serializes competing completion paths. Replaying the same commit
// is a no-op so a retry cannot discard a Dashboard already attached to it, and
// an expected-current mismatch fences a delayed completion from rolling a
// newer revision back.
func (s *Store) AdvanceSourceRevision(ctx context.Context, params AdvanceSourceRevisionParams) (SourceRevision, bool, error) {
	if err := s.ensureDB(); err != nil {
		return SourceRevision{}, false, err
	}
	if s.inTransactionStore() {
		return s.advanceSourceRevision(ctx, params)
	}
	var advanced SourceRevision
	var created bool
	if err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var err error
		advanced, created, err = s.WithDB(tx).advanceSourceRevision(ctx, params)
		return err
	}); err != nil {
		return SourceRevision{}, false, err
	}
	return advanced, created, nil
}

func (s *Store) advanceSourceRevision(ctx context.Context, params AdvanceSourceRevisionParams) (SourceRevision, bool, error) {
	revision := params.Revision
	revision.WorkspaceID = strings.TrimSpace(revision.WorkspaceID)
	revision.KnowledgeBaseID = strings.TrimSpace(revision.KnowledgeBaseID)
	revision.SourceID = strings.TrimSpace(revision.SourceID)
	revision.SourceCommitSHA = strings.TrimSpace(revision.SourceCommitSHA)
	if revision.WorkspaceID == "" || revision.KnowledgeBaseID == "" || revision.SourceID == "" || revision.SourceCommitSHA == "" {
		return SourceRevision{}, false, fmt.Errorf("advance kb source revision: scope and source commit are required")
	}
	if revision.ID == "" {
		return SourceRevision{}, false, fmt.Errorf("advance kb source revision: revision id is required")
	}
	var source Source
	if err := s.db.WithContext(ctx).
		Clauses(clause.Locking{Strength: "UPDATE"}).
		Where("workspace_id = ? AND knowledge_base_id = ? AND id = ?", revision.WorkspaceID, revision.KnowledgeBaseID, revision.SourceID).
		First(&source).Error; err != nil {
		return SourceRevision{}, false, fmt.Errorf("advance kb source revision: lock source: %w", err)
	}
	var current SourceRevision
	err := s.db.WithContext(ctx).
		Clauses(clause.Locking{Strength: "UPDATE"}).
		Where("workspace_id = ? AND knowledge_base_id = ? AND source_id = ? AND is_current = ?", revision.WorkspaceID, revision.KnowledgeBaseID, revision.SourceID, true).
		Order("updated_at DESC").First(&current).Error
	if err == nil && strings.TrimSpace(current.SourceCommitSHA) == revision.SourceCommitSHA {
		return current, false, nil
	}
	if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
		return SourceRevision{}, false, fmt.Errorf("advance kb source revision: load current: %w", err)
	}
	expected := strings.TrimSpace(params.ExpectedCurrentSourceCommitSHA)
	if params.EnforceExpectedCurrent && ((err == nil && strings.TrimSpace(current.SourceCommitSHA) != expected) || (errors.Is(err, gorm.ErrRecordNotFound) && expected != "")) {
		return current, false, nil
	}
	if err == nil {
		if updateErr := s.db.WithContext(ctx).Model(&SourceRevision{}).
			Where("workspace_id = ? AND knowledge_base_id = ? AND source_id = ? AND is_current = ?", revision.WorkspaceID, revision.KnowledgeBaseID, revision.SourceID, true).
			Update("is_current", false).Error; updateErr != nil {
			return SourceRevision{}, false, fmt.Errorf("advance kb source revision: retire current: %w", updateErr)
		}
	}
	revision.IsCurrent = true
	// Dashboard publications remain readable across source updates. The
	// manifest keeps the source commit represented by the graph, allowing the
	// read model to report that an incremental update is available.
	revision.UADashboardCommitSHA = ""
	revision.UADashboardManifestJSON = ""
	revision.UADashboardPublishedAt = nil
	if err == nil {
		revision.UADashboardCommitSHA = current.UADashboardCommitSHA
		revision.UADashboardManifestJSON = current.UADashboardManifestJSON
		revision.UADashboardPublishedAt = current.UADashboardPublishedAt
	}
	if createErr := s.db.WithContext(ctx).Create(&revision).Error; createErr != nil {
		return SourceRevision{}, false, fmt.Errorf("advance kb source revision: create current: %w", createErr)
	}
	return revision, true, nil
}

func (s *Store) GetCurrentSourceRevision(ctx context.Context, workspaceID, kbID, sourceID string) (SourceRevision, error) {
	if err := s.ensureDB(); err != nil {
		return SourceRevision{}, err
	}
	var row SourceRevision
	err := s.db.WithContext(ctx).Where("workspace_id = ? AND knowledge_base_id = ? AND source_id = ? AND is_current = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(kbID), strings.TrimSpace(sourceID), true).Order("updated_at DESC").First(&row).Error
	if err != nil {
		return SourceRevision{}, err
	}
	return row, nil
}

// PublishUADashboardParams contains the exact source-revision fence and the
// publication metadata committed with its audit event.
type PublishUADashboardParams struct {
	WorkspaceID        string
	KnowledgeBaseID    string
	SourceID           string
	RevisionID         string
	SourceCommitSHA    string
	PreviousCommitSHA  string
	DashboardCommitSHA string
	ManifestJSON       string
	PublishedAt        time.Time
	AuditEvent         AuditEvent
}

// UADashboardManifestMaxBytes is both the service serialization limit and the
// exact byte capacity of SourceRevision.UADashboardManifestJSON.
const UADashboardManifestMaxBytes = 16 << 10

// PublishUADashboard advances one current source revision only when every
// caller-supplied fence still matches. The pointer and audit row share one
// transaction, so an audit failure never exposes a partial publication.
func (s *Store) PublishUADashboard(ctx context.Context, params PublishUADashboardParams) (SourceRevision, bool, error) {
	if len(params.ManifestJSON) > UADashboardManifestMaxBytes {
		return SourceRevision{}, false, fmt.Errorf("publish UA dashboard: manifest exceeds %d bytes", UADashboardManifestMaxBytes)
	}
	var published SourceRevision
	updated := false
	err := s.Transaction(ctx, func(tx *Store) error {
		result := tx.db.WithContext(ctx).Model(&SourceRevision{}).
			Where(
				"workspace_id = ? AND knowledge_base_id = ? AND source_id = ? AND id = ? AND is_current = ? AND source_commit_sha = ? AND ua_dashboard_commit_sha = ?",
				strings.TrimSpace(params.WorkspaceID),
				strings.TrimSpace(params.KnowledgeBaseID),
				strings.TrimSpace(params.SourceID),
				strings.TrimSpace(params.RevisionID),
				true,
				strings.TrimSpace(params.SourceCommitSHA),
				strings.TrimSpace(params.PreviousCommitSHA),
			).
			Updates(map[string]any{
				"ua_dashboard_commit_sha":    strings.TrimSpace(params.DashboardCommitSHA),
				"ua_dashboard_manifest_json": params.ManifestJSON,
				"ua_dashboard_published_at":  params.PublishedAt,
				"updated_at":                 params.PublishedAt,
			})
		if result.Error != nil {
			return fmt.Errorf("publish UA dashboard: %w", result.Error)
		}
		if result.RowsAffected != 1 {
			return nil
		}
		updated = true
		if _, err := tx.CreateAuditEvent(ctx, params.AuditEvent); err != nil {
			return err
		}
		var err error
		published, err = tx.GetCurrentSourceRevision(ctx, params.WorkspaceID, params.KnowledgeBaseID, params.SourceID)
		return err
	})
	if err != nil {
		return SourceRevision{}, false, err
	}
	if !updated {
		return SourceRevision{}, false, nil
	}
	return published, true, nil
}

// ListCurrentSourceRevisions loads current revisions for an explicit source
// set in one workspace-scoped query. Ordering makes malformed duplicate-current
// rows deterministic for callers that select the first row per source.
func (s *Store) ListCurrentSourceRevisions(ctx context.Context, workspaceID string, sourceIDs []string) ([]SourceRevision, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	sourceIDs = uniqueTrimmedStrings(sourceIDs)
	if len(sourceIDs) == 0 {
		return []SourceRevision{}, nil
	}
	var revisions []SourceRevision
	if err := s.db.WithContext(ctx).
		Select("id", "workspace_id", "knowledge_base_id", "source_id", "source_commit_sha", "source_content_id", "source_content_version_id", "source_content_verified_at", "published_wiki_revision", "insights_commit_sha", "ua_dashboard_commit_sha", "ua_dashboard_manifest_json", "ua_dashboard_published_at", "freshness_status", "updated_at").
		Where("workspace_id = ? AND source_id IN ? AND is_current = ?", strings.TrimSpace(workspaceID), sourceIDs, true).
		Order("source_id ASC, updated_at DESC, id DESC").
		Find(&revisions).Error; err != nil {
		return nil, fmt.Errorf("list current kb source revisions: %w", err)
	}
	return revisions, nil
}

func (s *Store) CreateJob(ctx context.Context, job Job) (Job, error) {
	if err := s.ensureDB(); err != nil {
		return Job{}, err
	}
	if job.Priority <= 0 {
		job.Priority = 100
	}
	if err := s.db.WithContext(ctx).Create(&job).Error; err != nil {
		return Job{}, fmt.Errorf("create kb job: %w", err)
	}
	return job, nil
}

// ErrSourceJobAlreadyActive is returned by CreateUniqueSourceJob when an active
// (queued or running) job already exists for the same source and job type.
var ErrSourceJobAlreadyActive = errors.New("source job already active")

func (s *Store) CreateUniqueSourceJob(ctx context.Context, job Job) (Job, error) {
	if err := s.ensureDB(); err != nil {
		return Job{}, err
	}
	job.JobType = strings.TrimSpace(job.JobType)
	job.SourceID = strings.TrimSpace(job.SourceID)
	// Lock the source row to serialize concurrent enqueue attempts for the
	// same source. Without this, two concurrent transactions can both see no
	// active job and both insert one. The lock is held until the enclosing
	// transaction commits or rolls back.
	var locked Source
	if err := s.db.WithContext(ctx).
		Clauses(clause.Locking{Strength: "UPDATE", Options: "NOWAIT"}).
		Where("workspace_id = ? AND knowledge_base_id = ? AND id = ?", strings.TrimSpace(job.WorkspaceID), strings.TrimSpace(job.KnowledgeBaseID), job.SourceID).
		First(&locked).Error; err != nil {
		if errors.Is(err, gorm.ErrRecordNotFound) {
			return Job{}, fmt.Errorf("find unique kb source job: source not found: %w", err)
		}
		return Job{}, fmt.Errorf("find unique kb source job: lock source: %w", err)
	}
	var existing Job
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND knowledge_base_id = ? AND source_id = ? AND job_type = ? AND status IN ?", strings.TrimSpace(job.WorkspaceID), strings.TrimSpace(job.KnowledgeBaseID), job.SourceID, job.JobType, []string{"queued", "running"}).
		Order("created_at ASC").
		First(&existing).Error
	if err == nil {
		return existing, ErrSourceJobAlreadyActive
	}
	if err != gorm.ErrRecordNotFound {
		return Job{}, fmt.Errorf("find unique kb source job: %w", err)
	}
	return s.CreateJob(ctx, job)
}

func (s *Store) GetJob(ctx context.Context, workspaceID, jobID string) (Job, error) {
	if err := s.ensureDB(); err != nil {
		return Job{}, err
	}
	var job Job
	err := s.db.WithContext(ctx).Where("workspace_id = ? AND id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(jobID)).First(&job).Error
	if err != nil {
		return Job{}, err
	}
	return job, nil
}

func (s *Store) ListSourceJobs(ctx context.Context, workspaceID, kbID, sourceID, jobType string, page, perPage int) ([]Job, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	if page <= 0 {
		page = 1
	}
	if perPage <= 0 {
		perPage = 20
	}
	var jobs []Job
	query := s.db.WithContext(ctx).
		Where("workspace_id = ? AND knowledge_base_id = ? AND source_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(kbID), strings.TrimSpace(sourceID))
	if strings.TrimSpace(jobType) != "" {
		query = query.Where("job_type = ?", strings.TrimSpace(jobType))
	}
	err := query.Order("created_at DESC").
		Limit(perPage).
		Offset((page - 1) * perPage).
		Find(&jobs).Error
	if err != nil {
		return nil, fmt.Errorf("list kb source jobs: %w", err)
	}
	return jobs, nil
}

func (s *Store) UpdateJob(ctx context.Context, job Job) (Job, error) {
	if err := s.ensureDB(); err != nil {
		return Job{}, err
	}
	if err := s.updateJobQuery(ctx, job, nil); err != nil {
		return Job{}, err
	}
	return s.GetJob(ctx, job.WorkspaceID, job.ID)
}

// UpdateLeasedJob updates a running job only while the caller still owns the claim attempt.
func (s *Store) UpdateLeasedJob(ctx context.Context, job Job, leaseOwner string, attemptCount int) (Job, bool, error) {
	if err := s.ensureDB(); err != nil {
		return Job{}, false, err
	}
	leaseOwner = strings.TrimSpace(leaseOwner)
	if leaseOwner == "" {
		return Job{}, false, fmt.Errorf("update leased kb job: lease_owner is required")
	}
	if attemptCount <= 0 {
		return Job{}, false, fmt.Errorf("update leased kb job: attempt_count is required")
	}
	rowsAffected := int64(0)
	err := s.updateJobQuery(ctx, job, func(q *gorm.DB) *gorm.DB {
		q = q.Where("status = ? AND lease_owner = ? AND attempt_count = ?", "running", leaseOwner, attemptCount)
		return q
	}, &rowsAffected)
	if err != nil {
		return Job{}, false, err
	}
	if rowsAffected == 0 {
		return Job{}, false, nil
	}
	updated, err := s.GetJob(ctx, job.WorkspaceID, job.ID)
	return updated, true, err
}

func (s *Store) updateJobQuery(ctx context.Context, job Job, scope func(*gorm.DB) *gorm.DB, rowsAffected ...*int64) error {
	query := s.db.WithContext(ctx).Model(&Job{}).
		Where("workspace_id = ? AND id = ?", strings.TrimSpace(job.WorkspaceID), strings.TrimSpace(job.ID))
	if scope != nil {
		query = scope(query)
	}
	result := query.Updates(map[string]any{
		"status":        job.Status,
		"progress_json": job.Progress,
		"error":         job.Error,
		"attempt_count": job.AttemptCount,
		"lease_owner":   job.LeaseOwner,
		"lease_until":   job.LeaseUntil,
		"started_at":    job.StartedAt,
		"finished_at":   job.FinishedAt,
		"updated_at":    job.UpdatedAt,
	})
	if result.Error != nil {
		return fmt.Errorf("update kb job: %w", result.Error)
	}
	if len(rowsAffected) > 0 && rowsAffected[0] != nil {
		*rowsAffected[0] = result.RowsAffected
	}
	return nil
}

func (s *Store) ClaimNextQueuedJob(ctx context.Context, jobType, leaseOwner string, leaseDuration time.Duration, now time.Time) (Job, bool, error) {
	if err := s.ensureDB(); err != nil {
		return Job{}, false, err
	}
	var claimed Job
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var job Job
		err := tx.Where(
			"job_type = ? AND (status = ? OR (status = ? AND (lease_until IS NULL OR lease_until <= ?)))",
			strings.TrimSpace(jobType),
			"queued",
			"running",
			now,
		).
			Order("priority ASC").
			Order("created_at ASC").
			Order("id ASC").
			First(&job).Error
		if err != nil {
			if err == gorm.ErrRecordNotFound {
				return nil
			}
			return fmt.Errorf("claim kb job: %w", err)
		}
		leaseUntil := now.Add(leaseDuration)
		updates := map[string]any{
			"status":        "running",
			"lease_owner":   strings.TrimSpace(leaseOwner),
			"lease_until":   leaseUntil,
			"attempt_count": gorm.Expr("attempt_count + ?", 1),
			"updated_at":    now,
		}
		if job.StartedAt == nil {
			updates["started_at"] = now
		}
		result := tx.Model(&Job{}).
			Where(
				"workspace_id = ? AND id = ? AND job_type = ? AND (status = ? OR (status = ? AND (lease_until IS NULL OR lease_until <= ?)))",
				job.WorkspaceID,
				job.ID,
				strings.TrimSpace(jobType),
				"queued",
				"running",
				now,
			).
			Updates(updates)
		if result.Error != nil {
			return fmt.Errorf("claim kb job: update: %w", result.Error)
		}
		if result.RowsAffected == 0 {
			return nil
		}
		if err := tx.Where("workspace_id = ? AND id = ?", job.WorkspaceID, job.ID).First(&claimed).Error; err != nil {
			return fmt.Errorf("claim kb job: reload: %w", err)
		}
		return nil
	})
	if err != nil {
		return Job{}, false, err
	}
	if claimed.ID == "" {
		return Job{}, false, nil
	}
	return claimed, true, nil
}

// ClaimQueuedJobsFair claims up to limit jobs of the given type, distributing
// across distinct sources in round-robin order so that a single source with
// many queued jobs cannot monopolize the worker batch. Lower priority values
// are claimed first, with created_at and id providing stable FIFO tie-breaks.
func (s *Store) ClaimQueuedJobsFair(ctx context.Context, jobType, leaseOwner string, leaseDuration time.Duration, limit int, now time.Time, workspaceScope ...[]string) ([]Job, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	if limit <= 0 {
		limit = 1
	}
	leaseOwner = strings.TrimSpace(leaseOwner)
	jobType = strings.TrimSpace(jobType)
	leaseUntil := now.Add(leaseDuration)
	var claimed []Job
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		// Select one candidate source at a time, ranked by its highest-priority
		// job and then FIFO. The aggregate values only drive SQL ordering; only
		// source_id crosses the driver boundary, which keeps SQLite and TiDB
		// timestamp representations out of this scan.
		var sourceIDs []string
		sourceQuery := tx.Model(&Job{}).
			Select("source_id").
			Where("job_type = ? AND (status = ? OR (status = ? AND (lease_until IS NULL OR lease_until <= ?)))", jobType, "queued", "running", now)
		sourceQuery = applyWorkspaceScope(sourceQuery, "workspace_id", workspaceScope)
		rows, err := sourceQuery.
			Group("source_id").
			Order("MIN(priority) ASC").
			Order("MIN(created_at) ASC").
			Order("source_id ASC").
			Limit(limit).
			Rows()
		if err != nil {
			return fmt.Errorf("claim fair kb jobs: find sources: %w", err)
		}
		for rows.Next() {
			var sourceID string
			if err := rows.Scan(&sourceID); err != nil {
				_ = rows.Close()
				return fmt.Errorf("claim fair kb jobs: scan source: %w", err)
			}
			sourceIDs = append(sourceIDs, sourceID)
		}
		_ = rows.Close()
		if len(sourceIDs) == 0 {
			return nil
		}
		for _, sourceID := range sourceIDs {
			var job Job
			jobQuery := tx.Where(
				"job_type = ? AND source_id = ? AND (status = ? OR (status = ? AND (lease_until IS NULL OR lease_until <= ?)))",
				jobType, sourceID, "queued", "running", now,
			)
			jobQuery = applyWorkspaceScope(jobQuery, "workspace_id", workspaceScope)
			err := jobQuery.
				Order("priority ASC").
				Order("created_at ASC").
				Order("id ASC").
				First(&job).Error
			if err != nil {
				if errors.Is(err, gorm.ErrRecordNotFound) {
					continue
				}
				return fmt.Errorf("claim fair kb jobs: find job for source %s: %w", sourceID, err)
			}
			updates := map[string]any{
				"status":        "running",
				"lease_owner":   leaseOwner,
				"lease_until":   leaseUntil,
				"attempt_count": gorm.Expr("attempt_count + ?", 1),
				"updated_at":    now,
			}
			if job.StartedAt == nil {
				updates["started_at"] = now
			}
			result := tx.Model(&Job{}).
				Where(
					"workspace_id = ? AND id = ? AND job_type = ? AND (status = ? OR (status = ? AND (lease_until IS NULL OR lease_until <= ?)))",
					job.WorkspaceID, job.ID, jobType, "queued", "running", now,
				).
				Updates(updates)
			if result.Error != nil {
				return fmt.Errorf("claim fair kb jobs: update: %w", result.Error)
			}
			if result.RowsAffected == 0 {
				continue
			}
			if err := tx.Where("workspace_id = ? AND id = ?", job.WorkspaceID, job.ID).First(&job).Error; err != nil {
				return fmt.Errorf("claim fair kb jobs: reload: %w", err)
			}
			claimed = append(claimed, job)
		}
		return nil
	})
	if err != nil {
		return nil, err
	}
	return claimed, nil
}

// ReclaimStaleRunningJobs resets running jobs whose leases have expired back to
// queued so they can be picked up by the next worker tick. It returns the
// number of jobs reclaimed. This is intended to be called once at startup to
// recover jobs left running by a previous process crash or restart.
func (s *Store) ReclaimStaleRunningJobs(ctx context.Context, jobType string, now time.Time, workspaceScope ...[]string) (int64, error) {
	if err := s.ensureDB(); err != nil {
		return 0, err
	}
	query := s.db.WithContext(ctx).Model(&Job{}).
		Where("job_type = ? AND status = ? AND (lease_until IS NULL OR lease_until <= ?)", strings.TrimSpace(jobType), "running", now)
	query = applyWorkspaceScope(query, "workspace_id", workspaceScope)
	result := query.
		Updates(map[string]any{
			"status":      "queued",
			"lease_owner": "",
			"lease_until": nil,
			"updated_at":  now,
		})
	if result.Error != nil {
		return 0, fmt.Errorf("reclaim stale kb jobs: %w", result.Error)
	}
	return result.RowsAffected, nil
}

func (s *Store) CreateAuditEvent(ctx context.Context, event AuditEvent) (AuditEvent, error) {
	if err := s.ensureDB(); err != nil {
		return AuditEvent{}, err
	}
	if err := s.db.WithContext(ctx).Create(&event).Error; err != nil {
		return AuditEvent{}, fmt.Errorf("create kb audit event: %w", err)
	}
	return event, nil
}

func (s *Store) ListKBSavedViews(ctx context.Context, workspaceID string, principalID string, page int, perPage int) ([]KBSavedView, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	offset := (page - 1) * perPage
	var rows []KBSavedView
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND (owner_principal_id = ? OR visibility = ?)", strings.TrimSpace(workspaceID), strings.TrimSpace(principalID), "workspace").
		Order("updated_at DESC").Order("name ASC").Limit(perPage).Offset(offset).Find(&rows).Error
	if err != nil {
		return nil, fmt.Errorf("list kb saved views: %w", err)
	}
	return rows, nil
}

func (s *Store) CreateKBSavedView(ctx context.Context, view KBSavedView) (KBSavedView, error) {
	if err := s.ensureDB(); err != nil {
		return KBSavedView{}, err
	}
	if err := s.db.WithContext(ctx).Create(&view).Error; err != nil {
		return KBSavedView{}, fmt.Errorf("create kb saved view: %w", err)
	}
	return view, nil
}

func (s *Store) GetKBSavedView(ctx context.Context, workspaceID string, principalID string, viewID string) (KBSavedView, error) {
	if err := s.ensureDB(); err != nil {
		return KBSavedView{}, err
	}
	var row KBSavedView
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND id = ? AND (owner_principal_id = ? OR visibility = ?)", strings.TrimSpace(workspaceID), strings.TrimSpace(viewID), strings.TrimSpace(principalID), "workspace").
		First(&row).Error
	if err != nil {
		return KBSavedView{}, err
	}
	return row, nil
}

func (s *Store) UpdateKBSavedView(ctx context.Context, view KBSavedView) (KBSavedView, error) {
	if err := s.ensureDB(); err != nil {
		return KBSavedView{}, err
	}
	err := s.db.WithContext(ctx).Model(&KBSavedView{}).
		Where("workspace_id = ? AND id = ? AND owner_principal_id = ?", view.WorkspaceID, view.ID, view.OwnerPrincipalID).
		Updates(map[string]any{
			"name":        view.Name,
			"slug":        view.Slug,
			"description": view.Description,
			"filter":      view.Filter,
			"visibility":  view.Visibility,
			"updated_at":  view.UpdatedAt,
		}).Error
	if err != nil {
		return KBSavedView{}, fmt.Errorf("update kb saved view: %w", err)
	}
	return s.GetKBSavedView(ctx, view.WorkspaceID, view.OwnerPrincipalID, view.ID)
}

func (s *Store) DeleteKBSavedView(ctx context.Context, workspaceID string, principalID string, viewID string) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	result := s.db.WithContext(ctx).
		Where("workspace_id = ? AND id = ? AND owner_principal_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(viewID), strings.TrimSpace(principalID)).
		Delete(&KBSavedView{})
	if result.Error != nil {
		return fmt.Errorf("delete kb saved view: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (s *Store) ListAuditEvents(ctx context.Context, workspaceID, kbID string, page, perPage int) ([]AuditEvent, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	offset := (page - 1) * perPage
	var events []AuditEvent
	q := s.db.WithContext(ctx).Where("workspace_id = ?", strings.TrimSpace(workspaceID))
	if strings.TrimSpace(kbID) != "" {
		q = q.Where("knowledge_base_id = ?", strings.TrimSpace(kbID))
	}
	if err := q.Order("created_at DESC").Order("id DESC").Limit(perPage).Offset(offset).Find(&events).Error; err != nil {
		return nil, fmt.Errorf("list kb audit events: %w", err)
	}
	return events, nil
}

type AgentAccessibleKnowledgeBase struct {
	KnowledgeBase KnowledgeBase
	Grant         KnowledgeBaseGrant
}

func (s *Store) UpsertKnowledgeBaseGrant(ctx context.Context, grant KnowledgeBaseGrant) (KnowledgeBaseGrant, error) {
	if err := s.ensureDB(); err != nil {
		return KnowledgeBaseGrant{}, err
	}
	grant.RepositoryAccessProfile = NormalizeKnowledgeBaseRepositoryAccessProfile(grant.RepositoryAccessProfile)
	err := s.db.WithContext(ctx).Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "workspace_id"}, {Name: "knowledge_base_id"}, {Name: "subject_type"}, {Name: "subject_id"}},
		DoUpdates: clause.AssignmentColumns([]string{"role", "repository_access_profile", "updated_by", "updated_at"}),
	}).Create(&grant).Error
	if err != nil {
		return KnowledgeBaseGrant{}, fmt.Errorf("upsert knowledge base grant: %w", err)
	}
	created, err := s.GetKnowledgeBaseGrant(ctx, grant.WorkspaceID, grant.KnowledgeBaseID, grant.SubjectType, grant.SubjectID)
	if err != nil {
		return KnowledgeBaseGrant{}, fmt.Errorf("get knowledge base grant after upsert: %w", err)
	}
	return created, nil
}

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

func (s *Store) ListKnowledgeBaseAgentGrantSubjectIDs(ctx context.Context, workspaceID, kbID string) ([]string, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	var agentIDs []string
	if err := s.db.WithContext(ctx).
		Model(&KnowledgeBaseGrant{}).
		Distinct("subject_id").
		Where("workspace_id = ? AND knowledge_base_id = ? AND subject_type = ? AND role <> ?", strings.TrimSpace(workspaceID), strings.TrimSpace(kbID), "agent", "none").
		Order("subject_id ASC").
		Pluck("subject_id", &agentIDs).Error; err != nil {
		return nil, fmt.Errorf("list knowledge base agent grant subject ids: %w", err)
	}
	return uniqueTrimmedStrings(agentIDs), nil
}

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

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

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

func (s *Store) ListAgentAccessibleKnowledgeBases(ctx context.Context, workspaceID, agentID string, includeInactive bool, page, perPage int) ([]AgentAccessibleKnowledgeBase, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	if page <= 0 {
		page = 1
	}
	if perPage <= 0 {
		perPage = 50
	}
	offset := (page - 1) * perPage
	type row struct {
		KnowledgeBase
		GrantID                      string
		GrantRole                    string
		GrantRepositoryAccessProfile string
		GrantCreatedBy               string
		GrantUpdatedBy               string
		GrantCreatedAt               time.Time
		GrantUpdatedAt               time.Time
	}
	q := s.db.WithContext(ctx).
		Table("knowledge_bases").
		Select("knowledge_bases.*, knowledge_base_grants.id AS grant_id, knowledge_base_grants.role AS grant_role, knowledge_base_grants.repository_access_profile AS grant_repository_access_profile, knowledge_base_grants.created_by AS grant_created_by, knowledge_base_grants.updated_by AS grant_updated_by, knowledge_base_grants.created_at AS grant_created_at, knowledge_base_grants.updated_at AS grant_updated_at").
		Joins("JOIN knowledge_base_grants ON knowledge_base_grants.workspace_id = knowledge_bases.workspace_id AND knowledge_base_grants.knowledge_base_id = knowledge_bases.id").
		Where("knowledge_bases.workspace_id = ? AND knowledge_base_grants.subject_type = ? AND knowledge_base_grants.subject_id = ? AND knowledge_base_grants.role <> ?", strings.TrimSpace(workspaceID), "agent", strings.TrimSpace(agentID), "none")
	if !includeInactive {
		q = q.Where("knowledge_bases.is_active = ?", true)
	}
	var rows []row
	if err := q.Order("knowledge_base_grants.updated_at DESC").Order("knowledge_bases.name ASC").Limit(perPage).Offset(offset).Find(&rows).Error; err != nil {
		return nil, fmt.Errorf("list agent accessible knowledge bases: %w", err)
	}
	out := make([]AgentAccessibleKnowledgeBase, 0, len(rows))
	for _, r := range rows {
		base := r.KnowledgeBase
		out = append(out, AgentAccessibleKnowledgeBase{
			KnowledgeBase: base,
			Grant: KnowledgeBaseGrant{
				ID:                      r.GrantID,
				WorkspaceID:             base.WorkspaceID,
				KnowledgeBaseID:         base.ID,
				SubjectType:             "agent",
				SubjectID:               strings.TrimSpace(agentID),
				Role:                    r.GrantRole,
				RepositoryAccessProfile: NormalizeKnowledgeBaseRepositoryAccessProfile(r.GrantRepositoryAccessProfile),
				CreatedBy:               r.GrantCreatedBy,
				UpdatedBy:               r.GrantUpdatedBy,
				CreatedAt:               r.GrantCreatedAt,
				UpdatedAt:               r.GrantUpdatedAt,
			},
		})
	}
	return out, nil
}