package message

import (
	"context"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/store/attachment"
)

const (
	ChannelAttachmentKindAll   ChannelAttachmentKind = "all"
	ChannelAttachmentKindFile  ChannelAttachmentKind = "file"
	ChannelAttachmentKindImage ChannelAttachmentKind = "image"
)

type ChannelAttachmentKind string

type ChannelAttachmentListOptions struct {
	Kind           ChannelAttachmentKind
	Query          string
	FromMemberID   string
	DateFrom       *time.Time
	DateTo         *time.Time
	IncludeThreads bool
	Cursor         ChannelAttachmentCursor
	Limit          int
}

type ChannelAttachmentCursor struct {
	CreatedAt       time.Time
	MessageID       string
	ResourceOrdinal int
}

func (cursor ChannelAttachmentCursor) IsZero() bool {
	return cursor.CreatedAt.IsZero() || strings.TrimSpace(cursor.MessageID) == ""
}

type ChannelAttachmentMessageLocation struct {
	MessageID           string
	ScopeID             string
	ScopeType           string
	ParentChannelID     string
	ThreadChannelID     string
	ThreadRootMessageID string
	ThreadTitle         string
}

type ChannelAttachmentDetail struct {
	ID           string
	Filename     string
	ContentType  string
	SizeBytes    int64
	ThumbnailURL string
}

type ChannelAttachmentItem struct {
	ID         string
	Kind       ChannelAttachmentKind
	Title      string
	CreatedAt  time.Time
	Author     AuthorProfile
	Message    ChannelAttachmentMessageLocation
	Snippet    string
	Attachment *ChannelAttachmentDetail

	Cursor ChannelAttachmentCursor
}

type ChannelAttachmentPage struct {
	Items      []ChannelAttachmentItem
	HasMore    bool
	NextCursor ChannelAttachmentCursor
}

type ChannelAttachmentMessageCandidate struct {
	ID                    string
	Seq                   int64
	WorkspaceGlobalSeq    int64
	ScopeID               string
	ScopeType             string
	ParentChannelID       string
	ThreadRootMessageID   string
	ThreadRootMessageText string
	AuthorProfile         AuthorProfile
	Content               string
	CreatedAt             time.Time
}

type ChannelAttachmentMessageCandidateRequest struct {
	WorkspaceID    string
	ViewerMemberID string
	ChannelID      string
	Kind           ChannelAttachmentKind
	Query          string
	FromMemberID   string
	DateFrom       *time.Time
	DateTo         *time.Time
	IncludeThreads bool
	Cursor         ChannelAttachmentCursor
	CandidateLimit int
}

func NormalizeChannelAttachmentKind(kind ChannelAttachmentKind) ChannelAttachmentKind {
	switch ChannelAttachmentKind(strings.TrimSpace(string(kind))) {
	case ChannelAttachmentKindFile:
		return ChannelAttachmentKindFile
	case ChannelAttachmentKindImage:
		return ChannelAttachmentKindImage
	default:
		return ChannelAttachmentKindAll
	}
}

func (store *Store) ListChannelAttachmentMessageCandidates(ctx context.Context, request ChannelAttachmentMessageCandidateRequest) ([]ChannelAttachmentMessageCandidate, error) {
	query, args, err := BuildChannelAttachmentMessageCandidatesQuery(request)
	if err != nil {
		return nil, err
	}
	rows, err := store.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list channel resource messages: %w", err)
	}
	defer rows.Close()

	candidates := make([]ChannelAttachmentMessageCandidate, 0)
	for rows.Next() {
		var candidate ChannelAttachmentMessageCandidate
		var memberType string
		var authorMemberID string
		var userID string
		var agentID string
		var agentName string
		var userDisplayName string
		var memberDisplayName string
		var userName string
		var userAvatarURL string
		var agentAvatarURL string
		if err := rows.Scan(
			&candidate.ID,
			&candidate.Seq,
			&candidate.WorkspaceGlobalSeq,
			&candidate.ScopeID,
			&candidate.ScopeType,
			&candidate.ParentChannelID,
			&candidate.ThreadRootMessageID,
			&candidate.ThreadRootMessageText,
			&memberType,
			&authorMemberID,
			&userID,
			&agentID,
			&agentName,
			&userDisplayName,
			&memberDisplayName,
			&userName,
			&userAvatarURL,
			&agentAvatarURL,
			&candidate.Content,
			&candidate.CreatedAt,
		); err != nil {
			return nil, fmt.Errorf("scan channel resource message: %w", err)
		}
		candidate.AuthorProfile = authorProfileFromResourceRow(memberType, authorMemberID, userID, agentID, agentName, userDisplayName, memberDisplayName, userName, userAvatarURL, agentAvatarURL)
		candidates = append(candidates, candidate)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate channel resource messages: %w", err)
	}
	return candidates, nil
}

func BuildChannelAttachmentMessageCandidatesQuery(request ChannelAttachmentMessageCandidateRequest) (string, []any, error) {
	workspaceID := strings.TrimSpace(request.WorkspaceID)
	viewerMemberID := strings.TrimSpace(request.ViewerMemberID)
	channelID := strings.TrimSpace(request.ChannelID)
	if workspaceID == "" || viewerMemberID == "" || channelID == "" {
		return "", nil, fmt.Errorf("workspace, viewer member, and channel are required")
	}
	limit := request.CandidateLimit
	if limit <= 0 {
		limit = 100
	}

	where, args := buildChannelAttachmentMessageCandidatePredicates(request, workspaceID, viewerMemberID, channelID)
	args = append(args, limit)
	limitParam := len(args)

	query := fmt.Sprintf(`
SELECT DISTINCT
  m.id,
  m.seq,
  m.workspace_global_seq,
  m.scope_id,
  message_scope.type,
  CASE WHEN message_scope.type = 'thread' THEN COALESCE(parent_scope.id, '') ELSE message_scope.id END AS parent_channel_id,
  CASE WHEN message_scope.type = 'thread' THEN COALESCE(message_scope.root_message_id, '') ELSE '' END AS thread_root_message_id,
  CASE WHEN message_scope.type = 'thread' THEN COALESCE(root_message.content, '') ELSE '' END AS thread_root_message_text,
  author_member.type,
  author_member.id,
  COALESCE(u.id, ''),
  COALESCE(a.id, ''),
  COALESCE(a.name, ''),
  COALESCE(u.display_name, ''),
  COALESCE(author_member.display_name, ''),
  COALESCE(u.name, ''),
  COALESCE(u.avatar_url, ''),
  COALESCE(a.avatar_url, ''),
  m.content,
  m.created_at
FROM scope_messages m
JOIN scopes message_scope
  ON message_scope.id = m.scope_id
 AND message_scope.workspace_id = m.workspace_id
LEFT JOIN scopes parent_scope
  ON message_scope.type = 'thread'
 AND parent_scope.id = message_scope.parent_scope_id
 AND parent_scope.workspace_id = m.workspace_id
 AND parent_scope.status = 'active'
 AND parent_scope.archived_at IS NULL
LEFT JOIN scope_messages root_message
  ON message_scope.type = 'thread'
 AND root_message.workspace_id = m.workspace_id
 AND root_message.scope_id = parent_scope.id
 AND root_message.id = message_scope.root_message_id
`+scopeReadableMembershipJoinSQL("$2")+`
`+scopePrivateAuthorityGrantJoinsSQL+`
JOIN members author_member
  ON author_member.id = m.author_member_id
 AND author_member.workspace_id = m.workspace_id
LEFT JOIN users u
  ON author_member.type = 'human'
 AND u.id = author_member.user_id
LEFT JOIN agents a
  ON author_member.type = 'agent'
 AND a.member_id = author_member.id
WHERE %s
ORDER BY m.created_at DESC, m.id ASC
LIMIT $%d
`, strings.Join(where, "\n  AND "), limitParam)
	return query, args, nil
}

func buildChannelAttachmentMessageCandidatePredicates(request ChannelAttachmentMessageCandidateRequest, workspaceID string, viewerMemberID string, channelID string) ([]string, []any) {
	args := []any{workspaceID, viewerMemberID, channelID, request.IncludeThreads}
	where := []string{
		"m.workspace_id = $1",
		"message_scope.workspace_id = $1",
		"message_scope.status = 'active'",
		"message_scope.archived_at IS NULL",
		"(message_scope.id = $3 OR ($4 AND message_scope.type = 'thread' AND message_scope.parent_scope_id = $3))",
		"(message_scope.type <> 'thread' OR parent_scope.id IS NOT NULL)",
		HumanVisibleMessageTypeSQL("m"),
		scopeReadableMessagePredicateSQL,
		channelAttachmentKindPredicate(NormalizeChannelAttachmentKind(request.Kind)),
	}
	if fromMemberID := strings.TrimSpace(request.FromMemberID); fromMemberID != "" {
		args = append(args, fromMemberID)
		where = append(where, fmt.Sprintf("m.author_member_id = $%d", len(args)))
	}
	if request.DateFrom != nil && !request.DateFrom.IsZero() {
		args = append(args, request.DateFrom.UTC())
		where = append(where, fmt.Sprintf("m.created_at >= $%d", len(args)))
	}
	if request.DateTo != nil && !request.DateTo.IsZero() {
		args = append(args, request.DateTo.UTC())
		where = append(where, fmt.Sprintf("m.created_at <= $%d", len(args)))
	}
	if q := strings.ToLower(strings.TrimSpace(request.Query)); q != "" {
		args = append(args, "%"+q+"%")
		param := len(args)
		where = append(where, fmt.Sprintf(`(
  LOWER(m.content) LIKE $%[1]d
  OR EXISTS (
      SELECT 1
      FROM attachments resource_search_attachment
      WHERE resource_search_attachment.workspace_id = $1
        AND resource_search_attachment.message_id = m.id
        AND (
            LOWER(resource_search_attachment.filename) LIKE $%[1]d
            OR LOWER(resource_search_attachment.content_type) LIKE $%[1]d
        )
  )
)`, param))
	}
	if !request.Cursor.IsZero() {
		args = append(args, request.Cursor.CreatedAt.UTC(), strings.TrimSpace(request.Cursor.MessageID))
		createdAtParam := len(args) - 1
		messageIDParam := len(args)
		messageIDComparator := ">"
		if request.Cursor.ResourceOrdinal > 0 {
			messageIDComparator = ">="
		}
		where = append(where, fmt.Sprintf(`(
  m.created_at < $%[1]d
  OR (m.created_at = $%[1]d AND m.id %[3]s $%[2]d)
)`, createdAtParam, messageIDParam, messageIDComparator))
	}
	return where, args
}

func channelAttachmentKindPredicate(kind ChannelAttachmentKind) string {
	attachmentExists := `EXISTS (
      SELECT 1
      FROM attachments resource_attachment
      WHERE resource_attachment.workspace_id = $1
        AND resource_attachment.message_id = m.id
  )`
	imageAttachmentExists := `EXISTS (
      SELECT 1
      FROM attachments resource_attachment
      WHERE resource_attachment.workspace_id = $1
        AND resource_attachment.message_id = m.id
        AND LOWER(resource_attachment.content_type) LIKE 'image/%%'
  )`
	fileAttachmentExists := `EXISTS (
      SELECT 1
      FROM attachments resource_attachment
      WHERE resource_attachment.workspace_id = $1
        AND resource_attachment.message_id = m.id
        AND LOWER(resource_attachment.content_type) NOT LIKE 'image/%%'
  )`
	switch kind {
	case ChannelAttachmentKindFile:
		return fileAttachmentExists
	case ChannelAttachmentKindImage:
		return imageAttachmentExists
	default:
		return attachmentExists
	}
}

func authorProfileFromResourceRow(memberType string, authorMemberID string, userID string, agentID string, agentName string, userDisplayName string, memberDisplayName string, userName string, userAvatarURL string, agentAvatarURL string) AuthorProfile {
	authorMemberID = strings.TrimSpace(authorMemberID)
	switch strings.TrimSpace(memberType) {
	case "human":
		return AuthorProfile{
			ActorType:   AuthorActorTypeHuman,
			PrincipalID: strings.TrimSpace(userID),
			MemberID:    authorMemberID,
			TargetID:    authorMemberID,
			DisplayName: firstNonEmpty(userDisplayName, memberDisplayName, userName, authorMemberID),
			AvatarURL:   strings.TrimSpace(userAvatarURL),
		}.Normalize()
	case "agent":
		trimmedAgentName := strings.TrimSpace(agentName)
		return AuthorProfile{
			ActorType:   AuthorActorTypeAgent,
			PrincipalID: strings.TrimSpace(agentID),
			MemberID:    authorMemberID,
			TargetID:    firstNonEmpty(trimmedAgentName, authorMemberID),
			DisplayName: firstNonEmpty(memberDisplayName, trimmedAgentName, authorMemberID),
			AvatarURL:   strings.TrimSpace(agentAvatarURL),
		}.Normalize()
	case "service":
		return AuthorProfile{
			ActorType:   AuthorActorTypeService,
			PrincipalID: authorMemberID,
			MemberID:    authorMemberID,
			TargetID:    authorMemberID,
			DisplayName: firstNonEmpty(memberDisplayName, authorMemberID),
		}.Normalize()
	default:
		return AuthorProfile{}
	}
}

func ChannelAttachmentDetailFromAppAttachment(item attachment.AppAttachment) ChannelAttachmentDetail {
	return ChannelAttachmentDetail{
		ID:           strings.TrimSpace(item.ID),
		Filename:     strings.TrimSpace(item.Filename),
		ContentType:  strings.TrimSpace(item.ContentType),
		SizeBytes:    item.SizeBytes,
		ThumbnailURL: strings.TrimSpace(item.ThumbnailURL),
	}
}