package taskboard

import (
	"context"
	"database/sql"
	"fmt"
	"strconv"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/attachment"
	"github.com/tidbcloud/AgentTeam/internal/store/message"
)

const (
	taskDiscussionReactionEmojiKindsPerEntry = message.MaxReactionEmojiKindsPerMessage
	taskDiscussionReactionReactorsPerEmoji   = 10
)

type taskDiscussionReadOptions struct {
	skipReactions  bool
	viewerMemberID string
	viewerType     string
}

func taskDiscussionScopeMessagesSelectSQL() string {
	return `
SELECT
    sm.id,
    t.id,
    sm.content,
    sm.created_at,
    sm.updated_at,
    sm.edited_at,
    COALESCE(sm.edited_by_member_id, ''),
    sm.edit_revision,
    sm.metadata,
    created_member.type,
    created_member.id,
    created_user.id,
    created_user.display_name,
    created_user.avatar_url,
    created_member.display_name,
    created_user.name,
    created_agent.id,
    created_agent.name,
    created_agent.avatar_url,
    created_service_member.service_installation_id,
    created_service_member.service_kind,
    created_service_member.handle,
    created_service_member.display_name,
    created_service_member.avatar_url,
    created_service_definition.id,
    created_service_definition.slug,
    created_service_definition.name,
    created_service_definition.avatar_url
FROM tasks t
JOIN task_boards task_board
  ON task_board.id = t.board_id
 AND task_board.workspace_id = $1
JOIN scopes task_scope
  ON task_scope.id = t.task_scope_id
 AND task_scope.workspace_id = $1
 AND task_scope.type = 'task'
 AND task_scope.status = 'active'
 AND task_scope.archived_at IS NULL
JOIN scopes task_board_channel
  ON task_board_channel.id = task_board.channel_id
 AND task_board_channel.workspace_id = $1
 AND task_board_channel.type = 'channel'
 AND task_board_channel.status = 'active'
 AND task_board_channel.archived_at IS NULL
JOIN scope_messages sm
  ON sm.scope_id = task_scope.id
 AND sm.workspace_id = $1
 AND sm.message_type = 'task_discussion'
 AND t.channel_id = task_board.channel_id
JOIN members created_member
  ON created_member.id = sm.author_member_id
LEFT JOIN users created_user
  ON created_member.type = 'human'
 AND created_user.id = created_member.user_id
LEFT JOIN agents created_agent
  ON created_member.type = 'agent'
 AND created_agent.member_id = created_member.id
LEFT JOIN service_members created_service_member
  ON created_member.type = 'service'
 AND created_service_member.workspace_id = created_member.workspace_id
 AND created_service_member.member_id = created_member.id
LEFT JOIN service_definitions created_service_definition
  ON created_service_definition.id = created_service_member.service_definition_id
 AND created_service_definition.service_kind = created_service_member.service_kind
WHERE 1 = 1
`
}

func loadTaskDiscussionEntryByIDForViewer(ctx context.Context, queryer coredb.Queryer, workspaceID string, taskID string, entryID string, viewerMemberID string) (TaskDiscussionEntry, error) {
	entries, err := loadTaskDiscussionEntriesByIDMapWithOptions(ctx, queryer, workspaceID, taskID, []string{entryID}, taskDiscussionReadOptions{viewerMemberID: viewerMemberID})
	if err != nil {
		return TaskDiscussionEntry{}, err
	}
	entry, ok := entries[strings.TrimSpace(entryID)]
	if !ok {
		return TaskDiscussionEntry{}, ErrNotFound
	}
	return entry, nil
}

func loadTaskDiscussionEntriesByIDMap(ctx context.Context, queryer coredb.Queryer, workspaceID string, taskID string, entryIDs []string) (map[string]TaskDiscussionEntry, error) {
	return loadTaskDiscussionEntriesByIDMapWithOptions(ctx, queryer, workspaceID, taskID, entryIDs, taskDiscussionReadOptions{})
}

func loadTaskDiscussionEntriesByIDMapWithOptions(ctx context.Context, queryer coredb.Queryer, workspaceID string, taskID string, entryIDs []string, options taskDiscussionReadOptions) (map[string]TaskDiscussionEntry, error) {
	if len(entryIDs) == 0 {
		return map[string]TaskDiscussionEntry{}, nil
	}
	seen := make(map[string]struct{}, len(entryIDs))
	normalizedIDs := make([]string, 0, len(entryIDs))
	for _, entryID := range entryIDs {
		trimmed := strings.TrimSpace(entryID)
		if trimmed == "" {
			continue
		}
		if _, ok := seen[trimmed]; ok {
			continue
		}
		seen[trimmed] = struct{}{}
		normalizedIDs = append(normalizedIDs, trimmed)
	}
	if len(normalizedIDs) == 0 {
		return map[string]TaskDiscussionEntry{}, nil
	}
	if viewerMemberID := strings.TrimSpace(options.viewerMemberID); viewerMemberID != "" {
		items := make([]message.SidecarReadBatchItem, 0, len(normalizedIDs))
		for _, entryID := range normalizedIDs {
			items = append(items, message.SidecarReadBatchItem{
				RequestID:      entryID,
				MessageID:      entryID,
				ViewerMemberID: viewerMemberID,
			})
		}
		decisions, err := message.NewScopeAPIServiceForQueryer(queryer).AuthorizeSidecarReadBatch(ctx, message.SidecarReadBatchRequest{
			WorkspaceID: workspaceID,
			Kind:        message.SidecarKindDiagnostics,
			Items:       items,
		})
		if err != nil {
			return nil, fmt.Errorf("authorize task discussion entries: %w", err)
		}
		filtered := normalizedIDs[:0]
		for _, entryID := range normalizedIDs {
			if decision, ok := decisions[entryID]; ok && decision.Allowed {
				filtered = append(filtered, entryID)
			}
		}
		normalizedIDs = filtered
		if len(normalizedIDs) == 0 {
			return map[string]TaskDiscussionEntry{}, nil
		}
	}

	query := taskDiscussionScopeMessagesSelectSQL()
	args := []any{strings.TrimSpace(workspaceID), taskID}
	placeholders := make([]string, 0, len(normalizedIDs))
	for _, entryID := range normalizedIDs {
		args = append(args, entryID)
		placeholders = append(placeholders, "$"+strconv.Itoa(len(args)))
	}
	query += "  AND t.id = $2\n"
	query += "  AND sm.id IN (" + strings.Join(placeholders, ", ") + ")\n"
	query += "ORDER BY sm.seq DESC\n"

	entries, entryIndex, err := scanTaskDiscussionEntries(ctx, queryer, query, args, len(normalizedIDs))
	if err != nil {
		return nil, fmt.Errorf("query task discussion entries by id: %w", err)
	}
	if len(entries) == 0 {
		return map[string]TaskDiscussionEntry{}, nil
	}
	if err := attachMessageSubstrateToTaskDiscussionEntries(ctx, queryer, workspaceID, entries, entryIndex); err != nil {
		return nil, err
	}
	if !options.skipReactions {
		if err := hydrateMessageReactionAggregatesForDiscussionEntries(ctx, queryer, workspaceID, entries, entryIndex); err != nil {
			return nil, err
		}
	}
	if err := loadTaskDiscussionAttachmentsForEntries(ctx, queryer, workspaceID, entries, entryIndex); err != nil {
		return nil, err
	}
	entriesByID := make(map[string]TaskDiscussionEntry, len(entries))
	for _, entry := range entries {
		entriesByID[entry.ID] = entry
	}
	return entriesByID, nil
}

func scanTaskDiscussionEntries(ctx context.Context, queryer coredb.Queryer, query string, args []any, capacityHint int) ([]TaskDiscussionEntry, map[string]int, error) {
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close()

	if capacityHint < 0 {
		capacityHint = 0
	}
	entries := make([]TaskDiscussionEntry, 0, capacityHint)
	entryIndex := make(map[string]int, capacityHint)
	for rows.Next() {
		entry, err := scanTaskDiscussionEntryRow(rows)
		if err != nil {
			return nil, nil, err
		}
		entryIndex[entry.ID] = len(entries)
		entries = append(entries, entry)
	}
	if err := rows.Err(); err != nil {
		return nil, nil, fmt.Errorf("iterate task discussion entries: %w", err)
	}
	return entries, entryIndex, nil
}

func scanTaskDiscussionEntryRow(row interface {
	Scan(...any) error
}) (TaskDiscussionEntry, error) {
	var entry TaskDiscussionEntry
	var author taskDiscussionMessageAuthorProjection
	var rawMetadata []byte
	var updatedAt time.Time
	var editedAt sql.NullTime
	if err := row.Scan(
		&entry.ID,
		&entry.TaskID,
		&entry.Body,
		&entry.CreatedAt,
		&updatedAt,
		&editedAt,
		&entry.EditedByMemberID,
		&entry.EditRevision,
		&rawMetadata,
		&author.MemberType,
		&author.MemberID,
		&author.UserID,
		&author.UserDisplayName,
		&author.UserAvatarURL,
		&author.MemberDisplayName,
		&author.UserName,
		&author.AgentID,
		&author.AgentName,
		&author.AgentAvatarURL,
		&author.ServiceID,
		&author.ServiceKind,
		&author.ServiceHandle,
		&author.ServiceDisplayName,
		&author.ServiceAvatarURL,
		&author.ServiceDefinitionID,
		&author.ServiceDefinitionSlug,
		&author.ServiceDefinitionName,
		&author.ServiceDefinitionAvatarURL,
	); err != nil {
		return TaskDiscussionEntry{}, fmt.Errorf("scan task discussion entry: %w", err)
	}
	metadata, err := message.DecodeMessageMetadata(rawMetadata)
	if err != nil {
		return TaskDiscussionEntry{}, err
	}
	entry.metadata = metadata
	entry.MessagePresentation = taskDiscussionPresentationFromMetadata(metadata)
	createdByActor, err := author.compatibilityActor()
	if err != nil {
		return TaskDiscussionEntry{}, err
	}
	entry.AuthorProfile = author.authorProfile()
	entry.CreatedBy = createdByActor
	entry.UpdatedAt = &updatedAt
	if editedAt.Valid {
		entry.EditedAt = &editedAt.Time
	}
	return entry, nil
}

func taskDiscussionPresentationFromMetadata(metadata map[string]any) *domain.MessagePresentation {
	presentation, err := domain.MessagePresentationFromMetadata(metadata)
	if err != nil {
		return nil
	}
	return presentation
}

func attachMessageSubstrateToTaskDiscussionEntries(ctx context.Context, queryer coredb.Queryer, workspaceID string, entries []TaskDiscussionEntry, entryIndex map[string]int) error {
	if len(entries) == 0 || strings.TrimSpace(workspaceID) == "" {
		return nil
	}
	messages := make([]message.AppMessage, 0, len(entries))
	messageIndexes := make([]int, 0, len(entries))
	for index, entry := range entries {
		if strings.TrimSpace(entry.ID) == "" {
			continue
		}
		message := message.AppMessage{
			ID:               entry.ID,
			Content:          entry.Body,
			AuthorProfile:    entry.AuthorProfile,
			Metadata:         entry.metadata,
			CreatedAt:        entry.CreatedAt,
			EditedAt:         entry.EditedAt,
			EditedByMemberID: entry.EditedByMemberID,
			EditRevision:     entry.EditRevision,
			MessageType:      "task_discussion",
		}
		messages = append(messages, message)
		messageIndexes = append(messageIndexes, index)
	}
	if len(messages) == 0 {
		return nil
	}
	if err := message.AttachContributionSourcesForAppMessages(ctx, queryer, workspaceID, messages); err != nil {
		return fmt.Errorf("attach task discussion message sources: %w", err)
	}
	for index, message := range messages {
		entryPosition := messageIndexes[index]
		if _, ok := entryIndex[message.ID]; !ok {
			continue
		}
		entries[entryPosition].AuthorProfile = message.AuthorProfile
		entries[entryPosition].Source = message.Source
		entries[entryPosition].CreatedBy = taskActorFromAuthorProfile(message.AuthorProfile, entries[entryPosition].CreatedBy)
	}
	return nil
}

func attachResolvedMentionsForTaskDiscussionEntries(ctx context.Context, queryer coredb.Queryer, workspaceID string, viewerMemberID string, taskScopeID string, entries []TaskDiscussionEntry) error {
	if len(entries) == 0 {
		return nil
	}
	trimmedTaskScopeID := strings.TrimSpace(taskScopeID)
	if strings.TrimSpace(workspaceID) == "" || strings.TrimSpace(viewerMemberID) == "" || trimmedTaskScopeID == "" {
		return nil
	}
	messages := make([]message.AppMessage, 0, len(entries))
	entryIndexes := make([]int, 0, len(entries))
	for index, entry := range entries {
		if strings.TrimSpace(entry.Body) == "" {
			continue
		}
		messages = append(messages, message.AppMessage{
			ID:        entry.ID,
			ChannelID: trimmedTaskScopeID,
			Content:   entry.Body,
			Metadata:  entry.metadata,
		})
		entryIndexes = append(entryIndexes, index)
	}
	if len(messages) == 0 {
		return nil
	}
	if err := message.AttachResolvedMentionsForAppMessages(ctx, queryer, workspaceID, viewerMemberID, messages); err != nil {
		return err
	}
	for index, message := range messages {
		entries[entryIndexes[index]].ResolvedMentions = message.ResolvedMentions
	}
	return nil
}

func hydrateMessageReactionAggregatesForDiscussionEntries(ctx context.Context, queryer interface {
	Query(context.Context, string, ...any) (coredb.Rows, error)
}, workspaceID string, entries []TaskDiscussionEntry, entryIndex map[string]int) error {
	if len(entries) == 0 || strings.TrimSpace(workspaceID) == "" {
		return nil
	}

	placeholders := make([]string, 0, len(entries))
	args := []any{
		strings.TrimSpace(workspaceID),
		taskDiscussionReactionReactorsPerEmoji,
		taskDiscussionReactionEmojiKindsPerEntry,
	}
	entryIDPlaceholderStart := len(args) + 1
	for index, entry := range entries {
		placeholders = append(placeholders, "$"+strconv.Itoa(entryIDPlaceholderStart+index))
		args = append(args, entry.ID)
	}

	reactionsQuery := `
WITH ranked_reactions AS (
SELECT
    r.message_id,
    r.emoji,
    r.reactor_member_id,
    r.created_at,
    r.id,
    COUNT(*) OVER (PARTITION BY r.message_id, r.emoji) AS reaction_count,
    MIN(r.created_at) OVER (PARTITION BY r.message_id, r.emoji) AS emoji_first_created_at,
    MIN(r.id) OVER (PARTITION BY r.message_id, r.emoji) AS emoji_first_id,
    ROW_NUMBER() OVER (PARTITION BY r.message_id, r.emoji ORDER BY r.created_at ASC, r.id ASC) AS reactor_rank
FROM scope_message_reactions r
JOIN scope_messages sm
  ON sm.id = r.message_id
 AND sm.message_type = 'task_discussion'
WHERE r.workspace_id = $1
  AND r.message_id IN (` + strings.Join(placeholders, ", ") + `)
),
bounded_reactions AS (
SELECT
    ranked_reactions.*,
    DENSE_RANK() OVER (
        PARTITION BY ranked_reactions.message_id
        ORDER BY ranked_reactions.emoji_first_created_at ASC, ranked_reactions.emoji_first_id ASC, ranked_reactions.emoji ASC
    ) AS emoji_rank
FROM ranked_reactions
)
SELECT
    bounded_reactions.message_id,
    bounded_reactions.emoji,
    bounded_reactions.reaction_count,
    bounded_reactions.reactor_member_id,
    reactor.type,
    reactor.id,
    reactor_user.id,
    reactor_user.display_name,
    reactor.display_name,
    reactor_user.name,
    reactor_agent.id,
    reactor_agent.name
FROM bounded_reactions
JOIN members reactor
  ON reactor.id = bounded_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.member_id = reactor.id
WHERE bounded_reactions.emoji_rank <= $3
  AND bounded_reactions.reactor_rank <= $2
ORDER BY bounded_reactions.message_id ASC, bounded_reactions.emoji_first_created_at ASC, bounded_reactions.emoji_first_id ASC, bounded_reactions.reactor_rank ASC
`

	rows, err := queryer.Query(ctx, reactionsQuery, args...)
	if err != nil {
		return fmt.Errorf("query task discussion reactions: %w", err)
	}
	defer rows.Close()

	aggregateIndex := make(map[string]map[string]int, len(entries))
	for rows.Next() {
		var entryID string
		var emoji string
		var reactionCount sql.NullInt64
		var memberID string
		var actor taskActorProjection
		if err := rows.Scan(
			&entryID,
			&emoji,
			&reactionCount,
			&memberID,
			&actor.MemberType,
			&actor.MemberID,
			&actor.UserID,
			&actor.UserDisplayName,
			&actor.MemberDisplayName,
			&actor.UserName,
			&actor.AgentID,
			&actor.AgentName,
		); err != nil {
			return fmt.Errorf("scan task discussion reaction: %w", err)
		}
		entryPosition, ok := entryIndex[entryID]
		if !ok {
			continue
		}
		perEntry := aggregateIndex[entryID]
		if perEntry == nil {
			perEntry = make(map[string]int)
			aggregateIndex[entryID] = perEntry
		}
		reactions := entries[entryPosition].Reactions
		aggregatePosition, ok := perEntry[emoji]
		if !ok {
			aggregatePosition = len(reactions)
			perEntry[emoji] = aggregatePosition
			reactions = append(reactions, message.MessageReactionAggregate{Emoji: emoji})
		}
		reactions[aggregatePosition].Reactors = append(reactions[aggregatePosition].Reactors, message.MessageReactionReactor{
			AuthorProfile: taskDiscussionReactionAuthorProfile(strings.TrimSpace(memberID), actor),
		})
		if reactionCount.Valid && reactionCount.Int64 > 0 {
			reactions[aggregatePosition].Count = int(reactionCount.Int64)
		} else {
			reactions[aggregatePosition].Count = len(reactions[aggregatePosition].Reactors)
		}
		entries[entryPosition].Reactions = reactions
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate task discussion reactions: %w", err)
	}
	return nil
}

func loadTaskDiscussionAttachmentsForEntries(ctx context.Context, queryer interface {
	Query(context.Context, string, ...any) (coredb.Rows, error)
}, workspaceID string, entries []TaskDiscussionEntry, entryIndex map[string]int) error {
	if len(entries) == 0 {
		return nil
	}
	messageIDs := make([]string, 0, len(entries))
	for _, entry := range entries {
		messageIDs = append(messageIDs, entry.ID)
	}
	query, args, err := attachment.BuildLoadAttachmentsForMessagesQuery(workspaceID, messageIDs)
	if err != nil {
		return fmt.Errorf("build task discussion attachment ids: %w", err)
	}
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("query task discussion attachments: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var messageID string
		var item attachment.AppAttachment
		if err := rows.Scan(&messageID, &item.ID, &item.Filename, &item.ContentType, &item.SizeBytes); err != nil {
			return fmt.Errorf("scan task discussion attachment: %w", err)
		}
		entryPosition, ok := entryIndex[messageID]
		if !ok {
			continue
		}
		entries[entryPosition].Attachments = append(entries[entryPosition].Attachments, item)
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate task discussion attachments: %w", err)
	}
	return nil
}

func normalizeTaskTimelinePageSize(limit int) int {
	if limit <= 0 {
		return taskDetailTimelinePageSize
	}
	if limit > taskTimelineMaxPageSize {
		return taskTimelineMaxPageSize
	}
	return limit
}