package taskboard

import (
	"context"
	"fmt"
	"strings"

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

func updateTaskFieldOptionPositions(ctx context.Context, execer coredb.QueryExecer, workspaceID string, options []taskFieldOptionState) error {
	if len(options) == 0 {
		return nil
	}

	args := []any{strings.TrimSpace(workspaceID)}
	positionCases := make([]string, 0, len(options))
	idPlaceholders := make([]string, 0, len(options))
	for index, option := range options {
		optionID := strings.TrimSpace(option.ID)
		if optionID == "" {
			continue
		}
		idArg := len(args) + 1
		args = append(args, optionID)
		positionArg := len(args) + 1
		args = append(args, index)
		positionCases = append(positionCases, fmt.Sprintf("WHEN $%d THEN $%d", idArg, positionArg))
		idPlaceholders = append(idPlaceholders, fmt.Sprintf("$%d", idArg))
	}
	if len(idPlaceholders) == 0 {
		return nil
	}

	query := fmt.Sprintf(`
UPDATE task_field_options
SET position = CASE id
    %s
    ELSE position
  END,
    updated_at = NOW()
WHERE workspace_id = $1
  AND id IN (%s)
`, strings.Join(positionCases, "\n    "), strings.Join(idPlaceholders, ", "))
	if _, err := execer.Exec(ctx, query, args...); err != nil {
		return fmt.Errorf("update task field option positions: %w", err)
	}
	return nil
}

type taskFieldOptionDefaultInsert struct {
	ID          string
	WorkspaceID string
	FieldKind   string
	Key         string
	Label       string
	Position    int64
}

func insertDefaultTaskFieldOptions(ctx context.Context, execer coredb.QueryExecer, rows []taskFieldOptionDefaultInsert) error {
	if len(rows) == 0 {
		return nil
	}
	query, args := buildDefaultTaskFieldOptionsInsertQuery(rows)
	if _, err := execer.Exec(ctx, query, args...); err != nil {
		return fmt.Errorf("insert default task field options: %w", err)
	}
	return nil
}

func buildDefaultTaskFieldOptionsInsertQuery(rows []taskFieldOptionDefaultInsert) (string, []any) {
	values := make([]string, 0, len(rows))
	args := make([]any, 0, len(rows)*6)
	for _, row := range rows {
		base := len(args) + 1
		values = append(values, fmt.Sprintf("($%d, $%d, $%d, $%d, $%d, $%d, TRUE, NOW(), NOW())", base, base+1, base+2, base+3, base+4, base+5))
		args = append(args, row.ID, row.WorkspaceID, row.FieldKind, row.Key, row.Label, row.Position)
	}

	query := fmt.Sprintf(`
INSERT INTO task_field_options (
    id,
    workspace_id,
    field_kind,
    option_key,
    display_label,
    position,
    is_active,
    created_at,
    updated_at
) VALUES %s
ON DUPLICATE KEY UPDATE
    id = id
`, strings.Join(values, ",\n"))
	return query, args
}

type taskFieldOptionDefaultAlias struct {
	FieldKind string
	OptionKey string
	AliasKey  string
}

func loadTaskFieldOptionIDsForDefaultAliases(ctx context.Context, execer coredb.QueryExecer, workspaceID string, aliases []taskFieldOptionDefaultAlias) (map[string]string, error) {
	optionIDsByKey := make(map[string]string)
	if len(aliases) == 0 {
		return optionIDsByKey, nil
	}

	args := []any{strings.TrimSpace(workspaceID)}
	conditions := make([]string, 0, len(aliases))
	seenKeys := make(map[string]struct{}, len(aliases))
	for _, alias := range aliases {
		fieldKind := strings.TrimSpace(alias.FieldKind)
		optionKey := strings.TrimSpace(alias.OptionKey)
		if fieldKind == "" || optionKey == "" {
			continue
		}
		key := fieldKind + ":" + optionKey
		if _, seen := seenKeys[key]; seen {
			continue
		}
		seenKeys[key] = struct{}{}
		fieldKindArg := len(args) + 1
		args = append(args, fieldKind)
		optionKeyArg := len(args) + 1
		args = append(args, optionKey)
		conditions = append(conditions, fmt.Sprintf("(field_kind = $%d AND option_key = $%d)", fieldKindArg, optionKeyArg))
	}
	if len(conditions) == 0 {
		return optionIDsByKey, nil
	}

	query := fmt.Sprintf(`
SELECT id, field_kind, option_key
FROM task_field_options
WHERE workspace_id = $1
  AND (%s)
`, strings.Join(conditions, " OR "))
	rows, err := execer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("load default task field options: %w", err)
	}
	defer rows.Close()

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

type taskFieldOptionAliasInsert struct {
	ID          string
	OptionID    string
	WorkspaceID string
	FieldKind   string
	AliasKey    string
}

func insertDefaultTaskFieldOptionAliases(ctx context.Context, execer coredb.QueryExecer, rows []taskFieldOptionAliasInsert) error {
	if len(rows) == 0 {
		return nil
	}
	query, args := buildTaskFieldOptionAliasInsertQuery(rows, true)
	if _, err := execer.Exec(ctx, query, args...); err != nil {
		return fmt.Errorf("insert default task field option aliases: %w", err)
	}
	return nil
}

func buildTaskFieldOptionAliasInsertQuery(rows []taskFieldOptionAliasInsert, duplicateNoop bool) (string, []any) {
	values := make([]string, 0, len(rows))
	args := make([]any, 0, len(rows)*5)
	for _, row := range rows {
		base := len(args) + 1
		values = append(values, fmt.Sprintf("($%d, $%d, $%d, $%d, $%d, NOW(), NOW())", base, base+1, base+2, base+3, base+4))
		args = append(args, row.ID, row.OptionID, row.WorkspaceID, row.FieldKind, row.AliasKey)
	}

	duplicateClause := ""
	if duplicateNoop {
		duplicateClause = `
ON DUPLICATE KEY UPDATE
    id = id`
	}
	query := fmt.Sprintf(`
INSERT INTO task_field_option_aliases (
    id,
    option_id,
    workspace_id,
    field_kind,
    alias_key,
    created_at,
    updated_at
) VALUES %s
%s
`, strings.Join(values, ",\n"), duplicateClause)
	return query, args
}