package skills

import (
	"context"
	"errors"
	"testing"
	"time"

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

func newSkillStoreForTest(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(&Skill{}, &SkillVersion{}, &SkillRepositoryBranch{}, &SkillAgentBinding{}, &SkillGrant{}, &activity.ResourceActivityEvent{}, &activity.ResourceActivityLatestCommit{}))
	return New(db)
}

func TestListSkillVersionsUsesBoundedLookaheadAndStableTieBreaker(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 8, 20, 21, 30, 0, 0, time.UTC)
	_, err := store.CreateSkill(ctx, Skill{
		ID: "skill_1", WorkspaceID: "wsp_1", Slug: "reviewer", Name: "Reviewer",
		IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now,
	})
	require.NoError(t, err)
	for _, id := range []string{"skv_a", "skv_c", "skv_b"} {
		_, err := store.CreateSkillVersion(ctx, SkillVersion{
			ID: id, WorkspaceID: "wsp_1", SkillID: "skill_1", ContentID: "ctn_1", ContentVersionID: "ctv_" + id,
			RootDigest: "digest_" + id, FileCount: 1, SizeBytes: 10, SkillCount: 1, CreatedBy: "usr_1", CreatedAt: now,
		})
		require.NoError(t, err)
	}

	rows, err := store.ListSkillVersions(ctx, "wsp_1", "skill_1", 2, 0)
	require.NoError(t, err)
	require.Equal(t, []string{"skv_c", "skv_b", "skv_a"}, []string{rows[0].ID, rows[1].ID, rows[2].ID}, "store returns one bounded lookahead row ordered by created_at then id")

	lastPage, err := store.ListSkillVersions(ctx, "wsp_1", "skill_1", 2, 2)
	require.NoError(t, err)
	require.Equal(t, []string{"skv_a"}, []string{lastPage[0].ID})
}

func TestListSkillAgentBindingsUsesExactBoundedLookahead(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 8, 21, 2, 0, 0, 0, time.UTC)
	for _, binding := range []SkillAgentBinding{
		{ID: "sab_1", WorkspaceID: "ws_1", AgentID: "agt_1", SkillID: "skill_target", CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		{ID: "sab_2", WorkspaceID: "ws_1", AgentID: "agt_2", SkillID: "skill_target", CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		{ID: "sab_other", WorkspaceID: "ws_1", AgentID: "agt_3", SkillID: "skill_other", CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
	} {
		require.NoError(t, store.db.WithContext(ctx).Create(&binding).Error)
	}

	_, err := store.ListSkillAgentBindings(ctx, "ws_1", "skill_target", 1)
	require.ErrorContains(t, err, "skill agent binding list exceeds 1 rows")
	rows, err := store.ListSkillAgentBindings(ctx, "ws_1", "skill_target", 2)
	require.NoError(t, err)
	require.Equal(t, []string{"agt_1", "agt_2"}, []string{rows[0].AgentID, rows[1].AgentID})
}

func TestListVisibleSkillsIncludesUserAndWorkspaceGrants(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 6, 18, 1, 0, 0, 0, time.UTC)
	for _, skill := range []Skill{
		{ID: "skill_owned", WorkspaceID: "ws_1", Slug: "owned", Name: "Owned", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_hidden", WorkspaceID: "ws_1", Slug: "hidden", Name: "Hidden", IsActive: true, CreatedBy: "usr_2", UpdatedBy: "usr_2", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_granted", WorkspaceID: "ws_1", Slug: "granted", Name: "Granted", IsActive: true, CreatedBy: "usr_2", UpdatedBy: "usr_2", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_workspace", WorkspaceID: "ws_1", Slug: "workspace", Name: "Workspace", VisibilityScope: SkillVisibilityScopeWorkspace, VisibilityOwnerID: "ws_1", IsActive: true, CreatedBy: "usr_2", UpdatedBy: "usr_2", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_system", WorkspaceID: "ws_1", Slug: "system-visible", Name: "System Visible", VisibilityScope: SkillVisibilityScopeWorkspace, VisibilityOwnerID: "ws_1", IsActive: true, CreatedBy: "agentteam-system", UpdatedBy: "agentteam-system", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_inactive", WorkspaceID: "ws_1", Slug: "inactive", Name: "Inactive", IsActive: false, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
	} {
		_, err := store.CreateSkill(ctx, skill)
		require.NoError(t, err)
	}
	_, err := store.UpsertSkillGrant(ctx, SkillGrant{ID: "skg_granted", WorkspaceID: "ws_1", SkillID: "skill_granted", SubjectType: "user", SubjectID: "usr_1", Role: "view", CreatedBy: "usr_2", UpdatedBy: "usr_2", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)
	_, err = store.UpsertSkillGrant(ctx, SkillGrant{ID: "skg_workspace", WorkspaceID: "ws_1", SkillID: "skill_workspace", SubjectType: "workspace", SubjectID: "ws_1", Role: "view", CreatedBy: "usr_2", UpdatedBy: "usr_2", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)

	visible, err := store.ListVisibleSkills(ctx, "ws_1", "usr_1", false, 0)
	require.NoError(t, err)
	require.Len(t, visible, 3)
	require.Equal(t, []string{"skill_granted", "skill_owned", "skill_workspace"}, []string{visible[0].ID, visible[1].ID, visible[2].ID})
	_, err = store.ListVisibleSkills(ctx, "ws_1", "usr_1", false, 2)
	require.ErrorContains(t, err, "visible skill list exceeds 2 rows")

	assignable, err := store.ListAssignableSkillsForSubject(ctx, "ws_1", "usr_1", 0)
	require.NoError(t, err)
	require.Len(t, assignable, 3)
	require.Equal(t, []string{"skill_granted", "skill_owned", "skill_workspace"}, []string{assignable[0].ID, assignable[1].ID, assignable[2].ID})

	systemSkill := SkillIdentity{Slug: "system-visible", CreatedBy: "agentteam-system"}
	page, err := store.ListSkills(ctx, SkillListOptions{WorkspaceID: "ws_1", SubjectID: "usr_1", AlwaysVisibleSkills: []SkillIdentity{systemSkill}, Page: 1, PerPage: 20})
	require.NoError(t, err)
	require.Equal(t, int64(4), page.Total)
	require.Equal(t, []string{"skill_granted", "skill_owned", "skill_system", "skill_workspace"}, []string{page.Items[0].ID, page.Items[1].ID, page.Items[2].ID, page.Items[3].ID})

	createdByMe, err := store.ListSkills(ctx, SkillListOptions{WorkspaceID: "ws_1", SubjectID: "usr_1", View: "created_by_me", Page: 1, PerPage: 20})
	require.NoError(t, err)
	require.Equal(t, int64(1), createdByMe.Total)
	require.Equal(t, []string{"skill_owned"}, []string{createdByMe.Items[0].ID})

	official, err := store.ListSkills(ctx, SkillListOptions{WorkspaceID: "ws_1", SubjectID: "usr_1", AlwaysVisibleSkills: []SkillIdentity{systemSkill}, View: "official", OfficialSkills: []SkillIdentity{systemSkill}, Page: 1, PerPage: 20})
	require.NoError(t, err)
	require.Equal(t, int64(1), official.Total)
	require.Equal(t, []string{"skill_system"}, []string{official.Items[0].ID})

	shared, err := store.ListSkills(ctx, SkillListOptions{WorkspaceID: "ws_1", SubjectID: "usr_1", AlwaysVisibleSkills: []SkillIdentity{systemSkill}, View: "shared", OfficialSkills: []SkillIdentity{systemSkill}, Page: 1, PerPage: 20})
	require.NoError(t, err)
	require.Equal(t, int64(1), shared.Total)
	require.Equal(t, []string{"skill_workspace"}, []string{shared.Items[0].ID})

	visible, err = store.ListVisibleSkills(ctx, "ws_1", " ", false, 0)
	require.NoError(t, err)
	require.Empty(t, visible)

	assignable, err = store.ListAssignableSkillsForSubject(ctx, "ws_1", " ", 0)
	require.NoError(t, err)
	require.Empty(t, assignable)
}

func TestListSkillsOfficialIdentitiesUseExactSlugCreatorPairs(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 7, 24, 1, 0, 0, 0, time.UTC)
	officialSkills := []SkillIdentity{
		{Slug: "official-library-a", CreatedBy: "agentteam-system"},
		{Slug: "agentteam-workflow-plan-author-skills", CreatedBy: "agentteam-system"},
	}

	for _, skill := range []Skill{
		{ID: "skill_owned", WorkspaceID: "ws_1", Slug: "owned", Name: "Owned", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_official_a", WorkspaceID: "ws_1", Slug: "official-library-a", Name: "Official Library A", VisibilityScope: SkillVisibilityScopeWorkspace, VisibilityOwnerID: "ws_1", IsActive: true, CreatedBy: "agentteam-system", UpdatedBy: "agentteam-system", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_official_workflow", WorkspaceID: "ws_1", Slug: "agentteam-workflow-plan-author-skills", Name: "AgentTeam Workflow Plan Author", VisibilityScope: SkillVisibilityScopeWorkspace, VisibilityOwnerID: "ws_1", IsActive: true, CreatedBy: "agentteam-system", UpdatedBy: "agentteam-system", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_same_slug_wrong_creator", WorkspaceID: "ws_1", Slug: "official-library-a", Name: "User Library A", VisibilityScope: SkillVisibilityScopePrivate, VisibilityOwnerID: "usr_2", IsActive: true, CreatedBy: "usr_2", UpdatedBy: "usr_2", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_shared", WorkspaceID: "ws_1", Slug: "shared", Name: "Shared", VisibilityScope: SkillVisibilityScopeWorkspace, VisibilityOwnerID: "ws_1", IsActive: true, CreatedBy: "usr_2", UpdatedBy: "usr_2", CreatedAt: now, UpdatedAt: now},
	} {
		_, err := store.CreateSkill(ctx, skill)
		require.NoError(t, err)
	}
	for _, grant := range []SkillGrant{
		{ID: "skg_same_slug_wrong_creator", WorkspaceID: "ws_1", SkillID: "skill_same_slug_wrong_creator", SubjectType: "user", SubjectID: "usr_1", Role: "view", CreatedBy: "usr_2", UpdatedBy: "usr_2", CreatedAt: now, UpdatedAt: now},
		{ID: "skg_shared", WorkspaceID: "ws_1", SkillID: "skill_shared", SubjectType: "user", SubjectID: "usr_1", Role: "view", CreatedBy: "usr_2", UpdatedBy: "usr_2", CreatedAt: now, UpdatedAt: now},
	} {
		_, err := store.UpsertSkillGrant(ctx, grant)
		require.NoError(t, err)
	}

	// The default view makes each exact official identity visible without a
	// grant. A user-owned collection that reuses an official slug must not gain
	// the same treatment.
	unrelated, err := store.ListSkills(ctx, SkillListOptions{
		WorkspaceID:         "ws_1",
		SubjectID:           "usr_unrelated",
		AlwaysVisibleSkills: officialSkills,
		Page:                1,
		PerPage:             20,
	})
	require.NoError(t, err)
	require.Equal(t, int64(2), unrelated.Total)
	require.ElementsMatch(t, []string{"skill_official_a", "skill_official_workflow"}, skillPageIDs(unrelated))

	defaultView, err := store.ListSkills(ctx, SkillListOptions{
		WorkspaceID:         "ws_1",
		SubjectID:           "usr_1",
		AlwaysVisibleSkills: officialSkills,
		Page:                1,
		PerPage:             20,
	})
	require.NoError(t, err)
	require.Equal(t, int64(5), defaultView.Total)
	require.ElementsMatch(t, []string{"skill_owned", "skill_official_a", "skill_official_workflow", "skill_same_slug_wrong_creator", "skill_shared"}, skillPageIDs(defaultView))

	official, err := store.ListSkills(ctx, SkillListOptions{
		WorkspaceID:         "ws_1",
		SubjectID:           "usr_1",
		AlwaysVisibleSkills: officialSkills,
		OfficialSkills:      officialSkills,
		View:                "official",
		Page:                1,
		PerPage:             20,
	})
	require.NoError(t, err)
	require.Equal(t, int64(2), official.Total)
	require.ElementsMatch(t, []string{"skill_official_a", "skill_official_workflow"}, skillPageIDs(official))

	emptyOfficial, err := store.ListSkills(ctx, SkillListOptions{
		WorkspaceID:         "ws_1",
		SubjectID:           "usr_1",
		AlwaysVisibleSkills: officialSkills,
		View:                "official",
		Page:                1,
		PerPage:             20,
	})
	require.NoError(t, err)
	require.Zero(t, emptyOfficial.Total)
	require.Empty(t, emptyOfficial.Items)

	shared, err := store.ListSkills(ctx, SkillListOptions{
		WorkspaceID:         "ws_1",
		SubjectID:           "usr_1",
		AlwaysVisibleSkills: officialSkills,
		OfficialSkills:      officialSkills,
		View:                "shared",
		Page:                1,
		PerPage:             20,
	})
	require.NoError(t, err)
	require.Equal(t, int64(1), shared.Total)
	require.ElementsMatch(t, []string{"skill_shared"}, skillPageIDs(shared))
}

func TestCreateSkillUniquenessIncludesVisibilityOwner(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 7, 8, 1, 0, 0, 0, time.UTC)
	deletedAt := now.Add(time.Hour)

	for _, skill := range []Skill{
		{ID: "skill_alice", WorkspaceID: "ws_1", Slug: "abc", Name: "A private abc", VisibilityScope: SkillVisibilityScopePrivate, VisibilityOwnerID: "usr_a", IsActive: true, CreatedBy: "usr_a", UpdatedBy: "usr_a", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_bob", WorkspaceID: "ws_1", Slug: "abc", Name: "B private abc", VisibilityScope: SkillVisibilityScopePrivate, VisibilityOwnerID: "usr_b", IsActive: true, CreatedBy: "usr_b", UpdatedBy: "usr_b", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_workspace", WorkspaceID: "ws_1", Slug: "abc", Name: "Team abc", VisibilityScope: SkillVisibilityScopeWorkspace, VisibilityOwnerID: "ws_1", IsActive: true, CreatedBy: "usr_a", UpdatedBy: "usr_a", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_retired", WorkspaceID: "ws_1", Slug: "retired", Name: "Retired workspace Skill", VisibilityScope: SkillVisibilityScopeWorkspace, VisibilityOwnerID: "ws_1", IsActive: true, CreatedBy: "usr_a", UpdatedBy: "usr_a", CreatedAt: now, UpdatedAt: now, DeletedAt: &deletedAt},
	} {
		_, err := store.CreateSkill(ctx, skill)
		require.NoError(t, err)
	}

	_, err := store.CreateSkill(ctx, Skill{ID: "skill_alice_duplicate", WorkspaceID: "ws_1", Slug: "abc", Name: "A duplicate", VisibilityScope: SkillVisibilityScopePrivate, VisibilityOwnerID: "usr_a", IsActive: true, CreatedBy: "usr_a", UpdatedBy: "usr_a", CreatedAt: now, UpdatedAt: now})
	require.Error(t, err)

	for _, test := range []struct {
		name              string
		visibilityScope   string
		visibilityOwnerID string
		want              bool
	}{
		{name: "alice private", visibilityScope: SkillVisibilityScopePrivate, visibilityOwnerID: "usr_a", want: true},
		{name: "bob private", visibilityScope: SkillVisibilityScopePrivate, visibilityOwnerID: "usr_b", want: true},
		{name: "other private owner", visibilityScope: SkillVisibilityScopePrivate, visibilityOwnerID: "usr_c", want: false},
		{name: "workspace", visibilityScope: SkillVisibilityScopeWorkspace, visibilityOwnerID: "ws_1", want: true},
		{name: "other workspace owner", visibilityScope: SkillVisibilityScopeWorkspace, visibilityOwnerID: "ws_2", want: false},
	} {
		t.Run(test.name, func(t *testing.T) {
			exists, err := store.VisibilitySlugExists(
				ctx,
				"ws_1",
				test.visibilityScope,
				test.visibilityOwnerID,
				"abc",
			)
			require.NoError(t, err)
			require.Equal(t, test.want, exists)
		})
	}

	exists, err := store.VisibilitySlugExists(
		ctx,
		"ws_1",
		SkillVisibilityScopeWorkspace,
		"ws_1",
		"retired",
	)
	require.NoError(t, err)
	require.True(t, exists, "soft-deleted rows still occupy the database uniqueness scope")
}

func TestCountWorkspaceSkillsExcludesExactOfficialIdentities(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 6, 26, 1, 0, 0, 0, time.UTC)
	deletedAt := now.Add(time.Hour)
	for _, skill := range []Skill{
		{ID: "skill_owned_1", WorkspaceID: "ws_1", Slug: "owned-1", Name: "Owned 1", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_owned_2", WorkspaceID: "ws_1", Slug: "owned-2", Name: "Owned 2", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_other", WorkspaceID: "ws_1", Slug: "other", Name: "Other", IsActive: true, CreatedBy: "usr_2", UpdatedBy: "usr_2", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_deleted", WorkspaceID: "ws_1", Slug: "deleted", Name: "Deleted", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now, DeletedAt: &deletedAt},
		{ID: "skill_other_workspace", WorkspaceID: "ws_2", Slug: "other-workspace", Name: "Other workspace", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_official_a", WorkspaceID: "ws_1", Slug: "official-library-a", Name: "Official Library A", VisibilityScope: SkillVisibilityScopeWorkspace, VisibilityOwnerID: "ws_1", IsActive: true, CreatedBy: "agentteam-system", UpdatedBy: "agentteam-system", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_official_workflow", WorkspaceID: "ws_1", Slug: "agentteam-workflow-plan-author-skills", Name: "AgentTeam Workflow Plan Author", VisibilityScope: SkillVisibilityScopeWorkspace, VisibilityOwnerID: "ws_1", IsActive: true, CreatedBy: "agentteam-system", UpdatedBy: "agentteam-system", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_nonofficial_system", WorkspaceID: "ws_1", Slug: "system-helper", Name: "System Helper", VisibilityOwnerID: "agentteam-system", IsActive: true, CreatedBy: "agentteam-system", UpdatedBy: "agentteam-system", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_same_slug_wrong_creator", WorkspaceID: "ws_1", Slug: "official-library-a", Name: "User Library A", VisibilityOwnerID: "usr_1", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
	} {
		_, err := store.CreateSkill(ctx, skill)
		require.NoError(t, err)
	}

	officialSkills := []SkillIdentity{
		{Slug: "official-library-a", CreatedBy: "agentteam-system"},
		{Slug: "agentteam-workflow-plan-author-skills", CreatedBy: "agentteam-system"},
	}

	total, err := store.CountWorkspaceSkills(ctx, "ws_1", "", officialSkills)
	require.NoError(t, err)
	require.EqualValues(t, 5, total, "only the two exact official identities are excluded")

	total, err = store.CountWorkspaceSkills(ctx, "ws_1", "agentteam-system", officialSkills)
	require.NoError(t, err)
	require.EqualValues(t, 1, total, "non-official skills from the system creator still count")

	total, err = store.CountWorkspaceSkills(ctx, "ws_1", "usr_1", officialSkills)
	require.NoError(t, err)
	require.EqualValues(t, 3, total, "reusing an official slug under another creator must still count")
}

func TestPurgeSkillPermanentlyRemovesSoftDeletedRow(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 8, 4, 1, 0, 0, 0, time.UTC)
	deletedAt := now.Add(time.Minute)
	skill, err := store.CreateSkill(ctx, Skill{
		ID:                "skill_to_purge",
		WorkspaceID:       "ws_1",
		Slug:              "retired-official",
		Name:              "Retired Official",
		VisibilityScope:   SkillVisibilityScopeWorkspace,
		VisibilityOwnerID: "ws_1",
		IsActive:          true,
		CreatedBy:         "agentteam-system",
		UpdatedBy:         "agentteam-system",
		CreatedAt:         now,
		UpdatedAt:         now,
		DeletedAt:         &deletedAt,
	})
	require.NoError(t, err)
	require.NoError(t, store.PurgeSkill(ctx, skill.WorkspaceID, skill.ID))

	var count int64
	require.NoError(t, store.DB().Unscoped().Model(&Skill{}).Where("id = ?", skill.ID).Count(&count).Error)
	require.Zero(t, count)
	exists, err := store.VisibilitySlugExists(
		ctx,
		skill.WorkspaceID,
		skill.VisibilityScope,
		skill.VisibilityOwnerID,
		skill.Slug,
	)
	require.NoError(t, err)
	require.False(t, exists)
}

func TestGetSkillBySlugAndCreatorIncludingDeletedUsesExactIdentity(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 8, 4, 1, 0, 0, 0, time.UTC)
	deletedAt := now.Add(time.Minute)
	for _, skill := range []Skill{
		{
			ID: "retired_exact", WorkspaceID: "ws_1", Slug: "retired-official",
			Name: "Retired", VisibilityScope: SkillVisibilityScopeWorkspace, VisibilityOwnerID: "ws_1",
			IsActive: true, CreatedBy: "agentteam-system", UpdatedBy: "agentteam-system", CreatedAt: now, UpdatedAt: now,
			DeletedAt: &deletedAt,
		},
		{
			ID: "user_same_slug", WorkspaceID: "ws_1", Slug: "retired-official",
			Name: "User skill", VisibilityScope: SkillVisibilityScopePrivate, VisibilityOwnerID: "usr_1",
			IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now,
		},
	} {
		_, err := store.CreateSkill(ctx, skill)
		require.NoError(t, err)
	}

	got, err := store.GetSkillBySlugAndCreatorIncludingDeleted(ctx, "ws_1", "retired-official", "agentteam-system")
	require.NoError(t, err)
	require.Equal(t, "retired_exact", got.ID)
	require.NotNil(t, got.DeletedAt)
}

func TestDeleteSkillRepositoryBranchesForRepositoriesUsesExactRepositoryIdentity(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 8, 4, 1, 0, 0, 0, time.UTC)
	for _, branch := range []SkillRepositoryBranch{
		{ID: "srb_retired", WorkspaceID: "ws_1", RepoOwner: "ws_1", RepoName: "retired.sk", Branch: "main", CreatedBy: "agentteam-system", UpdatedBy: "agentteam-system", CreatedAt: now, UpdatedAt: now},
		{ID: "srb_same_name_other_owner", WorkspaceID: "ws_1", RepoOwner: "usr_1", RepoName: "retired.sk", Branch: "main", CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		{ID: "srb_same_repo_other_workspace", WorkspaceID: "ws_2", RepoOwner: "ws_1", RepoName: "retired.sk", Branch: "main", CreatedBy: "agentteam-system", UpdatedBy: "agentteam-system", CreatedAt: now, UpdatedAt: now},
	} {
		_, err := store.EnsureRepositoryBranch(ctx, branch)
		require.NoError(t, err)
	}

	require.NoError(t, store.DeleteSkillRepositoryBranchesForRepositories(ctx, []SkillRepositoryBranch{{
		WorkspaceID: "ws_1",
		RepoOwner:   "ws_1",
		RepoName:    "retired.sk",
	}}))

	var remaining []SkillRepositoryBranch
	require.NoError(t, store.DB().Order("id ASC").Find(&remaining).Error)
	require.ElementsMatch(t, []string{"srb_same_name_other_owner", "srb_same_repo_other_workspace"}, repositoryBranchIDsForTest(remaining))
}

func repositoryBranchIDsForTest(branches []SkillRepositoryBranch) []string {
	ids := make([]string, 0, len(branches))
	for _, branch := range branches {
		ids = append(ids, branch.ID)
	}
	return ids
}

func skillPageIDs(page SkillPage) []string {
	ids := make([]string, 0, len(page.Items))
	for _, item := range page.Items {
		ids = append(ids, item.ID)
	}
	return ids
}

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

	_, err := store.CreateSkill(ctx, Skill{ID: "skill_commit", WorkspaceID: "ws_1", Slug: "commit", Name: "Commit-backed", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: base, UpdatedAt: metadataUpdate})
	require.NoError(t, err)
	_, err = store.CreateSkill(ctx, Skill{ID: "skill_no_commit", WorkspaceID: "ws_1", Slug: "fallback", Name: "Metadata fallback", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: base, UpdatedAt: fallbackUpdate})
	require.NoError(t, err)
	_, err = activity.New(store.db).UpsertEvent(ctx, activity.ResourceActivityEvent{
		ID:          "act_skill_old_commit",
		WorkspaceID: "ws_1",
		AssetType:   activity.AssetTypeSkill,
		AssetID:     "skill_commit",
		EventType:   "commit_pushed",
		OccurredAt:  base.Add(30 * time.Minute),
	})
	require.NoError(t, err)
	_, err = activity.New(store.db).UpsertEvent(ctx, activity.ResourceActivityEvent{
		ID:          "act_skill_latest_commit",
		WorkspaceID: "ws_1",
		AssetType:   activity.AssetTypeSkill,
		AssetID:     "skill_commit",
		EventType:   "commit_pushed",
		OccurredAt:  latestCommit,
	})
	require.NoError(t, err)

	page, err := store.ListSkills(ctx, SkillListOptions{WorkspaceID: "ws_1", SubjectID: "usr_1", Page: 1, PerPage: 20})
	require.NoError(t, err)
	require.Len(t, page.Items, 2)
	require.Equal(t, "skill_no_commit", page.Items[0].ID, "skills without projected commits fall back to metadata updated_at for ordering")
	require.Equal(t, fallbackUpdate, page.Items[0].UpdatedAt)
	require.Equal(t, "skill_commit", page.Items[1].ID, "projected commit time should override metadata updated_at even when metadata is newer")
	require.Equal(t, latestCommit, page.Items[1].UpdatedAt)
}

func TestSkillListCommitQueryReadsLatestCommitProjection(t *testing.T) {
	store := newSkillStoreForTest(t)

	sqlStr := store.db.ToSQL(func(tx *gorm.DB) *gorm.DB {
		s := New(tx)
		var items []Skill
		return s.skillListRowsQuery(context.Background(), SkillListOptions{
			WorkspaceID: "wsp_a", SubjectID: "usr_1",
		}).Find(&items)
	})

	require.Contains(t, sqlStr, "resource_activity_latest_commits")
	require.Contains(t, sqlStr, `workspace_id = "wsp_a"`)
	require.Contains(t, sqlStr, `asset_type = "skill"`)
	require.Contains(t, sqlStr, "asset_id = skills.id")
	require.NotContains(t, sqlStr, "resource_activity_events")
}

func TestSkillDecorationCommitQueryReadsLatestCommitProjection(t *testing.T) {
	store := newSkillStoreForTest(t)

	sqlStr := store.db.ToSQL(func(tx *gorm.DB) *gorm.DB {
		var rows []struct {
			AssetID    string
			OccurredAt time.Time
		}
		return latestSkillCommitRowsQuery(tx, "wsp_a", []string{"skill_a", "skill_b"}).Find(&rows)
	})

	require.Contains(t, sqlStr, "resource_activity_latest_commits")
	require.Contains(t, sqlStr, `workspace_id = "wsp_a"`)
	require.Contains(t, sqlStr, `asset_type = "skill"`)
	require.Contains(t, sqlStr, `asset_id IN ("skill_a","skill_b")`)
	require.NotContains(t, sqlStr, "resource_activity_events")
}

func TestListAssignableSkillsReturnsActiveWorkspaceSkills(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 6, 18, 1, 0, 0, 0, time.UTC)
	deletedAt := now.Add(time.Minute)

	for _, skill := range []Skill{
		{ID: "skill_b", WorkspaceID: "ws_1", Slug: "beta", Name: "Beta", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_a", WorkspaceID: "ws_1", Slug: "alpha", Name: "Alpha", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_inactive", WorkspaceID: "ws_1", Slug: "inactive", Name: "Inactive", IsActive: false, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_deleted", WorkspaceID: "ws_1", Slug: "deleted", Name: "Deleted", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now, DeletedAt: &deletedAt},
		{ID: "skill_other", WorkspaceID: "ws_2", Slug: "other", Name: "Other", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
	} {
		_, err := store.CreateSkill(ctx, skill)
		require.NoError(t, err)
	}

	items, err := store.ListAssignableSkills(ctx, "ws_1")
	require.NoError(t, err)
	require.Len(t, items, 2)
	require.Equal(t, []string{"skill_a", "skill_b"}, []string{items[0].ID, items[1].ID})
}

func TestEnsureRepositoryBranchIsIdempotentForSameRepoAndBranch(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 6, 18, 1, 0, 0, 0, time.UTC)
	branch := SkillRepositoryBranch{ID: "srb_1", WorkspaceID: "ws_1", RepoOwner: "usr_1", RepoName: "reviewer.sk", Branch: "main", CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now}

	created, err := store.EnsureRepositoryBranch(ctx, branch)
	require.NoError(t, err)
	require.Equal(t, "main", created.Branch)

	branch.ID = "srb_2"
	branch.UpdatedBy = "usr_2"
	branch.UpdatedAt = now.Add(time.Hour)
	updated, err := store.EnsureRepositoryBranch(ctx, branch)
	require.NoError(t, err)
	require.Equal(t, created.ID, updated.ID)
	require.Equal(t, "main", updated.Branch)
	require.Equal(t, "usr_2", updated.UpdatedBy)

	var count int64
	require.NoError(t, store.DB().Model(&SkillRepositoryBranch{}).Count(&count).Error)
	require.EqualValues(t, 1, count)
}

func TestEnsureRepositoryBranchRejectsDifferentBranchForSameRepo(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 6, 18, 1, 0, 0, 0, time.UTC)
	_, err := store.EnsureRepositoryBranch(ctx, SkillRepositoryBranch{ID: "srb_1", WorkspaceID: "ws_1", RepoOwner: "usr_1", RepoName: "reviewer.sk", Branch: "main", CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)

	_, err = store.EnsureRepositoryBranch(ctx, SkillRepositoryBranch{ID: "srb_2", WorkspaceID: "ws_1", RepoOwner: "usr_1", RepoName: "reviewer.sk", Branch: "dev", CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now})
	require.ErrorIs(t, err, ErrRepositoryBranchConflict)
	var conflict RepositoryBranchConflictError
	require.True(t, errors.As(err, &conflict))
	require.Equal(t, "main", conflict.ExistingBranch)
	require.Equal(t, "dev", conflict.RequestedBranch)
}

func TestUpsertSkillAgentBindingEnforcesRepoBranchConstraint(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 6, 18, 1, 0, 0, 0, time.UTC)
	skill := Skill{ID: "skill_1", WorkspaceID: "ws_1", Slug: "reviewer", Name: "Reviewer", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now}
	_, err := store.CreateSkill(ctx, skill)
	require.NoError(t, err)
	binding := SkillAgentBinding{ID: "sab_1", WorkspaceID: "ws_1", AgentID: "agt_1", SkillID: "skill_1", Branch: "main", CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now}
	branch := SkillRepositoryBranch{ID: "srb_1", WorkspaceID: "ws_1", RepoOwner: "usr_1", RepoName: "reviewer.sk", Branch: binding.Branch, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now}

	created, err := store.UpsertSkillAgentBinding(ctx, binding, branch)
	require.NoError(t, err)
	require.Equal(t, "main", created.Branch)

	binding.Branch = "release"
	branch.Branch = "release"
	_, err = store.UpsertSkillAgentBinding(ctx, binding, branch)
	require.ErrorIs(t, err, ErrRepositoryBranchConflict)
}

func TestUpsertSkillAgentBindingAllowsNativeBindingWithoutRepositoryBranch(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 6, 18, 1, 0, 0, 0, time.UTC)
	_, err := store.CreateSkill(ctx, Skill{ID: "skill_native", WorkspaceID: "ws_1", Slug: "native", Name: "Native", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now})
	require.NoError(t, err)

	created, err := store.UpsertSkillAgentBinding(ctx, SkillAgentBinding{ID: "sab_native", WorkspaceID: "ws_1", AgentID: "agt_1", SkillID: "skill_native", CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now}, SkillRepositoryBranch{})
	require.NoError(t, err)
	require.Empty(t, created.Branch)
	var branches int64
	require.NoError(t, store.DB().Model(&SkillRepositoryBranch{}).Count(&branches).Error)
	require.Zero(t, branches)

	_, err = store.UpsertSkillAgentBinding(ctx, SkillAgentBinding{ID: "sab_legacy", WorkspaceID: "ws_1", AgentID: "agt_2", SkillID: "skill_native", Branch: "main", CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now}, SkillRepositoryBranch{})
	require.ErrorContains(t, err, "repository branch projection is required")
}

func TestListAgentAccessibleSkillsBatchesAgents(t *testing.T) {
	ctx := context.Background()
	store := newSkillStoreForTest(t)
	now := time.Date(2026, 6, 18, 1, 0, 0, 0, time.UTC)
	skills := []Skill{
		{ID: "skill_reviewer", WorkspaceID: "ws_1", Slug: "reviewer", Name: "Reviewer", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_planner", WorkspaceID: "ws_1", Slug: "planner", Name: "Planner", IsActive: true, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_inactive", WorkspaceID: "ws_1", Slug: "inactive", Name: "Inactive", IsActive: false, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
	}
	for _, skill := range skills {
		_, err := store.CreateSkill(ctx, skill)
		require.NoError(t, err)
	}
	for _, binding := range []struct {
		id      string
		agentID string
		skillID string
		repo    string
		branch  string
	}{
		{id: "sab_reviewer", agentID: "agt_1", skillID: "skill_reviewer", repo: "reviewer.sk", branch: "main"},
		{id: "sab_planner", agentID: "agt_2", skillID: "skill_planner", repo: "planner.sk", branch: "dev"},
		{id: "sab_inactive", agentID: "agt_inactive", skillID: "skill_inactive", repo: "inactive.sk", branch: "main"},
	} {
		_, err := store.UpsertSkillAgentBinding(ctx,
			SkillAgentBinding{ID: binding.id, WorkspaceID: "ws_1", AgentID: binding.agentID, SkillID: binding.skillID, Branch: binding.branch, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
			SkillRepositoryBranch{ID: "srb_" + binding.id, WorkspaceID: "ws_1", RepoOwner: "usr_1", RepoName: binding.repo, Branch: binding.branch, CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now},
		)
		require.NoError(t, err)
	}

	rowsByAgent, err := store.ListAgentAccessibleSkills(ctx, "ws_1", []string{"agt_1", "agt_2", "agt_inactive", "agt_1", ""}, 0)
	require.NoError(t, err)
	require.Equal(t, "skill_reviewer", rowsByAgent["agt_1"][0].Skill.ID)
	require.Equal(t, "skill_planner", rowsByAgent["agt_2"][0].Skill.ID)
	require.Empty(t, rowsByAgent["agt_inactive"])
}