package message

import (
	"context"
	"fmt"
	"net/url"
	"strconv"
	"strings"
	"time"

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

// MaterializeTaskConversationForMemberTx ensures the owner-local Inbox
// conversation row for one member and a visible Task scope exists, idempotently,
// and returns whether a row is present for the member. It is the authoritative
// "Task Watch immediately appears in Inbox" boundary: the caller has already
// established Task readability and (for Watch) set task_watch_state; this
// function only materializes the im_conversation_index row with the authoritative
// Task conversation projection. It never fabricates a discussion message,
// activity event, InboxMessage, or Agent wake, and it preserves any existing
// row (the underlying upsert keeps the greatest sort_at and never lets an older
// projection overwrite a newer one, so late/reordered Watch mutations cannot
// regress the conversation).
func MaterializeTaskConversationForMemberTx(ctx context.Context, execer coredb.QueryExecer, workspaceID string, memberID string, taskScopeID string) error {
	workspaceID = strings.TrimSpace(workspaceID)
	memberID = strings.TrimSpace(memberID)
	taskScopeID = strings.TrimSpace(taskScopeID)
	if workspaceID == "" || memberID == "" || taskScopeID == "" {
		return invalidArgument("workspace, member, and Task scope are required")
	}
	item, err := loadTaskConversationIndexItem(ctx, execer, workspaceID, memberID, taskScopeID)
	if err != nil {
		return err
	}
	if item == nil {
		return nil
	}
	// Watch only creates the missing task:<scope> row; an existing row (including
	// projection_json, sort_at, last_event_seq, pinned_at, archived_at, created_at,
	// and any member overlay) is preserved untouched. Never use the generic upsert
	// here — its ON DUPLICATE branch would overwrite pinned_at/archived_at with nil
	// and, on equal sort_at, replace projection_json, regressing history/pin/hide.
	if err := im.InsertConversationIfMissingTx(ctx, execer, workspaceID, memberID, *item); err != nil {
		return fmt.Errorf("materialize Task conversation for member: %w", err)
	}
	return nil
}

// taskConversationMaterializeFacts carries the authoritative Task conversation facts
// for a member, independent of an existing im_conversation_index row.
type taskConversationMaterializeFacts struct {
	TaskID                    string
	TaskScopeID               string
	ChannelID                 string
	TaskNumber                int64
	Title                     string
	Status                    string
	ParentTaskRef             string
	SourceThreadChannelID     string
	SourceParentChannelID     string
	SourceParentMessageID     string
	SourceThreadRef           string
	AssigneeMemberType        string
	AssigneeMemberID          string
	AssigneeUserID            string
	AssigneeUserName          string
	AssigneeUserDisplayName   string
	AssigneeUserAvatarURL     string
	AssigneeAgentID           string
	AssigneeAgentName         string
	AssigneeAgentAvatarURL    string
	AssigneeMemberDisplayName string
	SortAt                    time.Time
}

// loadTaskConversationIndexItem loads the authoritative owner-local Task
// conversation facts for one member (the same task/assignee source the
// conversation read path uses) and builds the ConversationIndexItem to
// materialize. It returns nil when the Task scope is not visible/active for the
// member, so the caller's fail-closed permission/workspace/deletion guarantees
// are preserved.
func loadTaskConversationIndexItem(ctx context.Context, queryer coredb.Queryer, workspaceID string, memberID string, taskScopeID string) (*im.ConversationIndexItem, error) {
	var facts taskConversationMaterializeFacts
	err := queryer.QueryRow(ctx, `
SELECT
  t.id,
  t.task_scope_id,
  task_board.channel_id,
  t.task_number,
  COALESCE(NULLIF(t.title, ''), ''),
  COALESCE(t.status, ''),
  COALESCE(t.parent_task_ref, ''),
  COALESCE(t.source_thread_channel_id, ''),
  COALESCE(t.source_thread_parent_channel_id, ''),
  COALESCE(t.source_thread_parent_message_id, ''),
  COALESCE(t.source_thread_ref, ''),
  CASE WHEN assignee_member.type IS NULL THEN '' ELSE assignee_member.type END,
  CASE WHEN assignee_member.id IS NULL THEN '' ELSE assignee_member.id END,
  CASE WHEN assignee_user.id IS NULL THEN '' ELSE assignee_user.id END,
  CASE WHEN assignee_user.name IS NULL THEN '' ELSE assignee_user.name END,
  CASE WHEN assignee_user.display_name IS NULL THEN '' ELSE assignee_user.display_name END,
  CASE WHEN assignee_user.avatar_url IS NULL THEN '' ELSE assignee_user.avatar_url END,
  CASE WHEN assignee_agent.id IS NULL THEN '' ELSE assignee_agent.id END,
  CASE WHEN assignee_agent.name IS NULL THEN '' ELSE assignee_agent.name END,
  CASE WHEN assignee_agent.avatar_url IS NULL THEN '' ELSE assignee_agent.avatar_url END,
  CASE WHEN assignee_member.display_name IS NULL THEN '' ELSE assignee_member.display_name END,
  COALESCE(t.last_activity_at, t.updated_at, t.created_at)
FROM tasks t
JOIN task_boards task_board
  ON task_board.id = t.board_id
 AND task_board.workspace_id = $1
JOIN scopes channel_scope
  ON channel_scope.id = task_board.channel_id
 AND channel_scope.workspace_id = $1
 AND channel_scope.type = 'channel'
 AND channel_scope.status = 'active'
 AND channel_scope.archived_at IS NULL
 AND t.channel_id = task_board.channel_id
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
LEFT JOIN members assignee_member
  ON assignee_member.workspace_id = channel_scope.workspace_id
 AND assignee_member.id = `+taskcompat.EffectiveAssigneeRefSQL("t")+`
 AND assignee_member.status = 'active'
LEFT JOIN users assignee_user
  ON assignee_member.type = 'human'
 AND assignee_user.id = assignee_member.user_id
LEFT JOIN agents assignee_agent
  ON assignee_member.type = 'agent'
 AND assignee_agent.member_id = assignee_member.id
 AND assignee_agent.status <> 'archived'
JOIN scope_memberships sm
  ON sm.scope_id = task_board.channel_id
 AND sm.member_id = $2
 AND sm.visibility IN ('public', 'private')
 AND sm.removed_at IS NULL
 AND sm.archived_at IS NULL
 AND sm.effective_to_seq IS NULL
WHERE task_scope.id = $3
  AND channel_scope.workspace_id = $1
LIMIT 1`, workspaceID, memberID, taskScopeID).Scan(
		&facts.TaskID,
		&facts.TaskScopeID,
		&facts.ChannelID,
		&facts.TaskNumber,
		&facts.Title,
		&facts.Status,
		&facts.ParentTaskRef,
		&facts.SourceThreadChannelID,
		&facts.SourceParentChannelID,
		&facts.SourceParentMessageID,
		&facts.SourceThreadRef,
		&facts.AssigneeMemberType,
		&facts.AssigneeMemberID,
		&facts.AssigneeUserID,
		&facts.AssigneeUserName,
		&facts.AssigneeUserDisplayName,
		&facts.AssigneeUserAvatarURL,
		&facts.AssigneeAgentID,
		&facts.AssigneeAgentName,
		&facts.AssigneeAgentAvatarURL,
		&facts.AssigneeMemberDisplayName,
		&facts.SortAt,
	)
	if err != nil {
		if coredb.IsNotFound(err) {
			return nil, nil
		}
		return nil, fmt.Errorf("load Task conversation materialize facts: %w", err)
	}
	if strings.TrimSpace(facts.TaskScopeID) == "" || strings.TrimSpace(facts.ChannelID) == "" {
		return nil, nil
	}
	item := taskConversationIndexItemFromFacts(facts)
	return &item, nil
}

// taskConversationIndexItemFromFacts builds the authoritative member-local Task
// conversation index item from the loaded Task facts. The projection shape
// matches the event-time authoritative Task conversation projection so realtime and
// the conversation read path stay coherent; the stored projection_json carries
// the full authoritative map and the read path re-derives mutable Task metadata.
func taskConversationIndexItemFromFacts(facts taskConversationMaterializeFacts) im.ConversationIndexItem {
	taskScopeID := strings.TrimSpace(facts.TaskScopeID)
	channelID := strings.TrimSpace(facts.ChannelID)
	route := "/channels/" + url.PathEscape(channelID) + "/tasks/" + strconv.FormatInt(facts.TaskNumber, 10)
	title := fmt.Sprintf("#t%d", facts.TaskNumber)
	if taskTitle := strings.TrimSpace(facts.Title); taskTitle != "" {
		title += " " + taskTitle
	}
	sortAt := facts.SortAt.UTC()
	taskProjection := map[string]any{
		"id":                          strings.TrimSpace(facts.TaskID),
		"scopeId":                     taskScopeID,
		"channelId":                   channelID,
		"number":                      facts.TaskNumber,
		"title":                       strings.TrimSpace(facts.Title),
		"status":                      strings.TrimSpace(facts.Status),
		"parentTaskRef":               strings.TrimSpace(facts.ParentTaskRef),
		"sourceThreadId":              strings.TrimSpace(facts.SourceThreadChannelID),
		"sourceThreadParentChannelId": strings.TrimSpace(facts.SourceParentChannelID),
		"sourceThreadParentMessageId": strings.TrimSpace(facts.SourceParentMessageID),
		"sourceThreadRef":             strings.TrimSpace(facts.SourceThreadRef),
		"route":                       route,
	}
	if profile := taskConversationAssigneeProfile(
		facts.AssigneeMemberType,
		facts.AssigneeMemberID,
		facts.AssigneeUserID,
		facts.AssigneeUserName,
		facts.AssigneeUserDisplayName,
		facts.AssigneeUserAvatarURL,
		facts.AssigneeAgentID,
		facts.AssigneeAgentName,
		facts.AssigneeAgentAvatarURL,
		facts.AssigneeMemberDisplayName,
	); profile != nil {
		taskProjection["assigneeProfile"] = authorProfileProjectionForMaterialize(*profile)
	}
	projection := map[string]any{
		"id":             "task:" + taskScopeID,
		"kind":           ConversationKindTask,
		"scopeId":        taskScopeID,
		"route":          route,
		"title":          title,
		"preview":        "",
		"lastActivityAt": sortAt,
		"task":           taskProjection,
	}
	return im.ConversationIndexItem{
		ConversationID: "task:" + taskScopeID,
		ScopeID:        taskScopeID,
		Kind:           ConversationKindTask,
		SortAt:         sortAt,
		Projection:     projection,
	}
}

// authorProfileProjectionForMaterialize mirrors the authoritative AuthorProfile
// projection map used by the event-time Task conversation projection.
func authorProfileProjectionForMaterialize(profile AuthorProfile) map[string]any {
	profile = profile.Normalize()
	return map[string]any{
		"actorType":   profile.ActorType,
		"principalId": profile.PrincipalID,
		"memberId":    profile.MemberID,
		"targetId":    profile.TargetID,
		"displayName": profile.DisplayName,
		"avatarUrl":   profile.AvatarURL,
	}
}