package kb

import (
	"context"
	"strings"
	"testing"
	"time"

	"github.com/stretchr/testify/require"
	"github.com/tidbcloud/AgentTeam/internal/store/resource/activity"
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

func newTestStore(t *testing.T) *Store {
	t.Helper()
	db, err := gorm.Open(sqlite.Open("file:"+t.Name()+"?mode=memory&cache=shared"), &gorm.Config{})
	require.NoError(t, err)
	require.NoError(t, db.AutoMigrate(&KnowledgeBase{}, &KnowledgeBaseGroup{}, &KnowledgeBaseTag{}, &KnowledgeBaseTagAssignment{}, &KnowledgeBasePin{}, &KnowledgeBaseGrant{}, &KnowledgeBaseSourceRepositoryAccessOverride{}, &KnowledgeBaseSetupSession{}, &KnowledgeBaseSetupSourceArtifact{}, &KnowledgeBaseGenerationRequest{}, &KnowledgeBasePublicationRequest{}, &Source{}, &SourceMirror{}, &AnalysisRun{}, &SourceRevision{}, &Job{}, &AuditEvent{}, &activity.ResourceActivityEvent{}, &activity.ResourceActivityLatestCommit{}))
	return New(db)
}

func TestStoreScopesKnowledgeBasesByWorkspace(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 1, 0, 0, 0, 0, time.UTC)
	_, err := store.CreateKnowledgeBase(ctx, KnowledgeBase{ID: "kb_1", WorkspaceID: "wsp_a", Name: "A", IsActive: true, CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)
	_, err = store.CreateKnowledgeBase(ctx, KnowledgeBase{ID: "kb_2", WorkspaceID: "wsp_b", Name: "B", IsActive: true, CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)

	bases, err := store.ListKnowledgeBases(ctx, "wsp_a", "", false, 1, 20)
	require.NoError(t, err)
	require.Len(t, bases, 1)
	require.Equal(t, "kb_1", bases[0].ID)
}

func TestStoreBatchLoadsOnlyExplicitKnowledgeScopeIDs(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 1, 0, 0, 0, 0, time.UTC)
	for _, base := range []KnowledgeBase{
		{ID: "kb_target", WorkspaceID: "wsp_a", Name: "Target", IsActive: true, CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now},
		{ID: "kb_unrelated", WorkspaceID: "wsp_a", Name: "Unrelated", IsActive: true, CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now},
		{ID: "kb_other_workspace", WorkspaceID: "wsp_b", Name: "Other", IsActive: true, CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now},
	} {
		_, err := store.CreateKnowledgeBase(ctx, base)
		require.NoError(t, err)
	}
	for _, source := range []Source{
		{ID: "src_target", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_target", Name: "Target", CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now},
		{ID: "src_unrelated", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_unrelated", Name: "Unrelated", CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now},
		{ID: "src_other_workspace", WorkspaceID: "wsp_b", KnowledgeBaseID: "kb_other_workspace", Name: "Other", CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now},
	} {
		_, err := store.CreateSource(ctx, source)
		require.NoError(t, err)
	}
	require.NoError(t, store.db.Create(&[]SourceRevision{
		{ID: "rev_target_old", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_target", SourceID: "src_target", SourceCommitSHA: "old", IsCurrent: false, CreatedAt: now, UpdatedAt: now},
		{ID: "rev_target", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_target", SourceID: "src_target", SourceCommitSHA: "target", IsCurrent: true, CreatedAt: now, UpdatedAt: now.Add(time.Minute)},
		{ID: "rev_unrelated", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_unrelated", SourceID: "src_unrelated", SourceCommitSHA: "unrelated", IsCurrent: true, CreatedAt: now, UpdatedAt: now},
		{ID: "rev_other", WorkspaceID: "wsp_b", KnowledgeBaseID: "kb_other_workspace", SourceID: "src_other_workspace", SourceCommitSHA: "other", IsCurrent: true, CreatedAt: now, UpdatedAt: now},
	}).Error)
	require.NoError(t, store.db.Model(&SourceRevision{}).Where("id = ?", "rev_target_old").Update("is_current", false).Error)
	require.NoError(t, store.db.Create(&[]SourceMirror{
		{SourceID: "src_target", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_target", UpstreamType: "generic_git", Enabled: true, SyncStatus: "failed", LastError: "sync failed", CreatedAt: now, UpdatedAt: now},
		{SourceID: "src_unrelated", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_unrelated", UpstreamType: "generic_git", CreatedAt: now, UpdatedAt: now},
		{SourceID: "src_other_workspace", WorkspaceID: "wsp_b", KnowledgeBaseID: "kb_other_workspace", UpstreamType: "generic_git", CreatedAt: now, UpdatedAt: now},
	}).Error)

	bases, err := store.ListKnowledgeBasesByIDs(ctx, "wsp_a", []string{"kb_target", "kb_other_workspace"})
	require.NoError(t, err)
	require.Len(t, bases, 1)
	require.Equal(t, "kb_target", bases[0].ID)
	sources, err := store.ListSourcesByIDs(ctx, "wsp_a", []string{"src_target", "src_other_workspace"})
	require.NoError(t, err)
	require.Len(t, sources, 1)
	require.Equal(t, "src_target", sources[0].ID)
	revisions, err := store.ListCurrentSourceRevisions(ctx, "wsp_a", []string{"src_target", "src_other_workspace"})
	require.NoError(t, err)
	require.Len(t, revisions, 1)
	require.Equal(t, "target", revisions[0].SourceCommitSHA)
	mirrors, err := store.ListSourceMirrorsBySourceIDs(ctx, "wsp_a", []string{"src_target", "src_other_workspace"})
	require.NoError(t, err)
	require.Len(t, mirrors, 1)
	require.Equal(t, "src_target", mirrors[0].SourceID)
	require.True(t, mirrors[0].Enabled)
	require.Equal(t, "failed", mirrors[0].SyncStatus)
	require.Equal(t, "sync failed", mirrors[0].LastError)
}

func TestStoreListsKnowledgeBaseCollectionsWithPaging(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 1, 0, 0, 0, 0, time.UTC)
	_, err := store.CreateKnowledgeBase(ctx, KnowledgeBase{ID: "kb_1", WorkspaceID: "wsp_a", GroupID: "group_ops", Name: "Alpha", Description: "Runbooks", PublicationState: "official", IsActive: true, CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now.Add(time.Minute)})
	require.NoError(t, err)
	_, err = store.CreateKnowledgeBase(ctx, KnowledgeBase{ID: "kb_2", WorkspaceID: "wsp_a", GroupID: "group_product", Name: "Beta", Description: "Guides", PublicationState: "draft", IsActive: true, CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now.Add(2 * time.Minute)})
	require.NoError(t, err)
	_, err = store.UpsertKnowledgeBasePin(ctx, KnowledgeBasePin{WorkspaceID: "wsp_a", PrincipalID: "user", KnowledgeBaseID: "kb_1", CreatedAt: now, UpdatedAt: now.Add(3 * time.Minute)})
	require.NoError(t, err)
	all, err := store.ListKnowledgeBaseCollection(ctx, KnowledgeBaseCollectionOptions{WorkspaceID: "wsp_a", PrincipalID: "user", View: "all", Page: 1, PerPage: 1})
	require.NoError(t, err)
	require.Len(t, all.Items, 1)
	require.Equal(t, int64(2), all.Total)
	require.True(t, all.HasMore)
	require.Equal(t, "kb_1", all.Items[0].ID, "All must partition Official before Draft before paginating")
	require.ElementsMatch(t, []KnowledgeBaseGroupCount{
		{GroupID: "group_ops", PublicationState: "official", Count: 1},
		{GroupID: "group_product", PublicationState: "draft", Count: 1},
	}, all.GroupCounts, "group totals must describe the filtered collection, not only the current page")

	pinned, err := store.ListKnowledgeBaseCollection(ctx, KnowledgeBaseCollectionOptions{WorkspaceID: "wsp_a", PrincipalID: "user", View: "pinned", Page: 1, PerPage: 10})
	require.NoError(t, err)
	require.Len(t, pinned.Items, 1)
	require.Equal(t, "kb_1", pinned.Items[0].ID)
	require.True(t, pinned.Items[0].Pinned)
	require.Equal(t, []KnowledgeBaseGroupCount{{GroupID: "group_ops", PublicationState: "official", Count: 1}}, pinned.GroupCounts)

	filtered, err := store.ListKnowledgeBaseCollection(ctx, KnowledgeBaseCollectionOptions{WorkspaceID: "wsp_a", PrincipalID: "user", View: "all", Query: "run", Page: 1, PerPage: 10})
	require.NoError(t, err)
	require.Len(t, filtered.Items, 1)
	require.Equal(t, "kb_1", filtered.Items[0].ID)
	require.Equal(t, []KnowledgeBaseGroupCount{{GroupID: "group_ops", PublicationState: "official", Count: 1}}, filtered.GroupCounts)
}

func TestKnowledgeBaseListRowsQuerySkipsLatestCommitJoinForPinnedView(t *testing.T) {
	store := newTestStore(t)

	pinnedSQL := store.db.ToSQL(func(tx *gorm.DB) *gorm.DB {
		s := New(tx)
		var items []KnowledgeBase
		return s.knowledgeBaseListRowsQuery(context.Background(), KnowledgeBaseCollectionOptions{
			WorkspaceID: "wsp_a", PrincipalID: "user", View: "pinned", Page: 1, PerPage: 20,
		}).Limit(20).Find(&items)
	})
	require.NotContains(t, pinnedSQL, latestKnowledgeCommitsAlias, "pinned ordering must not scan projected commit activity")
	require.Contains(t, pinnedSQL, "knowledge_base_pins.updated_at DESC", "pinned ordering must remain based on pin time")

	allSQL := store.db.ToSQL(func(tx *gorm.DB) *gorm.DB {
		s := New(tx)
		var items []KnowledgeBase
		return s.knowledgeBaseListRowsQuery(context.Background(), KnowledgeBaseCollectionOptions{
			WorkspaceID: "wsp_a", PrincipalID: "user", View: "all", Page: 1, PerPage: 20,
		}).Limit(20).Find(&items)
	})
	require.Contains(t, allSQL, latestKnowledgeCommitsAlias)
	require.Contains(t, allSQL, "CASE knowledge_bases.publication_state", "All must order its publication partitions before page slicing")
	require.Contains(t, allSQL, "knowledge_base_sources.workspace_id = \"wsp_a\"", "latest commit aggregation must stay inside the requested workspace")
}

func TestStoreListsKnowledgeBaseCollectionsWithLatestSourceCommitTime(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	baseTime := time.Date(2026, 6, 24, 1, 0, 0, 0, time.UTC)
	metadataUpdate := baseTime.Add(10 * time.Hour)
	latestCommit := baseTime.Add(time.Hour)
	fallbackUpdate := baseTime.Add(2 * time.Hour)

	_, err := store.CreateKnowledgeBase(ctx, KnowledgeBase{ID: "kb_commit", WorkspaceID: "wsp_a", Name: "Commit-backed", Description: "A", IsActive: true, CreatedBy: "user", UpdatedBy: "user", CreatedAt: baseTime, UpdatedAt: metadataUpdate})
	require.NoError(t, err)
	_, err = store.CreateKnowledgeBase(ctx, KnowledgeBase{ID: "kb_no_commit", WorkspaceID: "wsp_a", Name: "Metadata fallback", Description: "B", IsActive: true, CreatedBy: "user", UpdatedBy: "user", CreatedAt: baseTime, UpdatedAt: fallbackUpdate})
	require.NoError(t, err)
	_, err = store.CreateSource(ctx, Source{ID: "src_commit", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_commit", SourceType: "codebase", Status: "ready", Name: "AgentTeam", CreatedBy: "user", UpdatedBy: "user", CreatedAt: baseTime, UpdatedAt: metadataUpdate})
	require.NoError(t, err)
	_, err = store.CreateSource(ctx, Source{ID: "src_no_commit", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_no_commit", SourceType: "codebase", Status: "ready", Name: "Docs", CreatedBy: "user", UpdatedBy: "user", CreatedAt: baseTime, UpdatedAt: fallbackUpdate})
	require.NoError(t, err)
	_, err = activity.New(store.db).UpsertEvent(ctx, activity.ResourceActivityEvent{ID: "act_kb_old_commit", WorkspaceID: "wsp_a", AssetType: activity.AssetTypeKB, AssetID: "src_commit", EventType: "commit_pushed", OccurredAt: baseTime.Add(30 * time.Minute)})
	require.NoError(t, err)
	_, err = activity.New(store.db).UpsertEvent(ctx, activity.ResourceActivityEvent{ID: "act_kb_latest_commit", WorkspaceID: "wsp_a", AssetType: activity.AssetTypeKB, AssetID: "src_commit", EventType: "commit_pushed", OccurredAt: latestCommit})
	require.NoError(t, err)

	page, err := store.ListKnowledgeBaseCollection(ctx, KnowledgeBaseCollectionOptions{WorkspaceID: "wsp_a", PrincipalID: "user", View: "all", Page: 1, PerPage: 20})
	require.NoError(t, err)
	require.Len(t, page.Items, 2)
	require.Equal(t, "kb_no_commit", page.Items[0].ID, "KBs without projected source commits fall back to metadata updated_at for ordering")
	require.Equal(t, fallbackUpdate, page.Items[0].UpdatedAt)
	require.Equal(t, "kb_commit", page.Items[1].ID, "latest source commit should override KB metadata updated_at for display")
	require.Equal(t, latestCommit, page.Items[1].UpdatedAt)

	_, err = store.UpsertKnowledgeBasePin(ctx, KnowledgeBasePin{WorkspaceID: "wsp_a", PrincipalID: "user", KnowledgeBaseID: "kb_commit", CreatedAt: baseTime, UpdatedAt: baseTime})
	require.NoError(t, err)
	pinned, err := store.ListKnowledgeBaseCollection(ctx, KnowledgeBaseCollectionOptions{WorkspaceID: "wsp_a", PrincipalID: "user", View: "pinned", Page: 1, PerPage: 20})
	require.NoError(t, err)
	require.Len(t, pinned.Items, 1)
	require.Equal(t, "kb_commit", pinned.Items[0].ID)
	require.Equal(t, latestCommit, pinned.Items[0].UpdatedAt, "pinned collection keeps pin ordering but still displays latest source commit time")
}

func TestStoreListSourcesUsesProjectedCommitTime(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	baseTime := time.Date(2026, 6, 24, 1, 0, 0, 0, time.UTC)
	metadataUpdate := baseTime.Add(10 * time.Hour)
	latestCommit := baseTime.Add(time.Hour)
	fallbackUpdate := baseTime.Add(2 * time.Hour)

	_, err := store.CreateKnowledgeBase(ctx, KnowledgeBase{ID: "kb_1", WorkspaceID: "wsp_a", Name: "Runbooks", Description: "A", IsActive: true, CreatedBy: "user", UpdatedBy: "user", CreatedAt: baseTime, UpdatedAt: baseTime})
	require.NoError(t, err)
	_, err = store.CreateSource(ctx, Source{ID: "src_commit", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceType: "codebase", Status: "ready", Name: "Commit-backed", CreatedBy: "user", UpdatedBy: "user", CreatedAt: baseTime, UpdatedAt: metadataUpdate})
	require.NoError(t, err)
	_, err = store.CreateSource(ctx, Source{ID: "src_no_commit", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceType: "codebase", Status: "ready", Name: "Metadata fallback", CreatedBy: "user", UpdatedBy: "user", CreatedAt: baseTime, UpdatedAt: fallbackUpdate})
	require.NoError(t, err)
	_, err = activity.New(store.db).UpsertEvent(ctx, activity.ResourceActivityEvent{ID: "act_src_old_commit", WorkspaceID: "wsp_a", AssetType: activity.AssetTypeKB, AssetID: "src_commit", EventType: "commit_pushed", OccurredAt: baseTime.Add(30 * time.Minute)})
	require.NoError(t, err)
	_, err = activity.New(store.db).UpsertEvent(ctx, activity.ResourceActivityEvent{ID: "act_src_latest_commit", WorkspaceID: "wsp_a", AssetType: activity.AssetTypeKB, AssetID: "src_commit", EventType: "commit_pushed", OccurredAt: latestCommit})
	require.NoError(t, err)

	sources, err := store.ListSources(ctx, "wsp_a", "kb_1", 1, 20)
	require.NoError(t, err)
	require.Len(t, sources, 2)
	require.Equal(t, "src_no_commit", sources[0].ID, "sources without projected commits fall back to metadata updated_at for ordering")
	require.Equal(t, fallbackUpdate, sources[0].UpdatedAt)
	require.Equal(t, "src_commit", sources[1].ID, "projected source commit time should override source metadata updated_at for display")
	require.Equal(t, latestCommit, sources[1].UpdatedAt)
}

func TestListActivitySourcesForKnowledgeBasesSkipsProjectedCommitScan(t *testing.T) {
	store := newTestStore(t)

	sql := store.db.ToSQL(func(tx *gorm.DB) *gorm.DB {
		s := New(tx)
		var sources []Source
		return s.activitySourceRowsQuery(context.Background(), "wsp_a", []string{"kb_1"}).Find(&sources)
	})
	require.NotContains(t, sql, latestKBSourceCommitsAlias, "activity refresh source discovery must not scan projected commit activity")
	require.NotContains(t, sql, "resource_activity_events", "activity refresh source discovery only needs source identity and metadata")
}

func TestStoreListsPublicKnowledgeBasesAndHonorsExplicitMemberDenials(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 1, 0, 0, 0, 0, time.UTC)
	bases := []KnowledgeBase{
		{ID: "kb_public", WorkspaceID: "wsp_a", Name: "Public", DefaultWorkspaceMemberRole: "view", IsActive: true, CreatedBy: "owner", UpdatedBy: "owner", CreatedAt: now, UpdatedAt: now},
		{ID: "kb_private", WorkspaceID: "wsp_a", Name: "Private", DefaultWorkspaceMemberRole: "none", IsActive: true, CreatedBy: "owner", UpdatedBy: "owner", CreatedAt: now, UpdatedAt: now.Add(time.Minute)},
		{ID: "kb_private_granted", WorkspaceID: "wsp_a", Name: "Private Granted", DefaultWorkspaceMemberRole: "none", IsActive: true, CreatedBy: "owner", UpdatedBy: "owner", CreatedAt: now, UpdatedAt: now.Add(2 * time.Minute)},
		{ID: "kb_public_denied", WorkspaceID: "wsp_a", Name: "Public Denied", DefaultWorkspaceMemberRole: "view", IsActive: true, CreatedBy: "owner", UpdatedBy: "owner", CreatedAt: now, UpdatedAt: now.Add(3 * time.Minute)},
		{ID: "kb_viewer_owned", WorkspaceID: "wsp_a", Name: "Viewer Owned", DefaultWorkspaceMemberRole: "none", IsActive: true, CreatedBy: "viewer", UpdatedBy: "viewer", CreatedAt: now, UpdatedAt: now.Add(4 * time.Minute)},
	}
	for _, base := range bases {
		_, err := store.CreateKnowledgeBase(ctx, base)
		require.NoError(t, err)
	}
	_, err := store.UpsertKnowledgeBaseGrant(ctx, KnowledgeBaseGrant{ID: "kbg_private_granted", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_private_granted", SubjectType: "user", SubjectID: "viewer", Role: "view", CreatedBy: "owner", UpdatedBy: "owner", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)
	_, err = store.UpsertKnowledgeBaseGrant(ctx, KnowledgeBaseGrant{ID: "kbg_public_denied", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_public_denied", SubjectType: "user", SubjectID: "viewer", Role: "none", CreatedBy: "owner", UpdatedBy: "owner", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)
	_, err = store.UpsertKnowledgeBasePin(ctx, KnowledgeBasePin{WorkspaceID: "wsp_a", PrincipalID: "stranger", KnowledgeBaseID: "kb_private", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)

	viewer, err := store.ListKnowledgeBaseCollection(ctx, KnowledgeBaseCollectionOptions{WorkspaceID: "wsp_a", PrincipalID: "viewer", View: "all", Page: 1, PerPage: 10})
	require.NoError(t, err)
	require.ElementsMatch(t, []string{"kb_private_granted", "kb_private", "kb_public", "kb_viewer_owned"}, collectionKnowledgeBaseIDs(viewer))

	viewerOwned, err := store.ListKnowledgeBaseCollection(ctx, KnowledgeBaseCollectionOptions{WorkspaceID: "wsp_a", PrincipalID: "viewer", View: "owned", Page: 1, PerPage: 10})
	require.NoError(t, err)
	require.ElementsMatch(t, []string{"kb_viewer_owned"}, collectionKnowledgeBaseIDs(viewerOwned))

	stranger, err := store.ListKnowledgeBaseCollection(ctx, KnowledgeBaseCollectionOptions{WorkspaceID: "wsp_a", PrincipalID: "stranger", View: "all", Page: 1, PerPage: 10})
	require.NoError(t, err)
	require.ElementsMatch(t, []string{"kb_public", "kb_private", "kb_private_granted", "kb_public_denied", "kb_viewer_owned"}, collectionKnowledgeBaseIDs(stranger))

	owner, err := store.ListKnowledgeBaseCollection(ctx, KnowledgeBaseCollectionOptions{WorkspaceID: "wsp_a", PrincipalID: "owner", View: "all", Page: 1, PerPage: 10})
	require.NoError(t, err)
	require.ElementsMatch(t, []string{"kb_public", "kb_private", "kb_private_granted", "kb_public_denied", "kb_viewer_owned"}, collectionKnowledgeBaseIDs(owner))

	administrator, err := store.ListKnowledgeBaseCollection(ctx, KnowledgeBaseCollectionOptions{WorkspaceID: "wsp_a", PrincipalID: "viewer", BypassPrincipalAccessFilter: true, View: "all", Page: 1, PerPage: 10})
	require.NoError(t, err)
	require.ElementsMatch(t, []string{"kb_public", "kb_private", "kb_private_granted", "kb_public_denied", "kb_viewer_owned"}, collectionKnowledgeBaseIDs(administrator))

	pinned, err := store.ListKnowledgeBaseCollection(ctx, KnowledgeBaseCollectionOptions{WorkspaceID: "wsp_a", PrincipalID: "stranger", View: "pinned", Page: 1, PerPage: 10})
	require.NoError(t, err)
	require.ElementsMatch(t, []string{"kb_private"}, collectionKnowledgeBaseIDs(pinned))
	require.EqualValues(t, 1, pinned.Total)
}

func TestStoreManagesSourceRepositoryAccessOverrides(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 8, 1, 0, 0, 0, time.UTC)

	created, err := store.UpsertKnowledgeBaseSourceRepositoryAccessOverride(ctx, KnowledgeBaseSourceRepositoryAccessOverride{
		WorkspaceID:             " wsp_1 ",
		KnowledgeBaseID:         " kb_1 ",
		GrantID:                 " kbg_1 ",
		SourceID:                " src_1 ",
		RepoRole:                " primary ",
		SubjectType:             " user ",
		SubjectID:               " viewer ",
		RepositoryAccessProfile: KnowledgeBaseRepositoryAccessProfileManage,
		CreatedBy:               " owner ",
		UpdatedBy:               " owner ",
		CreatedAt:               now,
		UpdatedAt:               now,
	})
	require.NoError(t, err)
	require.Equal(t, KnowledgeBaseSourceRepositoryAccessOverrideID("wsp_1", "kb_1", "kbg_1", "src_1", "primary"), created.ID)
	require.Equal(t, "wsp_1", created.WorkspaceID)
	require.Equal(t, "kb_1", created.KnowledgeBaseID)
	require.Equal(t, "kbg_1", created.GrantID)
	require.Equal(t, "src_1", created.SourceID)
	require.Equal(t, "primary", created.RepoRole)
	require.Equal(t, "user", created.SubjectType)
	require.Equal(t, "viewer", created.SubjectID)
	require.Equal(t, KnowledgeBaseRepositoryAccessProfileManage, created.RepositoryAccessProfile)

	updated, err := store.UpsertKnowledgeBaseSourceRepositoryAccessOverride(ctx, KnowledgeBaseSourceRepositoryAccessOverride{
		WorkspaceID:             "wsp_1",
		KnowledgeBaseID:         "kb_1",
		GrantID:                 "kbg_1",
		SourceID:                "src_1",
		RepoRole:                "primary",
		SubjectType:             "user",
		SubjectID:               "viewer",
		RepositoryAccessProfile: KnowledgeBaseRepositoryAccessProfileNone,
		CreatedBy:               "owner",
		UpdatedBy:               "manager",
		CreatedAt:               now,
		UpdatedAt:               now.Add(time.Minute),
	})
	require.NoError(t, err)
	require.Equal(t, created.ID, updated.ID)
	require.Equal(t, KnowledgeBaseRepositoryAccessProfileNone, updated.RepositoryAccessProfile)
	require.Equal(t, "manager", updated.UpdatedBy)

	for _, repoRole := range []string{"canonical", "insights", "mirror"} {
		_, err = store.UpsertKnowledgeBaseSourceRepositoryAccessOverride(ctx, KnowledgeBaseSourceRepositoryAccessOverride{
			WorkspaceID: "wsp_1", KnowledgeBaseID: "kb_1", GrantID: "kbg_bound", SourceID: "src_bound", RepoRole: repoRole,
			SubjectType: "user", SubjectID: "bounded", RepositoryAccessProfile: KnowledgeBaseRepositoryAccessProfileRead,
			CreatedBy: "owner", UpdatedBy: "owner", CreatedAt: now, UpdatedAt: now,
		})
		require.NoError(t, err)
	}
	bounded, err := store.ListKnowledgeBaseSourceRepositoryAccessOverridesForSources(ctx, "wsp_1", []string{"kbg_bound"}, []string{"src_bound"})
	require.NoError(t, err)
	require.Len(t, bounded, 3, "the exact batch bound must retain all supported repository roles")
	overridePlan := sqliteQueryPlanDetails(t, store.db,
		"SELECT * FROM knowledge_base_source_repository_access_overrides WHERE workspace_id = ? AND grant_id IN (?) AND source_id IN (?) LIMIT 3",
		"wsp_1", "kbg_bound", "src_bound")
	require.Contains(t, overridePlan, "idx_kb_src_repo_access_override_lookup")

	_, err = store.UpsertKnowledgeBaseSourceRepositoryAccessOverride(ctx, KnowledgeBaseSourceRepositoryAccessOverride{
		WorkspaceID:             "wsp_1",
		KnowledgeBaseID:         "kb_1",
		GrantID:                 "kbg_2",
		SourceID:                "src_1",
		RepoRole:                "primary",
		SubjectType:             "user",
		SubjectID:               "editor",
		RepositoryAccessProfile: KnowledgeBaseRepositoryAccessProfileWrite,
		CreatedBy:               "owner",
		UpdatedBy:               "owner",
		CreatedAt:               now,
		UpdatedAt:               now,
	})
	require.NoError(t, err)
	_, err = store.UpsertKnowledgeBaseSourceRepositoryAccessOverride(ctx, KnowledgeBaseSourceRepositoryAccessOverride{
		WorkspaceID:             "wsp_1",
		KnowledgeBaseID:         "kb_1",
		GrantID:                 "kbg_3",
		SourceID:                "src_2",
		RepoRole:                "primary",
		SubjectType:             "user",
		SubjectID:               "maintainer",
		RepositoryAccessProfile: KnowledgeBaseRepositoryAccessProfileRead,
		CreatedBy:               "owner",
		UpdatedBy:               "owner",
		CreatedAt:               now,
		UpdatedAt:               now,
	})
	require.NoError(t, err)

	rows, err := store.ListKnowledgeBaseSourceRepositoryAccessOverrides(ctx, "wsp_1", "kb_1", []string{"kbg_1", "kbg_1", "kbg_2", ""})
	require.NoError(t, err)
	require.Len(t, rows, 2)

	require.NoError(t, store.DeleteKnowledgeBaseSourceRepositoryAccessOverride(ctx, "wsp_1", "kb_1", "kbg_1", "src_1", "primary"))
	rows, err = store.ListKnowledgeBaseSourceRepositoryAccessOverrides(ctx, "wsp_1", "kb_1", []string{"kbg_1", "kbg_2", "kbg_3"})
	require.NoError(t, err)
	require.Len(t, rows, 2)
	require.Equal(t, "kbg_2", rows[0].GrantID)
	require.Equal(t, "kbg_3", rows[1].GrantID)

	require.NoError(t, store.DeleteKnowledgeBaseSourceRepositoryAccessOverridesForGrant(ctx, "wsp_1", "kb_1", "kbg_2"))
	rows, err = store.ListKnowledgeBaseSourceRepositoryAccessOverrides(ctx, "wsp_1", "kb_1", []string{"kbg_2", "kbg_3"})
	require.NoError(t, err)
	require.Len(t, rows, 1)
	require.Equal(t, "src_2", rows[0].SourceID)

	require.NoError(t, store.DeleteKnowledgeBaseSourceRepositoryAccessOverridesForSource(ctx, "wsp_1", "kb_1", "src_2"))
	rows, err = store.ListKnowledgeBaseSourceRepositoryAccessOverrides(ctx, "wsp_1", "kb_1", []string{"kbg_3"})
	require.NoError(t, err)
	require.Empty(t, rows)
}

func TestStoreListsAgentAccessibleKnowledgeBasesByAgentPermission(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 8, 1, 0, 0, 0, time.UTC)
	_, err := store.CreateKnowledgeBase(ctx, KnowledgeBase{ID: "kb_allowed", WorkspaceID: "ws-1", Name: "Allowed", Description: "A", IsActive: true, CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)
	_, err = store.CreateKnowledgeBase(ctx, KnowledgeBase{ID: "kb_other", WorkspaceID: "ws-1", Name: "Other", Description: "B", IsActive: true, CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)
	_, err = store.CreateKnowledgeBase(ctx, KnowledgeBase{ID: "kb_inactive", WorkspaceID: "ws-1", Name: "Inactive", Description: "C", IsActive: true, CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)
	require.NoError(t, store.db.Model(&KnowledgeBase{}).Where("id = ?", "kb_inactive").Update("is_active", false).Error)
	_, err = store.UpsertKnowledgeBaseGrant(ctx, KnowledgeBaseGrant{ID: "kbg_allowed", WorkspaceID: "ws-1", KnowledgeBaseID: "kb_allowed", SubjectType: "agent", SubjectID: "agt_1", Role: "view", CreatedBy: "admin", UpdatedBy: "admin", CreatedAt: now, UpdatedAt: now.Add(time.Minute)})
	require.NoError(t, err)
	_, err = store.UpsertKnowledgeBaseGrant(ctx, KnowledgeBaseGrant{ID: "kbg_other", WorkspaceID: "ws-1", KnowledgeBaseID: "kb_other", SubjectType: "agent", SubjectID: "agt_2", Role: "view", CreatedBy: "admin", UpdatedBy: "admin", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)
	_, err = store.UpsertKnowledgeBaseGrant(ctx, KnowledgeBaseGrant{ID: "kbg_inactive", WorkspaceID: "ws-1", KnowledgeBaseID: "kb_inactive", SubjectType: "agent", SubjectID: "agt_1", Role: "view", CreatedBy: "admin", UpdatedBy: "admin", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)

	items, err := store.ListAgentAccessibleKnowledgeBases(ctx, "ws-1", "agt_1", false, 1, 50)
	require.NoError(t, err)
	require.Len(t, items, 1)
	require.Equal(t, "kb_allowed", items[0].KnowledgeBase.ID)
	require.Equal(t, "agt_1", items[0].Grant.SubjectID)
}

func TestStoreListsKnowledgeBaseAgentGrantSubjectIDs(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 8, 2, 0, 0, 0, time.UTC)
	grants := []KnowledgeBaseGrant{
		{ID: "kbg_agent_2", WorkspaceID: "ws-1", KnowledgeBaseID: "kb_1", SubjectType: "agent", SubjectID: "agt_2", Role: "view", CreatedBy: "admin", UpdatedBy: "admin", CreatedAt: now, UpdatedAt: now},
		{ID: "kbg_agent_1", WorkspaceID: "ws-1", KnowledgeBaseID: "kb_1", SubjectType: "agent", SubjectID: "agt_1", Role: "view", CreatedBy: "admin", UpdatedBy: "admin", CreatedAt: now, UpdatedAt: now},
		{ID: "kbg_user", WorkspaceID: "ws-1", KnowledgeBaseID: "kb_1", SubjectType: "user", SubjectID: "usr_1", Role: "view", CreatedBy: "admin", UpdatedBy: "admin", CreatedAt: now, UpdatedAt: now},
		{ID: "kbg_none", WorkspaceID: "ws-1", KnowledgeBaseID: "kb_1", SubjectType: "agent", SubjectID: "agt_none", Role: "none", CreatedBy: "admin", UpdatedBy: "admin", CreatedAt: now, UpdatedAt: now},
		{ID: "kbg_other_kb", WorkspaceID: "ws-1", KnowledgeBaseID: "kb_2", SubjectType: "agent", SubjectID: "agt_other_kb", Role: "view", CreatedBy: "admin", UpdatedBy: "admin", CreatedAt: now, UpdatedAt: now},
		{ID: "kbg_other_workspace", WorkspaceID: "ws-2", KnowledgeBaseID: "kb_1", SubjectType: "agent", SubjectID: "agt_other_workspace", Role: "view", CreatedBy: "admin", UpdatedBy: "admin", CreatedAt: now, UpdatedAt: now},
	}
	for _, grant := range grants {
		_, err := store.UpsertKnowledgeBaseGrant(ctx, grant)
		require.NoError(t, err)
	}

	agentIDs, err := store.ListKnowledgeBaseAgentGrantSubjectIDs(ctx, "ws-1", "kb_1")
	require.NoError(t, err)
	require.Equal(t, []string{"agt_1", "agt_2"}, agentIDs)
}

func TestStoreClaimsExpiredRunningJob(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 1, 0, 0, 0, 0, time.UTC)
	notExpired := now.Add(time.Minute)
	expired := now.Add(-time.Minute)
	_, err := store.CreateJob(ctx, Job{ID: "job_busy", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", JobType: "generate_repo_wiki_from_code", Status: "running", LeaseOwner: "worker-a", LeaseUntil: &notExpired, CreatedAt: now.Add(-2 * time.Minute), UpdatedAt: now.Add(-2 * time.Minute)})
	require.NoError(t, err)
	_, err = store.CreateJob(ctx, Job{ID: "job_expired", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", JobType: "generate_repo_wiki_from_code", Status: "running", LeaseOwner: "worker-a", LeaseUntil: &expired, CreatedAt: now.Add(-time.Minute), UpdatedAt: now.Add(-time.Minute)})
	require.NoError(t, err)

	claimed, ok, err := store.ClaimNextQueuedJob(ctx, "generate_repo_wiki_from_code", "worker-b", 30*time.Minute, now)
	require.NoError(t, err)
	require.True(t, ok)
	require.Equal(t, "job_expired", claimed.ID)
	require.Equal(t, "running", claimed.Status)
	require.Equal(t, "worker-b", claimed.LeaseOwner)
	require.Equal(t, 1, claimed.AttemptCount)
	require.NotNil(t, claimed.StartedAt)
	require.NotNil(t, claimed.LeaseUntil)
	require.True(t, claimed.LeaseUntil.After(now))
}

func TestStoreCreateJobDefaultsPriority(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 1, 0, 0, 0, 0, time.UTC)

	created, err := store.CreateJob(ctx, Job{ID: "job_default_priority", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", JobType: "sync_source_mirror", Status: "queued", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)
	require.Equal(t, 100, created.Priority)
}

func TestStoreUpdateLeasedJobRequiresCurrentClaimAttempt(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 1, 0, 0, 0, 0, time.UTC)
	leaseUntil := now.Add(time.Minute)
	_, err := store.CreateJob(ctx, Job{ID: "job_1", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", JobType: "sync_source_mirror", Status: "running", LeaseOwner: "worker-b", LeaseUntil: &leaseUntil, AttemptCount: 2, CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)

	lostUpdate := Job{ID: "job_1", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", JobType: "sync_source_mirror", Status: "succeeded", LeaseOwner: "", LeaseUntil: nil, Progress: `{"stage":"ready"}`, UpdatedAt: now.Add(time.Second)}
	_, ok, err := store.UpdateLeasedJob(ctx, lostUpdate, "worker-a", 1)
	require.NoError(t, err)
	require.False(t, ok)
	loaded, err := store.GetJob(ctx, "wsp_a", "job_1")
	require.NoError(t, err)
	require.Equal(t, "running", loaded.Status)
	require.Equal(t, "worker-b", loaded.LeaseOwner)

	_, ok, err = store.UpdateLeasedJob(ctx, lostUpdate, "worker-b", 1)
	require.NoError(t, err)
	require.False(t, ok)

	updated, ok, err := store.UpdateLeasedJob(ctx, lostUpdate, "worker-b", 2)
	require.NoError(t, err)
	require.True(t, ok)
	require.Equal(t, "succeeded", updated.Status)
	require.Empty(t, updated.LeaseOwner)
}

func TestStoreListsImportProgressJobsForRequestedSources(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 1, 0, 0, 0, 0, time.UTC)
	jobs := []Job{
		{ID: "job_src1_old", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceID: "src_1", JobType: "sync_source_mirror", Status: "failed", CreatedAt: now, UpdatedAt: now},
		{ID: "job_src1_latest", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceID: "src_1", JobType: "generate_repo_wiki_from_code", Status: "succeeded", CreatedAt: now.Add(time.Minute), UpdatedAt: now.Add(time.Minute)},
		{ID: "job_src2_running", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceID: "src_2", JobType: "sync_source_mirror", Status: "running", CreatedAt: now.Add(2 * time.Minute), UpdatedAt: now.Add(2 * time.Minute)},
		{ID: "job_src3_unrequested", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceID: "src_3", JobType: "sync_source_mirror", Status: "running", CreatedAt: now.Add(3 * time.Minute), UpdatedAt: now.Add(3 * time.Minute)},
		{ID: "job_src2_other_type", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceID: "src_2", JobType: "other", Status: "running", CreatedAt: now.Add(4 * time.Minute), UpdatedAt: now.Add(4 * time.Minute)},
	}
	for _, job := range jobs {
		_, err := store.CreateJob(ctx, job)
		require.NoError(t, err)
	}

	active, err := store.ListActiveSourceJobsForSources(ctx, "wsp_a", "kb_1", []string{"src_1", "src_2"}, []string{"sync_source_mirror", "generate_repo_wiki_from_code"})
	require.NoError(t, err)
	require.Len(t, active, 1)
	require.Equal(t, "job_src2_running", active[0].ID)

	terminal, err := store.ListLatestTerminalSourceJobsForSources(ctx, "wsp_a", "kb_1", []string{"src_1", "src_2"}, []string{"sync_source_mirror", "generate_repo_wiki_from_code"})
	require.NoError(t, err)
	require.Len(t, terminal, 1)
	require.Equal(t, "job_src1_latest", terminal[0].ID)
}

func TestStoreCancelSourceJobsCancelsQueuedAndRunning(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 1, 0, 0, 0, 0, time.UTC)
	leaseUntil := now.Add(30 * time.Minute)
	jobs := []Job{
		{ID: "job_queued", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceID: "src_1", JobType: "sync_source_mirror", Status: "queued", CreatedAt: now, UpdatedAt: now},
		{ID: "job_running", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceID: "src_1", JobType: "sync_source_mirror", Status: "running", LeaseOwner: "worker-1", LeaseUntil: &leaseUntil, AttemptCount: 1, CreatedAt: now, UpdatedAt: now},
		{ID: "job_succeeded", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceID: "src_1", JobType: "sync_source_mirror", Status: "succeeded", CreatedAt: now, UpdatedAt: now},
		{ID: "job_other_type", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceID: "src_1", JobType: "other", Status: "queued", CreatedAt: now, UpdatedAt: now},
		{ID: "job_other_source", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceID: "src_2", JobType: "sync_source_mirror", Status: "queued", CreatedAt: now, UpdatedAt: now},
	}
	for _, job := range jobs {
		_, err := store.CreateJob(ctx, job)
		require.NoError(t, err)
	}

	cancelled, err := store.CancelSourceJobs(ctx, "wsp_a", "kb_1", "src_1", "sync_source_mirror", now.Add(time.Second))
	require.NoError(t, err)
	require.Equal(t, int64(2), cancelled)

	queued, err := store.GetJob(ctx, "wsp_a", "job_queued")
	require.NoError(t, err)
	require.Equal(t, "cancelled", queued.Status)
	require.Empty(t, queued.LeaseOwner)
	require.Nil(t, queued.LeaseUntil)
	require.NotNil(t, queued.FinishedAt)

	running, err := store.GetJob(ctx, "wsp_a", "job_running")
	require.NoError(t, err)
	require.Equal(t, "cancelled", running.Status)
	require.Empty(t, running.LeaseOwner)
	require.Nil(t, running.LeaseUntil)

	succeeded, err := store.GetJob(ctx, "wsp_a", "job_succeeded")
	require.NoError(t, err)
	require.Equal(t, "succeeded", succeeded.Status, "terminal jobs must not be cancelled")

	otherType, err := store.GetJob(ctx, "wsp_a", "job_other_type")
	require.NoError(t, err)
	require.Equal(t, "queued", otherType.Status, "other job types must not be cancelled")

	otherSource, err := store.GetJob(ctx, "wsp_a", "job_other_source")
	require.NoError(t, err)
	require.Equal(t, "queued", otherSource.Status, "other sources must not be cancelled")
}

func TestStoreListsRecentJobsForKnowledgeBase(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 1, 0, 0, 0, 0, time.UTC)
	jobs := []Job{
		{ID: "job_old", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceID: "src_1", JobType: "sync_source_mirror", Status: "succeeded", CreatedAt: now, UpdatedAt: now},
		{ID: "job_other_type", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceID: "src_1", JobType: "other", Status: "failed", CreatedAt: now.Add(time.Minute), UpdatedAt: now.Add(time.Minute)},
		{ID: "job_other_kb", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_2", SourceID: "src_1", JobType: "sync_source_mirror", Status: "failed", CreatedAt: now.Add(2 * time.Minute), UpdatedAt: now.Add(2 * time.Minute)},
		{ID: "job_latest", WorkspaceID: "wsp_a", KnowledgeBaseID: "kb_1", SourceID: "src_2", JobType: "generate_repo_wiki_from_code", Status: "failed", CreatedAt: now.Add(3 * time.Minute), UpdatedAt: now.Add(3 * time.Minute)},
	}
	for _, job := range jobs {
		_, err := store.CreateJob(ctx, job)
		require.NoError(t, err)
	}

	recent, err := store.ListRecentJobsForKnowledgeBase(ctx, "wsp_a", "kb_1", []string{"sync_source_mirror", "generate_repo_wiki_from_code"}, 10)
	require.NoError(t, err)
	require.Equal(t, []string{"job_latest", "job_old"}, []string{recent[0].ID, recent[1].ID})

	limited, err := store.ListRecentJobsForKnowledgeBase(ctx, "wsp_a", "kb_1", nil, 1)
	require.NoError(t, err)
	require.Len(t, limited, 1)
	require.Equal(t, "job_latest", limited[0].ID)
}

func TestLatestTerminalSourceJobsQueryAvoidsReservedWindowFunctionAlias(t *testing.T) {
	normalized := strings.ToLower(listLatestTerminalSourceJobsForSourcesSQL)
	require.Contains(t, normalized, "row_number() over")
	require.Contains(t, normalized, " as source_job_rank")
	require.Contains(t, normalized, "where source_job_rank = 1")
	require.NotContains(t, normalized, " as row_number")
	require.NotContains(t, normalized, "where row_number")
}

func TestSourceMirrorUpstreamRegistryMatchesGiteaParitySet(t *testing.T) {
	presets := SourceMirrorUpstreamPresets()
	require.Len(t, presets, 4)
	require.Equal(t, SourceMirrorUpstreamTypeGenericGit, presets[0].Type)

	for _, upstreamType := range []string{
		SourceMirrorUpstreamTypeGenericGit,
		SourceMirrorUpstreamTypeGitHub,
		SourceMirrorUpstreamTypeGitea,
		SourceMirrorUpstreamTypeGitLab,
	} {
		normalized, err := ValidateSourceMirrorUpstreamType(upstreamType)
		require.NoError(t, err)
		require.Equal(t, upstreamType, normalized)
	}

	normalized, err := ValidateSourceMirrorUpstreamType("")
	require.NoError(t, err)
	require.Equal(t, SourceMirrorUpstreamTypeGenericGit, normalized)
	for _, legacy := range []string{
		SourceMirrorUpstreamTypeGogs,
		SourceMirrorUpstreamTypeOneDev,
		SourceMirrorUpstreamTypeGitBucket,
		SourceMirrorUpstreamTypeCodebase,
		SourceMirrorUpstreamTypeCodeCommit,
		"bitbucket",
	} {
		_, err := ValidateSourceMirrorUpstreamType(legacy)
		require.Error(t, err)
	}
}

func TestStorePersistsSourceMirrorMetadataWithoutSecretPlaintext(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 12, 10, 0, 0, 0, time.UTC)
	next := now.Add(time.Hour)
	_, err := store.CreateSource(ctx, Source{
		ID:              "src_1",
		WorkspaceID:     "wsp_1",
		KnowledgeBaseID: "kb_1",
		SourceType:      "codebase",
		Status:          "ready",
		Name:            "Mirror Source",
		Config:          "{}",
		CreatedBy:       "user",
		UpdatedBy:       "user",
		CreatedAt:       now,
		UpdatedAt:       now,
	})
	require.NoError(t, err)

	created, err := store.CreateSourceMirror(ctx, SourceMirror{
		SourceID:        "src_1",
		WorkspaceID:     "wsp_1",
		KnowledgeBaseID: "kb_1",
		UpstreamType:    SourceMirrorUpstreamTypeGitLab,
		RemoteURL:       "https://gitlab.example.com/group/project.git",
		CredentialRef:   "secret://kb/source/src_1/mirror",
		DefaultRef:      "main",
		IntervalSeconds: 3600,
		Enabled:         true,
		SyncStatus:      SourceMirrorSyncStatusQueued,
		NextSyncAt:      &next,
		CreatedBy:       "user",
		UpdatedBy:       "user",
		CreatedAt:       now,
		UpdatedAt:       now,
	})
	require.NoError(t, err)
	require.Equal(t, "secret://kb/source/src_1/mirror", created.CredentialRef)

	loaded, err := store.GetSourceMirror(ctx, "wsp_1", "kb_1", "src_1")
	require.NoError(t, err)
	require.Equal(t, SourceMirrorUpstreamTypeGitLab, loaded.UpstreamType)
	require.Equal(t, "https://gitlab.example.com/group/project.git", loaded.RemoteURL)
	require.Equal(t, "secret://kb/source/src_1/mirror", loaded.CredentialRef)
	require.Equal(t, "main", loaded.DefaultRef)
	require.Equal(t, 3600, loaded.IntervalSeconds)
	require.True(t, loaded.Enabled)
	require.Equal(t, SourceMirrorSyncStatusQueued, loaded.SyncStatus)
	require.Equal(t, next, *loaded.NextSyncAt)
	require.NotContains(t, loaded.RemoteURL, "token")
	require.NotContains(t, loaded.CredentialRef, "plaintext")
}

func TestStoreUpsertsAndUpdatesSourceMirrorState(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	createdAt := time.Date(2026, 6, 12, 10, 0, 0, 0, time.UTC)
	updatedAt := createdAt.Add(time.Minute)
	lastSync := updatedAt.Add(time.Minute)
	lastSuccess := lastSync.Add(time.Minute)

	created, err := store.UpsertSourceMirror(ctx, SourceMirror{
		SourceID:        "src_1",
		WorkspaceID:     "wsp_1",
		KnowledgeBaseID: "kb_1",
		RemoteURL:       "https://git.example.com/org/repo.git",
		DefaultRef:      "main",
		Enabled:         false,
		CreatedBy:       "user",
		UpdatedBy:       "user",
		CreatedAt:       createdAt,
		UpdatedAt:       createdAt,
	})
	require.NoError(t, err)
	require.Equal(t, SourceMirrorUpstreamTypeGenericGit, created.UpstreamType)
	require.Equal(t, SourceMirrorSyncStatusDisabled, created.SyncStatus)

	updated, err := store.UpsertSourceMirror(ctx, SourceMirror{
		SourceID:           "src_1",
		WorkspaceID:        "wsp_1",
		KnowledgeBaseID:    "kb_1",
		UpstreamType:       SourceMirrorUpstreamTypeGitea,
		RemoteURL:          "https://gitea.example.com/org/repo.git",
		CredentialRef:      "secret://mirror/src_1",
		DefaultRef:         "release",
		IntervalSeconds:    7200,
		Enabled:            true,
		SyncStatus:         SourceMirrorSyncStatusSuccess,
		LastSyncAt:         &lastSync,
		LastSuccessAt:      &lastSuccess,
		LastMirroredCommit: "abc123",
		CreatedBy:          "user",
		UpdatedBy:          "admin",
		CreatedAt:          createdAt,
		UpdatedAt:          updatedAt,
	})
	require.NoError(t, err)
	require.Equal(t, SourceMirrorUpstreamTypeGitea, updated.UpstreamType)
	require.Equal(t, "https://gitea.example.com/org/repo.git", updated.RemoteURL)
	require.Equal(t, "secret://mirror/src_1", updated.CredentialRef)
	require.Equal(t, SourceMirrorSyncStatusSuccess, updated.SyncStatus)
	require.Equal(t, "abc123", updated.LastMirroredCommit)

	updated.LastError = "fetch failed"
	updated.SyncStatus = SourceMirrorSyncStatusFailed
	updated.UpdatedAt = updatedAt.Add(time.Minute)
	loaded, err := store.UpdateSourceMirror(ctx, updated)
	require.NoError(t, err)
	require.Equal(t, SourceMirrorSyncStatusFailed, loaded.SyncStatus)
	require.Equal(t, "fetch failed", loaded.LastError)
}

func TestStorePersistsSourceRevisionAuthority(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 8, 12, 0, 0, 0, time.UTC)
	_, err := store.CreateSource(ctx, Source{ID: "src_db", WorkspaceID: "ws-1", KnowledgeBaseID: "kb-1", SourceType: "database_schema", Status: "ready", Name: "Prod schema", IndexingMode: "kb_repo_materialized", SyncStatus: "ready", IndexStatus: "ready", AnalysisStatus: "pending", FreshnessStatus: "unknown", Config: "{}", CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)

	run, err := store.CreateAnalysisRun(ctx, AnalysisRun{ID: "run_1", WorkspaceID: "ws-1", KnowledgeBaseID: "kb-1", SourceID: "src_db", Trigger: "manual", Status: "succeeded", InputKind: "source_repo", OutputRepoRole: "insights", OutputCommitSHA: "ins-sha", Progress: "{}", CreatedBy: "user", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)

	revision, err := store.CreateSourceRevision(ctx, SourceRevision{ID: "rev_1", WorkspaceID: "ws-1", KnowledgeBaseID: "kb-1", SourceID: "src_db", SourceCommitSHA: "src-sha", InsightsCommitSHA: "ins-sha", AnalysisRunID: run.ID, FreshnessStatus: "current", IsCurrent: true, CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)
	current, err := store.GetCurrentSourceRevision(ctx, "ws-1", "kb-1", "src_db")
	require.NoError(t, err)
	require.Equal(t, revision.ID, current.ID)
	require.Equal(t, "ins-sha", current.InsightsCommitSHA)
}

func TestAdvanceSourceRevisionIsIdempotentAndCarriesDashboardToNewCommit(t *testing.T) {
	ctx := context.Background()
	store := newTestStore(t)
	now := time.Date(2026, 6, 1, 0, 0, 0, 0, time.UTC)
	_, err := store.CreateKnowledgeBase(ctx, KnowledgeBase{ID: "kb_rev", WorkspaceID: "ws-1", Slug: "rev", Name: "Revision", RetrievalPolicy: "{}", SourceCollectionState: "{}", CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)
	_, err = store.CreateSource(ctx, Source{ID: "src_rev", WorkspaceID: "ws-1", KnowledgeBaseID: "kb_rev", SourceType: "codebase", Status: "ready", Name: "Revision", Config: "{}", CreatedBy: "user", UpdatedBy: "user", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)

	first, created, err := store.AdvanceSourceRevision(ctx, AdvanceSourceRevisionParams{Revision: SourceRevision{ID: "rev_1", WorkspaceID: "ws-1", KnowledgeBaseID: "kb_rev", SourceID: "src_rev", SourceCommitSHA: "source-1", FreshnessStatus: "current", CreatedAt: now, UpdatedAt: now}})
	require.NoError(t, err)
	require.True(t, created)
	manifest := `{"schemaVersion":1}`
	publishedAt := now.Add(time.Minute)
	require.NoError(t, store.db.WithContext(ctx).Model(&SourceRevision{}).Where("id = ?", first.ID).Updates(map[string]any{"ua_dashboard_commit_sha": "dashboard-1", "ua_dashboard_manifest_json": manifest, "ua_dashboard_published_at": publishedAt}).Error)

	replayed, created, err := store.AdvanceSourceRevision(ctx, AdvanceSourceRevisionParams{Revision: SourceRevision{ID: "rev_retry", WorkspaceID: "ws-1", KnowledgeBaseID: "kb_rev", SourceID: "src_rev", SourceCommitSHA: "source-1", FreshnessStatus: "current", CreatedAt: now.Add(2 * time.Minute), UpdatedAt: now.Add(2 * time.Minute)}})
	require.NoError(t, err)
	require.False(t, created)
	require.Equal(t, first.ID, replayed.ID)
	require.Equal(t, "dashboard-1", replayed.UADashboardCommitSHA)

	next, created, err := store.AdvanceSourceRevision(ctx, AdvanceSourceRevisionParams{ExpectedCurrentSourceCommitSHA: "source-1", EnforceExpectedCurrent: true, Revision: SourceRevision{ID: "rev_2", WorkspaceID: "ws-1", KnowledgeBaseID: "kb_rev", SourceID: "src_rev", SourceCommitSHA: "source-2", UADashboardCommitSHA: "must-not-copy", UADashboardManifestJSON: manifest, UADashboardPublishedAt: &publishedAt, FreshnessStatus: "current", CreatedAt: now.Add(3 * time.Minute), UpdatedAt: now.Add(3 * time.Minute)}})
	require.NoError(t, err)
	require.True(t, created)
	require.Equal(t, "source-2", next.SourceCommitSHA)
	require.Equal(t, "dashboard-1", next.UADashboardCommitSHA)
	require.Equal(t, manifest, next.UADashboardManifestJSON)
	require.Equal(t, publishedAt, *next.UADashboardPublishedAt)
	stale, created, err := store.AdvanceSourceRevision(ctx, AdvanceSourceRevisionParams{ExpectedCurrentSourceCommitSHA: "source-1", EnforceExpectedCurrent: true, Revision: SourceRevision{ID: "rev_stale", WorkspaceID: "ws-1", KnowledgeBaseID: "kb_rev", SourceID: "src_rev", SourceCommitSHA: "source-stale", FreshnessStatus: "current", CreatedAt: now.Add(4 * time.Minute), UpdatedAt: now.Add(4 * time.Minute)}})
	require.NoError(t, err)
	require.False(t, created)
	require.Equal(t, next.ID, stale.ID, "a delayed completion must not roll the current source revision back")
	current, err := store.GetCurrentSourceRevision(ctx, "ws-1", "kb_rev", "src_rev")
	require.NoError(t, err)
	require.Equal(t, "source-2", current.SourceCommitSHA)
}