package reaction

import (
	"context"
	"fmt"
	"strings"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/message"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
)

func buildMessageReactionAggregateRowsQuery(workspaceID string, messageIDs []string, currentReactorMemberID string) (string, []any, error) {
	normalizedMessageIDs := normalizeNonEmptyStrings(messageIDs)
	if len(normalizedMessageIDs) == 0 {
		return "", nil, nil
	}

	requestedMessageIDsCTE, requestedMessageIDArgs, _, err := coredb.BuildStringValuesCTE("requested_message_ids", "message_id", normalizedMessageIDs, 4)
	if err != nil {
		return "", nil, err
	}

	query := fmt.Sprintf(`
WITH %s,
ranked_reactions AS (
SELECT
    mr.message_id,
    mr.emoji,
    COUNT(*) OVER (PARTITION BY mr.message_id, mr.emoji) AS reaction_count,
    mr.reactor_member_id,
    MIN(mr.created_at) OVER (PARTITION BY mr.message_id, mr.emoji) AS emoji_first_created_at,
    MIN(mr.id) OVER (PARTITION BY mr.message_id, mr.emoji) AS emoji_first_id,
    ROW_NUMBER() OVER (PARTITION BY mr.message_id, mr.emoji ORDER BY mr.created_at ASC, mr.id ASC) AS reactor_rank
FROM requested_message_ids
JOIN scope_message_reactions mr ON mr.message_id = requested_message_ids.message_id
)
SELECT
    ranked_reactions.message_id,
    ranked_reactions.emoji,
    ranked_reactions.reaction_count,
    ranked_reactions.reactor_member_id,
    reactor.type AS actor_type,
    CASE
        WHEN reactor.type = 'agent' THEN COALESCE(NULLIF(reactor_agent.name, ''), reactor.id)
        ELSE reactor.id
    END AS actor_name,
    COALESCE(NULLIF(reactor.display_name, ''), NULLIF(reactor_user.display_name, ''), NULLIF(reactor_agent.name, ''), NULLIF(reactor_app_def.name, ''), NULLIF(reactor_app.handle, ''), reactor.id) AS display_name,
    COALESCE(NULLIF(reactor_user.avatar_url, ''), NULLIF(reactor_agent.avatar_url, ''), NULLIF(reactor_app_def.avatar_url, ''), '') AS avatar_url,
    COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(COALESCE(reactor_app_def.config, JSON_OBJECT()), '$.appKind')), ''), '') AS service_kind,
    COALESCE(NULLIF(reactor_app_def.slug, ''), NULLIF(reactor_app.handle, ''), '') AS service_slug
FROM ranked_reactions
JOIN members reactor ON reactor.id = ranked_reactions.reactor_member_id AND reactor.workspace_id = $1
LEFT JOIN users reactor_user ON reactor.type = 'human' AND reactor_user.id = reactor.user_id
LEFT JOIN agents reactor_agent ON reactor.type = 'agent' AND reactor_agent.workspace_id = reactor.workspace_id AND reactor_agent.member_id = reactor.id
LEFT JOIN service_members reactor_app ON reactor.type = 'service' AND reactor_app.workspace_id = reactor.workspace_id AND reactor_app.member_id = reactor.id
LEFT JOIN service_installations reactor_app_inst ON reactor_app_inst.id = reactor_app.service_installation_id
LEFT JOIN service_definitions reactor_app_def ON reactor.type = 'service' AND reactor_app_def.id = reactor_app_inst.service_definition_id
WHERE ranked_reactions.reactor_rank <= $2
   OR (NULLIF($3, '') IS NOT NULL AND ranked_reactions.reactor_member_id = $3)
ORDER BY ranked_reactions.message_id ASC, ranked_reactions.emoji_first_created_at ASC, ranked_reactions.emoji_first_id ASC, ranked_reactions.reactor_rank ASC
`, requestedMessageIDsCTE)
	args := append([]any{
		strings.TrimSpace(workspaceID),
		maxReactionReactorsPerEmoji,
		strings.TrimSpace(currentReactorMemberID),
	}, requestedMessageIDArgs...)
	return query, args, nil
}

func (store *Store) loadMessageReactionAggregateRows(ctx context.Context, queryer reactionQueryer, workspaceID string, currentReactorMemberID string, messageIDs []string) ([]model.ReactionAggregateRow, error) {
	query, args, err := buildMessageReactionAggregateRowsQuery(workspaceID, messageIDs, currentReactorMemberID)
	if err != nil {
		return nil, err
	}
	if strings.TrimSpace(query) == "" {
		return nil, nil
	}

	var rows []reactionAggregateRow
	if err := coredb.Select(ctx, queryer, &rows, query, args...); err != nil {
		return nil, fmt.Errorf("load message reaction aggregate rows: %w", err)
	}

	aggregates := make([]model.ReactionAggregateRow, 0, len(rows))
	for _, row := range rows {
		aggregates = append(aggregates, row.modelRow())
	}
	return aggregates, nil
}

func (store *Store) loadMessageReactionAggregates(ctx context.Context, queryer reactionQueryer, workspaceID string, currentReactorMemberID string, messageIDs []string) ([]message.MessageReactionAggregate, error) {
	lookup, err := store.loadMessageReactionAggregatesByMessageID(ctx, queryer, workspaceID, currentReactorMemberID, messageIDs)
	if err != nil {
		return nil, err
	}
	normalized := normalizeNonEmptyStrings(messageIDs)
	if len(normalized) == 0 {
		return []message.MessageReactionAggregate{}, nil
	}
	reactions := lookup[normalized[0]]
	if reactions == nil {
		return []message.MessageReactionAggregate{}, nil
	}
	return reactions, nil
}

func (store *Store) loadMessageReactionAggregatesByMessageID(ctx context.Context, queryer reactionQueryer, workspaceID string, currentReactorMemberID string, messageIDs []string) (map[string][]message.MessageReactionAggregate, error) {
	rows, err := store.loadMessageReactionAggregateRows(ctx, queryer, workspaceID, currentReactorMemberID, messageIDs)
	if err != nil {
		return nil, err
	}

	return reactionAggregateRowsToLookup(messageIDs, rows), nil
}

func reactionAggregateRowsToLookup(messageIDs []string, rows []model.ReactionAggregateRow) map[string][]message.MessageReactionAggregate {
	result := make(map[string][]message.MessageReactionAggregate, len(messageIDs))
	indexByMessageAndEmoji := make(map[string]map[string]int, len(messageIDs))
	for _, row := range rows {
		messageID := strings.TrimSpace(row.MessageID)
		emoji := strings.TrimSpace(row.Emoji)
		if messageID == "" || emoji == "" {
			continue
		}
		perMessage := indexByMessageAndEmoji[messageID]
		if perMessage == nil {
			perMessage = make(map[string]int)
			indexByMessageAndEmoji[messageID] = perMessage
		}
		aggregates := result[messageID]
		index, exists := perMessage[emoji]
		if !exists {
			index = len(aggregates)
			perMessage[emoji] = index
			aggregates = append(aggregates, message.MessageReactionAggregate{Emoji: emoji, Count: row.Count})
		}
		reactor := message.MessageReactionReactor{
			AuthorProfile: message.ReactionReactorAuthorProfile(row.MemberID, row.ActorType, row.ActorName, row.DisplayName, row.AvatarURL, row.ServiceKind, row.ServiceSlug),
		}
		aggregates[index].Reactors = append(aggregates[index].Reactors, reactor)
		if row.Count > 0 {
			aggregates[index].Count = row.Count
		} else {
			aggregates[index].Count = len(aggregates[index].Reactors)
		}
		result[messageID] = aggregates
	}
	return result
}

func (store *Store) AttachAppMessageReactionAggregates(ctx context.Context, workspaceID string, currentReactorMemberID string, messages []message.AppMessage) error {
	reactionsByMessageID, err := store.loadMessageReactionAggregatesByMessageID(ctx, store.db, workspaceID, currentReactorMemberID, collectAppMessageIDs(messages))
	if err != nil {
		return err
	}
	attachAppMessageReactions(messages, reactionsByMessageID)
	return nil
}

func (store *Store) AttachHistoryReactionAggregates(ctx context.Context, workspaceID string, currentReactorMemberID string, messages []message.HistoryMessage) error {
	messageIDs := make([]string, 0, len(messages))
	for _, message := range messages {
		trimmed := strings.TrimSpace(message.ID)
		if trimmed == "" {
			continue
		}
		messageIDs = append(messageIDs, trimmed)
	}
	reactionsByMessageID, err := store.loadMessageReactionAggregatesByMessageID(ctx, store.db, workspaceID, currentReactorMemberID, messageIDs)
	if err != nil {
		return err
	}
	attachHistoryMessageReactions(messages, reactionsByMessageID)
	return nil
}