package taskboard

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

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

const (
	taskMemberLookupDefaultLimit = 20
	taskMemberLookupMaxLimit     = 100
)

type taskMemberLookup struct {
	Role   string
	Query  string
	Limit  int
	Cursor string
	Exact  bool
}

type taskMemberOptionsQuery struct {
	sql    string
	args   []any
	limit  int
	offset int
}

type taskMemberOptionsPage struct {
	options    []TaskMemberOption
	hasMore    bool
	nextCursor string
	limit      int
}

const taskMemberOptionLabelExpr = `
CASE
  WHEN m.type = 'human' THEN COALESCE(NULLIF(u.display_name, ''), NULLIF(m.display_name, ''), NULLIF(u.name, ''), m.id)
  WHEN m.type = 'agent' THEN COALESCE(NULLIF(m.display_name, ''), NULLIF(a.name, ''), m.id)
  ELSE COALESCE(NULLIF(m.display_name, ''), m.id)
END`

const taskMemberOptionVisibilityCondition = `
m.workspace_id = $2
  AND m.type IN ('human', 'agent')
  AND (m.type <> 'agent' OR a.id IS NOT NULL)
  AND (
      sm.visibility = 'public'
      OR (
        m.type = 'agent'
        AND sm.visibility = 'private'
        AND (
          sm.owner_member_id = $3
          OR sm.member_id = $3
          OR EXISTS (
            SELECT 1
            FROM scope_memberships actor_sm
            WHERE actor_sm.scope_id = sm.scope_id
              AND actor_sm.member_id = $3
              AND actor_sm.visibility = 'private'
              AND actor_sm.removed_at IS NULL
              AND actor_sm.owner_member_id = sm.owner_member_id
          )
        )
      )
  )`

func (store *Store) ListTaskOptions(ctx context.Context, request ListTaskOptionsRequest) (ListTaskOptionsResponse, error) {
	var out ListTaskOptionsResponse
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var err error
		out, err = store.InTx(tx).ListTaskOptions(ctx, request)
		return err
	})
	if err != nil {
		return out, err
	}
	return out, nil
}

func (txs TxStore) ListTaskOptions(ctx context.Context, request ListTaskOptionsRequest) (ListTaskOptionsResponse, error) {
	workspaceID, err := request.Context.requireWorkspaceID()
	if err != nil {
		return ListTaskOptionsResponse{}, err
	}
	agent, err := request.Context.requireAgentActor()
	if err != nil {
		return ListTaskOptionsResponse{}, err
	}
	channelID, err := txs.resolveAccessibleAgentBoard(ctx, txs.tx, agent, request.Board)
	if err != nil {
		return ListTaskOptionsResponse{}, err
	}

	if err := EnsureDefaultTaskFieldOptions(ctx, txs.tx, workspaceID); err != nil {
		return ListTaskOptionsResponse{}, err
	}
	catalog, err := loadTaskFieldCatalog(ctx, txs.tx, workspaceID)
	if err != nil {
		return ListTaskOptionsResponse{}, err
	}
	role := strings.TrimSpace(request.Role)
	if role == "" {
		role = "assignee"
	}
	memberPage, err := loadTaskMemberOptions(ctx, txs.tx, workspaceID, channelID, agent.MemberID, taskMemberLookup{
		Role:   role,
		Query:  request.Query,
		Limit:  request.Limit,
		Cursor: request.Cursor,
	})
	if err != nil {
		return ListTaskOptionsResponse{}, err
	}
	return ListTaskOptionsResponse{
		PriorityOptions: taskFieldOptionsFromStates(catalog.priorityOptions, nil),
		KindOptions:     taskFieldOptionsFromStates(catalog.kindOptions, nil),
		AssigneeOptions: memberPage.options,
		Limit:           memberPage.limit,
		HasMore:         memberPage.hasMore,
		NextCursor:      memberPage.nextCursor,
	}, nil
}

func loadTaskMemberOptions(ctx context.Context, queryer coredb.Queryer, workspaceID string, channelID string, actorMemberID string, lookup taskMemberLookup) (taskMemberOptionsPage, error) {
	query, err := buildTaskMemberOptionsQuery(workspaceID, channelID, actorMemberID, lookup)
	if err != nil {
		return taskMemberOptionsPage{}, err
	}
	rows, err := queryer.Query(ctx, query.sql, query.args...)
	if err != nil {
		return taskMemberOptionsPage{}, fmt.Errorf("load task member options: %w", err)
	}
	defer rows.Close()

	options := make([]TaskMemberOption, 0, query.limit+1)
	for rows.Next() {
		var option TaskMemberOption
		var actor taskActorProjection
		if err := rows.Scan(
			&option.MemberID,
			&actor.MemberType,
			&actor.MemberID,
			&actor.UserID,
			&actor.UserDisplayName,
			&actor.MemberDisplayName,
			&actor.UserName,
			&actor.AgentID,
			&actor.AgentName,
		); err != nil {
			return taskMemberOptionsPage{}, fmt.Errorf("scan task member option: %w", err)
		}
		option.Type = trimTaskNullString(actor.MemberType)
		option.UserID = trimTaskNullString(actor.UserID)
		option.AgentID = trimTaskNullString(actor.AgentID)
		memberID := strings.TrimSpace(option.MemberID)
		switch option.Type {
		case memberTypeHuman:
			option.Name = firstTaskString(trimTaskNullString(actor.UserName), trimTaskNullString(actor.MemberDisplayName), memberID)
			option.Label = firstTaskString(trimTaskNullString(actor.UserDisplayName), trimTaskNullString(actor.MemberDisplayName), trimTaskNullString(actor.UserName), memberID)
		case memberTypeAgent:
			option.Name = firstTaskString(trimTaskNullString(actor.AgentName), trimTaskNullString(actor.MemberDisplayName), memberID)
			option.Label = firstTaskString(trimTaskNullString(actor.MemberDisplayName), trimTaskNullString(actor.AgentName), memberID)
		default:
			option.Name = firstTaskString(trimTaskNullString(actor.MemberDisplayName), memberID)
			option.Label = option.Name
		}
		options = append(options, option)
	}
	if err := rows.Err(); err != nil {
		return taskMemberOptionsPage{}, fmt.Errorf("iterate task member options: %w", err)
	}
	hasMore := len(options) > query.limit
	if hasMore {
		options = options[:query.limit]
	}
	nextCursor := ""
	if hasMore {
		nextCursor = strconv.Itoa(query.offset + query.limit)
	}
	return taskMemberOptionsPage{
		options:    options,
		hasMore:    hasMore,
		nextCursor: nextCursor,
		limit:      query.limit,
	}, nil
}

func buildTaskMemberOptionsQuery(workspaceID string, channelID string, actorMemberID string, lookup taskMemberLookup) (taskMemberOptionsQuery, error) {
	limit := normalizeTaskMemberLookupLimit(lookup.Limit)
	offset, err := parseTaskMemberLookupCursor(lookup.Cursor)
	if err != nil {
		return taskMemberOptionsQuery{}, err
	}
	args := []any{strings.TrimSpace(channelID), strings.TrimSpace(workspaceID), strings.TrimSpace(actorMemberID)}
	conditions := []string{taskMemberOptionVisibilityCondition}
	role := strings.TrimSpace(lookup.Role)
	switch role {
	case "", "assignee":
		conditions = append(conditions, "m.type IN ('human', 'agent')")
	case "agent":
		conditions = append(conditions, "m.type = 'agent'")
	default:
		return taskMemberOptionsQuery{}, badRequest("invalid member lookup role")
	}
	if query := strings.ToLower(strings.TrimSpace(strings.TrimPrefix(lookup.Query, "@"))); query != "" {
		args = append(args, "%"+query+"%")
		placeholder := fmt.Sprintf("$%d", len(args))
		predicate := `LOWER(CONCAT_WS(' ', m.id, COALESCE(u.id, ''), COALESCE(u.name, ''), COALESCE(u.display_name, ''), COALESCE(m.display_name, ''), COALESCE(a.id, ''), COALESCE(a.name, ''))) LIKE ` + placeholder
		if lookup.Exact {
			args[len(args)-1] = query
			placeholder = fmt.Sprintf("$%d", len(args))
			predicate = `LOWER(TRIM(m.id)) = ` + placeholder + `
  OR LOWER(TRIM(COALESCE(u.id, ''))) = ` + placeholder + `
  OR LOWER(TRIM(COALESCE(u.name, ''))) = ` + placeholder + `
  OR LOWER(TRIM(COALESCE(a.id, ''))) = ` + placeholder + `
  OR LOWER(TRIM(COALESCE(a.name, ''))) = ` + placeholder
		}
		conditions = append(conditions, "("+predicate+")")
	}
	args = append(args, limit+1, offset)
	limitPlaceholder := fmt.Sprintf("$%d", len(args)-1)
	offsetPlaceholder := fmt.Sprintf("$%d", len(args))

	sql := `
SELECT
  m.id,
  m.type,
  m.id,
  u.id,
  u.display_name,
  m.display_name,
  u.name,
  a.id,
  a.name
FROM members m
LEFT JOIN users u
  ON m.type = 'human'
 AND u.id = m.user_id
LEFT JOIN agents a
  ON m.type = 'agent'
 AND a.member_id = m.id
 AND a.status <> 'archived'
LEFT JOIN scope_memberships sm
  ON sm.scope_id = $1
 AND sm.member_id = m.id
 AND sm.removed_at IS NULL
WHERE ` + strings.Join(conditions, "\n  AND ") + `
ORDER BY m.type ASC, ` + taskMemberOptionLabelExpr + ` ASC, m.id ASC
LIMIT ` + limitPlaceholder + ` OFFSET ` + offsetPlaceholder + `
`
	return taskMemberOptionsQuery{
		sql:    sql,
		args:   args,
		limit:  limit,
		offset: offset,
	}, nil
}

func normalizeTaskMemberLookupLimit(limit int) int {
	switch {
	case limit <= 0:
		return taskMemberLookupDefaultLimit
	case limit > taskMemberLookupMaxLimit:
		return taskMemberLookupMaxLimit
	default:
		return limit
	}
}

func parseTaskMemberLookupCursor(cursor string) (int, error) {
	trimmed := strings.TrimSpace(cursor)
	if trimmed == "" {
		return 0, nil
	}
	offset, err := strconv.Atoi(trimmed)
	if err != nil || offset < 0 {
		return 0, badRequest("invalid member lookup cursor")
	}
	return offset, nil
}