package taskboard

import (
	"context"
	"fmt"
	"sort"
	"strings"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/access"
)

const (
	taskFieldKindKind     = "kind"
	taskFieldKindPriority = "priority"
)

type taskFieldDefault struct {
	FieldKind string
	Key       string
	Label     string
	Aliases   []string
}

var defaultTaskFieldOptions = []taskFieldDefault{
	{FieldKind: taskFieldKindPriority, Key: "p0", Label: "Critical", Aliases: []string{"critical"}},
	{FieldKind: taskFieldKindPriority, Key: "p1", Label: "High", Aliases: []string{"high"}},
	{FieldKind: taskFieldKindPriority, Key: "p2", Label: "Medium", Aliases: []string{"medium"}},
	{FieldKind: taskFieldKindPriority, Key: "p3", Label: "Low", Aliases: []string{"low"}},
	{FieldKind: taskFieldKindKind, Key: "feature", Label: "Feature", Aliases: []string{"feat"}},
	{FieldKind: taskFieldKindKind, Key: "bug", Label: "Bug"},
	{FieldKind: taskFieldKindKind, Key: "design", Label: "Design"},
	{FieldKind: taskFieldKindKind, Key: "product", Label: "Product"},
	{FieldKind: taskFieldKindKind, Key: "refactor", Label: "Refactor"},
	{FieldKind: taskFieldKindKind, Key: "docs", Label: "Docs"},
	{FieldKind: taskFieldKindKind, Key: "test", Label: "Test"},
	{FieldKind: taskFieldKindKind, Key: "chore", Label: "Chore"},
	{FieldKind: taskFieldKindKind, Key: "research", Label: "Research"},
	{FieldKind: taskFieldKindKind, Key: "spike", Label: "Spike"},
	{FieldKind: taskFieldKindKind, Key: "release", Label: "Release"},
	{FieldKind: taskFieldKindKind, Key: "ops", Label: "Ops"},
}

type taskFieldOptionState struct {
	ID        string
	FieldKind string
	Key       string
	Label     string
	Position  int64
	IsActive  bool
	Aliases   []string
}

type taskFieldCatalog struct {
	optionsByID            map[string]taskFieldOptionState
	kindOptions            []taskFieldOptionState
	priorityOptions        []taskFieldOptionState
	kindResolverAll        map[string]taskFieldOptionState
	priorityResolverAll    map[string]taskFieldOptionState
	kindResolverActive     map[string]taskFieldOptionState
	priorityResolverActive map[string]taskFieldOptionState
}

type taskFieldWorkspaceAccess struct {
	CanManage bool
}

type resolvedTaskFieldUpdate struct {
	Present     bool
	EchoValue   string
	OptionID    string
	LegacyValue string
}

func EnsureDefaultTaskFieldOptions(ctx context.Context, execer coredb.QueryExecer, workspaceID string) error {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedWorkspaceID == "" {
		return badRequest("workspaceId is required")
	}

	existing := make(map[string]bool)
	rows, err := execer.Query(ctx, `
SELECT field_kind, option_key
FROM task_field_options
WHERE workspace_id = $1
`, trimmedWorkspaceID)
	if err != nil {
		return fmt.Errorf("load existing task field options: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var fieldKind string
		var key string
		if err := rows.Scan(&fieldKind, &key); err != nil {
			return fmt.Errorf("scan existing task field option: %w", err)
		}
		existing[fieldKind+":"+key] = true
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate existing task field options: %w", err)
	}

	nextPosition := map[string]int64{
		taskFieldKindKind:     0,
		taskFieldKindPriority: 0,
	}
	positionRows, err := execer.Query(ctx, `
SELECT field_kind, COALESCE(MAX(position), -1)
FROM task_field_options
WHERE workspace_id = $1
GROUP BY field_kind
`, trimmedWorkspaceID)
	if err != nil {
		return fmt.Errorf("load task field option positions: %w", err)
	}
	defer positionRows.Close()

	for positionRows.Next() {
		var fieldKind string
		var maxPosition int64
		if err := positionRows.Scan(&fieldKind, &maxPosition); err != nil {
			return fmt.Errorf("scan task field option position: %w", err)
		}
		nextPosition[strings.TrimSpace(fieldKind)] = maxPosition + 1
	}
	if err := positionRows.Err(); err != nil {
		return fmt.Errorf("iterate task field option positions: %w", err)
	}

	defaultOptions := make([]taskFieldOptionDefaultInsert, 0)
	defaultAliasDefinitions := make([]taskFieldOptionDefaultAlias, 0)
	for _, definition := range defaultTaskFieldOptions {
		existingKey := definition.FieldKind + ":" + definition.Key
		if existing[existingKey] {
			continue
		}

		optionID, err := newTaskFieldOptionID()
		if err != nil {
			return fmt.Errorf("generate task field option id: %w", err)
		}
		position := nextPosition[definition.FieldKind]
		nextPosition[definition.FieldKind] = position + 1
		defaultOptions = append(defaultOptions, taskFieldOptionDefaultInsert{
			ID:          optionID,
			WorkspaceID: trimmedWorkspaceID,
			FieldKind:   definition.FieldKind,
			Key:         definition.Key,
			Label:       definition.Label,
			Position:    position,
		})
		for _, alias := range definition.Aliases {
			defaultAliasDefinitions = append(defaultAliasDefinitions, taskFieldOptionDefaultAlias{
				FieldKind: definition.FieldKind,
				OptionKey: definition.Key,
				AliasKey:  alias,
			})
		}
	}
	if err := insertDefaultTaskFieldOptions(ctx, execer, defaultOptions); err != nil {
		return err
	}

	optionIDsByDefaultKey, err := loadTaskFieldOptionIDsForDefaultAliases(ctx, execer, trimmedWorkspaceID, defaultAliasDefinitions)
	if err != nil {
		return err
	}
	defaultAliases := make([]taskFieldOptionAliasInsert, 0, len(defaultAliasDefinitions))
	for _, definition := range defaultAliasDefinitions {
		optionID := optionIDsByDefaultKey[definition.FieldKind+":"+definition.OptionKey]
		if optionID == "" {
			return fmt.Errorf("load default task field option %s:%s: missing after insert", definition.FieldKind, definition.OptionKey)
		}
		aliasID, err := newTaskFieldOptionAliasID()
		if err != nil {
			return fmt.Errorf("generate task field option alias id: %w", err)
		}
		defaultAliases = append(defaultAliases, taskFieldOptionAliasInsert{
			ID:          aliasID,
			OptionID:    optionID,
			WorkspaceID: trimmedWorkspaceID,
			FieldKind:   definition.FieldKind,
			AliasKey:    definition.AliasKey,
		})
	}
	if err := insertDefaultTaskFieldOptionAliases(ctx, execer, defaultAliases); err != nil {
		return err
	}

	return nil
}

func (txs TxStore) ListTaskFieldOptions(ctx context.Context, request ListTaskFieldOptionsRequest) (ListTaskFieldOptionsResponse, error) {
	workspaceID, err := request.Context.requireWorkspaceID()
	if err != nil {
		return ListTaskFieldOptionsResponse{}, err
	}
	userID, _, err := request.Context.requireUser()
	if err != nil {
		return ListTaskFieldOptionsResponse{}, err
	}

	tx := txs.tx
	access, err := loadTaskFieldWorkspaceAccess(ctx, tx, workspaceID, userID)
	if err != nil {
		return ListTaskFieldOptionsResponse{}, err
	}
	if err := EnsureDefaultTaskFieldOptions(ctx, tx, workspaceID); err != nil {
		return ListTaskFieldOptionsResponse{}, err
	}
	catalog, err := loadTaskFieldCatalog(ctx, tx, workspaceID)
	if err != nil {
		return ListTaskFieldOptionsResponse{}, err
	}
	usageCounts, err := loadTaskFieldOptionUsageCounts(ctx, tx, workspaceID, catalog)
	if err != nil {
		return ListTaskFieldOptionsResponse{}, err
	}
	return ListTaskFieldOptionsResponse{
		CanManage:       access.CanManage,
		PriorityOptions: taskFieldOptionsFromStates(catalog.priorityOptions, usageCounts),
		KindOptions:     taskFieldOptionsFromStates(catalog.kindOptions, usageCounts),
	}, nil
}

func (txs TxStore) UpsertTaskFieldOption(ctx context.Context, request UpsertTaskFieldOptionRequest) (UpsertTaskFieldOptionResponse, error) {
	workspaceID, err := request.Context.requireWorkspaceID()
	if err != nil {
		return UpsertTaskFieldOptionResponse{}, err
	}
	userID, _, err := request.Context.requireUser()
	if err != nil {
		return UpsertTaskFieldOptionResponse{}, err
	}

	tx := txs.tx
	access, err := loadTaskFieldWorkspaceAccess(ctx, tx, workspaceID, userID)
	if err != nil {
		return UpsertTaskFieldOptionResponse{}, err
	}
	if !access.CanManage {
		return UpsertTaskFieldOptionResponse{}, ErrForbidden
	}
	if err := EnsureDefaultTaskFieldOptions(ctx, tx, workspaceID); err != nil {
		return UpsertTaskFieldOptionResponse{}, err
	}
	catalog, err := loadTaskFieldCatalog(ctx, tx, workspaceID)
	if err != nil {
		return UpsertTaskFieldOptionResponse{}, err
	}

	optionID := strings.TrimSpace(request.OptionID)
	var previousOption taskFieldOptionState
	var hadPreviousOption bool
	if optionID != "" {
		previousOption, hadPreviousOption = catalog.optionByID(optionID)
	}
	switch {
	case optionID == "":
		if err := insertTaskFieldOption(ctx, tx, workspaceID, catalog, request.Option); err != nil {
			return UpsertTaskFieldOptionResponse{}, err
		}
	default:
		if err := updateTaskFieldOption(ctx, tx, workspaceID, catalog, optionID, request.Option); err != nil {
			return UpsertTaskFieldOptionResponse{}, err
		}
	}

	catalog, err = loadTaskFieldCatalog(ctx, tx, workspaceID)
	if err != nil {
		return UpsertTaskFieldOptionResponse{}, err
	}
	usageCounts, err := loadTaskFieldOptionUsageCounts(ctx, tx, workspaceID, catalog)
	if err != nil {
		return UpsertTaskFieldOptionResponse{}, err
	}

	if optionID == "" {
		fieldKind, err := validateTaskFieldKind(request.Option.FieldKind)
		if err != nil {
			return UpsertTaskFieldOptionResponse{}, err
		}
		created, ok := catalog.resolveByKey(fieldKind, request.Option.Key)
		if !ok {
			return UpsertTaskFieldOptionResponse{}, ErrNotFound
		}
		optionID = created.ID
	}

	option, ok := catalog.optionByID(optionID)
	if !ok {
		return UpsertTaskFieldOptionResponse{}, ErrNotFound
	}
	affectedTasks := []Task{}
	if hadPreviousOption {
		affectedTasks, err = txs.listTasksByFieldOptionImpact(
			ctx,
			tx,
			workspaceID,
			option.FieldKind,
			option.ID,
			taskFieldOptionImpactLookups(previousOption, option),
		)
		if err != nil {
			return UpsertTaskFieldOptionResponse{}, err
		}
	}
	return UpsertTaskFieldOptionResponse{
		Option:        taskFieldOptionFromState(option, usageCounts),
		AffectedTasks: affectedTasks,
	}, nil
}

func (txs TxStore) ReorderTaskFieldOption(ctx context.Context, request ReorderTaskFieldOptionRequest) (ReorderTaskFieldOptionResponse, error) {
	workspaceID, err := request.Context.requireWorkspaceID()
	if err != nil {
		return ReorderTaskFieldOptionResponse{}, err
	}
	userID, _, err := request.Context.requireUser()
	if err != nil {
		return ReorderTaskFieldOptionResponse{}, err
	}
	if request.Position < 0 {
		return ReorderTaskFieldOptionResponse{}, badRequest("position must be non-negative")
	}

	tx := txs.tx
	access, err := loadTaskFieldWorkspaceAccess(ctx, tx, workspaceID, userID)
	if err != nil {
		return ReorderTaskFieldOptionResponse{}, err
	}
	if !access.CanManage {
		return ReorderTaskFieldOptionResponse{}, ErrForbidden
	}
	if err := EnsureDefaultTaskFieldOptions(ctx, tx, workspaceID); err != nil {
		return ReorderTaskFieldOptionResponse{}, err
	}
	catalog, err := loadTaskFieldCatalog(ctx, tx, workspaceID)
	if err != nil {
		return ReorderTaskFieldOptionResponse{}, err
	}

	optionID := strings.TrimSpace(request.OptionID)
	option, ok := catalog.optionByID(optionID)
	if !ok {
		return ReorderTaskFieldOptionResponse{}, ErrNotFound
	}

	var options []taskFieldOptionState
	switch option.FieldKind {
	case taskFieldKindKind:
		options = append(options, catalog.kindOptions...)
	case taskFieldKindPriority:
		options = append(options, catalog.priorityOptions...)
	default:
		return ReorderTaskFieldOptionResponse{}, badRequest("fieldKind is invalid")
	}

	currentIndex := -1
	for index, candidate := range options {
		if candidate.ID == optionID {
			currentIndex = index
			break
		}
	}
	if currentIndex < 0 {
		return ReorderTaskFieldOptionResponse{}, ErrNotFound
	}
	targetIndex := int(request.Position)
	if targetIndex >= len(options) {
		targetIndex = len(options) - 1
	}
	reordered := make([]taskFieldOptionState, 0, len(options))
	reordered = append(reordered, options[:currentIndex]...)
	reordered = append(reordered, options[currentIndex+1:]...)
	reordered = append(reordered[:targetIndex], append([]taskFieldOptionState{option}, reordered[targetIndex:]...)...)

	if err := updateTaskFieldOptionPositions(ctx, tx, workspaceID, reordered); err != nil {
		return ReorderTaskFieldOptionResponse{}, err
	}

	catalog, err = loadTaskFieldCatalog(ctx, tx, workspaceID)
	if err != nil {
		return ReorderTaskFieldOptionResponse{}, err
	}
	usageCounts, err := loadTaskFieldOptionUsageCounts(ctx, tx, workspaceID, catalog)
	if err != nil {
		return ReorderTaskFieldOptionResponse{}, err
	}
	option, ok = catalog.optionByID(optionID)
	if !ok {
		return ReorderTaskFieldOptionResponse{}, ErrNotFound
	}
	return ReorderTaskFieldOptionResponse{Option: taskFieldOptionFromState(option, usageCounts)}, nil
}

func (txs TxStore) SetTaskFieldOptionActive(ctx context.Context, request SetTaskFieldOptionActiveRequest) (SetTaskFieldOptionActiveResponse, error) {
	workspaceID, err := request.Context.requireWorkspaceID()
	if err != nil {
		return SetTaskFieldOptionActiveResponse{}, err
	}
	userID, _, err := request.Context.requireUser()
	if err != nil {
		return SetTaskFieldOptionActiveResponse{}, err
	}

	tx := txs.tx
	access, err := loadTaskFieldWorkspaceAccess(ctx, tx, workspaceID, userID)
	if err != nil {
		return SetTaskFieldOptionActiveResponse{}, err
	}
	if !access.CanManage {
		return SetTaskFieldOptionActiveResponse{}, ErrForbidden
	}
	if err := EnsureDefaultTaskFieldOptions(ctx, tx, workspaceID); err != nil {
		return SetTaskFieldOptionActiveResponse{}, err
	}

	result, err := tx.Exec(ctx, `
			UPDATE task_field_options
			SET is_active = $3,
			    updated_at = NOW()
			WHERE id = $1
			  AND workspace_id = $2
		`, strings.TrimSpace(request.OptionID), workspaceID, request.Active)
	if err != nil {
		return SetTaskFieldOptionActiveResponse{}, fmt.Errorf("update task field option active: %w", err)
	}
	if result.RowsAffected() == 0 {
		return SetTaskFieldOptionActiveResponse{}, ErrNotFound
	}

	catalog, err := loadTaskFieldCatalog(ctx, tx, workspaceID)
	if err != nil {
		return SetTaskFieldOptionActiveResponse{}, err
	}
	usageCounts, err := loadTaskFieldOptionUsageCounts(ctx, tx, workspaceID, catalog)
	if err != nil {
		return SetTaskFieldOptionActiveResponse{}, err
	}
	option, ok := catalog.optionByID(strings.TrimSpace(request.OptionID))
	if !ok {
		return SetTaskFieldOptionActiveResponse{}, ErrNotFound
	}
	affectedTasks, err := txs.listTasksByFieldOptionImpact(
		ctx,
		tx,
		workspaceID,
		option.FieldKind,
		option.ID,
		taskFieldOptionImpactLookups(option),
	)
	if err != nil {
		return SetTaskFieldOptionActiveResponse{}, err
	}
	return SetTaskFieldOptionActiveResponse{
		Option:        taskFieldOptionFromState(option, usageCounts),
		AffectedTasks: affectedTasks,
	}, nil
}

func taskFieldOptionImpactLookups(options ...taskFieldOptionState) []string {
	lookups := make([]string, 0, len(options)*2)
	seen := make(map[string]struct{}, len(options)*2)
	for _, option := range options {
		for _, candidate := range append([]string{option.Key}, option.Aliases...) {
			normalized := normalizeTaskFieldLookupKey(candidate)
			if normalized == "" {
				continue
			}
			if _, ok := seen[normalized]; ok {
				continue
			}
			seen[normalized] = struct{}{}
			lookups = append(lookups, normalized)
		}
	}
	sort.Strings(lookups)
	return lookups
}

func loadTaskFieldWorkspaceAccess(ctx context.Context, queryer coredb.QueryRower, workspaceID string, userID string) (taskFieldWorkspaceAccess, error) {
	row := queryer.QueryRow(ctx, `
SELECT COALESCE(workspace_role, '')
FROM members
WHERE workspace_id = $1
  AND user_id = $2
  AND type = 'human'
  AND status = 'active'
LIMIT 1
`, workspaceID, userID)

	var workspaceRole string
	if err := row.Scan(&workspaceRole); err != nil {
		if coredb.IsNotFound(err) {
			return taskFieldWorkspaceAccess{}, ErrForbidden
		}
		return taskFieldWorkspaceAccess{}, fmt.Errorf("load task field workspace access: %w", err)
	}
	return taskFieldWorkspaceAccess{
		CanManage: access.HasWorkspaceAdminRole(domain.WorkspaceRole(strings.TrimSpace(workspaceRole))),
	}, nil
}

func loadTaskFieldCatalog(ctx context.Context, queryer coredb.Queryer, workspaceID string) (taskFieldCatalog, error) {
	rows, err := queryer.Query(ctx, `
SELECT id, field_kind, option_key, display_label, position, is_active
FROM task_field_options
WHERE workspace_id = $1
ORDER BY field_kind ASC, position ASC, created_at ASC, id ASC
`, workspaceID)
	if err != nil {
		return taskFieldCatalog{}, fmt.Errorf("load task field options: %w", err)
	}
	defer rows.Close()

	catalog := taskFieldCatalog{
		optionsByID:            make(map[string]taskFieldOptionState),
		kindResolverAll:        make(map[string]taskFieldOptionState),
		priorityResolverAll:    make(map[string]taskFieldOptionState),
		kindResolverActive:     make(map[string]taskFieldOptionState),
		priorityResolverActive: make(map[string]taskFieldOptionState),
	}

	for rows.Next() {
		var option taskFieldOptionState
		if err := rows.Scan(&option.ID, &option.FieldKind, &option.Key, &option.Label, &option.Position, &option.IsActive); err != nil {
			return taskFieldCatalog{}, fmt.Errorf("scan task field option: %w", err)
		}
		option.FieldKind = strings.TrimSpace(option.FieldKind)
		option.Key = strings.TrimSpace(option.Key)
		option.Label = strings.TrimSpace(option.Label)
		catalog.optionsByID[option.ID] = option
		switch option.FieldKind {
		case taskFieldKindKind:
			catalog.kindOptions = append(catalog.kindOptions, option)
		case taskFieldKindPriority:
			catalog.priorityOptions = append(catalog.priorityOptions, option)
		}
	}
	if err := rows.Err(); err != nil {
		return taskFieldCatalog{}, fmt.Errorf("iterate task field options: %w", err)
	}

	aliasRows, err := queryer.Query(ctx, `
SELECT option_id, alias_key
FROM task_field_option_aliases
WHERE workspace_id = $1
ORDER BY alias_key ASC
`, workspaceID)
	if err != nil {
		return taskFieldCatalog{}, fmt.Errorf("load task field option aliases: %w", err)
	}
	defer aliasRows.Close()

	aliasesByID := make(map[string][]string)
	for aliasRows.Next() {
		var optionID string
		var alias string
		if err := aliasRows.Scan(&optionID, &alias); err != nil {
			return taskFieldCatalog{}, fmt.Errorf("scan task field option alias: %w", err)
		}
		aliasesByID[optionID] = append(aliasesByID[optionID], strings.TrimSpace(alias))
	}
	if err := aliasRows.Err(); err != nil {
		return taskFieldCatalog{}, fmt.Errorf("iterate task field option aliases: %w", err)
	}

	for optionID, option := range catalog.optionsByID {
		option.Aliases = append([]string(nil), aliasesByID[optionID]...)
		catalog.optionsByID[optionID] = option
		catalog.registerOption(option)
	}
	catalog.kindOptions = catalog.rebuildOptions(taskFieldKindKind)
	catalog.priorityOptions = catalog.rebuildOptions(taskFieldKindPriority)
	return catalog, nil
}

func (catalog taskFieldCatalog) rebuildOptions(fieldKind string) []taskFieldOptionState {
	source := catalog.priorityOptions
	if fieldKind == taskFieldKindKind {
		source = catalog.kindOptions
	}
	rebuilt := make([]taskFieldOptionState, 0, len(source))
	for _, option := range source {
		if latest, ok := catalog.optionsByID[option.ID]; ok {
			rebuilt = append(rebuilt, latest)
		}
	}
	return rebuilt
}

func (catalog *taskFieldCatalog) registerOption(option taskFieldOptionState) {
	lookupKeys := []string{
		option.ID,
		option.Key,
		option.Label,
	}
	lookupKeys = append(lookupKeys, option.Aliases...)
	switch option.FieldKind {
	case taskFieldKindKind:
		for _, lookup := range lookupKeys {
			lookupKey := normalizeTaskFieldLookupKey(lookup)
			if lookupKey == "" {
				continue
			}
			catalog.kindResolverAll[lookupKey] = option
			if option.IsActive {
				catalog.kindResolverActive[lookupKey] = option
			}
		}
	case taskFieldKindPriority:
		for _, lookup := range lookupKeys {
			lookupKey := normalizeTaskFieldLookupKey(lookup)
			if lookupKey == "" {
				continue
			}
			catalog.priorityResolverAll[lookupKey] = option
			if option.IsActive {
				catalog.priorityResolverActive[lookupKey] = option
			}
		}
	}
}

func (catalog taskFieldCatalog) optionByID(optionID string) (taskFieldOptionState, bool) {
	option, ok := catalog.optionsByID[strings.TrimSpace(optionID)]
	return option, ok
}

func (catalog taskFieldCatalog) resolveByKey(fieldKind string, key string) (taskFieldOptionState, bool) {
	lookupKey := normalizeTaskFieldLookupKey(key)
	switch strings.TrimSpace(fieldKind) {
	case taskFieldKindKind:
		option, ok := catalog.kindResolverAll[lookupKey]
		return option, ok
	case taskFieldKindPriority:
		option, ok := catalog.priorityResolverAll[lookupKey]
		return option, ok
	default:
		return taskFieldOptionState{}, false
	}
}

func (catalog taskFieldCatalog) resolveAny(fieldKind string, value string) (taskFieldOptionState, bool) {
	lookupKey := normalizeTaskFieldLookupKey(value)
	switch strings.TrimSpace(fieldKind) {
	case taskFieldKindKind:
		option, ok := catalog.kindResolverAll[lookupKey]
		return option, ok
	case taskFieldKindPriority:
		option, ok := catalog.priorityResolverAll[lookupKey]
		return option, ok
	default:
		return taskFieldOptionState{}, false
	}
}

func (catalog taskFieldCatalog) resolveActive(fieldKind string, value string) (taskFieldOptionState, bool) {
	lookupKey := normalizeTaskFieldLookupKey(value)
	switch strings.TrimSpace(fieldKind) {
	case taskFieldKindKind:
		option, ok := catalog.kindResolverActive[lookupKey]
		return option, ok
	case taskFieldKindPriority:
		option, ok := catalog.priorityResolverActive[lookupKey]
		return option, ok
	default:
		return taskFieldOptionState{}, false
	}
}

func loadTaskFieldOptionUsageCounts(ctx context.Context, queryer coredb.Queryer, workspaceID string, catalog taskFieldCatalog) (map[string]int64, error) {
	rows, err := queryer.Query(ctx, `
SELECT
    COALESCE(t.kind_option_id, ''),
    COALESCE(NULLIF(t.legacy_kind_value, ''), t.kind, ''),
    COALESCE(t.priority_option_id, ''),
    COALESCE(NULLIF(t.legacy_priority_value, ''), t.priority, '')
FROM tasks t
JOIN task_boards tb
  ON tb.id = t.board_id
 AND tb.workspace_id = $1
JOIN scopes task_scope
  ON task_scope.id = t.task_scope_id
 AND task_scope.workspace_id = $1
 AND task_scope.type = 'task'
 AND task_scope.status = 'active'
 AND task_scope.archived_at IS NULL
JOIN scopes task_channel
  ON task_channel.id = tb.channel_id
 AND task_channel.workspace_id = $1
 AND task_channel.type = 'channel'
 AND task_channel.status = 'active'
 AND task_channel.archived_at IS NULL
WHERE t.channel_id = tb.channel_id
`, workspaceID)
	if err != nil {
		return nil, fmt.Errorf("load task field option usage: %w", err)
	}
	defer rows.Close()

	usageCounts := make(map[string]int64)
	for rows.Next() {
		var kindOptionID string
		var kindRawValue string
		var priorityOptionID string
		var priorityRawValue string
		if err := rows.Scan(&kindOptionID, &kindRawValue, &priorityOptionID, &priorityRawValue); err != nil {
			return nil, fmt.Errorf("scan task field option usage: %w", err)
		}
		if option, ok := resolveTaskFieldUsageOption(catalog, taskFieldKindKind, kindOptionID, kindRawValue); ok {
			usageCounts[option.ID]++
		}
		if option, ok := resolveTaskFieldUsageOption(catalog, taskFieldKindPriority, priorityOptionID, priorityRawValue); ok {
			usageCounts[option.ID]++
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate task field option usage: %w", err)
	}
	return usageCounts, nil
}

func resolveTaskFieldUsageOption(catalog taskFieldCatalog, fieldKind string, optionID string, rawValue string) (taskFieldOptionState, bool) {
	if option, ok := catalog.optionByID(optionID); ok {
		return option, true
	}
	return catalog.resolveAny(fieldKind, rawValue)
}

func insertTaskFieldOption(ctx context.Context, execer coredb.QueryExecer, workspaceID string, catalog taskFieldCatalog, input TaskFieldOptionInput) error {
	fieldKind, err := validateTaskFieldKind(input.FieldKind)
	if err != nil {
		return err
	}
	key, err := normalizeTaskFieldOptionKey(input.Key)
	if err != nil {
		return err
	}
	label := strings.TrimSpace(input.Label)
	if label == "" {
		return badRequest("label is required")
	}
	aliases := normalizeTaskFieldOptionAliases(input.Aliases, key)
	if aliases == nil {
		aliases = []string{}
	}
	if err := ensureTaskFieldOptionLookupAvailability(catalog, fieldKind, "", key, aliases); err != nil {
		return err
	}

	position := int64(len(catalog.kindOptions))
	if fieldKind == taskFieldKindPriority {
		position = int64(len(catalog.priorityOptions))
	}
	optionID, err := newTaskFieldOptionID()
	if err != nil {
		return fmt.Errorf("generate task field option id: %w", err)
	}
	if _, err := execer.Exec(ctx, `
INSERT INTO task_field_options (
    id,
    workspace_id,
    field_kind,
    option_key,
    display_label,
    position,
    is_active,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, $5, $6, TRUE, NOW(), NOW())
`, optionID, workspaceID, fieldKind, key, label, position); err != nil {
		if coredb.IsUniqueViolation(err) {
			return conflict("task field option already exists")
		}
		return fmt.Errorf("insert task field option: %w", err)
	}
	if err := replaceTaskFieldOptionAliases(ctx, execer, optionID, workspaceID, fieldKind, aliases); err != nil {
		return err
	}
	return nil
}

func updateTaskFieldOption(ctx context.Context, execer coredb.QueryExecer, workspaceID string, catalog taskFieldCatalog, optionID string, input TaskFieldOptionInput) error {
	existing, ok := catalog.optionByID(optionID)
	if !ok {
		return ErrNotFound
	}

	label := strings.TrimSpace(input.Label)
	if label == "" {
		label = existing.Label
	}
	aliases := existing.Aliases
	if input.Aliases != nil {
		aliases = normalizeTaskFieldOptionAliases(input.Aliases, existing.Key)
	}
	if err := ensureTaskFieldOptionLookupAvailability(catalog, existing.FieldKind, existing.ID, existing.Key, aliases); err != nil {
		return err
	}

	if _, err := execer.Exec(ctx, `
UPDATE task_field_options
SET display_label = $3,
    updated_at = NOW()
WHERE id = $1
  AND workspace_id = $2
`, existing.ID, workspaceID, label); err != nil {
		return fmt.Errorf("update task field option: %w", err)
	}
	if input.Aliases != nil {
		if err := replaceTaskFieldOptionAliases(ctx, execer, existing.ID, workspaceID, existing.FieldKind, aliases); err != nil {
			return err
		}
	}
	return nil
}

func replaceTaskFieldOptionAliases(ctx context.Context, execer coredb.QueryExecer, optionID string, workspaceID string, fieldKind string, aliases []string) error {
	if _, err := execer.Exec(ctx, `
DELETE FROM task_field_option_aliases
WHERE option_id = $1
`, optionID); err != nil {
		return fmt.Errorf("delete task field option aliases: %w", err)
	}
	if len(aliases) == 0 {
		return nil
	}

	rows := make([]taskFieldOptionAliasInsert, 0, len(aliases))
	for _, alias := range aliases {
		aliasID, err := newTaskFieldOptionAliasID()
		if err != nil {
			return fmt.Errorf("generate task field option alias id: %w", err)
		}
		rows = append(rows, taskFieldOptionAliasInsert{
			ID:          aliasID,
			OptionID:    optionID,
			WorkspaceID: workspaceID,
			FieldKind:   fieldKind,
			AliasKey:    alias,
		})
	}
	query, args := buildTaskFieldOptionAliasInsertQuery(rows, false)
	if _, err := execer.Exec(ctx, query, args...); err != nil {
		if coredb.IsUniqueViolation(err) {
			return conflict("task field option alias already exists")
		}
		return fmt.Errorf("insert task field option aliases: %w", err)
	}
	return nil
}

func ensureTaskFieldOptionLookupAvailability(catalog taskFieldCatalog, fieldKind string, optionID string, key string, aliases []string) error {
	for _, lookup := range append([]string{key}, aliases...) {
		option, ok := catalog.resolveAny(fieldKind, lookup)
		if !ok {
			continue
		}
		if option.ID == strings.TrimSpace(optionID) {
			continue
		}
		if normalizeTaskFieldLookupKey(option.Key) == normalizeTaskFieldLookupKey(lookup) {
			return conflict("task field option already exists")
		}
		return conflict("task field option alias already exists")
	}
	return nil
}

func validateTaskFieldKind(value string) (string, error) {
	switch strings.TrimSpace(value) {
	case taskFieldKindKind:
		return taskFieldKindKind, nil
	case taskFieldKindPriority:
		return taskFieldKindPriority, nil
	default:
		return "", badRequest("fieldKind is invalid")
	}
}

func normalizeTaskFieldOptionKey(value string) (string, error) {
	key := normalizeTaskFieldLookupKey(value)
	if key == "" {
		return "", badRequest("key is required")
	}
	for _, runeValue := range key {
		if (runeValue >= 'a' && runeValue <= 'z') || (runeValue >= '0' && runeValue <= '9') || runeValue == '-' || runeValue == '_' {
			continue
		}
		return "", badRequest("key is invalid")
	}
	return key, nil
}

func normalizeTaskFieldOptionAliases(aliases []string, key string) []string {
	if aliases == nil {
		return nil
	}
	normalizedKey := normalizeTaskFieldLookupKey(key)
	deduped := make([]string, 0, len(aliases))
	seen := make(map[string]bool)
	for _, alias := range aliases {
		normalized := normalizeTaskFieldLookupKey(alias)
		if normalized == "" || normalized == normalizedKey || seen[normalized] {
			continue
		}
		seen[normalized] = true
		deduped = append(deduped, normalized)
	}
	sort.Strings(deduped)
	return deduped
}

func normalizeTaskFieldLookupKey(value string) string {
	return strings.ToLower(strings.TrimSpace(value))
}

func taskFieldOptionsFromStates(states []taskFieldOptionState, usageCounts map[string]int64) []TaskFieldOption {
	options := make([]TaskFieldOption, 0, len(states))
	for _, state := range states {
		options = append(options, taskFieldOptionFromState(state, usageCounts))
	}
	return options
}

func taskFieldOptionFromState(state taskFieldOptionState, usageCounts map[string]int64) TaskFieldOption {
	return TaskFieldOption{
		ID:         state.ID,
		FieldKind:  state.FieldKind,
		Key:        state.Key,
		Label:      state.Label,
		Aliases:    append([]string(nil), state.Aliases...),
		Position:   state.Position,
		IsActive:   state.IsActive,
		UsageCount: usageCounts[state.ID],
	}
}

func (txs TxStore) resolveTaskFieldUpdate(ctx context.Context, execer coredb.QueryExecer, workspaceID string, fieldKind string, optionID *string, rawValue *string, strict bool) (resolvedTaskFieldUpdate, error) {
	if optionID == nil && rawValue == nil {
		return resolvedTaskFieldUpdate{}, nil
	}
	catalog, err := txs.ensureWorkspaceTaskFieldCatalog(ctx, execer, workspaceID)
	if err != nil {
		return resolvedTaskFieldUpdate{}, err
	}
	if optionID != nil {
		trimmedOptionID := strings.TrimSpace(*optionID)
		if trimmedOptionID == "" {
			return resolvedTaskFieldUpdate{Present: true}, nil
		}
		option, ok := catalog.optionByID(trimmedOptionID)
		if !ok || option.FieldKind != fieldKind || !option.IsActive {
			return resolvedTaskFieldUpdate{}, badRequest(fieldKind + "OptionId is invalid")
		}
		return resolvedTaskFieldUpdate{
			Present:   true,
			EchoValue: option.Key,
			OptionID:  option.ID,
		}, nil
	}

	trimmedRawValue := strings.TrimSpace(*rawValue)
	if trimmedRawValue == "" {
		return resolvedTaskFieldUpdate{Present: true}, nil
	}
	if option, ok := catalog.resolveActive(fieldKind, trimmedRawValue); ok {
		return resolvedTaskFieldUpdate{
			Present:   true,
			EchoValue: option.Key,
			OptionID:  option.ID,
		}, nil
	}
	if strict {
		return resolvedTaskFieldUpdate{}, badRequest(fieldKind + " must match an active task option; call task_options to discover valid values")
	}
	return resolvedTaskFieldUpdate{
		Present:     true,
		EchoValue:   trimmedRawValue,
		LegacyValue: trimmedRawValue,
	}, nil
}