package taskboard

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

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

const (
	taskBrowseActivityExpr        = "COALESCE(t.last_activity_at, t.updated_at, t.created_at)"
	taskBrowseTitleExpr           = "LOWER(COALESCE(t.title, ''))"
	taskBrowsePriorityMissingExpr = "CASE WHEN NULLIF(t.priority_option_id, '') IS NULL AND LOWER(TRIM(COALESCE(NULLIF(t.legacy_priority_value, ''), t.priority, ''))) = '' THEN 1 ELSE 0 END"
	taskBrowsePriorityLabelExpr   = "LOWER(COALESCE(NULLIF(priority_option.display_label, ''), NULLIF(t.legacy_priority_value, ''), NULLIF(t.priority, ''), ''))"
	taskBrowseCreatedByRefExpr    = "COALESCE(t.created_by_ref, t.created_by_member_id, '')"
	taskBrowseBoardColumnExpr     = "COALESCE(t.status, '')"
)

var taskBrowseAssigneeRefExpr = "COALESCE(" + taskcompat.EffectiveAssigneeRefSQL("t") + ", '')"

type taskBrowseSQLBuilder struct {
	args []any
}

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

func buildListTasksByIDsQuery(workspaceID string, taskIDs []string) (string, []any, error) {
	cte, cteArgs, _, err := coredb.BuildStringValuesCTE("requested_task_ids", "task_id", taskIDs, 2)
	if err != nil {
		return "", nil, err
	}

	query := "WITH " + cte + ",\n" + buildTaskViewRowsCTE("$1", `EXISTS (
      SELECT 1
      FROM requested_task_ids requested
      WHERE requested.task_id = t.id
  )`) + `
SELECT task_view_rows.*
FROM task_view_rows
ORDER BY task_number ASC, id ASC
`
	args := append([]any{workspaceID}, cteArgs...)
	return query, args, nil
}

func buildListTasksByNumbersQuery(workspaceID string, channelID string, taskNumbers []int64) (string, []any, error) {
	cte, cteArgs, _, err := coredb.BuildInt64ValuesCTE("requested_task_numbers", "task_number", taskNumbers, 3)
	if err != nil {
		return "", nil, err
	}

	query := "WITH " + cte + ",\n" + buildTaskViewRowsCTE("$1", "tb.channel_id = $2", `EXISTS (
      SELECT 1
      FROM requested_task_numbers requested
      WHERE requested.task_number = t.task_number
  )`) + `
SELECT task_view_rows.*
FROM task_view_rows
ORDER BY task_number ASC, id ASC
`
	args := append([]any{workspaceID, channelID}, cteArgs...)
	return query, args, nil
}

func buildListTasksByChannelQuery(workspaceID string, channelID string, includeSubtasks bool) (string, []any) {
	conditions := []string{"tb.channel_id = $2"}
	if !includeSubtasks {
		conditions = append(conditions, "t.parent_task_ref = ''")
	}
	query := "WITH " + buildTaskViewRowsCTE("$1", conditions...) + `
SELECT task_view_rows.*
FROM task_view_rows
ORDER BY task_number ASC, id ASC
`
	return query, []any{workspaceID, channelID}
}

func buildListWorkspaceTasksQuery(workspaceID string, memberID string, includeSubtasks bool) (string, []any) {
	conditions := []string{}
	if !includeSubtasks {
		conditions = append(conditions, "t.parent_task_ref = ''")
	}
	query := "WITH " + buildTaskViewRowsCTE("$1", conditions...) + `
SELECT task_view_rows.*
FROM task_view_rows
JOIN scope_memberships sm
  ON sm.scope_id = task_view_rows.channel_id
 AND sm.member_id = $2
 AND sm.visibility = 'public'
 AND sm.removed_at IS NULL
`
	query += `
ORDER BY channel_id ASC, task_number ASC, id ASC
`
	return query, []any{workspaceID, memberID}
}

func buildTaskBrowseRowsQuery(scope taskBrowseScope, cursor TaskBrowseCursor) (string, []any, error) {
	baseQuery, args, err := buildTaskBrowseSelectQuery(scope, cursor, true)
	if err != nil {
		return "", nil, err
	}
	builder := strings.Builder{}
	builder.WriteString(baseQuery)
	builder.WriteString("\nORDER BY ")
	builder.WriteString(taskBrowseOrderBy(scope.Query.SortMode))
	builder.WriteString("\nLIMIT ")
	placeholder := fmt.Sprintf("$%d", len(args)+1)
	builder.WriteString(placeholder)
	builder.WriteByte('\n')
	args = append(args, scope.Query.Limit+1)
	return builder.String(), args, nil
}

func buildTaskBrowseCountQuery(scope taskBrowseScope) (string, []any, error) {
	baseQuery, args, err := buildTaskBrowseFromWhereClause(scope, TaskBrowseCursor{}, false)
	if err != nil {
		return "", nil, err
	}
	return "SELECT COUNT(*)\n" + baseQuery, args, nil
}

func buildTaskBrowseBoardCountsQuery(scope taskBrowseScope, layout taskBoardLayout) (string, []any, error) {
	baseQuery, args, err := buildTaskBrowseFromWhereClause(scope, TaskBrowseCursor{}, false)
	if err != nil {
		return "", nil, err
	}
	groupExpr := taskBrowseBoardColumnExpr
	return "SELECT " + groupExpr + " AS column_key, COUNT(*)\n" + baseQuery + "\nGROUP BY 1", args, nil
}

func buildTaskBrowseBoardRowsQuery(scope taskBrowseScope, layout taskBoardLayout, columnKey string, cursor TaskBrowseCursor) (string, []any, error) {
	sqlBuilder := &taskBrowseSQLBuilder{}
	columnCondition, err := buildTaskBrowseBoardColumnCondition(layout, columnKey, sqlBuilder)
	if err != nil {
		return "", nil, err
	}
	baseQuery, args, err := buildTaskBrowseSelectQueryWithBuilder(scope, cursor, true, sqlBuilder, columnCondition)
	if err != nil {
		return "", nil, err
	}
	builder := strings.Builder{}
	builder.WriteString(baseQuery)
	builder.WriteString("\nORDER BY ")
	builder.WriteString(taskBrowseOrderBy(scope.Query.SortMode))
	builder.WriteString("\nLIMIT ")
	placeholder := fmt.Sprintf("$%d", len(args)+1)
	builder.WriteString(placeholder)
	builder.WriteByte('\n')
	args = append(args, scope.Query.Limit+1)
	return builder.String(), args, nil
}

func buildTaskBrowseSelectQuery(scope taskBrowseScope, cursor TaskBrowseCursor, includeCursor bool, extraConditions ...string) (string, []any, error) {
	return buildTaskBrowseSelectQueryWithBuilder(scope, cursor, includeCursor, &taskBrowseSQLBuilder{}, extraConditions...)
}

func buildTaskBrowseSelectQueryWithBuilder(scope taskBrowseScope, cursor TaskBrowseCursor, includeCursor bool, sqlBuilder *taskBrowseSQLBuilder, extraConditions ...string) (string, []any, error) {
	baseQuery, err := buildTaskBrowseFromWhereClauseWithBuilder(scope, cursor, includeCursor, sqlBuilder, extraConditions...)
	if err != nil {
		return "", nil, err
	}
	return taskSelectColumns + baseQuery, sqlBuilder.args, nil
}

func buildTaskBrowseFromWhereClause(scope taskBrowseScope, cursor TaskBrowseCursor, includeCursor bool, extraConditions ...string) (string, []any, error) {
	sqlBuilder := &taskBrowseSQLBuilder{}
	query, err := buildTaskBrowseFromWhereClauseWithBuilder(scope, cursor, includeCursor, sqlBuilder, extraConditions...)
	if err != nil {
		return "", nil, err
	}
	return query, sqlBuilder.args, nil
}

func buildTaskBrowseFromWhereClauseWithBuilder(scope taskBrowseScope, cursor TaskBrowseCursor, includeCursor bool, sqlBuilder *taskBrowseSQLBuilder, extraConditions ...string) (string, error) {
	query := strings.Builder{}
	query.WriteString(taskSelectFromJoins)
	if strings.TrimSpace(scope.ChannelID) == "" {
		query.WriteString(`
JOIN scope_memberships browse_sm
  ON browse_sm.scope_id = tb.channel_id
 AND browse_sm.removed_at IS NULL
 AND browse_sm.visibility = 'public'
 AND browse_sm.member_id = `)
		query.WriteString(sqlBuilder.add(strings.TrimSpace(scope.MemberID)))
	}

	workspacePlaceholder := sqlBuilder.add(strings.TrimSpace(scope.WorkspaceID))
	conditions := []string{
		"tb.workspace_id = " + workspacePlaceholder,
	}
	if channelID := strings.TrimSpace(scope.ChannelID); channelID != "" {
		conditions = append(conditions, "tb.channel_id = "+sqlBuilder.add(channelID))
	}
	if !scope.Query.IncludeSubtasks {
		conditions = append(conditions, "t.parent_task_ref = ''")
	}
	statusCondition, err := buildTaskBrowseStatusCondition(sqlBuilder, scope.Query.StatusFilter)
	if err != nil {
		return "", err
	}
	if statusCondition != "" {
		conditions = append(conditions, statusCondition)
	}
	if strings.TrimSpace(scope.Query.SearchQuery) != "" {
		if !scope.SearchApplied {
			return "", badRequest("task search plan is required")
		}
		conditions = append(conditions, buildTaskBrowseSearchIDCondition(sqlBuilder, scope.SearchTaskIDs))
	}
	quickConditions, err := buildTaskBrowseQuickFilterConditions(sqlBuilder, scope.WorkspaceID, scope.MemberID, scope.Query.QuickFilters)
	if err != nil {
		return "", err
	}
	conditions = append(conditions, quickConditions...)
	structuredConditions, err := buildTaskBrowseStructuredFilterConditions(sqlBuilder, scope.Query.StructuredFilters)
	if err != nil {
		return "", err
	}
	conditions = append(conditions, structuredConditions...)
	conditions = append(conditions, extraConditions...)
	if includeCursor && cursor != (TaskBrowseCursor{}) {
		cursorClause, err := buildTaskBrowseCursorClause(sqlBuilder, scope.Query.SortMode, cursor)
		if err != nil {
			return "", err
		}
		if cursorClause != "" {
			conditions = append(conditions, cursorClause)
		}
	}

	query.WriteString("\nWHERE ")
	query.WriteString(strings.Join(conditions, "\n  AND "))
	return query.String(), nil
}

func buildTaskBrowseStatusCondition(builder *taskBrowseSQLBuilder, status string) (string, error) {
	switch strings.TrimSpace(status) {
	case "":
		return "", nil
	case "active":
		return "t.status <> " + builder.add(string(StatusDone)), nil
	case string(StatusTodo), string(StatusInProgress), string(StatusInReview), string(StatusDone):
		return "t.status = " + builder.add(strings.TrimSpace(status)), nil
	default:
		return "", badRequest("invalid status")
	}
}

func buildTaskBrowseBoardColumnCondition(layout taskBoardLayout, columnKey string, builder *taskBrowseSQLBuilder) (string, error) {
	trimmedKey := strings.TrimSpace(columnKey)
	if trimmedKey == "" {
		return "", badRequest("invalid board column")
	}
	if _, ok := allowedTaskBrowseColumnKeys[trimmedKey]; !ok {
		return "", badRequest("invalid board column")
	}
	return taskBrowseBoardColumnExpr + " = " + builder.add(trimmedKey), nil
}

func buildTaskBrowseSearchIDCondition(builder *taskBrowseSQLBuilder, taskIDs []string) string {
	placeholders := make([]string, 0, len(taskIDs))
	for _, taskID := range taskIDs {
		trimmed := strings.TrimSpace(taskID)
		if trimmed == "" {
			continue
		}
		placeholders = append(placeholders, builder.add(trimmed))
	}
	if len(placeholders) == 0 {
		return "FALSE"
	}
	return "t.id IN (" + strings.Join(placeholders, ", ") + ")"
}

func buildTaskBrowseOwnedAgentRefCondition(builder *taskBrowseSQLBuilder, workspaceID string, ownerMemberPlaceholder string, memberExprs ...string) string {
	workspacePlaceholder := builder.add(strings.TrimSpace(workspaceID))
	memberValues := make([]string, 0, len(memberExprs))
	for _, memberExpr := range memberExprs {
		if strings.TrimSpace(memberExpr) == "" {
			continue
		}
		memberValues = append(memberValues, "NULLIF("+memberExpr+", '')")
	}
	if len(memberValues) == 0 {
		return "FALSE"
	}
	return `EXISTS (
  SELECT 1
  FROM agents owned_agent
  JOIN machines owned_machine
    ON owned_machine.workspace_id = owned_agent.workspace_id
   AND owned_machine.id = owned_agent.machine_id
   AND owned_machine.status = 'active'
  JOIN members owner_member
    ON owner_member.workspace_id = owned_agent.workspace_id
   AND owner_member.user_id = owned_machine.created_by_user_id
   AND owner_member.type = 'human'
   AND owner_member.status = 'active'
  WHERE owned_agent.workspace_id = ` + workspacePlaceholder + `
    AND owned_agent.status <> 'archived'
    AND owner_member.id = ` + ownerMemberPlaceholder + `
    AND owned_agent.member_id IN (` + strings.Join(memberValues, ", ") + `)
)`
}

func buildTaskBrowseRelatedToMemberCondition(builder *taskBrowseSQLBuilder, workspaceID string, memberID string) string {
	memberPlaceholder := builder.add(strings.TrimSpace(memberID))
	return "(" + strings.Join([]string{
		taskBrowseAssigneeRefExpr + " = " + memberPlaceholder,
		taskBrowseCreatedByRefExpr + " = " + memberPlaceholder,
		buildTaskBrowseOwnedAgentRefCondition(
			builder,
			workspaceID,
			memberPlaceholder,
			taskBrowseAssigneeRefExpr,
			taskBrowseCreatedByRefExpr,
		),
	}, " OR ") + ")"
}

func buildTaskBrowseQuickFilterConditions(builder *taskBrowseSQLBuilder, workspaceID string, memberID string, filters []string) ([]string, error) {
	conditions := make([]string, 0, len(filters))
	trimmedMemberID := strings.TrimSpace(memberID)
	for _, filter := range filters {
		switch strings.TrimSpace(filter) {
		case "related_to_me":
			conditions = append(conditions, buildTaskBrowseRelatedToMemberCondition(builder, workspaceID, trimmedMemberID))
		case "assigned_to_agents":
			conditions = append(conditions, "assignee_member.type = 'agent'")
		case "blocked":
			conditions = append(conditions, "TRIM(t.blocked_reason) <> ''")
		case "needs_clarification":
			conditions = append(conditions, "t.status = 'todo' AND t.discussion_count > 0 AND "+taskBrowseAssigneeRefExpr+" = '' AND TRIM(t.blocked_reason) = ''")
		case "created_by_me":
			conditions = append(conditions, taskBrowseCreatedByRefExpr+" = "+builder.add(trimmedMemberID))
		case "":
		default:
			return nil, badRequest("quickFilters contains invalid value")
		}
	}
	return conditions, nil
}

func buildTaskBrowseStructuredFilterConditions(builder *taskBrowseSQLBuilder, filters map[string][]string) ([]string, error) {
	if len(filters) == 0 {
		return nil, nil
	}
	conditions := make([]string, 0, len(filters))
	for key, values := range filters {
		condition, err := buildTaskBrowseStructuredFilterCondition(builder, key, values)
		if err != nil {
			return nil, err
		}
		if strings.TrimSpace(condition) == "" {
			continue
		}
		conditions = append(conditions, condition)
	}
	return conditions, nil
}

func buildTaskBrowseStructuredFilterCondition(builder *taskBrowseSQLBuilder, key string, values []string) (string, error) {
	switch strings.TrimSpace(key) {
	case "status":
		return buildTaskBrowseExactFilterCondition(builder, "t.status", values)
	case "channel":
		return buildTaskBrowseExactFilterCondition(builder, "tb.channel_id", values)
	case "assignee":
		return buildTaskBrowseActorFilterCondition(builder, taskBrowseAssigneeRefExpr, "assignee_user.id", "assignee_agent.id", values)
	case "kind":
		return buildTaskBrowseFieldFilterCondition(builder, "t.kind_option_id", "kind_option.option_key", "LOWER(TRIM(COALESCE(NULLIF(t.legacy_kind_value, ''), t.kind, '')))", values)
	case "priority":
		return buildTaskBrowseFieldFilterCondition(builder, "t.priority_option_id", "priority_option.option_key", "LOWER(TRIM(COALESCE(NULLIF(t.legacy_priority_value, ''), t.priority, '')))", values)
	case "":
		return "", nil
	default:
		return "", badRequest("structuredFilters contains invalid key")
	}
}

func buildTaskBrowseExactFilterCondition(builder *taskBrowseSQLBuilder, expression string, values []string) (string, error) {
	placeholders := make([]string, 0, len(values))
	for _, value := range values {
		trimmed := strings.TrimSpace(value)
		if trimmed == "" {
			continue
		}
		placeholders = append(placeholders, builder.add(trimmed))
	}
	if len(placeholders) == 0 {
		return "", nil
	}
	return expression + " IN (" + strings.Join(placeholders, ", ") + ")", nil
}

func buildTaskBrowseActorFilterCondition(builder *taskBrowseSQLBuilder, memberExpr string, userExpr string, agentExpr string, values []string) (string, error) {
	includeNone := false
	ids := make([]string, 0, len(values))
	for _, value := range values {
		trimmed := strings.TrimSpace(value)
		switch trimmed {
		case "":
		case "none":
			includeNone = true
		default:
			ids = append(ids, trimmed)
		}
	}
	clauses := make([]string, 0, 2)
	if includeNone {
		clauses = append(clauses, "NULLIF("+memberExpr+", '') IS NULL")
	}
	if len(ids) > 0 {
		placeholders := make([]string, 0, len(ids))
		for _, value := range ids {
			placeholders = append(placeholders, builder.add(value))
		}
		clauses = append(clauses, "COALESCE(NULLIF("+agentExpr+", ''), NULLIF("+userExpr+", '')) IN ("+strings.Join(placeholders, ", ")+")")
	}
	if len(clauses) == 0 {
		return "", nil
	}
	return "(" + strings.Join(clauses, " OR ") + ")", nil
}

func buildTaskBrowseFieldFilterCondition(builder *taskBrowseSQLBuilder, optionIDExpr string, optionKeyExpr string, rawExpr string, values []string) (string, error) {
	includeNone := false
	includeLegacy := false
	keys := make([]string, 0, len(values))
	for _, value := range values {
		trimmed := strings.TrimSpace(value)
		switch trimmed {
		case "":
		case "none":
			includeNone = true
		case "legacy/custom":
			includeLegacy = true
		default:
			keys = append(keys, trimmed)
		}
	}
	clauses := make([]string, 0, 3)
	if includeNone {
		clauses = append(clauses, "NULLIF("+optionIDExpr+", '') IS NULL AND "+rawExpr+" = ''")
	}
	if includeLegacy {
		clauses = append(clauses, "NULLIF("+optionIDExpr+", '') IS NULL AND "+rawExpr+" <> ''")
	}
	if len(keys) > 0 {
		placeholders := make([]string, 0, len(keys))
		for _, value := range keys {
			placeholders = append(placeholders, builder.add(value))
		}
		clauses = append(clauses, optionKeyExpr+" IN ("+strings.Join(placeholders, ", ")+")")
	}
	if len(clauses) == 0 {
		return "", nil
	}
	return "(" + strings.Join(clauses, " OR ") + ")", nil
}

func taskBrowseOrderBy(sortMode string) string {
	switch normalizeTaskBoardSortMode(sortMode) {
	case "recently_updated":
		return taskBrowseActivityExpr + " DESC, t.task_number DESC, t.id DESC"
	case "created_time":
		return "t.created_at DESC, " + taskBrowseActivityExpr + " DESC, t.task_number DESC, t.id DESC"
	case "title":
		return taskBrowseTitleExpr + " ASC, t.created_at ASC, t.task_number ASC, t.id ASC"
	case "priority":
		return taskBrowsePriorityMissingExpr + " ASC, " + taskBrowsePriorityLabelExpr + " ASC, t.created_at ASC, t.task_number ASC, t.id ASC"
	case "manual":
		fallthrough
	default:
		return "t.created_at ASC, t.task_number ASC, t.id ASC"
	}
}

func buildTaskBrowseCursorClause(builder *taskBrowseSQLBuilder, sortMode string, cursor TaskBrowseCursor) (string, error) {
	cursor, err := normalizeTaskBrowseCursor(cursor, sortMode)
	if err != nil {
		return "", err
	}
	switch normalizeTaskBoardSortMode(sortMode) {
	case "recently_updated":
		activityPlaceholder := builder.add(cursor.ActivityAt)
		taskNumberPlaceholder := builder.add(cursor.TaskNumber)
		taskIDPlaceholder := builder.add(cursor.TaskID)
		return "(" + taskBrowseActivityExpr + " < " + activityPlaceholder + " OR (" + taskBrowseActivityExpr + " = " + activityPlaceholder + " AND t.task_number < " + taskNumberPlaceholder + ") OR (" + taskBrowseActivityExpr + " = " + activityPlaceholder + " AND t.task_number = " + taskNumberPlaceholder + " AND t.id < " + taskIDPlaceholder + "))", nil
	case "created_time":
		createdAtPlaceholder := builder.add(cursor.CreatedAt)
		activityAtPlaceholder := builder.add(cursor.ActivityAt)
		taskNumberPlaceholder := builder.add(cursor.TaskNumber)
		taskIDPlaceholder := builder.add(cursor.TaskID)
		return "(t.created_at < " + createdAtPlaceholder + " OR (t.created_at = " + createdAtPlaceholder + " AND " + taskBrowseActivityExpr + " < " + activityAtPlaceholder + ") OR (t.created_at = " + createdAtPlaceholder + " AND " + taskBrowseActivityExpr + " = " + activityAtPlaceholder + " AND t.task_number < " + taskNumberPlaceholder + ") OR (t.created_at = " + createdAtPlaceholder + " AND " + taskBrowseActivityExpr + " = " + activityAtPlaceholder + " AND t.task_number = " + taskNumberPlaceholder + " AND t.id < " + taskIDPlaceholder + "))", nil
	case "title":
		titlePlaceholder := builder.add(strings.TrimSpace(cursor.Title))
		createdAtPlaceholder := builder.add(cursor.CreatedAt)
		taskNumberPlaceholder := builder.add(cursor.TaskNumber)
		taskIDPlaceholder := builder.add(cursor.TaskID)
		return "(" + taskBrowseTitleExpr + " > " + titlePlaceholder + " OR (" + taskBrowseTitleExpr + " = " + titlePlaceholder + " AND t.created_at > " + createdAtPlaceholder + ") OR (" + taskBrowseTitleExpr + " = " + titlePlaceholder + " AND t.created_at = " + createdAtPlaceholder + " AND t.task_number > " + taskNumberPlaceholder + ") OR (" + taskBrowseTitleExpr + " = " + titlePlaceholder + " AND t.created_at = " + createdAtPlaceholder + " AND t.task_number = " + taskNumberPlaceholder + " AND t.id > " + taskIDPlaceholder + "))", nil
	case "priority":
		createdAtPlaceholder := builder.add(cursor.CreatedAt)
		missingPlaceholder := builder.add(boolToInt(cursor.PriorityMissing))
		labelPlaceholder := builder.add(strings.TrimSpace(cursor.PriorityLabel))
		taskNumberPlaceholder := builder.add(cursor.TaskNumber)
		taskIDPlaceholder := builder.add(cursor.TaskID)
		return "(" + taskBrowsePriorityMissingExpr + " > " + missingPlaceholder + " OR (" + taskBrowsePriorityMissingExpr + " = " + missingPlaceholder + " AND " + taskBrowsePriorityLabelExpr + " > " + labelPlaceholder + ") OR (" + taskBrowsePriorityMissingExpr + " = " + missingPlaceholder + " AND " + taskBrowsePriorityLabelExpr + " = " + labelPlaceholder + " AND t.created_at > " + createdAtPlaceholder + ") OR (" + taskBrowsePriorityMissingExpr + " = " + missingPlaceholder + " AND " + taskBrowsePriorityLabelExpr + " = " + labelPlaceholder + " AND t.created_at = " + createdAtPlaceholder + " AND t.task_number > " + taskNumberPlaceholder + ") OR (" + taskBrowsePriorityMissingExpr + " = " + missingPlaceholder + " AND " + taskBrowsePriorityLabelExpr + " = " + labelPlaceholder + " AND t.created_at = " + createdAtPlaceholder + " AND t.task_number = " + taskNumberPlaceholder + " AND t.id > " + taskIDPlaceholder + "))", nil
	case "manual":
		fallthrough
	default:
		createdAtPlaceholder := builder.add(cursor.CreatedAt)
		taskNumberPlaceholder := builder.add(cursor.TaskNumber)
		taskIDPlaceholder := builder.add(cursor.TaskID)
		return "(t.created_at > " + createdAtPlaceholder + " OR (t.created_at = " + createdAtPlaceholder + " AND t.task_number > " + taskNumberPlaceholder + ") OR (t.created_at = " + createdAtPlaceholder + " AND t.task_number = " + taskNumberPlaceholder + " AND t.id > " + taskIDPlaceholder + "))", nil
	}
}

func boolToInt(value bool) int {
	if value {
		return 1
	}
	return 0
}

func buildTaskByIDQuery(workspaceID string, taskID string) (string, []any) {
	query := "WITH " + buildTaskViewRowsCTE("$1", "t.id = $2") + `
SELECT task_view_rows.*
FROM task_view_rows
`
	return query, []any{workspaceID, taskID}
}

func buildTaskByNumberQuery(workspaceID string, channelID string, taskNumber int64) (string, []any) {
	query := "WITH " + buildTaskViewRowsCTE("$1", "tb.channel_id = $2", "t.task_number = $3") + `
SELECT task_view_rows.*
FROM task_view_rows
`
	return query, []any{workspaceID, channelID, taskNumber}
}

func buildTaskViewRowsCTE(workspaceParam string, extraConditions ...string) string {
	conditions := []string{"tb.workspace_id = " + strings.TrimSpace(workspaceParam)}
	for _, condition := range extraConditions {
		trimmed := strings.TrimSpace(condition)
		if trimmed == "" {
			continue
		}
		conditions = append(conditions, trimmed)
	}
	return "task_view_rows AS (\n" + taskSelectBase + `
WHERE ` + strings.Join(conditions, "\n  AND ") + `
)`
}

func insertTaskBoardIfMissing(ctx context.Context, execer interface {
	Exec(context.Context, string, ...any) (coredb.Result, error)
}, boardID string, workspaceID string, channelID string, nextTaskNumber int64, now time.Time) error {
	_, err := execer.Exec(ctx, `
INSERT INTO task_boards (
    id,
    channel_id,
    workspace_id,
    next_task_number,
    created_at,
    updated_at
) SELECT
    $1,
    $2,
    $3,
    $4,
    $5,
    $5
WHERE NOT EXISTS (
    SELECT 1
    FROM task_boards
    WHERE id = $1
       OR (workspace_id = $3 AND channel_id = $2)
)
`, boardID, channelID, workspaceID, nextTaskNumber, now)
	if err == nil {
		return nil
	}
	return fmt.Errorf("insert task board: %w", err)
}

func insertTaskCounterIfMissing(ctx context.Context, queryer interface {
	Exec(context.Context, string, ...any) (coredb.Result, error)
	QueryRow(context.Context, string, ...any) coredb.Row
}, boardID string, nextTaskNumber int64, now time.Time) error {
	var existingBoardID string
	if err := queryer.QueryRow(ctx, `
SELECT board_id
FROM task_counters
WHERE board_id = $1
`, boardID).Scan(&existingBoardID); err == nil {
		return nil
	} else if !coredb.IsNotFound(err) {
		return fmt.Errorf("check task counter: %w", err)
	}

	_, err := queryer.Exec(ctx, `
INSERT INTO task_counters (board_id, next_task_number, updated_at)
SELECT $1,
       GREATEST($2, COALESCE((
           SELECT MAX(t.task_number) + 1
           FROM tasks t
           WHERE t.board_id = $1
       ), $2)),
       $3
WHERE NOT EXISTS (
    SELECT 1
    FROM task_counters
    WHERE board_id = $1
)
`, boardID, nextTaskNumber, now)
	if err == nil {
		return nil
	}
	return fmt.Errorf("insert task counter: %w", err)
}

func allocateTaskNumbersWithExecer(ctx context.Context, queryer interface {
	Exec(context.Context, string, ...any) (coredb.Result, error)
	QueryRow(context.Context, string, ...any) coredb.Row
}, board taskBoardRecord, count int, now time.Time) (int64, error) {
	updateTag, err := queryer.Exec(ctx, `
UPDATE task_counters
SET next_task_number = next_task_number + $2,
    updated_at = $3
WHERE board_id = $1
`, board.ID, count, now)
	if err != nil {
		return 0, fmt.Errorf("bump task counter: %w", err)
	}
	if updateTag.RowsAffected() == 0 {
		return 0, ErrNotFound
	}

	var nextTaskNumber int64
	if err := queryer.QueryRow(ctx, `
SELECT next_task_number
FROM task_counters
WHERE board_id = $1
`, board.ID).Scan(&nextTaskNumber); err != nil {
		if coredb.IsNotFound(err) {
			return 0, ErrNotFound
		}
		return 0, fmt.Errorf("load task counter: %w", err)
	}

	return nextTaskNumber - int64(count), nil
}