package repos

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

	"gorm.io/gorm"
	"gorm.io/gorm/clause"
)

// Store owns persistence and transaction boundaries for resource-to-AGS
// repository bindings, lifecycle compensation records, and collaborator
// materialization state. It does not own AgentTeam server repositories/projects,
// workspace membership, or embedded AGS repository storage.
type Store struct{ db *gorm.DB }

// UpsertResult reports the binding persisted by Upsert and whether the unique
// resource/role binding was newly created rather than updating an existing row.
type UpsertResult struct {
	Repository ResourceRepository
	Created    bool
}

func New(db *gorm.DB) *Store               { return &Store{db: db} }
func (s *Store) WithDB(db *gorm.DB) *Store { return &Store{db: db} }

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

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

func (s *Store) Upsert(ctx context.Context, repo ResourceRepository) (UpsertResult, error) {
	if err := s.ensureDB(); err != nil {
		return UpsertResult{}, err
	}
	repo.WorkspaceID = strings.TrimSpace(repo.WorkspaceID)
	repo.ResourceType = strings.TrimSpace(repo.ResourceType)
	repo.ResourceID = strings.TrimSpace(repo.ResourceID)
	repo.RepoRole = strings.TrimSpace(repo.RepoRole)
	if repo.WorkspaceID == "" || repo.ResourceType == "" || repo.ResourceID == "" || repo.RepoRole == "" {
		return UpsertResult{}, fmt.Errorf("resource repository identity is required")
	}
	if repo.RepoFullName == "" && repo.RepoOwner != "" && repo.RepoName != "" {
		repo.RepoFullName = repo.RepoOwner + "/" + repo.RepoName
	}
	created := false
	var existing ResourceRepository
	err := s.db.WithContext(ctx).
		Select("id").
		Where("workspace_id = ? AND resource_type = ? AND resource_id = ? AND repo_role = ?", repo.WorkspaceID, repo.ResourceType, repo.ResourceID, repo.RepoRole).
		First(&existing).Error
	if err == nil {
		created = false
	} else if err == gorm.ErrRecordNotFound {
		created = true
	} else {
		return UpsertResult{}, fmt.Errorf("lookup resource repository: %w", err)
	}
	if err := s.db.WithContext(ctx).Clauses(clause.OnConflict{
		Columns: []clause.Column{
			{Name: "workspace_id"},
			{Name: "resource_type"},
			{Name: "resource_id"},
			{Name: "repo_role"},
		},
		DoUpdates: clause.AssignmentColumns([]string{
			"owner_kind",
			"repo_owner",
			"repo_name",
			"repo_full_name",
			"ags_repo_id",
			"default_ref",
			"visibility",
			"user_visible",
			"updated_by",
			"updated_at",
		}),
	}).Create(&repo).Error; err != nil {
		return UpsertResult{}, fmt.Errorf("upsert resource repository: %w", err)
	}
	stored, err := s.Get(ctx, repo.WorkspaceID, repo.ResourceType, repo.ResourceID, repo.RepoRole)
	if err != nil {
		return UpsertResult{}, err
	}
	return UpsertResult{Repository: stored, Created: created}, nil
}

func (s *Store) Get(ctx context.Context, workspaceID, resourceType, resourceID, repoRole string) (ResourceRepository, error) {
	if err := s.ensureDB(); err != nil {
		return ResourceRepository{}, err
	}
	var repo ResourceRepository
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND resource_type = ? AND resource_id = ? AND repo_role = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(resourceType), strings.TrimSpace(resourceID), strings.TrimSpace(repoRole)).
		First(&repo).Error
	if err != nil {
		return ResourceRepository{}, err
	}
	return repo, nil
}

// UpdateDefaultRef updates one explicitly identified resource repository.
// Its cost is bounded by the resource/role unique key and it never enumerates
// unrelated repository mappings.
func (s *Store) UpdateDefaultRef(ctx context.Context, workspaceID, resourceType, resourceID, repoRole, defaultRef string, updatedAt time.Time) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	result := s.db.WithContext(ctx).Model(&ResourceRepository{}).
		Where("workspace_id = ? AND resource_type = ? AND resource_id = ? AND repo_role = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(resourceType), strings.TrimSpace(resourceID), strings.TrimSpace(repoRole)).
		Updates(map[string]any{"default_ref": strings.TrimSpace(defaultRef), "updated_at": updatedAt.UTC()})
	if result.Error != nil {
		return fmt.Errorf("update resource repository default ref: %w", result.Error)
	}
	if result.RowsAffected != 1 {
		return fmt.Errorf("update resource repository default ref: expected one row, updated %d", result.RowsAffected)
	}
	return nil
}

func (s *Store) ListForResource(ctx context.Context, workspaceID, resourceType, resourceID string) ([]ResourceRepository, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	var repos []ResourceRepository
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND resource_type = ? AND resource_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(resourceType), strings.TrimSpace(resourceID)).
		Order("repo_role ASC").
		Find(&repos).Error
	if err != nil {
		return nil, fmt.Errorf("list resource repositories: %w", err)
	}
	return repos, nil
}

func (s *Store) ListByRepoFullName(ctx context.Context, repoFullName string) ([]ResourceRepository, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	normalized := strings.ToLower(strings.TrimSpace(repoFullName))
	if normalized == "" {
		return []ResourceRepository{}, nil
	}
	var repos []ResourceRepository
	err := s.db.WithContext(ctx).
		Where("LOWER(repo_full_name) = ?", normalized).
		Order("workspace_id ASC, repo_role ASC, resource_type ASC, resource_id ASC").
		Find(&repos).Error
	if err != nil {
		return nil, fmt.Errorf("list resource repositories by full name: %w", err)
	}
	return repos, nil
}

// IsBoundToWorkspace checks one explicit workspace/repository pair. The query
// is bounded by the canonical repository identity from the request and never
// enumerates repositories from another workspace.
func (s *Store) IsBoundToWorkspace(ctx context.Context, workspaceID, repoFullName string) (bool, error) {
	if err := s.ensureDB(); err != nil {
		return false, err
	}
	workspaceID = strings.TrimSpace(workspaceID)
	repoFullName = strings.TrimSpace(repoFullName)
	if workspaceID == "" || repoFullName == "" {
		return false, nil
	}
	var row struct{ ID string }
	result := s.db.WithContext(ctx).
		Model(&ResourceRepository{}).
		Select("id").
		Where("workspace_id = ? AND repo_full_name = ?", workspaceID, repoFullName).
		Limit(1).
		Take(&row)
	if result.Error == gorm.ErrRecordNotFound {
		return false, nil
	}
	if result.Error != nil {
		return false, fmt.Errorf("check workspace repository binding: %w", result.Error)
	}
	return strings.TrimSpace(row.ID) != "", nil
}

// ResolveUniqueAutomationRepository resolves an Automation repository when a
// legacy AGS caller has no workspace-qualified route. The repository name is
// request-bound, the product selectors are fixed, and LIMIT 2 is intentional:
// one row identifies the exact workspace while two rows make the legacy
// request ambiguous and therefore unsafe to route after a native cutover.
func (s *Store) ResolveUniqueAutomationRepository(ctx context.Context, repoFullName string) (ResourceRepository, bool, error) {
	if err := s.ensureDB(); err != nil {
		return ResourceRepository{}, false, err
	}
	repoFullName = strings.TrimSpace(repoFullName)
	if repoFullName == "" {
		return ResourceRepository{}, false, nil
	}
	var rows []ResourceRepository
	if err := s.db.WithContext(ctx).
		Model(&ResourceRepository{}).
		Select("workspace_id", "repo_full_name").
		Where(
			"resource_type = ? AND resource_id = ? AND repo_role = ?",
			ResourceTypeAutomationRepository,
			AutomationRepositoryResourceID(repoFullName),
			RepoRolePrimary,
		).
		Limit(2).
		Find(&rows).Error; err != nil {
		return ResourceRepository{}, false, fmt.Errorf("resolve Automation repository workspace: %w", err)
	}
	switch len(rows) {
	case 0:
		return ResourceRepository{}, false, nil
	case 1:
		if !strings.EqualFold(strings.TrimSpace(rows[0].RepoFullName), repoFullName) {
			return ResourceRepository{}, false, fmt.Errorf("Automation repository binding does not match canonical repository")
		}
		rows[0].WorkspaceID = strings.TrimSpace(rows[0].WorkspaceID)
		rows[0].RepoFullName = strings.TrimSpace(rows[0].RepoFullName)
		if rows[0].WorkspaceID == "" {
			return ResourceRepository{}, false, fmt.Errorf("Automation repository workspace is empty")
		}
		return rows[0], true, nil
	default:
		return ResourceRepository{}, false, fmt.Errorf("Automation repository workspace is ambiguous")
	}
}

func (s *Store) ListByAGSRepoID(ctx context.Context, agsRepoID string) ([]ResourceRepository, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	agsRepoID = strings.TrimSpace(agsRepoID)
	if agsRepoID == "" {
		return []ResourceRepository{}, nil
	}
	var repos []ResourceRepository
	err := s.db.WithContext(ctx).
		Where("ags_repo_id = ?", agsRepoID).
		Order("workspace_id ASC, resource_type ASC, resource_id ASC, repo_role ASC").
		Find(&repos).Error
	if err != nil {
		return nil, fmt.Errorf("list resource repositories by AGS repo id: %w", err)
	}
	return repos, nil
}

// ListActivityProjectorRepositoriesOptions bounds activity-projector source
// discovery to an explicit limit and resource-type/repository-role selectors.
// It is an internal store query contract, not an API repository search request.
type ListActivityProjectorRepositoriesOptions struct {
	Limit     int
	Selectors []ActivityProjectorRepositorySelector
}

// ActivityProjectorRepositorySelector narrows activity source discovery to one
// resource type and one or more repository roles. It selects persisted resource
// bindings, not arbitrary AgentTeam or embedded AGS repositories.
type ActivityProjectorRepositorySelector struct {
	ResourceType string
	RepoRoles    []string
}

// ListActivityProjectorRepositories returns bounded activity-projection
// repository bindings matching explicit resource/role selectors. Permission
// checks intentionally do not happen here; activity list APIs filter visible
// assets at read time.
func (s *Store) ListActivityProjectorRepositories(ctx context.Context, opts ListActivityProjectorRepositoriesOptions) ([]ResourceRepository, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	limit := opts.Limit
	if limit <= 0 {
		limit = 25
	}
	if limit > 500 {
		limit = 500
	}
	selectors := normalizeActivityProjectorRepositorySelectors(opts.Selectors)
	if len(selectors) == 0 {
		return []ResourceRepository{}, nil
	}
	var repos []ResourceRepository
	query := s.db.WithContext(ctx).
		Model(&ResourceRepository{}).
		Select("resource_repositories.*").
		Joins("LEFT JOIN (SELECT workspace_id, resource_type, resource_id, MIN(last_polled_at) AS last_activity_polled_at FROM resource_activity_projector_checkpoints GROUP BY workspace_id, resource_type, resource_id) rac ON rac.workspace_id = resource_repositories.workspace_id AND rac.resource_type = resource_repositories.resource_type AND rac.resource_id = resource_repositories.resource_id").
		Where("resource_repositories.repo_full_name <> ?", "")
	query = applyActivityProjectorRepositorySelectors(query, selectors)
	if err := query.
		Order("CASE WHEN rac.last_activity_polled_at IS NULL THEN 0 ELSE 1 END ASC").
		Order("rac.last_activity_polled_at ASC").
		Order("resource_repositories.updated_at ASC").
		Order("resource_repositories.id ASC").
		Limit(limit).
		Find(&repos).Error; err != nil {
		return nil, fmt.Errorf("list activity projector repositories: %w", err)
	}
	return repos, nil
}

func applyActivityProjectorRepositorySelectors(query *gorm.DB, selectors []ActivityProjectorRepositorySelector) *gorm.DB {
	clauses := make([]string, 0, len(selectors))
	args := make([]any, 0, len(selectors)*2)
	for _, selector := range selectors {
		if len(selector.RepoRoles) == 1 {
			clauses = append(clauses, "(resource_repositories.resource_type = ? AND resource_repositories.repo_role = ?)")
			args = append(args, selector.ResourceType, selector.RepoRoles[0])
			continue
		}
		clauses = append(clauses, "(resource_repositories.resource_type = ? AND resource_repositories.repo_role IN ?)")
		args = append(args, selector.ResourceType, selector.RepoRoles)
	}
	return query.Where(strings.Join(clauses, " OR "), args...)
}

func normalizeActivityProjectorRepositorySelectors(selectors []ActivityProjectorRepositorySelector) []ActivityProjectorRepositorySelector {
	out := make([]ActivityProjectorRepositorySelector, 0, len(selectors))
	for _, selector := range selectors {
		resourceType := strings.TrimSpace(selector.ResourceType)
		roles := uniqueActivityProjectorSelectorValues(selector.RepoRoles)
		if resourceType == "" || len(roles) == 0 {
			continue
		}
		out = append(out, ActivityProjectorRepositorySelector{ResourceType: resourceType, RepoRoles: roles})
	}
	return out
}

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

// ListForResourceBatch returns resource repositories for many resource IDs in
// a single query. Callers pass the same (workspaceID, resourceType) for every
// entry; only resourceID varies. Returns an empty slice when ids is empty so
// callers can branch without a special case.
func (s *Store) ListForResourceBatch(ctx context.Context, workspaceID, resourceType string, resourceIDs []string) ([]ResourceRepository, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	ids := make([]string, 0, len(resourceIDs))
	for _, id := range resourceIDs {
		if trimmed := strings.TrimSpace(id); trimmed != "" {
			ids = append(ids, trimmed)
		}
	}
	if len(ids) == 0 {
		return nil, nil
	}
	var repos []ResourceRepository
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND resource_type = ? AND resource_id IN ?", strings.TrimSpace(workspaceID), strings.TrimSpace(resourceType), ids).
		Order("resource_id ASC").
		Order("repo_role ASC").
		Find(&repos).Error
	if err != nil {
		return nil, fmt.Errorf("list resource repositories batch: %w", err)
	}
	return repos, nil
}

func (s *Store) DeleteForResource(ctx context.Context, workspaceID, resourceType, resourceID string) ([]ResourceRepository, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	workspaceID = strings.TrimSpace(workspaceID)
	resourceType = strings.TrimSpace(resourceType)
	resourceID = strings.TrimSpace(resourceID)
	var deleted []ResourceRepository
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		if err := tx.Where("workspace_id = ? AND resource_type = ? AND resource_id = ?", workspaceID, resourceType, resourceID).Order("repo_role ASC").Find(&deleted).Error; err != nil {
			return fmt.Errorf("list resource repositories before delete: %w", err)
		}
		if err := tx.Where("workspace_id = ? AND resource_type = ? AND resource_id = ?", workspaceID, resourceType, resourceID).Delete(&ResourceRepositoryAccessGrant{}).Error; err != nil {
			return fmt.Errorf("delete resource repository access grants: %w", err)
		}
		if err := tx.Where("workspace_id = ? AND resource_type = ? AND resource_id = ?", workspaceID, resourceType, resourceID).Delete(&ResourceRepository{}).Error; err != nil {
			return fmt.Errorf("delete resource repositories: %w", err)
		}
		return nil
	})
	if err != nil {
		return nil, err
	}
	return deleted, nil
}

func (s *Store) CreateLifecycleEvent(ctx context.Context, event ResourceRepositoryLifecycleEvent) (ResourceRepositoryLifecycleEvent, error) {
	if err := s.ensureDB(); err != nil {
		return ResourceRepositoryLifecycleEvent{}, err
	}
	event = normalizeLifecycleEvent(event)
	if event.ID == "" || event.WorkspaceID == "" || event.ResourceType == "" || event.ResourceID == "" || event.RepoRole == "" || event.Operation == "" || event.Status == "" {
		return ResourceRepositoryLifecycleEvent{}, fmt.Errorf("resource repository lifecycle event identity is required")
	}
	if err := s.db.WithContext(ctx).Create(&event).Error; err != nil {
		return ResourceRepositoryLifecycleEvent{}, fmt.Errorf("create resource repository lifecycle event: %w", err)
	}
	return event, nil
}

func (s *Store) UpdateLifecycleEventStatus(ctx context.Context, id, status, lastError, updatedBy string, updatedAt time.Time) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	if updatedAt.IsZero() {
		updatedAt = time.Now().UTC()
	}
	result := s.db.WithContext(ctx).Model(&ResourceRepositoryLifecycleEvent{}).
		Where("id = ?", strings.TrimSpace(id)).
		Updates(map[string]any{
			"status":     strings.TrimSpace(status),
			"last_error": strings.TrimSpace(lastError),
			"updated_by": strings.TrimSpace(updatedBy),
			"updated_at": updatedAt,
		})
	if result.Error != nil {
		return fmt.Errorf("update resource repository lifecycle event: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (s *Store) ListLifecycleEventsForResource(ctx context.Context, workspaceID, resourceType, resourceID string) ([]ResourceRepositoryLifecycleEvent, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	var events []ResourceRepositoryLifecycleEvent
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND resource_type = ? AND resource_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(resourceType), strings.TrimSpace(resourceID)).
		Order("created_at ASC").
		Find(&events).Error
	if err != nil {
		return nil, fmt.Errorf("list resource repository lifecycle events: %w", err)
	}
	return events, nil
}

func (s *Store) UpsertAccessGrant(ctx context.Context, grant ResourceRepositoryAccessGrant) (ResourceRepositoryAccessGrant, error) {
	if err := s.ensureDB(); err != nil {
		return ResourceRepositoryAccessGrant{}, err
	}
	grant = normalizeAccessGrant(grant)
	if grant.WorkspaceID == "" || grant.ResourceType == "" || grant.ResourceID == "" || grant.RepoRole == "" ||
		grant.RepoFullName == "" || grant.SubjectType == "" || grant.SubjectID == "" ||
		grant.Permission == "" || grant.IntentType == "" || grant.IntentID == "" {
		return ResourceRepositoryAccessGrant{}, fmt.Errorf("resource repository access grant identity is required")
	}
	grant.ID = ResourceRepositoryAccessGrantID(grant.WorkspaceID, grant.ResourceType, grant.ResourceID, grant.RepoRole, grant.SubjectType, grant.SubjectID, grant.IntentType, grant.IntentID)
	if grant.Status == "" {
		grant.Status = AccessStatusPending
	}
	if err := s.db.WithContext(ctx).Clauses(clause.OnConflict{
		Columns: []clause.Column{
			{Name: "workspace_id"},
			{Name: "resource_type"},
			{Name: "resource_id"},
			{Name: "repo_role"},
			{Name: "subject_type"},
			{Name: "subject_id"},
			{Name: "intent_type"},
			{Name: "intent_id"},
		},
		DoUpdates: clause.AssignmentColumns([]string{
			"repo_full_name",
			"ags_repo_id",
			"permission",
			"intent_profile",
			"materialized",
			"status",
			"last_error",
			"updated_by",
			"updated_at",
			"reconciled_by",
			"revoked_by",
			"last_reconciled_at",
			"revoked_at",
		}),
	}).Create(&grant).Error; err != nil {
		return ResourceRepositoryAccessGrant{}, fmt.Errorf("upsert resource repository access grant: %w", err)
	}
	return s.GetAccessGrant(ctx, grant.WorkspaceID, grant.ID)
}

func normalizeLifecycleEvent(event ResourceRepositoryLifecycleEvent) ResourceRepositoryLifecycleEvent {
	event.ID = strings.TrimSpace(event.ID)
	event.WorkspaceID = strings.TrimSpace(event.WorkspaceID)
	event.ResourceType = strings.TrimSpace(event.ResourceType)
	event.ResourceID = strings.TrimSpace(event.ResourceID)
	event.RepoRole = strings.TrimSpace(event.RepoRole)
	event.RepoOwner = strings.TrimSpace(event.RepoOwner)
	event.RepoName = strings.TrimSpace(event.RepoName)
	event.RepoFullName = strings.TrimSpace(event.RepoFullName)
	if event.RepoFullName == "" && event.RepoOwner != "" && event.RepoName != "" {
		event.RepoFullName = event.RepoOwner + "/" + event.RepoName
	}
	event.AGSRepoID = strings.TrimSpace(event.AGSRepoID)
	event.Operation = strings.TrimSpace(event.Operation)
	event.Status = strings.TrimSpace(event.Status)
	event.LastError = strings.TrimSpace(event.LastError)
	event.CreatedBy = strings.TrimSpace(event.CreatedBy)
	event.UpdatedBy = strings.TrimSpace(event.UpdatedBy)
	now := time.Now().UTC()
	if event.CreatedAt.IsZero() {
		event.CreatedAt = now
	}
	if event.UpdatedAt.IsZero() {
		event.UpdatedAt = event.CreatedAt
	}
	return event
}

func normalizeAccessGrant(grant ResourceRepositoryAccessGrant) ResourceRepositoryAccessGrant {
	grant.WorkspaceID = strings.TrimSpace(grant.WorkspaceID)
	grant.ResourceType = strings.TrimSpace(grant.ResourceType)
	grant.ResourceID = strings.TrimSpace(grant.ResourceID)
	grant.RepoRole = strings.TrimSpace(grant.RepoRole)
	grant.RepoFullName = strings.TrimSpace(grant.RepoFullName)
	grant.AGSRepoID = strings.TrimSpace(grant.AGSRepoID)
	grant.SubjectType = strings.TrimSpace(grant.SubjectType)
	grant.SubjectID = strings.TrimSpace(grant.SubjectID)
	grant.Permission = strings.TrimSpace(grant.Permission)
	grant.IntentType = strings.TrimSpace(grant.IntentType)
	grant.IntentID = strings.TrimSpace(grant.IntentID)
	grant.IntentProfile = strings.TrimSpace(grant.IntentProfile)
	grant.Status = strings.TrimSpace(grant.Status)
	grant.LastError = strings.TrimSpace(grant.LastError)
	grant.CreatedBy = strings.TrimSpace(grant.CreatedBy)
	grant.UpdatedBy = strings.TrimSpace(grant.UpdatedBy)
	grant.ReconciledBy = strings.TrimSpace(grant.ReconciledBy)
	grant.RevokedBy = strings.TrimSpace(grant.RevokedBy)
	return grant
}

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

func (s *Store) ListAccessGrantsForIntent(ctx context.Context, workspaceID, intentType, intentID string) ([]ResourceRepositoryAccessGrant, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	var grants []ResourceRepositoryAccessGrant
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND intent_type = ? AND intent_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(intentType), strings.TrimSpace(intentID)).
		Order("resource_type ASC").Order("resource_id ASC").Order("repo_role ASC").
		Find(&grants).Error; err != nil {
		return nil, fmt.Errorf("list resource repository access grants for intent: %w", err)
	}
	return grants, nil
}

func (s *Store) ListAccessGrantsByIntentIDs(ctx context.Context, workspaceID, intentType string, intentIDs []string) ([]ResourceRepositoryAccessGrant, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	ids := make([]string, 0, len(intentIDs))
	seen := make(map[string]struct{}, len(intentIDs))
	for _, id := range intentIDs {
		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 []ResourceRepositoryAccessGrant{}, nil
	}
	var grants []ResourceRepositoryAccessGrant
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND intent_type = ? AND intent_id IN ?", strings.TrimSpace(workspaceID), strings.TrimSpace(intentType), ids).
		Order("intent_id ASC").Order("resource_type ASC").Order("resource_id ASC").Order("repo_role ASC").
		Find(&grants).Error; err != nil {
		return nil, fmt.Errorf("list resource repository access grants by intent ids: %w", err)
	}
	return grants, nil
}

func (s *Store) ListActiveAccessGrantsForRepositorySubject(ctx context.Context, workspaceID, repoFullName, subjectType, subjectID string) ([]ResourceRepositoryAccessGrant, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	repoFullName = strings.TrimSpace(repoFullName)
	if repoFullName == "" {
		return []ResourceRepositoryAccessGrant{}, nil
	}
	var grants []ResourceRepositoryAccessGrant
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND repo_full_name = ? AND subject_type = ? AND subject_id = ? AND status <> ?", strings.TrimSpace(workspaceID), repoFullName, strings.TrimSpace(subjectType), strings.TrimSpace(subjectID), AccessStatusRevoked).
		Order("intent_type ASC").Order("intent_id ASC").Order("resource_type ASC").Order("resource_id ASC").Order("repo_role ASC").
		Find(&grants).Error; err != nil {
		return nil, fmt.Errorf("list active resource repository access grants for repository subject: %w", err)
	}
	return grants, nil
}