package taskboard

import (
	"context"
	"fmt"
	"strconv"
	"strings"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/taskcompat"
)

// browseSearchTextSQL is the canonical task profile search text expression for
// task browse/list surfaces. Task discussion message body search remains owned
// by the message search substrate; browse query builders consume resolved task
// IDs instead of keeping private copies of this profile expression.
func browseSearchTextSQL() string {
	return `LOWER(CONCAT_WS(
    '\n',
    CONCAT('#t', t.task_number),
    CONCAT('t', t.task_number),
    COALESCE(t.title, ''),
    COALESCE(t.description, ''),
    COALESCE(NULLIF(assignee_user.display_name, ''), NULLIF(assignee_member.display_name, ''), assignee_user.name, NULLIF(assignee_agent.name, ''), ''),
    COALESCE(t.blocked_reason, ''),
    COALESCE(t.source_thread_ref, ''),
    COALESCE(NULLIF(kind_option.display_label, ''), NULLIF(t.legacy_kind_value, ''), NULLIF(t.kind, ''), ''),
    COALESCE(NULLIF(priority_option.display_label, ''), NULLIF(t.legacy_priority_value, ''), NULLIF(t.priority, ''), '')
))`
}

func normalizeBrowseSearchQuery(value string) string {
	return strings.ToLower(strings.Join(strings.Fields(strings.TrimSpace(value)), " "))
}

func browseSearchLikePattern(value string) string {
	normalized := normalizeBrowseSearchQuery(value)
	if normalized == "" {
		return ""
	}
	return "%" + normalized + "%"
}

func buildBrowseSearchCondition(placeholder string) string {
	return browseSearchTextSQL() + " LIKE " + placeholder
}

// taskSearchPlan is the canonical task profile search plan for task browse
// surfaces. The browse pipeline supplies scope/visibility inputs and consumes
// the returned task IDs before applying its filters and cursor rules.
type taskSearchPlan struct {
	WorkspaceID string
	MemberID    string
	ChannelID   string
	Query       string
}

type taskSearchResult struct {
	NormalizedQuery string
	TaskIDs         []string
}

type taskSearchSQLBuilder struct {
	args []any
}

func (builder *taskSearchSQLBuilder) add(value any) string {
	builder.args = append(builder.args, value)
	return fmt.Sprintf("$%d", len(builder.args))
}

// searchTaskProfileIDs resolves the task profile search plan into canonical
// task IDs. It owns query normalization, profile SQL, and workspace/channel
// visibility for the search stage; downstream browse queries only
// consume the TaskIDs as an already-resolved search result.
func searchTaskProfileIDs(ctx context.Context, queryer coredb.Queryer, plan taskSearchPlan) (taskSearchResult, error) {
	query, args, normalized, err := buildTaskProfileSearchIDsQuery(plan)
	if err != nil {
		return taskSearchResult{}, err
	}
	if normalized == "" {
		return taskSearchResult{NormalizedQuery: "", TaskIDs: []string{}}, nil
	}

	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return taskSearchResult{}, fmt.Errorf("search task profile ids: %w", err)
	}
	defer rows.Close()

	taskIDs := []string{}
	for rows.Next() {
		var taskID string
		if err := rows.Scan(&taskID); err != nil {
			return taskSearchResult{}, fmt.Errorf("scan task profile search id: %w", err)
		}
		taskIDs = append(taskIDs, taskID)
	}
	if err := rows.Err(); err != nil {
		return taskSearchResult{}, fmt.Errorf("iterate task profile search ids: %w", err)
	}
	return taskSearchResult{NormalizedQuery: normalized, TaskIDs: taskIDs}, nil
}

func buildTaskProfileSearchIDsQuery(plan taskSearchPlan) (string, []any, string, error) {
	normalized := normalizeBrowseSearchQuery(plan.Query)
	if normalized == "" {
		return "", nil, "", nil
	}
	workspaceID := strings.TrimSpace(plan.WorkspaceID)
	if workspaceID == "" {
		return "", nil, "", fmt.Errorf("workspaceID is required")
	}
	channelID := strings.TrimSpace(plan.ChannelID)
	memberID := strings.TrimSpace(plan.MemberID)
	if channelID == "" && memberID == "" {
		return "", nil, "", fmt.Errorf("memberID is required for workspace task search")
	}
	if taskNumber, ok := parseExactTaskNumberSearchQuery(normalized); ok {
		return buildTaskNumberSearchIDsQuery(workspaceID, channelID, memberID, normalized, taskNumber)
	}

	builder := &taskSearchSQLBuilder{}
	workspacePlaceholder := builder.add(workspaceID)
	query := strings.Builder{}
	query.WriteString(`SELECT t.id
`)
	query.WriteString(taskProfileSearchFromJoinsSQL())
	conditions := []string{"tb.workspace_id = " + workspacePlaceholder}
	if channelID != "" {
		conditions = append(conditions, "tb.channel_id = "+builder.add(channelID))
	} else {
		query.WriteString(`JOIN scope_memberships search_sm
  ON search_sm.scope_id = tb.channel_id
 AND search_sm.removed_at IS NULL
 AND search_sm.archived_at IS NULL
 AND search_sm.effective_to_seq IS NULL
 AND search_sm.visibility = 'public'
 AND search_sm.member_id = `)
		query.WriteString(builder.add(memberID))
		query.WriteByte('\n')
	}
	conditions = append(conditions, buildBrowseSearchCondition(builder.add("%"+normalized+"%")))
	query.WriteString("WHERE ")
	query.WriteString(strings.Join(conditions, "\n  AND "))
	query.WriteString("\nORDER BY t.id ASC\n")
	return query.String(), builder.args, normalized, nil
}

func parseExactTaskNumberSearchQuery(normalized string) (int64, bool) {
	token := strings.TrimSpace(normalized)
	var rawNumber string
	switch {
	case strings.HasPrefix(token, "#t"):
		rawNumber = strings.TrimPrefix(token, "#t")
	case strings.HasPrefix(token, "t"):
		rawNumber = strings.TrimPrefix(token, "t")
	default:
		return 0, false
	}
	if rawNumber == "" || (len(rawNumber) > 1 && rawNumber[0] == '0') {
		return 0, false
	}
	for _, char := range rawNumber {
		if char < '0' || char > '9' {
			return 0, false
		}
	}
	taskNumber, err := strconv.ParseInt(rawNumber, 10, 64)
	if err != nil || taskNumber <= 0 {
		return 0, false
	}
	return taskNumber, true
}

func buildTaskNumberSearchIDsQuery(workspaceID string, channelID string, memberID string, normalized string, taskNumber int64) (string, []any, string, error) {
	builder := &taskSearchSQLBuilder{}
	workspacePlaceholder := builder.add(workspaceID)
	query := strings.Builder{}
	query.WriteString(`SELECT t.id
` + taskProfileSearchFromJoinsSQL())
	conditions := []string{"tb.workspace_id = " + workspacePlaceholder}
	if channelID != "" {
		conditions = append(conditions, "tb.channel_id = "+builder.add(channelID))
	} else {
		query.WriteString(`JOIN scope_memberships search_sm
  ON search_sm.scope_id = tb.channel_id
 AND search_sm.removed_at IS NULL
 AND search_sm.archived_at IS NULL
 AND search_sm.effective_to_seq IS NULL
 AND search_sm.visibility = 'public'
 AND search_sm.member_id = `)
		query.WriteString(builder.add(memberID))
		query.WriteByte('\n')
	}
	conditions = append(conditions, "t.task_number = "+builder.add(taskNumber))
	query.WriteString("WHERE ")
	query.WriteString(strings.Join(conditions, "\n  AND "))
	query.WriteString("\nORDER BY t.id ASC\n")
	return query.String(), builder.args, normalized, nil
}

func taskProfileSearchFromJoinsSQL() string {
	return `FROM tasks t
JOIN task_boards tb ON tb.id = t.board_id
JOIN scopes task_board_channel
  ON task_board_channel.id = tb.channel_id
 AND task_board_channel.workspace_id = tb.workspace_id
 AND task_board_channel.type = 'channel'
 AND task_board_channel.status = 'active'
 AND task_board_channel.archived_at IS NULL
 AND t.channel_id = tb.channel_id
JOIN scopes task_scope
  ON task_scope.id = t.task_scope_id
 AND task_scope.workspace_id = tb.workspace_id
 AND task_scope.type = 'task'
 AND task_scope.status = 'active'
 AND task_scope.archived_at IS NULL
LEFT JOIN task_field_options kind_option ON kind_option.id = NULLIF(t.kind_option_id, '')
LEFT JOIN task_field_options priority_option ON priority_option.id = NULLIF(t.priority_option_id, '')
LEFT JOIN members assignee_member ON assignee_member.id = ` + taskcompat.EffectiveAssigneeRefSQL("t") + `
LEFT JOIN users assignee_user ON assignee_member.type = 'human' AND assignee_user.id = assignee_member.user_id
LEFT JOIN agents assignee_agent ON assignee_member.type = 'agent' AND assignee_agent.member_id = assignee_member.id
`
}