package roles

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

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

// Store owns resource runtime Role catalog, version, grant, assignment, and
// realization persistence. It does not authorize operations or expose API DTOs,
// and it is distinct from the Approval-driven rolemanagement execution store.
type Store struct{ db *gorm.DB }

// ErrRoleSlugConflict indicates that the workspace-scoped role slug is already reserved.
var ErrRoleSlugConflict = errors.New("role slug conflict")

const (
	// VisibilityPrivate restricts a resource Role to its owner and explicit grants.
	VisibilityPrivate = "private"
	// VisibilityWorkspace exposes a resource Role to its workspace.
	VisibilityWorkspace = "workspace"

	// ListViewAll applies no additional resource Role catalog view filter.
	ListViewAll = "all"
	// ListViewWorkspace selects workspace-visible resource Roles.
	ListViewWorkspace = "workspace"
	// ListViewCreatedByMe selects resource Roles created by the principal.
	ListViewCreatedByMe = "created_by_me"
	// ListViewPrivate selects private resource Roles.
	ListViewPrivate = "private"

	// MaxRoleVersionPageSize bounds one exact Role history query before any
	// manifest hydration or content resolution.
	MaxRoleVersionPageSize = 100
	// MaxRoleVersionPageOffset prevents deep OFFSET scans on immutable history.
	MaxRoleVersionPageOffset = 10_000
	// MaxRolePackageManifestItems is the migration bound for one exact Role
	// package; the lookahead query fails before legacy payload fanout.
	MaxRolePackageManifestItems = 256
)

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

// DB exposes the bound GORM handle to the resource Role service composition.
func (s *Store) DB() *gorm.DB {
	if s == nil {
		return nil
	}
	return s.db
}

// WithDB binds the same resource Role persistence owner to another transaction or database handle.
func (s *Store) WithDB(db *gorm.DB) *Store { return &Store{db: db} }

// Transaction runs resource Role persistence work in one GORM transaction.
func (s *Store) Transaction(ctx context.Context, fn func(*Store) error) error {
	if s == nil || s.db == nil {
		return fmt.Errorf("role store is not configured")
	}
	return s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		return fn(s.WithDB(tx))
	})
}

// GetRoleOperationReceipt loads one workspace-scoped resource Role operation receipt.
func (s *Store) GetRoleOperationReceipt(ctx context.Context, workspaceID, operationKey string) (RoleOperationReceipt, error) {
	var receipt RoleOperationReceipt
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND operation_key = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(operationKey)).
		First(&receipt).Error
	return receipt, err
}

// ReserveRoleOperationReceipt atomically reserves one resource Role operation identity.
func (s *Store) ReserveRoleOperationReceipt(ctx context.Context, receipt RoleOperationReceipt) (bool, error) {
	result := s.db.WithContext(ctx).Clauses(clause.OnConflict{DoNothing: true}).Create(&receipt)
	if result.Error != nil {
		return false, fmt.Errorf("reserve role operation receipt: %w", result.Error)
	}
	return result.RowsAffected == 1, nil
}

// CompleteRoleOperationReceipt persists the stable result for a reserved resource Role operation.
func (s *Store) CompleteRoleOperationReceipt(ctx context.Context, receipt RoleOperationReceipt) error {
	result := s.db.WithContext(ctx).
		Model(&RoleOperationReceipt{}).
		Where(
			"workspace_id = ? AND operation_key = ? AND action = ? AND request_digest = ?",
			strings.TrimSpace(receipt.WorkspaceID),
			strings.TrimSpace(receipt.OperationKey),
			strings.TrimSpace(receipt.Action),
			strings.TrimSpace(receipt.RequestDigest),
		).
		Update("result_json", receipt.ResultJSON)
	if result.Error != nil {
		return fmt.Errorf("complete role operation receipt: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

// PublishedRoleVersion groups an immutable resource Role version with its manifest items.
type PublishedRoleVersion struct {
	RoleVersion   RoleVersion
	ManifestItems []RolePackageManifestItem
}

// RealizationWrite groups the atomic resource Role realization, items, and optional agent assignment.
type RealizationWrite struct {
	Realization RoleRealization
	Items       []RoleRealizationItem
	Binding     *AgentRoleBinding
}

// SaveRoleRealization atomically persists a resource Role realization and optional agent assignment.
func (s *Store) SaveRoleRealization(ctx context.Context, write RealizationWrite) error {
	if s == nil || s.db == nil {
		return fmt.Errorf("role store is not configured")
	}
	if strings.TrimSpace(write.Realization.ID) == "" {
		return fmt.Errorf("role realization id is required")
	}
	return s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		if err := tx.Create(&write.Realization).Error; err != nil {
			return fmt.Errorf("create role realization: %w", err)
		}
		if len(write.Items) > 0 {
			if err := tx.Create(&write.Items).Error; err != nil {
				return fmt.Errorf("create role realization items: %w", err)
			}
		}
		if write.Binding != nil {
			binding := *write.Binding
			if strings.TrimSpace(binding.ID) == "" {
				return fmt.Errorf("agent role binding id is required")
			}
			if err := tx.Clauses(clause.OnConflict{
				Columns: []clause.Column{{Name: "workspace_id"}, {Name: "agent_id"}},
				DoUpdates: clause.Assignments(map[string]any{
					"binding_kind":             binding.BindingKind,
					"role_id":                  binding.RoleID,
					"role_version_id":          binding.RoleVersionID,
					"source_preset_role_id":    binding.SourcePresetRoleID,
					"source_preset_version_id": binding.SourcePresetVersionID,
					"active_definition_path":   binding.ActiveDefinitionPath,
					"realization_id":           binding.RealizationID,
					"updated_by":               binding.UpdatedBy,
					"updated_at":               binding.UpdatedAt,
				}),
			}).Create(&binding).Error; err != nil {
				return fmt.Errorf("upsert agent role binding: %w", err)
			}
		}
		return nil
	})
}

// GetRoleRealization loads one resource Role realization and its ordered items.
func (s *Store) GetRoleRealization(ctx context.Context, workspaceID, realizationID string) (RoleRealization, []RoleRealizationItem, error) {
	var realization RoleRealization
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(realizationID)).
		First(&realization).Error; err != nil {
		return RoleRealization{}, nil, err
	}
	var items []RoleRealizationItem
	if err := s.db.WithContext(ctx).
		Where("realization_id = ?", realization.ID).
		Order("sort_order ASC, id ASC").
		Find(&items).Error; err != nil {
		return RoleRealization{}, nil, err
	}
	return realization, items, nil
}

// PublishRoleVersion persists one immutable resource Role version, its manifest, and the active-version pointer.
func (s *Store) PublishRoleVersion(ctx context.Context, role Role, sourcePresetID string, sourcePresetVersion int, sourceAgentID string, manifestItems []RolePackageManifestItem, now time.Time) (PublishedRoleVersion, error) {
	var maxVersion int
	if err := s.db.WithContext(ctx).Model(&RoleVersion{}).Where("workspace_id = ? AND role_id = ?", strings.TrimSpace(role.WorkspaceID), strings.TrimSpace(role.ID)).Select("COALESCE(MAX(version), 0)").Scan(&maxVersion).Error; err != nil {
		return PublishedRoleVersion{}, fmt.Errorf("load current max role version: %w", err)
	}
	versionRow := buildRoleVersion(role, maxVersion+1, sourcePresetID, sourcePresetVersion, sourceAgentID, manifestItems, now)
	for i := range manifestItems {
		manifestItems[i].RoleID = role.ID
		manifestItems[i].WorkspaceID = role.WorkspaceID
		manifestItems[i].RoleVersionID = versionRow.ID
	}
	if err := s.db.WithContext(ctx).Create(&versionRow).Error; err != nil {
		return PublishedRoleVersion{}, fmt.Errorf("create role version: %w", err)
	}
	if len(manifestItems) > 0 {
		if err := s.db.WithContext(ctx).Create(&manifestItems).Error; err != nil {
			return PublishedRoleVersion{}, fmt.Errorf("create role package manifest items: %w", err)
		}
	}
	if err := s.db.WithContext(ctx).Model(&Role{}).Where("workspace_id = ? AND id = ? AND deleted_at IS NULL", role.WorkspaceID, role.ID).Update("active_version_id", versionRow.ID).Error; err != nil {
		return PublishedRoleVersion{}, fmt.Errorf("set active role version: %w", err)
	}
	return PublishedRoleVersion{RoleVersion: versionRow, ManifestItems: manifestItems}, nil
}

// ListActiveManifestItemsByRoleIDs batch-loads active manifest items for explicit resource Role IDs.
func (s *Store) ListActiveManifestItemsByRoleIDs(ctx context.Context, workspaceID string, roleIDs []string) (map[string][]RolePackageManifestItem, error) {
	ids := normalizeIDs(roleIDs)
	if len(ids) == 0 {
		return map[string][]RolePackageManifestItem{}, nil
	}
	var rows []RolePackageManifestItem
	err := s.db.WithContext(ctx).
		Table("role_package_manifest_items AS items").
		Select("items.*").
		Joins("JOIN roles ON roles.active_version_id = items.role_version_id AND roles.workspace_id = items.workspace_id").
		Where("roles.workspace_id = ? AND roles.id IN ? AND roles.deleted_at IS NULL", strings.TrimSpace(workspaceID), ids).
		Order("items.role_id ASC, items.sort_order ASC, items.id ASC").
		Find(&rows).Error
	if err != nil {
		return nil, fmt.Errorf("list active role manifest items: %w", err)
	}
	out := make(map[string][]RolePackageManifestItem, len(ids))
	for _, row := range rows {
		out[row.RoleID] = append(out[row.RoleID], row)
	}
	for _, roleID := range ids {
		items := out[roleID]
		sort.SliceStable(items, func(i, j int) bool {
			if items[i].SortOrder == items[j].SortOrder {
				return items[i].ID < items[j].ID
			}
			return items[i].SortOrder < items[j].SortOrder
		})
		out[roleID] = items
	}
	return out, nil
}

// ListManifestItemsByRoleVersion loads the ordered manifest for one immutable resource Role version.
func (s *Store) ListManifestItemsByRoleVersion(ctx context.Context, workspaceID string, roleID string, roleVersionID string) ([]RolePackageManifestItem, error) {
	var rows []RolePackageManifestItem
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND role_id = ? AND role_version_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(roleID), strings.TrimSpace(roleVersionID)).
		Order("sort_order ASC, id ASC").
		Find(&rows).Error
	if err != nil {
		return nil, fmt.Errorf("list role manifest items by version: %w", err)
	}
	return rows, nil
}

// ListManifestItemsByRoleVersionBounded loads one explicit RoleVersion plus a
// single lookahead row so callers can fail closed before payload fanout.
func (s *Store) ListManifestItemsByRoleVersionBounded(ctx context.Context, workspaceID, roleID, roleVersionID string, limit int) ([]RolePackageManifestItem, error) {
	if s == nil || s.db == nil || limit <= 0 || limit > MaxRolePackageManifestItems {
		return nil, fmt.Errorf("role package manifest limit is out of bounds")
	}
	var rows []RolePackageManifestItem
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND role_id = ? AND role_version_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(roleID), strings.TrimSpace(roleVersionID)).
		Order("sort_order ASC, id ASC").Limit(limit + 1).
		Find(&rows).Error
	if err != nil {
		return nil, fmt.Errorf("list bounded role manifest items by version: %w", err)
	}
	return rows, nil
}

// GetActiveRoleVersion returns the published version currently selected by a
// non-deleted Role.
func (s *Store) GetActiveRoleVersion(ctx context.Context, workspaceID, roleID string) (RoleVersion, error) {
	var version RoleVersion
	err := s.db.WithContext(ctx).
		Table("role_versions AS versions").
		Select("versions.*").
		Joins("JOIN roles ON roles.active_version_id = versions.id AND roles.workspace_id = versions.workspace_id").
		Where("roles.workspace_id = ? AND roles.id = ? AND roles.deleted_at IS NULL", strings.TrimSpace(workspaceID), strings.TrimSpace(roleID)).
		First(&version).Error
	if err != nil {
		return RoleVersion{}, err
	}
	return version, nil
}

// GetRoleVersion loads one immutable resource Role version.
func (s *Store) GetRoleVersion(ctx context.Context, workspaceID, roleID, roleVersionID string) (RoleVersion, error) {
	var version RoleVersion
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND role_id = ? AND id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(roleID), strings.TrimSpace(roleVersionID)).
		First(&version).Error
	if err != nil {
		return RoleVersion{}, err
	}
	return version, nil
}

// ListRoleVersions returns at most one lookahead row beyond a bounded page for
// one explicit Role and Content lineage. Only exact Content Store projections
// are exposed as native history; other lineages and older legacy-only rows
// remain mapping or migration input.
func (s *Store) ListRoleVersions(ctx context.Context, workspaceID, roleID, contentID string, limit, offset int) ([]RoleVersionSummary, error) {
	if s == nil || s.db == nil {
		return nil, fmt.Errorf("role store is not configured")
	}
	workspaceID = strings.TrimSpace(workspaceID)
	roleID = strings.TrimSpace(roleID)
	contentID = strings.TrimSpace(contentID)
	if workspaceID == "" || roleID == "" || contentID == "" || limit <= 0 || limit > MaxRoleVersionPageSize || offset < 0 || offset > MaxRoleVersionPageOffset {
		return nil, fmt.Errorf("role version page is out of bounds")
	}
	var versions []RoleVersionSummary
	if err := s.db.WithContext(ctx).Model(&RoleVersion{}).
		Select("id", "version", "content_id", "content_version_id", "created_by", "created_at").
		Where("workspace_id = ? AND role_id = ? AND content_id = ? AND content_version_id <> ''", workspaceID, roleID, contentID).
		Order("created_at DESC").Order("id DESC").Limit(limit + 1).Offset(offset).Find(&versions).Error; err != nil {
		return nil, fmt.Errorf("list role versions: %w", err)
	}
	return versions, nil
}

func buildRoleVersion(role Role, version int, sourcePresetID string, sourcePresetVersion int, sourceAgentID string, items []RolePackageManifestItem, now time.Time) RoleVersion {
	versionID := ""
	contentID := ""
	contentVersionID := ""
	if len(items) > 0 {
		versionID = items[0].RoleVersionID
		contentID = strings.TrimSpace(items[0].ContentID)
		contentVersionID = strings.TrimSpace(items[0].ContentVersionID)
		for _, item := range items[1:] {
			if item.ContentID == "" && item.ContentVersionID == "" {
				continue
			}
			if strings.TrimSpace(item.ContentID) != contentID || strings.TrimSpace(item.ContentVersionID) != contentVersionID {
				contentID = ""
				contentVersionID = ""
				break
			}
		}
	}
	if strings.TrimSpace(versionID) == "" {
		versionID = fmt.Sprintf("%s_v%d", strings.TrimSpace(role.ID), version)
	}
	return RoleVersion{ID: versionID, RoleID: role.ID, WorkspaceID: role.WorkspaceID, Version: version, Status: "published", Definition: role.Definition, RuntimeHintsJSON: firstNonEmpty(strings.TrimSpace(role.RuntimeHintsJSON), "{}"), ProvenanceJSON: roleProvenanceJSON(role, sourcePresetID, sourcePresetVersion, sourceAgentID), NonPortableSummaryJSON: "[]", ContentID: contentID, ContentVersionID: contentVersionID, CreatedBy: role.UpdatedBy, CreatedAt: now}
}

func roleProvenanceJSON(role Role, sourcePresetID string, sourcePresetVersion int, sourceAgentID string) string {
	sourceAgentID = strings.TrimSpace(sourceAgentID)
	sourcePresetID = strings.TrimSpace(sourcePresetID)
	source := "manual"
	ref := firstNonEmpty(strings.TrimSpace(role.CreatedBy), strings.TrimSpace(role.OwnerID))
	version := 0
	if sourceAgentID != "" {
		source = "agent"
		ref = sourceAgentID
	} else if sourcePresetID != "" {
		source = "preset"
		ref = sourcePresetID
		version = sourcePresetVersion
	}
	if version > 0 {
		return fmt.Sprintf(`{"kind":%q,"ref":%q,"version":%d}`, source, ref, version)
	}
	return fmt.Sprintf(`{"kind":%q,"ref":%q}`, source, ref)
}

func firstNonEmpty(values ...string) string {
	for _, value := range values {
		if strings.TrimSpace(value) != "" {
			return strings.TrimSpace(value)
		}
	}
	return ""
}

// CreateRole persists one resource Role catalog entry and reloads its canonical row.
func (s *Store) CreateRole(ctx context.Context, role Role) (Role, error) {
	if err := validateRole(role); err != nil {
		return Role{}, err
	}
	if err := s.db.WithContext(ctx).Create(&role).Error; err != nil {
		if isRoleSlugConflict(err) {
			return Role{}, fmt.Errorf("create role: %w", ErrRoleSlugConflict)
		}
		return Role{}, fmt.Errorf("create role: %w", err)
	}
	return s.GetRole(ctx, role.WorkspaceID, role.ID)
}

// UpdateRole persists mutable resource Role catalog fields and reloads the canonical row.
func (s *Store) UpdateRole(ctx context.Context, role Role) (Role, error) {
	if err := validateRole(role); err != nil {
		return Role{}, err
	}
	result := s.db.WithContext(ctx).
		Model(&Role{}).
		Where("workspace_id = ? AND id = ? AND deleted_at IS NULL", strings.TrimSpace(role.WorkspaceID), strings.TrimSpace(role.ID)).
		Updates(map[string]any{
			"slug":                  strings.TrimSpace(role.Slug),
			"name":                  strings.TrimSpace(role.Name),
			"description":           strings.TrimSpace(role.Description),
			"locale":                strings.TrimSpace(role.Locale),
			"scenario":              strings.TrimSpace(role.Scenario),
			"icon":                  strings.TrimSpace(role.Icon),
			"tags_json":             strings.TrimSpace(role.TagsJSON),
			"definition":            strings.TrimSpace(role.Definition),
			"runtime_hints_json":    strings.TrimSpace(role.RuntimeHintsJSON),
			"attachments_json":      strings.TrimSpace(role.AttachmentsJSON),
			"source_preset_id":      strings.TrimSpace(role.SourcePresetID),
			"source_preset_version": role.SourcePresetVersion,
			"source_agent_id":       strings.TrimSpace(role.SourceAgentID),
			"is_active":             role.IsActive,
			"updated_by":            strings.TrimSpace(role.UpdatedBy),
			"updated_at":            role.UpdatedAt,
		})
	if result.Error != nil {
		return Role{}, fmt.Errorf("update role: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return Role{}, gorm.ErrRecordNotFound
	}
	return s.GetRole(ctx, role.WorkspaceID, role.ID)
}

// UpdateRoleIfActiveVersion applies mutable Role metadata only while the
// caller's immutable version fence is still active.
func (s *Store) UpdateRoleIfActiveVersion(ctx context.Context, role Role, expectedVersionID string) (Role, error) {
	if err := validateRole(role); err != nil {
		return Role{}, err
	}
	result := s.db.WithContext(ctx).
		Model(&Role{}).
		Where(
			"workspace_id = ? AND id = ? AND active_version_id = ? AND deleted_at IS NULL",
			strings.TrimSpace(role.WorkspaceID),
			strings.TrimSpace(role.ID),
			strings.TrimSpace(expectedVersionID),
		).
		Updates(map[string]any{
			"slug":                  strings.TrimSpace(role.Slug),
			"name":                  strings.TrimSpace(role.Name),
			"description":           strings.TrimSpace(role.Description),
			"locale":                strings.TrimSpace(role.Locale),
			"scenario":              strings.TrimSpace(role.Scenario),
			"icon":                  strings.TrimSpace(role.Icon),
			"tags_json":             strings.TrimSpace(role.TagsJSON),
			"definition":            strings.TrimSpace(role.Definition),
			"runtime_hints_json":    strings.TrimSpace(role.RuntimeHintsJSON),
			"attachments_json":      strings.TrimSpace(role.AttachmentsJSON),
			"source_preset_id":      strings.TrimSpace(role.SourcePresetID),
			"source_preset_version": role.SourcePresetVersion,
			"source_agent_id":       strings.TrimSpace(role.SourceAgentID),
			"visibility":            strings.TrimSpace(role.Visibility),
			"is_active":             role.IsActive,
			"updated_by":            strings.TrimSpace(role.UpdatedBy),
			"updated_at":            role.UpdatedAt,
		})
	if result.Error != nil {
		return Role{}, fmt.Errorf("update role with active version fence: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return Role{}, gorm.ErrRecordNotFound
	}
	return s.GetRole(ctx, role.WorkspaceID, role.ID)
}

// UpdateRoleVisibility changes resource Role visibility and reloads the canonical row.
func (s *Store) UpdateRoleVisibility(ctx context.Context, workspaceID, id, visibility, updatedBy string, now time.Time) (Role, error) {
	visibility = strings.TrimSpace(visibility)
	switch visibility {
	case VisibilityPrivate, VisibilityWorkspace:
	default:
		return Role{}, fmt.Errorf("role visibility must be private or workspace")
	}
	result := s.db.WithContext(ctx).
		Model(&Role{}).
		Where("workspace_id = ? AND id = ? AND deleted_at IS NULL", strings.TrimSpace(workspaceID), strings.TrimSpace(id)).
		Updates(map[string]any{
			"visibility": visibility,
			"updated_by": strings.TrimSpace(updatedBy),
			"updated_at": now,
		})
	if result.Error != nil {
		return Role{}, fmt.Errorf("update role visibility: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return Role{}, gorm.ErrRecordNotFound
	}
	return s.GetRole(ctx, workspaceID, id)
}

// DeleteRole soft-deletes one resource Role catalog entry.
func (s *Store) DeleteRole(ctx context.Context, workspaceID, id string, now time.Time) error {
	result := s.db.WithContext(ctx).
		Model(&Role{}).
		Where("workspace_id = ? AND id = ? AND deleted_at IS NULL", strings.TrimSpace(workspaceID), strings.TrimSpace(id)).
		Updates(map[string]any{
			"deleted_at": now,
			"is_active":  false,
			"updated_at": now,
		})
	if result.Error != nil {
		return fmt.Errorf("delete role: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

// DeleteRoleIfActiveVersion soft-deletes a resource Role only while its immutable version fence is active.
func (s *Store) DeleteRoleIfActiveVersion(ctx context.Context, workspaceID, id, expectedVersionID string, now time.Time) error {
	result := s.db.WithContext(ctx).
		Model(&Role{}).
		Where(
			"workspace_id = ? AND id = ? AND active_version_id = ? AND deleted_at IS NULL",
			strings.TrimSpace(workspaceID),
			strings.TrimSpace(id),
			strings.TrimSpace(expectedVersionID),
		).
		Updates(map[string]any{
			"deleted_at": now,
			"is_active":  false,
			"updated_at": now,
		})
	if result.Error != nil {
		return fmt.Errorf("delete role with active version fence: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

// GetRole loads one non-deleted resource Role catalog entry.
func (s *Store) GetRole(ctx context.Context, workspaceID, id string) (Role, error) {
	var role Role
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND id = ? AND deleted_at IS NULL", strings.TrimSpace(workspaceID), strings.TrimSpace(id)).
		First(&role).Error
	if err != nil {
		return Role{}, err
	}
	return role, nil
}

// ListRoles returns one bounded, workspace-scoped resource Role catalog page and its filtered count.
func (s *Store) ListRoles(ctx context.Context, workspaceID, principalID, view, query string, page, perPage int) ([]Role, int64, error) {
	page, perPage = normalizePagination(page, perPage)
	db := s.db.WithContext(ctx).Model(&Role{}).Where("roles.workspace_id = ? AND roles.deleted_at IS NULL", strings.TrimSpace(workspaceID))
	principalID = strings.TrimSpace(principalID)
	if principalID != "" {
		db = db.Joins("LEFT JOIN role_grants AS user_role_grants ON user_role_grants.workspace_id = roles.workspace_id AND user_role_grants.role_id = roles.id AND user_role_grants.subject_type = ? AND user_role_grants.subject_id = ?", "user", principalID).
			Where("roles.created_by = ? OR roles.visibility = ? OR user_role_grants.id IS NOT NULL", principalID, VisibilityWorkspace)
	}
	switch normalizeListView(view) {
	case ListViewWorkspace:
		db = db.Where("roles.visibility = ?", VisibilityWorkspace)
	case ListViewCreatedByMe:
		db = db.Where("roles.created_by = ?", principalID)
	case ListViewPrivate:
		db = db.Where("roles.visibility = ?", VisibilityPrivate)
	}
	if q := strings.TrimSpace(query); q != "" {
		like := "%" + q + "%"
		db = db.Where("roles.name LIKE ? OR roles.description LIKE ? OR roles.scenario LIKE ?", like, like, like)
	}
	var total int64
	if err := db.Count(&total).Error; err != nil {
		return nil, 0, err
	}
	var roles []Role
	err := db.Order("roles.updated_at DESC, roles.id ASC").Offset((page - 1) * perPage).Limit(perPage).Find(&roles).Error
	return roles, total, err
}

func normalizeListView(view string) string {
	switch strings.ToLower(strings.TrimSpace(view)) {
	case ListViewWorkspace:
		return ListViewWorkspace
	case ListViewCreatedByMe:
		return ListViewCreatedByMe
	case ListViewPrivate:
		return ListViewPrivate
	default:
		return ListViewAll
	}
}

// RoleSlugExists checks one workspace-scoped resource Role slug without loading catalog rows.
func (s *Store) RoleSlugExists(ctx context.Context, workspaceID, slug, excludeID string) (bool, error) {
	var count int64
	db := s.db.WithContext(ctx).Model(&Role{}).Where("workspace_id = ? AND slug = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(slug))
	if strings.TrimSpace(excludeID) != "" {
		db = db.Where("id <> ?", strings.TrimSpace(excludeID))
	}
	if err := db.Count(&count).Error; err != nil {
		return false, err
	}
	return count > 0, nil
}

func isRoleSlugConflict(err error) bool {
	if err == nil {
		return false
	}
	text := strings.ToLower(err.Error())
	return strings.Contains(text, "uk_roles_workspace_slug") ||
		strings.Contains(text, "roles.workspace_id, roles.slug")
}

// UpsertRoleGrant persists one resource Role authorization assignment and reloads it.
func (s *Store) UpsertRoleGrant(ctx context.Context, grant RoleGrant) (RoleGrant, error) {
	if err := validateGrant(grant); err != nil {
		return RoleGrant{}, err
	}
	err := s.db.WithContext(ctx).Clauses(clause.OnConflict{
		Columns: []clause.Column{{Name: "workspace_id"}, {Name: "role_id"}, {Name: "subject_type"}, {Name: "subject_id"}},
		DoUpdates: clause.Assignments(map[string]any{
			"role":       grant.Role,
			"updated_by": grant.UpdatedBy,
			"updated_at": grant.UpdatedAt,
		}),
	}).Create(&grant).Error
	if err != nil {
		return RoleGrant{}, fmt.Errorf("upsert role grant: %w", err)
	}
	return s.GetRoleGrant(ctx, grant.WorkspaceID, grant.RoleID, grant.SubjectType, grant.SubjectID)
}

// GetRoleGrant loads one resource Role authorization assignment.
func (s *Store) GetRoleGrant(ctx context.Context, workspaceID, roleID, subjectType, subjectID string) (RoleGrant, error) {
	var grant RoleGrant
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND role_id = ? AND subject_type = ? AND subject_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(roleID), strings.TrimSpace(subjectType), strings.TrimSpace(subjectID)).
		First(&grant).Error
	if err != nil {
		return RoleGrant{}, err
	}
	return grant, nil
}

// ListRoleGrantsForSubject batch-loads one subject's grants for explicit resource Role IDs.
func (s *Store) ListRoleGrantsForSubject(ctx context.Context, workspaceID, subjectType, subjectID string, roleIDs []string) ([]RoleGrant, error) {
	ids := normalizeIDs(roleIDs)
	if len(ids) == 0 {
		return nil, nil
	}
	var grants []RoleGrant
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND subject_type = ? AND subject_id = ? AND role_id IN ?", strings.TrimSpace(workspaceID), strings.TrimSpace(subjectType), strings.TrimSpace(subjectID), ids).
		Find(&grants).Error
	return grants, err
}

func validateRole(role Role) error {
	if strings.TrimSpace(role.ID) == "" || strings.TrimSpace(role.WorkspaceID) == "" || strings.TrimSpace(role.Slug) == "" || strings.TrimSpace(role.Name) == "" {
		return fmt.Errorf("role id, workspace, slug, and name are required")
	}
	if role.CreatedAt.IsZero() || role.UpdatedAt.IsZero() {
		return fmt.Errorf("role timestamps are required")
	}
	return nil
}

func validateGrant(grant RoleGrant) error {
	if strings.TrimSpace(grant.ID) == "" || strings.TrimSpace(grant.WorkspaceID) == "" || strings.TrimSpace(grant.RoleID) == "" || strings.TrimSpace(grant.SubjectType) == "" || strings.TrimSpace(grant.SubjectID) == "" || strings.TrimSpace(grant.Role) == "" {
		return fmt.Errorf("role grant id, scope, subject, and role are required")
	}
	if grant.CreatedAt.IsZero() || grant.UpdatedAt.IsZero() {
		return fmt.Errorf("role grant timestamps are required")
	}
	return nil
}

func normalizePagination(page, perPage int) (int, int) {
	if page < 1 {
		page = 1
	}
	if perPage < 1 {
		perPage = 20
	}
	if perPage > 100 {
		perPage = 100
	}
	return page, perPage
}

func normalizeIDs(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
		}
		seen[value] = struct{}{}
		out = append(out, value)
	}
	return out
}