package skills

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

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

// ErrRepositoryBranchConflict identifies incompatible branch pins for one Skill repository.
var ErrRepositoryBranchConflict = errors.New("skill repository branch conflict")

// ErrSkillSlugConflict indicates that the visibility-scoped skill slug is already reserved.
var ErrSkillSlugConflict = errors.New("skill slug conflict")

// ErrSkillVersionConflict reports reuse of one immutable ContentVersion for a
// different Skill-domain projection.
var ErrSkillVersionConflict = errors.New("skill version conflict")

const MaxAgentAccessibleSkillRows = 2000

// RepositoryBranchConflictError records the existing and requested branch pins.
type RepositoryBranchConflictError struct {
	WorkspaceID     string
	RepoOwner       string
	RepoName        string
	ExistingBranch  string
	RequestedBranch string
}

func (e RepositoryBranchConflictError) Error() string {
	return fmt.Sprintf("skill repository %s/%s is already bound to branch %q and cannot be bound to %q", e.RepoOwner, e.RepoName, e.ExistingBranch, e.RequestedBranch)
}

func (e RepositoryBranchConflictError) Unwrap() error { return ErrRepositoryBranchConflict }

// Store owns resource-runtime Skill collection, repository-branch, agent-binding,
// and grant persistence. Authorization and API translation remain outside it.
type Store struct{ db *gorm.DB }

// New constructs the resource Skill persistence owner.
func New(db *gorm.DB) *Store { return &Store{db: db} }

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

// DB exposes the underlying GORM handle for schema composition and fixtures.
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("skill store is not configured")
	}
	return nil
}

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

// SkillListOptions defines a bounded, workspace-scoped Skill listing query.
type SkillListOptions struct {
	WorkspaceID         string
	SubjectID           string
	AlwaysVisibleSkills []SkillIdentity
	OfficialSkills      []SkillIdentity
	Query               string
	View                string
	IncludeInactive     bool
	Page                int
	PerPage             int
}

// SkillIdentity identifies one reserved Skill collection without trusting a
// slug alone. Official libraries are always matched by both slug and creator.
type SkillIdentity struct {
	Slug      string
	CreatedBy string
}

// SkillPage is the persisted Skill listing result before API conversion.
type SkillPage struct {
	Items   []Skill
	Total   int64
	Page    int
	PerPage int
	HasMore bool
}

const (
	latestSkillCommitsAlias = "latest_skill_commits"

	SkillVisibilityScopePrivate   = "private"
	SkillVisibilityScopeWorkspace = "workspace"
)

func (s *Store) skillQuery(ctx context.Context, opts SkillListOptions) *gorm.DB {
	workspaceID := strings.TrimSpace(opts.WorkspaceID)
	q := s.db.WithContext(ctx).Model(&Skill{}).Where("skills.workspace_id = ? AND skills.deleted_at IS NULL", workspaceID)
	if !opts.IncludeInactive {
		q = q.Where("skills.is_active = ?", true)
	}
	if subjectID := strings.TrimSpace(opts.SubjectID); subjectID != "" {
		visibleClause := "skills.created_by = ? OR EXISTS (SELECT 1 FROM skill_grants WHERE skill_grants.workspace_id = skills.workspace_id AND skill_grants.skill_id = skills.id AND skill_grants.role <> ? AND ((skill_grants.subject_type = ? AND skill_grants.subject_id = ?) OR (skill_grants.subject_type = ? AND skill_grants.subject_id = ?)))"
		visibleArgs := []any{
			subjectID,
			"none",
			"user",
			subjectID,
			"workspace",
			workspaceID,
		}
		if identityClause, identityArgs := skillIdentityPredicate(opts.AlwaysVisibleSkills); identityClause != "" {
			visibleClause += " OR " + identityClause
			visibleArgs = append(visibleArgs, identityArgs...)
		}
		q = q.Where(visibleClause, visibleArgs...)
	}
	if query := strings.TrimSpace(opts.Query); query != "" {
		pattern := "%" + strings.ToLower(query) + "%"
		q = q.Where("LOWER(skills.name) LIKE ? OR LOWER(skills.description) LIKE ? OR LOWER(skills.slug) LIKE ?", pattern, pattern, pattern)
	}
	switch strings.TrimSpace(opts.View) {
	case "created_by_me":
		if subjectID := strings.TrimSpace(opts.SubjectID); subjectID != "" {
			q = q.Where("skills.created_by = ?", subjectID)
		}
	case "official":
		officialClause, officialArgs := skillIdentityPredicate(opts.OfficialSkills)
		if officialClause == "" {
			q = q.Where("1 = 0")
		} else {
			q = q.Where(officialClause, officialArgs...)
		}
	case "shared":
		if subjectID := strings.TrimSpace(opts.SubjectID); subjectID != "" {
			q = q.Where("skills.visibility_scope = ? AND skills.created_by <> ?", SkillVisibilityScopeWorkspace, subjectID)
		} else {
			q = q.Where("skills.visibility_scope = ?", SkillVisibilityScopeWorkspace)
		}
		if officialClause, officialArgs := skillIdentityPredicate(opts.OfficialSkills); officialClause != "" {
			q = q.Where("NOT ("+officialClause+")", officialArgs...)
		}
	}
	return q
}

func skillIdentityPredicate(identities []SkillIdentity) (string, []any) {
	clauses := make([]string, 0, len(identities))
	args := make([]any, 0, len(identities)*2)
	seen := make(map[string]struct{}, len(identities))
	for _, identity := range identities {
		slug := strings.TrimSpace(identity.Slug)
		createdBy := strings.TrimSpace(identity.CreatedBy)
		if slug == "" || createdBy == "" {
			continue
		}
		key := slug + "\x00" + createdBy
		if _, duplicate := seen[key]; duplicate {
			continue
		}
		seen[key] = struct{}{}
		clauses = append(clauses, "(skills.slug = ? AND skills.created_by = ?)")
		args = append(args, slug, createdBy)
	}
	if len(clauses) == 0 {
		return "", nil
	}
	return "(" + strings.Join(clauses, " OR ") + ")", args
}

func joinLatestSkillCommits(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 "+latestSkillCommitsAlias+" ON "+latestSkillCommitsAlias+".workspace_id = skills.workspace_id AND "+latestSkillCommitsAlias+".asset_id = skills.id", strings.TrimSpace(workspaceID), activity.AssetTypeSkill)
}

func (s *Store) skillListRowsQuery(ctx context.Context, opts SkillListOptions) *gorm.DB {
	return joinLatestSkillCommits(s.skillQuery(ctx, opts), opts.WorkspaceID).
		Order("COALESCE(" + latestSkillCommitsAlias + ".last_commit_at, skills.updated_at) DESC").
		Order("skills.name ASC")
}

func (s *Store) decorateSkillCollectionCommitTimes(ctx context.Context, workspaceID string, items []Skill) error {
	if len(items) == 0 {
		return nil
	}
	ids := make([]string, 0, len(items))
	seen := make(map[string]struct{}, len(items))
	for _, item := range items {
		id := strings.TrimSpace(item.ID)
		if id == "" {
			continue
		}
		if _, ok := seen[id]; ok {
			continue
		}
		seen[id] = struct{}{}
		ids = append(ids, id)
	}
	if len(ids) == 0 {
		return nil
	}
	var rows []struct {
		AssetID    string
		OccurredAt time.Time
	}
	err := latestSkillCommitRowsQuery(s.db.WithContext(ctx), workspaceID, ids).Scan(&rows).Error
	if err != nil {
		return fmt.Errorf("decorate skill 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 items {
		if occurredAt, ok := latestByID[strings.TrimSpace(items[i].ID)]; ok {
			items[i].UpdatedAt = occurredAt
		}
	}
	return nil
}

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

func (s *Store) ListSkills(ctx context.Context, opts SkillListOptions) (SkillPage, error) {
	if err := s.ensureDB(); err != nil {
		return SkillPage{}, err
	}
	if opts.Page <= 0 {
		opts.Page = 1
	}
	if opts.PerPage <= 0 {
		opts.PerPage = 50
	}
	offset := (opts.Page - 1) * opts.PerPage
	var total int64
	if err := s.skillQuery(ctx, opts).Count(&total).Error; err != nil {
		return SkillPage{}, fmt.Errorf("count skills: %w", err)
	}
	var items []Skill
	if err := s.skillListRowsQuery(ctx, opts).Limit(opts.PerPage).Offset(offset).Find(&items).Error; err != nil {
		return SkillPage{}, fmt.Errorf("list skills: %w", err)
	}
	if err := s.decorateSkillCollectionCommitTimes(ctx, strings.TrimSpace(opts.WorkspaceID), items); err != nil {
		return SkillPage{}, err
	}
	return SkillPage{Items: items, Total: total, Page: opts.Page, PerPage: opts.PerPage, HasMore: int64(offset+len(items)) < total}, nil
}

func (s *Store) CountWorkspaceSkills(ctx context.Context, workspaceID string, createdBy string, excluded []SkillIdentity) (int64, error) {
	if err := s.ensureDB(); err != nil {
		return 0, err
	}
	query := s.db.WithContext(ctx).Model(&Skill{}).Where("workspace_id = ? AND deleted_at IS NULL", strings.TrimSpace(workspaceID))
	if owner := strings.TrimSpace(createdBy); owner != "" {
		query = query.Where("created_by = ?", owner)
	}
	if excludedClause, excludedArgs := skillIdentityPredicate(excluded); excludedClause != "" {
		query = query.Where("NOT ("+excludedClause+")", excludedArgs...)
	}
	var total int64
	if err := query.Count(&total).Error; err != nil {
		return 0, fmt.Errorf("count workspace skills: %w", err)
	}
	return total, nil
}

// ListVisibleSkills returns the Skill resources visible to a principal. Skills
// are visible to their creator, directly granted user subjects, or a workspace
// grant for the current workspace. A positive maxRows applies maxRows+1
// lookahead so native activity reads fail before authority/projector fanout;
// zero preserves the disabled legacy contract.
func (s *Store) ListVisibleSkills(ctx context.Context, workspaceID, principalID string, includeInactive bool, maxRows int) ([]Skill, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	principalID = strings.TrimSpace(principalID)
	if principalID == "" {
		return []Skill{}, nil
	}
	var items []Skill
	query := s.skillQuery(ctx, SkillListOptions{WorkspaceID: workspaceID, SubjectID: principalID, IncludeInactive: includeInactive}).Order("skills.id ASC")
	if maxRows > 0 {
		query = query.Limit(maxRows + 1)
	}
	if err := query.Find(&items).Error; err != nil {
		return nil, fmt.Errorf("list visible skills: %w", err)
	}
	if maxRows > 0 && len(items) > maxRows {
		return nil, fmt.Errorf("visible skill list exceeds %d rows", maxRows)
	}
	return items, nil
}

func (s *Store) ListAssignableSkills(ctx context.Context, workspaceID string) ([]Skill, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	var items []Skill
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND deleted_at IS NULL AND is_active = ?", strings.TrimSpace(workspaceID), true).
		Order("name ASC").
		Order("slug ASC").
		Find(&items).Error; err != nil {
		return nil, fmt.Errorf("list assignable skills: %w", err)
	}
	return items, nil
}

// ListAssignableSkillsForSubject uses maxRows+1 lookahead when maxRows is
// positive. Native first paint passes the product bound; zero preserves the
// legacy path while Content Store is disabled.
func (s *Store) ListAssignableSkillsForSubject(ctx context.Context, workspaceID, subjectID string, maxRows int) ([]Skill, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	subjectID = strings.TrimSpace(subjectID)
	if subjectID == "" {
		return []Skill{}, nil
	}
	var items []Skill
	query := s.skillQuery(ctx, SkillListOptions{WorkspaceID: workspaceID, SubjectID: subjectID}).Order("name ASC").Order("slug ASC")
	if maxRows > 0 {
		query = query.Limit(maxRows + 1)
	}
	if err := query.Find(&items).Error; err != nil {
		return nil, fmt.Errorf("list assignable skills for subject: %w", err)
	}
	if maxRows > 0 && len(items) > maxRows {
		return nil, fmt.Errorf("assignable skill list exceeds %d rows", maxRows)
	}
	return items, nil
}

func (s *Store) CreateSkill(ctx context.Context, skill Skill) (Skill, error) {
	if err := s.ensureDB(); err != nil {
		return Skill{}, err
	}
	skill.WorkspaceID = strings.TrimSpace(skill.WorkspaceID)
	skill.Slug = strings.TrimSpace(skill.Slug)
	skill.CreatedBy = strings.TrimSpace(skill.CreatedBy)
	skill.VisibilityScope = strings.TrimSpace(skill.VisibilityScope)
	if skill.VisibilityScope == "" {
		skill.VisibilityScope = SkillVisibilityScopePrivate
	}
	skill.VisibilityOwnerID = strings.TrimSpace(skill.VisibilityOwnerID)
	if skill.VisibilityOwnerID == "" {
		skill.VisibilityOwnerID = skill.CreatedBy
	}
	if err := s.db.WithContext(ctx).Select("*").Create(&skill).Error; err != nil {
		if isSkillSlugConflict(err) {
			return Skill{}, fmt.Errorf("create skill: %w", ErrSkillSlugConflict)
		}
		return Skill{}, fmt.Errorf("create skill: %w", err)
	}
	return skill, nil
}

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

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

func (s *Store) GetSkillBySlugAndCreator(ctx context.Context, workspaceID, slug, createdBy string) (Skill, error) {
	if err := s.ensureDB(); err != nil {
		return Skill{}, err
	}
	var skill Skill
	err := s.db.WithContext(ctx).Where("workspace_id = ? AND slug = ? AND created_by = ? AND deleted_at IS NULL", strings.TrimSpace(workspaceID), strings.TrimSpace(slug), strings.TrimSpace(createdBy)).First(&skill).Error
	return skill, err
}

// GetSkillBySlugAndCreatorIncludingDeleted loads an exact reserved identity
// regardless of soft-delete state. Official retirement uses it so an older
// user deletion tombstone cannot keep the retired slug or row indefinitely.
func (s *Store) GetSkillBySlugAndCreatorIncludingDeleted(ctx context.Context, workspaceID, slug, createdBy string) (Skill, error) {
	if err := s.ensureDB(); err != nil {
		return Skill{}, err
	}
	var skill Skill
	err := s.db.WithContext(ctx).
		Unscoped().
		Where("workspace_id = ? AND slug = ? AND created_by = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(slug), strings.TrimSpace(createdBy)).
		First(&skill).Error
	return skill, err
}

func (s *Store) UpdateSkill(ctx context.Context, skill Skill) (Skill, error) {
	if err := s.ensureDB(); err != nil {
		return Skill{}, err
	}
	err := s.db.WithContext(ctx).Model(&Skill{}).Where("workspace_id = ? AND id = ?", strings.TrimSpace(skill.WorkspaceID), strings.TrimSpace(skill.ID)).Updates(map[string]any{
		"slug":                      skill.Slug,
		"name":                      skill.Name,
		"description":               skill.Description,
		"visibility_scope":          skill.VisibilityScope,
		"visibility_owner_id":       skill.VisibilityOwnerID,
		"skill_count":               skill.SkillCount,
		"content_id":                strings.TrimSpace(skill.ContentID),
		"active_content_version_id": strings.TrimSpace(skill.ActiveContentVersionID),
		"is_active":                 skill.IsActive,
		"updated_by":                skill.UpdatedBy,
		"updated_at":                skill.UpdatedAt,
		"deleted_at":                skill.DeletedAt,
	}).Error
	if err != nil {
		if isSkillSlugConflict(err) {
			return Skill{}, fmt.Errorf("update skill: %w", ErrSkillSlugConflict)
		}
		return Skill{}, fmt.Errorf("update skill: %w", err)
	}
	if skill.DeletedAt != nil {
		return skill, nil
	}
	return s.GetSkill(ctx, skill.WorkspaceID, skill.ID)
}

func (s *Store) UpdateOfficialSourceFingerprint(ctx context.Context, workspaceID, skillID, fingerprint, updatedBy string, updatedAt time.Time) (Skill, error) {
	if err := s.ensureDB(); err != nil {
		return Skill{}, err
	}
	workspaceID = strings.TrimSpace(workspaceID)
	skillID = strings.TrimSpace(skillID)
	if workspaceID == "" || skillID == "" {
		return Skill{}, fmt.Errorf("skill identity is required")
	}
	if err := s.db.WithContext(ctx).Model(&Skill{}).
		Where("workspace_id = ? AND id = ? AND deleted_at IS NULL", workspaceID, skillID).
		Updates(map[string]any{
			"official_source_fingerprint": strings.TrimSpace(fingerprint),
			"updated_by":                  strings.TrimSpace(updatedBy),
			"updated_at":                  updatedAt,
		}).Error; err != nil {
		return Skill{}, fmt.Errorf("update official skill fingerprint: %w", err)
	}
	return s.GetSkill(ctx, workspaceID, skillID)
}

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

// PurgeSkill permanently removes a Skill row even when deleted_at is already
// populated and ordinary identity reads exclude it. It is reserved for
// retiring an exact AgentTeam-owned identity.
func (s *Store) PurgeSkill(ctx context.Context, workspaceID, id string) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	result := s.db.WithContext(ctx).
		Unscoped().
		Where("workspace_id = ? AND id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(id)).
		Delete(&Skill{})
	if result.Error != nil {
		return fmt.Errorf("purge skill: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (s *Store) EnsureRepositoryBranch(ctx context.Context, branch SkillRepositoryBranch) (SkillRepositoryBranch, error) {
	if err := s.ensureDB(); err != nil {
		return SkillRepositoryBranch{}, err
	}
	branch.WorkspaceID = strings.TrimSpace(branch.WorkspaceID)
	branch.RepoOwner = strings.TrimSpace(branch.RepoOwner)
	branch.RepoName = strings.TrimSpace(branch.RepoName)
	branch.Branch = strings.TrimSpace(branch.Branch)
	if branch.WorkspaceID == "" || branch.RepoOwner == "" || branch.RepoName == "" || branch.Branch == "" {
		return SkillRepositoryBranch{}, fmt.Errorf("skill repository branch identity is required")
	}
	var existing SkillRepositoryBranch
	err := s.db.WithContext(ctx).Where("workspace_id = ? AND repo_owner = ? AND repo_name = ?", branch.WorkspaceID, branch.RepoOwner, branch.RepoName).First(&existing).Error
	if err == nil {
		if existing.Branch != branch.Branch {
			return SkillRepositoryBranch{}, RepositoryBranchConflictError{WorkspaceID: branch.WorkspaceID, RepoOwner: branch.RepoOwner, RepoName: branch.RepoName, ExistingBranch: existing.Branch, RequestedBranch: branch.Branch}
		}
		existing.UpdatedBy = branch.UpdatedBy
		existing.UpdatedAt = branch.UpdatedAt
		if err := s.db.WithContext(ctx).Model(&SkillRepositoryBranch{}).Where("id = ?", existing.ID).Updates(map[string]any{"updated_by": existing.UpdatedBy, "updated_at": existing.UpdatedAt}).Error; err != nil {
			return SkillRepositoryBranch{}, fmt.Errorf("touch skill repository branch: %w", err)
		}
		return existing, nil
	}
	if !errors.Is(err, gorm.ErrRecordNotFound) {
		return SkillRepositoryBranch{}, err
	}
	if err := s.db.WithContext(ctx).Create(&branch).Error; err != nil {
		var raced SkillRepositoryBranch
		if loadErr := s.db.WithContext(ctx).Where("workspace_id = ? AND repo_owner = ? AND repo_name = ?", branch.WorkspaceID, branch.RepoOwner, branch.RepoName).First(&raced).Error; loadErr == nil && raced.Branch != branch.Branch {
			return SkillRepositoryBranch{}, RepositoryBranchConflictError{WorkspaceID: branch.WorkspaceID, RepoOwner: branch.RepoOwner, RepoName: branch.RepoName, ExistingBranch: raced.Branch, RequestedBranch: branch.Branch}
		}
		return SkillRepositoryBranch{}, fmt.Errorf("create skill repository branch: %w", err)
	}
	return branch, nil
}

func (s *Store) UpsertSkillAgentBinding(ctx context.Context, binding SkillAgentBinding, branch SkillRepositoryBranch) (SkillAgentBinding, error) {
	if err := s.ensureDB(); err != nil {
		return SkillAgentBinding{}, err
	}
	var out SkillAgentBinding
	err := s.Transaction(ctx, func(tx *Store) error {
		hasRepositoryBranch := strings.TrimSpace(branch.WorkspaceID) != "" || strings.TrimSpace(branch.RepoOwner) != "" || strings.TrimSpace(branch.RepoName) != "" || strings.TrimSpace(branch.Branch) != ""
		if hasRepositoryBranch {
			if _, err := tx.EnsureRepositoryBranch(ctx, branch); err != nil {
				return err
			}
		} else if strings.TrimSpace(binding.Branch) != "" {
			return fmt.Errorf("skill binding repository branch projection is required")
		}
		row := map[string]any{
			"id": binding.ID, "workspace_id": binding.WorkspaceID, "agent_id": binding.AgentID,
			"skill_id": binding.SkillID, "branch": binding.Branch, "created_by": binding.CreatedBy,
			"updated_by": binding.UpdatedBy, "created_at": binding.CreatedAt, "updated_at": binding.UpdatedAt,
		}
		if err := tx.db.WithContext(ctx).Model(&SkillAgentBinding{}).Clauses(clause.OnConflict{
			Columns:   []clause.Column{{Name: "workspace_id"}, {Name: "agent_id"}, {Name: "skill_id"}},
			DoUpdates: clause.AssignmentColumns([]string{"branch", "updated_by", "updated_at"}),
		}).Create(row).Error; err != nil {
			return fmt.Errorf("upsert skill agent binding: %w", err)
		}
		loaded, err := tx.GetSkillAgentBinding(ctx, binding.WorkspaceID, binding.AgentID, binding.SkillID)
		if err != nil {
			return err
		}
		out = loaded
		return nil
	})
	return out, err
}

func (s *Store) GetSkillAgentBinding(ctx context.Context, workspaceID, agentID, skillID string) (SkillAgentBinding, error) {
	if err := s.ensureDB(); err != nil {
		return SkillAgentBinding{}, err
	}
	var binding SkillAgentBinding
	err := s.db.WithContext(ctx).Where("workspace_id = ? AND agent_id = ? AND skill_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(agentID), strings.TrimSpace(skillID)).First(&binding).Error
	return binding, err
}

func (s *Store) DeleteSkillAgentBinding(ctx context.Context, workspaceID, agentID, skillID string) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	result := s.db.WithContext(ctx).Where("workspace_id = ? AND agent_id = ? AND skill_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(agentID), strings.TrimSpace(skillID)).Delete(&SkillAgentBinding{})
	if result.Error != nil {
		return fmt.Errorf("delete skill agent binding: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

// DeleteSkillAgentBindingsForSkill removes every agent binding for a skill. It
// is meant to be called inside the DeleteSkill transaction so bindings are
// cleaned up with the skill itself. Deleting zero rows is not an error.
func (s *Store) DeleteSkillAgentBindingsForSkill(ctx context.Context, workspaceID, skillID string) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND skill_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(skillID)).
		Delete(&SkillAgentBinding{}).Error; err != nil {
		return fmt.Errorf("delete skill agent bindings for skill: %w", err)
	}
	return nil
}

// DeleteSkillVersionsForSkill removes only the immutable projections owned by
// the Skill domain. Content Store retains and garbage-collects payload versions
// under its own retention policy.
func (s *Store) DeleteSkillVersionsForSkill(ctx context.Context, workspaceID, skillID string) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND skill_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(skillID)).
		Delete(&SkillVersion{}).Error; err != nil {
		return fmt.Errorf("delete skill versions for skill: %w", err)
	}
	return nil
}

func (s *Store) DeleteSkillRepositoryBranchIfUnused(ctx context.Context, branch SkillRepositoryBranch, skillIDs []string) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	workspaceID := strings.TrimSpace(branch.WorkspaceID)
	repoOwner := strings.TrimSpace(branch.RepoOwner)
	repoName := strings.TrimSpace(branch.RepoName)
	if workspaceID == "" || repoOwner == "" || repoName == "" {
		return nil
	}
	ids := make([]string, 0, len(skillIDs))
	seen := make(map[string]struct{}, len(skillIDs))
	for _, id := range skillIDs {
		id = strings.TrimSpace(id)
		if id == "" {
			continue
		}
		if _, ok := seen[id]; ok {
			continue
		}
		seen[id] = struct{}{}
		ids = append(ids, id)
	}
	if len(ids) == 0 {
		return nil
	}
	var count int64
	if err := s.db.WithContext(ctx).Model(&SkillAgentBinding{}).Where("workspace_id = ? AND skill_id IN ?", workspaceID, ids).Count(&count).Error; err != nil {
		return fmt.Errorf("count skill repo branch bindings: %w", err)
	}
	if count > 0 {
		return nil
	}
	if err := s.db.WithContext(ctx).Where("workspace_id = ? AND repo_owner = ? AND repo_name = ?", workspaceID, repoOwner, repoName).Delete(&SkillRepositoryBranch{}).Error; err != nil {
		return fmt.Errorf("delete unused skill repository branch: %w", err)
	}
	return nil
}

// DeleteSkillRepositoryBranchesForRepositories removes branch pins for exact
// repository identities. Official retirement passes the repository mappings
// that belonged to the retired library, so a pin for an unrelated repository
// (including a user-owned Skill with the same slug) is never matched.
func (s *Store) DeleteSkillRepositoryBranchesForRepositories(ctx context.Context, branches []SkillRepositoryBranch) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	seen := make(map[string]struct{}, len(branches))
	for _, branch := range branches {
		workspaceID := strings.TrimSpace(branch.WorkspaceID)
		repoOwner := strings.TrimSpace(branch.RepoOwner)
		repoName := strings.TrimSpace(branch.RepoName)
		if workspaceID == "" || repoOwner == "" || repoName == "" {
			continue
		}
		key := workspaceID + "\x00" + repoOwner + "\x00" + repoName
		if _, duplicate := seen[key]; duplicate {
			continue
		}
		seen[key] = struct{}{}
		if err := s.db.WithContext(ctx).
			Where("workspace_id = ? AND repo_owner = ? AND repo_name = ?", workspaceID, repoOwner, repoName).
			Delete(&SkillRepositoryBranch{}).Error; err != nil {
			return fmt.Errorf("delete skill repository branch: %w", err)
		}
	}
	return nil
}

// ListWorkspaceSkillAgentBindings uses maxRows+1 lookahead when maxRows is
// positive. Zero retains the unbounded legacy contract.
func (s *Store) ListWorkspaceSkillAgentBindings(ctx context.Context, workspaceID string, maxRows int) ([]SkillAgentBinding, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	var bindings []SkillAgentBinding
	query := s.db.WithContext(ctx).Where("workspace_id = ?", strings.TrimSpace(workspaceID)).Order("agent_id ASC").Order("skill_id ASC")
	if maxRows > 0 {
		query = query.Limit(maxRows + 1)
	}
	if err := query.Find(&bindings).Error; err != nil {
		return nil, fmt.Errorf("list workspace skill agent bindings: %w", err)
	}
	if maxRows > 0 && len(bindings) > maxRows {
		return nil, fmt.Errorf("workspace skill agent binding list exceeds %d rows", maxRows)
	}
	return bindings, nil
}

// ListSkillAgentBindings reads bindings for one exact Skill. Positive maxRows
// uses limit+1 so retirement and other explicit-skill mutations cannot turn
// into a workspace-wide scan before emitting agent access changes.
func (s *Store) ListSkillAgentBindings(ctx context.Context, workspaceID, skillID string, maxRows int) ([]SkillAgentBinding, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	var bindings []SkillAgentBinding
	query := s.db.WithContext(ctx).
		Where("workspace_id = ? AND skill_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(skillID)).
		Order("agent_id ASC")
	if maxRows > 0 {
		query = query.Limit(maxRows + 1)
	}
	if err := query.Find(&bindings).Error; err != nil {
		return nil, fmt.Errorf("list skill agent bindings: %w", err)
	}
	if maxRows > 0 && len(bindings) > maxRows {
		return nil, fmt.Errorf("skill agent binding list exceeds %d rows", maxRows)
	}
	return bindings, nil
}

func (s *Store) GetAgentAccessibleSkills(ctx context.Context, workspaceID, agentID string, maxRows int) ([]AgentAccessibleSkill, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	rowsByAgent, err := s.ListAgentAccessibleSkills(ctx, workspaceID, []string{agentID}, maxRows)
	if err != nil {
		return nil, err
	}
	return rowsByAgent[strings.TrimSpace(agentID)], nil
}

// ListAgentAccessibleSkills applies a maxRows+1 bound before product authority
// batching when maxRows is positive. Zero preserves the legacy contract.
func (s *Store) ListAgentAccessibleSkills(ctx context.Context, workspaceID string, agentIDs []string, maxRows int) (map[string][]AgentAccessibleSkill, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	ids := make([]string, 0, len(agentIDs))
	seen := make(map[string]struct{}, len(agentIDs))
	for _, id := range agentIDs {
		id = strings.TrimSpace(id)
		if id == "" {
			continue
		}
		if _, ok := seen[id]; ok {
			continue
		}
		seen[id] = struct{}{}
		ids = append(ids, id)
	}
	out := make(map[string][]AgentAccessibleSkill, len(ids))
	if len(ids) == 0 {
		return out, nil
	}
	type row struct {
		Skill
		BindingAgentID   string
		BindingID        string
		BindingBranch    string
		BindingCreatedBy string
		BindingUpdatedBy string
		BindingCreatedAt time.Time
		BindingUpdatedAt time.Time
	}
	var rows []row
	query := s.db.WithContext(ctx).
		Table("skills").
		Select("skills.*, skill_agent_bindings.agent_id AS binding_agent_id, skill_agent_bindings.id AS binding_id, skill_agent_bindings.branch AS binding_branch, skill_agent_bindings.created_by AS binding_created_by, skill_agent_bindings.updated_by AS binding_updated_by, skill_agent_bindings.created_at AS binding_created_at, skill_agent_bindings.updated_at AS binding_updated_at").
		Joins("JOIN skill_agent_bindings ON skill_agent_bindings.workspace_id = skills.workspace_id AND skill_agent_bindings.skill_id = skills.id").
		Where("skills.workspace_id = ? AND skills.deleted_at IS NULL AND skills.is_active = ? AND skill_agent_bindings.agent_id IN ?", strings.TrimSpace(workspaceID), true, ids).
		Order("skill_agent_bindings.agent_id ASC").
		Order("skills.slug ASC").Order("skill_agent_bindings.id ASC")
	if maxRows > 0 {
		query = query.Limit(maxRows + 1)
	}
	err := query.Find(&rows).Error
	if err != nil {
		return nil, fmt.Errorf("list agent accessible skills: %w", err)
	}
	if maxRows > 0 && len(rows) > maxRows {
		return nil, fmt.Errorf("agent accessible skill batch exceeds %d rows", maxRows)
	}
	for _, r := range rows {
		agentID := strings.TrimSpace(r.BindingAgentID)
		out[agentID] = append(out[agentID], AgentAccessibleSkill{Skill: r.Skill, Binding: SkillAgentBinding{ID: r.BindingID, WorkspaceID: r.WorkspaceID, AgentID: agentID, SkillID: r.ID, Branch: r.BindingBranch, CreatedBy: r.BindingCreatedBy, UpdatedBy: r.BindingUpdatedBy, CreatedAt: r.BindingCreatedAt, UpdatedAt: r.BindingUpdatedAt}})
	}
	return out, nil
}

// VisibilitySlugExists reports whether a Skill row already occupies the same
// database uniqueness scope as a prospective Skill. Soft-deleted rows remain
// included because the unique index does not include deleted_at. Skills with
// the same slug but a different visibility scope or owner are intentionally
// allowed.
func (s *Store) VisibilitySlugExists(ctx context.Context, workspaceID, visibilityScope, visibilityOwnerID, slug string) (bool, error) {
	if err := s.ensureDB(); err != nil {
		return false, err
	}
	var count int64
	if err := s.db.WithContext(ctx).
		Model(&Skill{}).
		Where(
			"workspace_id = ? AND visibility_scope = ? AND visibility_owner_id = ? AND slug = ?",
			strings.TrimSpace(workspaceID),
			strings.TrimSpace(visibilityScope),
			strings.TrimSpace(visibilityOwnerID),
			strings.TrimSpace(slug),
		).
		Count(&count).Error; err != nil {
		return false, fmt.Errorf("check skill visibility slug: %w", err)
	}
	return count > 0, nil
}

func isSkillSlugConflict(err error) bool {
	if err == nil {
		return false
	}
	text := strings.ToLower(err.Error())
	return strings.Contains(text, "uk_skill_workspace_visibility_slug") ||
		strings.Contains(text, "skills.workspace_id, skills.visibility_scope, skills.visibility_owner_id, skills.slug")
}

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

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

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

func (s *Store) GetEffectiveHumanSkillGrant(ctx context.Context, workspaceID, skillID, subjectID string) (SkillGrant, error) {
	if err := s.ensureDB(); err != nil {
		return SkillGrant{}, err
	}
	var grant SkillGrant
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND skill_id = ? AND role <> ? AND ((subject_type = ? AND subject_id = ?) OR (subject_type = ? AND subject_id = ?))",
			strings.TrimSpace(workspaceID),
			strings.TrimSpace(skillID),
			"none",
			"user",
			strings.TrimSpace(subjectID),
			"workspace",
			strings.TrimSpace(workspaceID),
		).
		Order("CASE WHEN subject_type = 'user' THEN 0 ELSE 1 END").
		First(&grant).Error
	return grant, err
}

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

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

// DeleteSkillGrantsForSkill removes every grant for a skill. It is meant to be
// called inside the DeleteSkill transaction so grants are cleaned up with the
// skill itself.
func (s *Store) DeleteSkillGrantsForSkill(ctx context.Context, workspaceID, skillID string) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND skill_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(skillID)).
		Delete(&SkillGrant{}).Error; err != nil {
		return fmt.Errorf("delete skill grants for skill: %w", err)
	}
	return nil
}