teg/backend/chat.go

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"math"
	"net/http"
	"sort"
	"strings"
	"time"

	"github.com/jackc/pgx/v5"
)

type ChatUser struct {
	ID             string     `json:"id"`
	Username       string     `json:"username"`
	DisplayName    string     `json:"displayName"`
	Email          string     `json:"email"`
	Avatar         string     `json:"avatar"`
	Unit           string     `json:"unit"`
	Online         bool       `json:"onlineStatus"`
	PresenceStatus string     `json:"presenceStatus"`
	LastSeenAt     *time.Time `json:"lastSeenAt"`
	AwaySince      *time.Time `json:"awaySince"`
	LastOnlineAt   *time.Time `json:"lastOnlineAt"`
}

type ChatMessage struct {
	ID                     string                `json:"id"`
	ConversationID         string                `json:"conversationId"`
	Type                   string                `json:"type"`
	Body                   string                `json:"body"`
	SystemEvent            json.RawMessage       `json:"systemEvent,omitempty"`
	CreatedAt              time.Time             `json:"createdAt"`
	EditedAt               *time.Time            `json:"editedAt"`
	ExpiresAt              *time.Time            `json:"expiresAt,omitempty"`
	Sender                 ChatUser              `json:"sender"`
	Attachments            []ChatAttachment      `json:"attachments"`
	ReplyTo                *ChatMessageReference `json:"replyTo"`
	ForwardedFrom          *ChatMessageReference `json:"forwardedFrom"`
	Location               *ChatLocation         `json:"location"`
	LinkPreview            *ChatLinkPreview      `json:"linkPreview"`
	Reactions              []ChatReaction        `json:"reactions"`
	replyToMessageID       string
	forwardedFromMessageID string
}

type ChatReaction struct {
	Emoji string             `json:"emoji"`
	Users []ChatReactionUser `json:"users"`
	Count int                `json:"count"`
}

type ChatReactionUser struct {
	ID          string `json:"id"`
	DisplayName string `json:"displayName"`
}

type ChatLocation struct {
	Lat   float64 `json:"lat"`
	Lng   float64 `json:"lng"`
	Label string  `json:"label"`
}

type ChatLinkPreview struct {
	URL         string `json:"url"`
	Title       string `json:"title"`
	Description string `json:"description"`
	ImageURL    string `json:"imageUrl"`
	SiteName    string `json:"siteName"`
}

type ChatAttachment struct {
	ID          string `json:"id"`
	FileName    string `json:"fileName"`
	ContentType string `json:"contentType"`
	SizeBytes   int64  `json:"sizeBytes"`
	URL         string `json:"url"`
}

type ChatMessageReference struct {
	ID                string    `json:"id"`
	Body              string    `json:"body"`
	SenderDisplayName string    `json:"senderDisplayName"`
	CreatedAt         time.Time `json:"createdAt"`
}

type ChatConversation struct {
	ID                  string       `json:"id"`
	Type                string       `json:"type"`
	Name                string       `json:"name"`
	Image               string       `json:"image"`
	TeamID              string       `json:"teamId"`
	OwnerID             string       `json:"ownerId"`
	IsPublic            bool         `json:"isPublic"`
	IsMember            bool         `json:"isMember"`
	DisappearingSeconds int          `json:"disappearingSeconds"`
	Participants        []ChatUser   `json:"participants"`
	LastMessage         *ChatMessage `json:"lastMessage"`
	LastMessageAt       *time.Time   `json:"lastMessageAt"`
	Muted               bool         `json:"muted"`
	UnreadCount         int          `json:"unreadCount"`
	CreatedAt           time.Time    `json:"createdAt"`
}

type createDirectConversationRequest struct {
	UserID string `json:"userId"`
}

type createChatMessageRequest struct {
	Body                   string        `json:"body"`
	AttachmentIDs          []string      `json:"attachmentIds"`
	ReplyToMessageID       string        `json:"replyToMessageId"`
	ForwardedFromMessageID string        `json:"forwardedFromMessageId"`
	Location               *ChatLocation `json:"location"`
}

type createChatMessageInput struct {
	Body                   string
	AttachmentIDs          []string
	ReplyToMessageID       string
	ForwardedFromMessageID string
	Location               *ChatLocation
}

type updateChatReactionRequest struct {
	Emoji string `json:"emoji"`
}

var (
	errChatAccessDenied    = errors.New("kein zugriff auf diesen chat")
	errChatMessageEmpty    = errors.New("nachricht darf nicht leer sein")
	errChatMessageTooLong  = errors.New("nachricht ist zu lang")
	errChatLocationInvalid = errors.New("ungültiger standort")
	errChatForwardSame     = errors.New("nachricht kann nicht in denselben chat weitergeleitet werden")
	errChatReadOnly        = errors.New("channel ist schreibgeschützt")
)

func normalizeChatLocation(location *ChatLocation) (*ChatLocation, error) {
	if location == nil {
		return nil, nil
	}

	if math.IsNaN(location.Lat) || math.IsNaN(location.Lng) ||
		location.Lat < -90 || location.Lat > 90 ||
		location.Lng < -180 || location.Lng > 180 {
		return nil, errChatLocationInvalid
	}

	label := strings.TrimSpace(location.Label)
	if labelRunes := []rune(label); len(labelRunes) > 200 {
		label = string(labelRunes[:200])
	}

	return &ChatLocation{Lat: location.Lat, Lng: location.Lng, Label: label}, nil
}

func (s *Server) handleListChatUsers(w http.ResponseWriter, r *http.Request) {
	user, ok := userFromContext(r.Context())
	if !ok {
		writeError(w, http.StatusUnauthorized, "Unauthorized")
		return
	}

	rows, err := s.db.Query(
		r.Context(),
		`
		SELECT
			id::TEXT,
			COALESCE(username, ''),
			COALESCE(display_name, ''),
			email,
			COALESCE(avatar, ''),
			COALESCE(unit, ''),
			`+presenceStatusSQL+`,
			NULL::TIMESTAMPTZ,
			NULL::TIMESTAMPTZ,
			NULL::TIMESTAMPTZ
		FROM users
		WHERE id <> $1
		ORDER BY
			(`+presenceStatusSQL+`) = 'online' DESC,
			lower(COALESCE(NULLIF(display_name, ''), username, email))
		`,
		user.ID,
	)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "Personen konnten nicht geladen werden")
		return
	}
	defer rows.Close()

	users := []ChatUser{}
	for rows.Next() {
		var chatUser ChatUser
		if err := rows.Scan(
			&chatUser.ID,
			&chatUser.Username,
			&chatUser.DisplayName,
			&chatUser.Email,
			&chatUser.Avatar,
			&chatUser.Unit,
			&chatUser.PresenceStatus,
			&chatUser.LastSeenAt,
			&chatUser.AwaySince,
			&chatUser.LastOnlineAt,
		); err != nil {
			writeError(w, http.StatusInternalServerError, "Personen konnten nicht gelesen werden")
			return
		}
		chatUser.Online = chatUser.PresenceStatus == "online"
		users = append(users, chatUser)
	}

	if err := rows.Err(); err != nil {
		writeError(w, http.StatusInternalServerError, "Personen konnten nicht vollständig geladen werden")
		return
	}

	writeJSON(w, http.StatusOK, map[string]any{"users": users})
}

func (s *Server) ensureTeamGroupConversations(ctx context.Context) error {
	_, err := s.db.Exec(
		ctx,
		`
		WITH team_groups AS (
			INSERT INTO conversations (type, name, team_id, created_by)
			SELECT 'group', t.name, t.id, NULL
			FROM teams t
			ON CONFLICT (team_id) WHERE team_id IS NOT NULL
			DO UPDATE SET
				type = 'group',
				name = EXCLUDED.name,
				created_by = NULL,
				updated_at = now()
			RETURNING id, team_id
		),
		removed_members AS (
			DELETE FROM conversation_members cm
			USING conversations c
			WHERE cm.conversation_id = c.id
			  AND c.team_id IS NOT NULL
			  AND NOT EXISTS (
				SELECT 1
				FROM user_teams ut
				WHERE ut.team_id = c.team_id
				  AND ut.user_id = cm.user_id
			  )
		)
		INSERT INTO conversation_members (conversation_id, user_id)
		SELECT tg.id, ut.user_id
		FROM team_groups tg
		JOIN user_teams ut ON ut.team_id = tg.team_id
		ON CONFLICT (conversation_id, user_id) DO NOTHING
		`,
	)

	return err
}

func (s *Server) handleListChatConversations(w http.ResponseWriter, r *http.Request) {
	user, ok := userFromContext(r.Context())
	if !ok {
		writeError(w, http.StatusUnauthorized, "Unauthorized")
		return
	}

	if err := s.ensureTeamGroupConversations(r.Context()); err != nil {
		writeError(w, http.StatusInternalServerError, "Team-Gruppenchats konnten nicht vorbereitet werden")
		return
	}
	if err := s.ensureAllUserChannels(r.Context(), user.ID); err != nil {
		writeError(w, http.StatusInternalServerError, "Channels konnten nicht vorbereitet werden")
		return
	}

	rows, err := s.db.Query(
		r.Context(),
		`
		SELECT
			c.id::TEXT,
			c.type,
			c.name,
			c.channel_image,
			COALESCE(c.team_id::TEXT, ''),
			(
				SELECT MAX(last_visible_message.created_at)
				FROM messages last_visible_message
				LEFT JOIN conversation_members last_visible_membership
					ON last_visible_membership.conversation_id = c.id
					AND last_visible_membership.user_id = $1
				WHERE last_visible_message.conversation_id = c.id
				  AND last_visible_message.deleted_at IS NULL
				  AND (last_visible_message.expires_at IS NULL OR last_visible_message.expires_at > now())
				  AND last_visible_message.created_at > COALESCE(
					last_visible_membership.last_cleared_at,
					'-infinity'::TIMESTAMPTZ
				  )
			),
			COALESCE((
				SELECT cm.muted
				FROM conversation_members cm
				WHERE cm.conversation_id = c.id
				  AND cm.user_id = $1
			), false),
			COALESCE((
				SELECT COUNT(*)::INT
				FROM messages unread_message
				LEFT JOIN conversation_members current_membership
					ON current_membership.conversation_id = c.id
					AND current_membership.user_id = $1
				WHERE unread_message.conversation_id = c.id
				  AND unread_message.deleted_at IS NULL
				  AND unread_message.message_type <> 'system'
				  AND unread_message.sender_id IS DISTINCT FROM $1::UUID
				  AND (unread_message.expires_at IS NULL OR unread_message.expires_at > now())
				  AND current_membership.user_id IS NOT NULL
				  AND unread_message.created_at > COALESCE(
					current_membership.last_cleared_at,
					'-infinity'::TIMESTAMPTZ
				  )
				  AND unread_message.created_at > COALESCE(
					current_membership.last_read_at,
					'-infinity'::TIMESTAMPTZ
				  )
			), 0),
			c.created_at,
			COALESCE(c.created_by::TEXT, ''),
			c.is_public,
			EXISTS (
				SELECT 1
				FROM conversation_members current_user_membership
				WHERE current_user_membership.conversation_id = c.id
				  AND current_user_membership.user_id = $1
			),
			c.disappearing_seconds
		FROM conversations c
		WHERE (
			EXISTS (
				SELECT 1
				FROM conversation_members cm
				WHERE cm.conversation_id = c.id
				  AND cm.user_id = $1
			)
			OR c.is_public = true
		)
		ORDER BY COALESCE(c.last_message_at, c.created_at) DESC
		`,
		user.ID,
	)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "Chats konnten nicht geladen werden")
		return
	}
	defer rows.Close()

	conversations := []ChatConversation{}
	for rows.Next() {
		var conversation ChatConversation
		if err := rows.Scan(
			&conversation.ID,
			&conversation.Type,
			&conversation.Name,
			&conversation.Image,
			&conversation.TeamID,
			&conversation.LastMessageAt,
			&conversation.Muted,
			&conversation.UnreadCount,
			&conversation.CreatedAt,
			&conversation.OwnerID,
			&conversation.IsPublic,
			&conversation.IsMember,
			&conversation.DisappearingSeconds,
		); err != nil {
			writeError(w, http.StatusInternalServerError, "Chats konnten nicht gelesen werden")
			return
		}

		participants, err := s.listChatParticipants(r.Context(), conversation.ID, user.ShowLastSeen)
		if err != nil {
			writeError(w, http.StatusInternalServerError, "Chatmitglieder konnten nicht geladen werden")
			return
		}
		conversation.Participants = participants

		lastMessage, err := s.getLastChatMessage(r.Context(), conversation.ID, user.ID)
		if err != nil && !errors.Is(err, pgx.ErrNoRows) {
			writeError(w, http.StatusInternalServerError, "Letzte Nachricht konnte nicht geladen werden")
			return
		}
		if err == nil {
			conversation.LastMessage = &lastMessage
		}

		conversations = append(conversations, conversation)
	}

	if err := rows.Err(); err != nil {
		writeError(w, http.StatusInternalServerError, "Chats konnten nicht vollständig geladen werden")
		return
	}

	writeJSON(w, http.StatusOK, map[string]any{"conversations": conversations})
}

func (s *Server) handleCreateDirectConversation(w http.ResponseWriter, r *http.Request) {
	currentUser, ok := userFromContext(r.Context())
	if !ok {
		writeError(w, http.StatusUnauthorized, "Unauthorized")
		return
	}

	var input createDirectConversationRequest
	if err := readJSON(r, &input); err != nil {
		writeError(w, http.StatusBadRequest, "Ungültige Anfrage")
		return
	}

	otherUserID := strings.TrimSpace(input.UserID)
	if otherUserID == "" || otherUserID == currentUser.ID {
		writeError(w, http.StatusBadRequest, "Eine andere Person ist erforderlich")
		return
	}

	var otherUserExists bool
	if err := s.db.QueryRow(
		r.Context(),
		`SELECT EXISTS(SELECT 1 FROM users WHERE id = $1)`,
		otherUserID,
	).Scan(&otherUserExists); err != nil || !otherUserExists {
		writeError(w, http.StatusNotFound, "Person wurde nicht gefunden")
		return
	}

	userIDs := []string{currentUser.ID, otherUserID}
	sort.Strings(userIDs)
	directKey := strings.Join(userIDs, ":")

	tx, err := s.db.Begin(r.Context())
	if err != nil {
		writeError(w, http.StatusInternalServerError, "Chat konnte nicht erstellt werden")
		return
	}
	defer tx.Rollback(r.Context())

	var conversationID string
	err = tx.QueryRow(
		r.Context(),
		`
		INSERT INTO conversations (type, direct_key, created_by)
		VALUES ('direct', $1, $2)
		ON CONFLICT (direct_key) WHERE direct_key <> ''
		DO UPDATE SET updated_at = conversations.updated_at
		RETURNING id::TEXT
		`,
		directKey,
		currentUser.ID,
	).Scan(&conversationID)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "Chat konnte nicht erstellt werden")
		return
	}

	for _, userID := range userIDs {
		if _, err := tx.Exec(
			r.Context(),
			`
			INSERT INTO conversation_members (conversation_id, user_id)
			VALUES ($1, $2)
			ON CONFLICT (conversation_id, user_id) DO NOTHING
			`,
			conversationID,
			userID,
		); err != nil {
			writeError(w, http.StatusInternalServerError, "Chatmitglieder konnten nicht gespeichert werden")
			return
		}
	}

	if err := tx.Commit(r.Context()); err != nil {
		writeError(w, http.StatusInternalServerError, "Chat konnte nicht gespeichert werden")
		return
	}

	conversation, err := s.getChatConversation(r.Context(), conversationID, currentUser.ID, currentUser.ShowLastSeen)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "Chat konnte nicht geladen werden")
		return
	}

	writeJSON(w, http.StatusOK, map[string]any{"conversation": conversation})
}

func (s *Server) handleListChatMessages(w http.ResponseWriter, r *http.Request) {
	user, ok := userFromContext(r.Context())
	if !ok {
		writeError(w, http.StatusUnauthorized, "Unauthorized")
		return
	}

	conversationID := strings.TrimSpace(r.PathValue("id"))
	if !s.isConversationMember(r.Context(), conversationID, user.ID) {
		writeError(w, http.StatusForbidden, "Kein Zugriff auf diesen Chat")
		return
	}

	rows, err := s.db.Query(
		r.Context(),
		`
		SELECT
			m.id::TEXT,
			m.conversation_id::TEXT,
			m.body,
			m.created_at,
			m.edited_at,
			COALESCE(m.reply_to_message_id::TEXT, ''),
			COALESCE(m.forwarded_from_message_id::TEXT, ''),
			COALESCE(u.id::TEXT, ''),
			COALESCE(u.username, NULLIF(c.channel_source_name, ''), c.name, ''),
			COALESCE(u.display_name, NULLIF(c.channel_source_name, ''), c.name, ''),
			COALESCE(u.email, ''),
			COALESCE(NULLIF(u.avatar, ''), NULLIF(c.channel_image, ''), ''),
			COALESCE(u.unit, ''),
			CASE
				WHEN u.presence_mode = 'offline'
					OR u.last_seen_at IS NULL
					OR u.last_seen_at < now() - INTERVAL '2 minutes'
					THEN 'offline'
				WHEN u.presence_mode = 'away'
					OR u.last_active_at IS NULL
					OR u.last_active_at < now() - make_interval(mins => u.away_after_minutes)
					THEN 'away'
				ELSE 'online'
			END,
			CASE
				WHEN c.type = 'direct' AND u.show_last_seen
					THEN u.last_seen_at
				ELSE NULL
			END,
			CASE
				WHEN c.type <> 'direct' OR NOT u.show_last_seen THEN NULL
				WHEN u.presence_mode = 'away' THEN u.presence_mode_updated_at
				WHEN u.presence_mode = 'online'
					AND u.last_seen_at >= now() - INTERVAL '2 minutes'
					AND u.last_active_at < now() - make_interval(mins => u.away_after_minutes)
					THEN u.last_active_at + make_interval(mins => u.away_after_minutes)
				ELSE NULL
			END,
			CASE
				WHEN c.type = 'direct' AND u.show_last_seen
					THEN CASE
						WHEN u.presence_mode = 'offline' THEN u.presence_mode_updated_at
						ELSE u.last_seen_at
					END
				ELSE NULL
			END,
			m.message_type,
			m.system_event,
			m.expires_at
		FROM messages m
		JOIN conversations c ON c.id = m.conversation_id
		LEFT JOIN users u ON u.id = m.sender_id
		LEFT JOIN conversation_members viewer_membership
			ON viewer_membership.conversation_id = m.conversation_id
			AND viewer_membership.user_id = $2
		WHERE m.conversation_id = $1
		  AND m.deleted_at IS NULL
		  AND (m.expires_at IS NULL OR m.expires_at > now())
		  AND m.created_at > COALESCE(
			viewer_membership.last_cleared_at,
			'-infinity'::TIMESTAMPTZ
		  )
		ORDER BY m.created_at ASC, m.id ASC
		LIMIT 200
		`,
		conversationID,
		user.ID,
	)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "Nachrichten konnten nicht geladen werden")
		return
	}
	defer rows.Close()

	messages := []ChatMessage{}
	for rows.Next() {
		message, err := scanChatMessage(rows)
		if err != nil {
			writeError(w, http.StatusInternalServerError, "Nachrichten konnten nicht gelesen werden")
			return
		}
		if err := s.enrichChatMessage(r.Context(), &message); err != nil {
			writeError(w, http.StatusInternalServerError, "Nachrichtendetails konnten nicht geladen werden")
			return
		}
		messages = append(messages, message)
	}

	if err := rows.Err(); err != nil {
		writeError(w, http.StatusInternalServerError, "Nachrichten konnten nicht vollständig geladen werden")
		return
	}

	_ = s.markChatConversationRead(r.Context(), conversationID, user.ID)

	// Gegenseitigkeit: Wer selbst nichts teilt, bekommt auch keine Status-Details.
	if !user.ShowLastSeen {
		for i := range messages {
			messages[i].Sender.LastSeenAt = nil
			messages[i].Sender.AwaySince = nil
			messages[i].Sender.LastOnlineAt = nil
		}
	}

	writeJSON(w, http.StatusOK, map[string]any{"messages": messages})
}

func (s *Server) handleCreateChatMessage(w http.ResponseWriter, r *http.Request) {
	user, ok := userFromContext(r.Context())
	if !ok {
		writeError(w, http.StatusUnauthorized, "Unauthorized")
		return
	}

	conversationID := strings.TrimSpace(r.PathValue("id"))

	var input createChatMessageRequest
	if err := readJSON(r, &input); err != nil {
		writeError(w, http.StatusBadRequest, "Ungültige Anfrage")
		return
	}

	message, err := s.createChatMessage(
		r.Context(),
		user,
		conversationID,
		createChatMessageInput{
			Body:                   input.Body,
			AttachmentIDs:          input.AttachmentIDs,
			ReplyToMessageID:       input.ReplyToMessageID,
			ForwardedFromMessageID: input.ForwardedFromMessageID,
			Location:               input.Location,
		},
	)
	if err != nil {
		writeChatMessageError(w, err)
		return
	}

	s.publishChatMessage(r.Context(), message, "")
	writeJSON(w, http.StatusCreated, map[string]any{"message": message})
}

func normalizeChatReactionEmoji(value string) string {
	value = strings.TrimSpace(value)
	if value == "" {
		return ""
	}

	runes := []rune(value)
	if len(runes) > 8 {
		return ""
	}

	return value
}

func (s *Server) handleToggleChatMessageReaction(w http.ResponseWriter, r *http.Request) {
	user, ok := userFromContext(r.Context())
	if !ok {
		writeError(w, http.StatusUnauthorized, "Unauthorized")
		return
	}

	messageID := strings.TrimSpace(r.PathValue("id"))

	var input updateChatReactionRequest
	if err := readJSON(r, &input); err != nil {
		writeError(w, http.StatusBadRequest, "Ungültige Anfrage")
		return
	}

	emoji := normalizeChatReactionEmoji(input.Emoji)
	if emoji == "" {
		writeError(w, http.StatusBadRequest, "Emoji ist erforderlich")
		return
	}

	var conversationID string
	err := s.db.QueryRow(
		r.Context(),
		`
		SELECT conversation_id::TEXT
		FROM messages
		WHERE id = $1
		  AND deleted_at IS NULL
		  AND message_type <> 'system'
		  AND (expires_at IS NULL OR expires_at > now())
		`,
		messageID,
	).Scan(&conversationID)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			writeError(w, http.StatusNotFound, "Nachricht wurde nicht gefunden")
			return
		}
		writeError(w, http.StatusInternalServerError, "Nachricht konnte nicht geladen werden")
		return
	}

	if !s.isConversationMember(r.Context(), conversationID, user.ID) {
		writeError(w, http.StatusForbidden, "Kein Zugriff auf diesen Chat")
		return
	}

	tx, err := s.db.Begin(r.Context())
	if err != nil {
		writeError(w, http.StatusInternalServerError, "Reaktion konnte nicht gespeichert werden")
		return
	}
	defer tx.Rollback(r.Context())

	var existingEmoji string
	err = tx.QueryRow(
		r.Context(),
		`
		SELECT emoji
		FROM message_reactions
		WHERE message_id = $1
		  AND user_id = $2
		ORDER BY created_at DESC
		LIMIT 1
		`,
		messageID,
		user.ID,
	).Scan(&existingEmoji)
	if err != nil && !errors.Is(err, pgx.ErrNoRows) {
		writeError(w, http.StatusInternalServerError, "Reaktion konnte nicht geladen werden")
		return
	}

	if _, err := tx.Exec(
		r.Context(),
		`
		DELETE FROM message_reactions
		WHERE message_id = $1
		  AND user_id = $2
		`,
		messageID,
		user.ID,
	); err != nil {
		writeError(w, http.StatusInternalServerError, "Reaktion konnte nicht entfernt werden")
		return
	}

	if existingEmoji != emoji {
		if _, err := tx.Exec(
			r.Context(),
			`
			INSERT INTO message_reactions (message_id, user_id, emoji)
			VALUES ($1, $2, $3)
			ON CONFLICT (message_id, user_id) DO UPDATE
			SET emoji = EXCLUDED.emoji,
			    created_at = now()
			`,
			messageID,
			user.ID,
			emoji,
		); err != nil {
			writeError(w, http.StatusInternalServerError, "Reaktion konnte nicht gespeichert werden")
			return
		}
	}

	if err := tx.Commit(r.Context()); err != nil {
		writeError(w, http.StatusInternalServerError, "Reaktion konnte nicht gespeichert werden")
		return
	}

	message, err := s.getChatMessage(r.Context(), messageID)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "Nachricht konnte nicht aktualisiert werden")
		return
	}

	s.publishChatMessageUpdate(r.Context(), message)
	writeJSON(w, http.StatusOK, map[string]any{"message": message})
}

func (s *Server) createChatMessage(
	ctx context.Context,
	user User,
	conversationID string,
	input createChatMessageInput,
) (ChatMessage, error) {
	var message ChatMessage

	conversationID = strings.TrimSpace(conversationID)
	if !s.canAccessConversation(ctx, conversationID, user.ID) {
		return message, errChatAccessDenied
	}
	if !s.isConversationWritable(ctx, conversationID) {
		return message, errChatReadOnly
	}
	if !s.isConversationMember(ctx, conversationID, user.ID) {
		return message, errChatAccessDenied
	}

	if replyToMessageID := strings.TrimSpace(input.ReplyToMessageID); replyToMessageID != "" {
		var valid bool
		err := s.db.QueryRow(
			ctx,
			`
			SELECT EXISTS(
				SELECT 1
				FROM messages
				WHERE id = $1
				  AND conversation_id = $2
				  AND deleted_at IS NULL
			)
			`,
			replyToMessageID,
			conversationID,
		).Scan(&valid)
		if err != nil {
			return message, err
		}
		if !valid {
			return message, errChatAccessDenied
		}
	}

	if forwardedFromMessageID := strings.TrimSpace(input.ForwardedFromMessageID); forwardedFromMessageID != "" {
		var sourceConversationID string
		err := s.db.QueryRow(
			ctx,
			`
			SELECT conversation_id::TEXT
			FROM messages
			WHERE id = $1
			  AND deleted_at IS NULL
			`,
			forwardedFromMessageID,
		).Scan(&sourceConversationID)
		if err != nil || !s.canAccessConversation(ctx, sourceConversationID, user.ID) {
			return message, errChatAccessDenied
		}
		if sourceConversationID == conversationID {
			return message, errChatForwardSame
		}
	}

	location, err := normalizeChatLocation(input.Location)
	if err != nil {
		return message, err
	}

	body := strings.TrimSpace(input.Body)
	if body == "" &&
		len(input.AttachmentIDs) == 0 &&
		strings.TrimSpace(input.ForwardedFromMessageID) == "" &&
		location == nil {
		return message, errChatMessageEmpty
	}
	if len([]rune(body)) > 4000 {
		return message, errChatMessageTooLong
	}

	var locationLat, locationLng *float64
	locationLabel := ""
	if location != nil {
		locationLat = &location.Lat
		locationLng = &location.Lng
		locationLabel = location.Label
	}

	tx, err := s.db.Begin(ctx)
	if err != nil {
		return message, err
	}
	defer tx.Rollback(ctx)

	var messageID string
	err = tx.QueryRow(
		ctx,
		`
		INSERT INTO messages (
			conversation_id,
			sender_id,
			body,
			reply_to_message_id,
			forwarded_from_message_id,
			location_lat,
			location_lng,
			location_label,
			expires_at
		)
		SELECT
			c.id,
			$2,
			$3,
			NULLIF($4, '')::UUID,
			NULLIF($5, '')::UUID,
			$6,
			$7,
			$8,
			CASE
				WHEN c.disappearing_seconds > 0
					THEN now() + make_interval(secs => c.disappearing_seconds)
				ELSE NULL
			END
		FROM conversations c
		WHERE c.id = $1
		RETURNING id::TEXT
		`,
		conversationID,
		user.ID,
		body,
		strings.TrimSpace(input.ReplyToMessageID),
		strings.TrimSpace(input.ForwardedFromMessageID),
		locationLat,
		locationLng,
		locationLabel,
	).Scan(&messageID)
	if err != nil {
		return message, err
	}

	if _, err := tx.Exec(
		ctx,
		`
		UPDATE conversations
		SET last_message_at = now(), updated_at = now()
		WHERE id = $1
		`,
		conversationID,
	); err != nil {
		return message, err
	}

	for _, attachmentID := range input.AttachmentIDs {
		commandTag, err := tx.Exec(
			ctx,
			`
			UPDATE message_attachments
			SET message_id = $1
			WHERE id = $2
			  AND uploader_id = $3
			  AND message_id IS NULL
			`,
			messageID,
			strings.TrimSpace(attachmentID),
			user.ID,
		)
		if err != nil {
			return message, err
		}
		if commandTag.RowsAffected() == 0 {
			return message, errors.New("anhang wurde nicht gefunden")
		}
	}

	if forwardedFromMessageID := strings.TrimSpace(input.ForwardedFromMessageID); forwardedFromMessageID != "" {
		if _, err := tx.Exec(
			ctx,
			`
			INSERT INTO message_attachments (
				message_id,
				uploader_id,
				file_name,
				content_type,
				size_bytes,
				data
			)
			SELECT
				$1,
				$2,
				file_name,
				content_type,
				size_bytes,
				data
			FROM message_attachments
			WHERE message_id = $3
			ORDER BY created_at ASC
			`,
			messageID,
			user.ID,
			forwardedFromMessageID,
		); err != nil {
			return message, err
		}
	}

	if _, err := tx.Exec(
		ctx,
		`
		UPDATE conversation_members
		SET last_read_at = now()
		WHERE conversation_id = $1 AND user_id = $2
		`,
		conversationID,
		user.ID,
	); err != nil {
		return message, err
	}

	if err := tx.Commit(ctx); err != nil {
		return message, err
	}

	message, err = s.getChatMessage(ctx, messageID)
	if err != nil {
		return message, err
	}

	return message, nil
}

func writeChatMessageError(w http.ResponseWriter, err error) {
	switch {
	case errors.Is(err, errChatAccessDenied):
		writeError(w, http.StatusForbidden, "Kein Zugriff auf diesen Chat")
	case errors.Is(err, errChatMessageEmpty):
		writeError(w, http.StatusBadRequest, "Nachricht darf nicht leer sein")
	case errors.Is(err, errChatMessageTooLong):
		writeError(w, http.StatusBadRequest, "Nachricht darf höchstens 4000 Zeichen enthalten")
	case errors.Is(err, errChatLocationInvalid):
		writeError(w, http.StatusBadRequest, "Ungültiger Standort")
	case errors.Is(err, errChatForwardSame):
		writeError(w, http.StatusBadRequest, "Nachrichten können nicht in denselben Chat weitergeleitet werden")
	case errors.Is(err, errChatReadOnly):
		writeError(w, http.StatusForbidden, "Channels sind schreibgeschützt")
	default:
		writeError(w, http.StatusInternalServerError, "Nachricht konnte nicht gesendet werden")
	}
}

func (s *Server) canAccessConversation(ctx context.Context, conversationID string, userID string) bool {
	if conversationID == "" || userID == "" {
		return false
	}

	var allowed bool
	err := s.db.QueryRow(
		ctx,
		`
		SELECT EXISTS(
			SELECT 1
			FROM conversations c
			WHERE c.id = $1
			  AND (
				EXISTS (
					SELECT 1
					FROM conversation_members cm
					WHERE cm.conversation_id = c.id
					  AND cm.user_id = $2
				)
				OR c.is_public = true
			  )
		)
		`,
		conversationID,
		userID,
	).Scan(&allowed)

	return err == nil && allowed
}

func (s *Server) isConversationMember(ctx context.Context, conversationID string, userID string) bool {
	if conversationID == "" || userID == "" {
		return false
	}

	var member bool
	err := s.db.QueryRow(
		ctx,
		`
		SELECT EXISTS(
			SELECT 1
			FROM conversation_members cm
			WHERE cm.conversation_id = $1
			  AND cm.user_id = $2
		)
		`,
		conversationID,
		userID,
	).Scan(&member)

	return err == nil && member
}

func (s *Server) canPostToConversation(ctx context.Context, conversationID string, userID string) bool {
	if conversationID == "" || userID == "" {
		return false
	}

	var allowed bool
	err := s.db.QueryRow(
		ctx,
		`
		SELECT EXISTS(
			SELECT 1
			FROM conversations c
			JOIN conversation_members cm
			  ON cm.conversation_id = c.id
			 AND cm.user_id = $2
			WHERE c.id = $1
			  AND c.type <> 'channel'
		)
		`,
		conversationID,
		userID,
	).Scan(&allowed)

	return err == nil && allowed
}

func (s *Server) isConversationWritable(ctx context.Context, conversationID string) bool {
	var writable bool
	err := s.db.QueryRow(
		ctx,
		`
		SELECT type <> 'channel'
		FROM conversations
		WHERE id = $1
		`,
		conversationID,
	).Scan(&writable)
	return err == nil && writable
}

func (s *Server) listChatParticipants(ctx context.Context, conversationID string, viewerShowLastSeen bool) ([]ChatUser, error) {
	rows, err := s.db.Query(
		ctx,
		`
		SELECT
			u.id::TEXT,
			COALESCE(u.username, NULLIF(c.channel_source_name, ''), c.name, ''),
			COALESCE(u.display_name, NULLIF(c.channel_source_name, ''), c.name, ''),
			u.email,
			COALESCE(NULLIF(u.avatar, ''), NULLIF(c.channel_image, ''), ''),
			COALESCE(u.unit, ''),
			CASE
				WHEN u.presence_mode = 'offline'
					OR u.last_seen_at IS NULL
					OR u.last_seen_at < now() - INTERVAL '2 minutes'
					THEN 'offline'
				WHEN u.presence_mode = 'away'
					OR u.last_active_at IS NULL
					OR u.last_active_at < now() - make_interval(mins => u.away_after_minutes)
					THEN 'away'
				ELSE 'online'
			END,
			CASE
				WHEN c.type = 'direct' AND u.show_last_seen
					THEN u.last_seen_at
				ELSE NULL
			END,
			CASE
				WHEN c.type <> 'direct' OR NOT u.show_last_seen THEN NULL
				WHEN u.presence_mode = 'away' THEN u.presence_mode_updated_at
				WHEN u.presence_mode = 'online'
					AND u.last_seen_at >= now() - INTERVAL '2 minutes'
					AND u.last_active_at < now() - make_interval(mins => u.away_after_minutes)
					THEN u.last_active_at + make_interval(mins => u.away_after_minutes)
				ELSE NULL
			END,
			CASE
				WHEN c.type = 'direct' AND u.show_last_seen
					THEN CASE
						WHEN u.presence_mode = 'offline' THEN u.presence_mode_updated_at
						ELSE u.last_seen_at
					END
				ELSE NULL
			END
		FROM conversations c
		JOIN conversation_members cm ON cm.conversation_id = c.id
		JOIN users u ON u.id = cm.user_id
		WHERE c.id = $1
		ORDER BY lower(COALESCE(NULLIF(u.display_name, ''), u.username, u.email))
		`,
		conversationID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	participants := []ChatUser{}
	for rows.Next() {
		var participant ChatUser
		if err := rows.Scan(
			&participant.ID,
			&participant.Username,
			&participant.DisplayName,
			&participant.Email,
			&participant.Avatar,
			&participant.Unit,
			&participant.PresenceStatus,
			&participant.LastSeenAt,
			&participant.AwaySince,
			&participant.LastOnlineAt,
		); err != nil {
			return nil, err
		}
		participant.Online = participant.PresenceStatus == "online"
		participants = append(participants, participant)
	}

	// Gegenseitigkeit: Wer den eigenen letzten Online-Zeitpunkt nicht teilt,
	// sieht auch bei anderen keine Status-Details (zuletzt online / Abwesenheitsdauer).
	if !viewerShowLastSeen {
		for i := range participants {
			participants[i].LastSeenAt = nil
			participants[i].AwaySince = nil
			participants[i].LastOnlineAt = nil
		}
	}

	return participants, rows.Err()
}

func (s *Server) getChatConversation(ctx context.Context, conversationID string, userID string, viewerShowLastSeen bool) (ChatConversation, error) {
	var conversation ChatConversation
	err := s.db.QueryRow(
		ctx,
		`
		SELECT
			c.id::TEXT,
			c.type,
			c.name,
			c.channel_image,
			COALESCE(c.team_id::TEXT, ''),
			(
				SELECT MAX(last_visible_message.created_at)
				FROM messages last_visible_message
				LEFT JOIN conversation_members last_visible_membership
					ON last_visible_membership.conversation_id = c.id
					AND last_visible_membership.user_id = $2
				WHERE last_visible_message.conversation_id = c.id
				  AND last_visible_message.deleted_at IS NULL
				  AND (last_visible_message.expires_at IS NULL OR last_visible_message.expires_at > now())
				  AND last_visible_message.created_at > COALESCE(
					last_visible_membership.last_cleared_at,
					'-infinity'::TIMESTAMPTZ
				  )
			),
			COALESCE((
				SELECT cm.muted
				FROM conversation_members cm
				WHERE cm.conversation_id = c.id
				  AND cm.user_id = $2
			), false),
			COALESCE((
				SELECT COUNT(*)::INT
				FROM messages unread_message
				LEFT JOIN conversation_members current_membership
					ON current_membership.conversation_id = c.id
					AND current_membership.user_id = $2
				WHERE unread_message.conversation_id = c.id
				  AND unread_message.deleted_at IS NULL
				  AND unread_message.message_type <> 'system'
				  AND unread_message.sender_id IS DISTINCT FROM $2::UUID
				  AND (unread_message.expires_at IS NULL OR unread_message.expires_at > now())
				  AND current_membership.user_id IS NOT NULL
				  AND unread_message.created_at > COALESCE(
					current_membership.last_cleared_at,
					'-infinity'::TIMESTAMPTZ
				  )
				  AND unread_message.created_at > COALESCE(
					current_membership.last_read_at,
					'-infinity'::TIMESTAMPTZ
				  )
			), 0),
			c.created_at,
			COALESCE(c.created_by::TEXT, ''),
			c.is_public,
			EXISTS (
				SELECT 1
				FROM conversation_members current_user_membership
				WHERE current_user_membership.conversation_id = c.id
				  AND current_user_membership.user_id = $2
			),
			c.disappearing_seconds
		FROM conversations c
		WHERE c.id = $1
		`,
		conversationID,
		userID,
	).Scan(
		&conversation.ID,
		&conversation.Type,
		&conversation.Name,
		&conversation.Image,
		&conversation.TeamID,
		&conversation.LastMessageAt,
		&conversation.Muted,
		&conversation.UnreadCount,
		&conversation.CreatedAt,
		&conversation.OwnerID,
		&conversation.IsPublic,
		&conversation.IsMember,
		&conversation.DisappearingSeconds,
	)
	if err != nil {
		return conversation, err
	}
	if !s.canAccessConversation(ctx, conversation.ID, userID) {
		return conversation, pgx.ErrNoRows
	}

	conversation.Participants, err = s.listChatParticipants(ctx, conversation.ID, viewerShowLastSeen)
	if err != nil {
		return conversation, err
	}

	lastMessage, err := s.getLastChatMessage(ctx, conversation.ID, userID)
	if err == nil {
		conversation.LastMessage = &lastMessage
	} else if !errors.Is(err, pgx.ErrNoRows) {
		return conversation, err
	}

	return conversation, nil
}

type chatMessageScanner interface {
	Scan(dest ...any) error
}

func scanChatMessage(scanner chatMessageScanner) (ChatMessage, error) {
	var message ChatMessage
	err := scanner.Scan(
		&message.ID,
		&message.ConversationID,
		&message.Body,
		&message.CreatedAt,
		&message.EditedAt,
		&message.replyToMessageID,
		&message.forwardedFromMessageID,
		&message.Sender.ID,
		&message.Sender.Username,
		&message.Sender.DisplayName,
		&message.Sender.Email,
		&message.Sender.Avatar,
		&message.Sender.Unit,
		&message.Sender.PresenceStatus,
		&message.Sender.LastSeenAt,
		&message.Sender.AwaySince,
		&message.Sender.LastOnlineAt,
		&message.Type,
		&message.SystemEvent,
		&message.ExpiresAt,
	)
	message.Sender.Online = message.Sender.PresenceStatus == "online"
	return message, err
}

func (s *Server) getChatMessage(ctx context.Context, messageID string) (ChatMessage, error) {
	row := s.db.QueryRow(
		ctx,
		`
		SELECT
			m.id::TEXT,
			m.conversation_id::TEXT,
			m.body,
			m.created_at,
			m.edited_at,
			COALESCE(m.reply_to_message_id::TEXT, ''),
			COALESCE(m.forwarded_from_message_id::TEXT, ''),
			COALESCE(u.id::TEXT, ''),
			COALESCE(u.username, NULLIF(c.channel_source_name, ''), c.name, ''),
			COALESCE(u.display_name, NULLIF(c.channel_source_name, ''), c.name, ''),
			COALESCE(u.email, ''),
			COALESCE(NULLIF(u.avatar, ''), NULLIF(c.channel_image, ''), ''),
			COALESCE(u.unit, ''),
			CASE
				WHEN u.presence_mode = 'offline'
					OR u.last_seen_at IS NULL
					OR u.last_seen_at < now() - INTERVAL '2 minutes'
					THEN 'offline'
				WHEN u.presence_mode = 'away'
					OR u.last_active_at IS NULL
					OR u.last_active_at < now() - make_interval(mins => u.away_after_minutes)
					THEN 'away'
				ELSE 'online'
			END,
			CASE
				WHEN c.type = 'direct' AND u.show_last_seen
					THEN u.last_seen_at
				ELSE NULL
			END,
			CASE
				WHEN c.type <> 'direct' OR NOT u.show_last_seen THEN NULL
				WHEN u.presence_mode = 'away' THEN u.presence_mode_updated_at
				WHEN u.presence_mode = 'online'
					AND u.last_seen_at >= now() - INTERVAL '2 minutes'
					AND u.last_active_at < now() - make_interval(mins => u.away_after_minutes)
					THEN u.last_active_at + make_interval(mins => u.away_after_minutes)
				ELSE NULL
			END,
			CASE
				WHEN c.type = 'direct' AND u.show_last_seen
					THEN CASE
						WHEN u.presence_mode = 'offline' THEN u.presence_mode_updated_at
						ELSE u.last_seen_at
					END
				ELSE NULL
			END,
			m.message_type,
			m.system_event,
			m.expires_at
		FROM messages m
		JOIN conversations c ON c.id = m.conversation_id
		LEFT JOIN users u ON u.id = m.sender_id
		WHERE m.id = $1
		`,
		messageID,
	)
	message, err := scanChatMessage(row)
	if err != nil {
		return message, err
	}
	err = s.enrichChatMessage(ctx, &message)
	return message, err
}

func (s *Server) getLastChatMessage(ctx context.Context, conversationID string, userID string) (ChatMessage, error) {
	row := s.db.QueryRow(
		ctx,
		`
		SELECT
			m.id::TEXT,
			m.conversation_id::TEXT,
			m.body,
			m.created_at,
			m.edited_at,
			COALESCE(m.reply_to_message_id::TEXT, ''),
			COALESCE(m.forwarded_from_message_id::TEXT, ''),
			COALESCE(u.id::TEXT, ''),
			COALESCE(u.username, NULLIF(c.channel_source_name, ''), c.name, ''),
			COALESCE(u.display_name, NULLIF(c.channel_source_name, ''), c.name, ''),
			COALESCE(u.email, ''),
			COALESCE(NULLIF(u.avatar, ''), NULLIF(c.channel_image, ''), ''),
			COALESCE(u.unit, ''),
			CASE
				WHEN u.presence_mode = 'offline'
					OR u.last_seen_at IS NULL
					OR u.last_seen_at < now() - INTERVAL '2 minutes'
					THEN 'offline'
				WHEN u.presence_mode = 'away'
					OR u.last_active_at IS NULL
					OR u.last_active_at < now() - make_interval(mins => u.away_after_minutes)
					THEN 'away'
				ELSE 'online'
			END,
			CASE
				WHEN c.type = 'direct' AND u.show_last_seen
					THEN u.last_seen_at
				ELSE NULL
			END,
			CASE
				WHEN c.type <> 'direct' OR NOT u.show_last_seen THEN NULL
				WHEN u.presence_mode = 'away' THEN u.presence_mode_updated_at
				WHEN u.presence_mode = 'online'
					AND u.last_seen_at >= now() - INTERVAL '2 minutes'
					AND u.last_active_at < now() - make_interval(mins => u.away_after_minutes)
					THEN u.last_active_at + make_interval(mins => u.away_after_minutes)
				ELSE NULL
			END,
			CASE
				WHEN c.type = 'direct' AND u.show_last_seen
					THEN CASE
						WHEN u.presence_mode = 'offline' THEN u.presence_mode_updated_at
						ELSE u.last_seen_at
					END
				ELSE NULL
			END,
			m.message_type,
			m.system_event,
			m.expires_at
		FROM messages m
		JOIN conversations c ON c.id = m.conversation_id
		LEFT JOIN users u ON u.id = m.sender_id
		LEFT JOIN conversation_members viewer_membership
			ON viewer_membership.conversation_id = m.conversation_id
			AND viewer_membership.user_id = $2
		WHERE m.conversation_id = $1
		  AND m.deleted_at IS NULL
		  AND (m.expires_at IS NULL OR m.expires_at > now())
		  AND m.created_at > COALESCE(
			viewer_membership.last_cleared_at,
			'-infinity'::TIMESTAMPTZ
		  )
		ORDER BY m.created_at DESC, m.id DESC
		LIMIT 1
		`,
		conversationID,
		userID,
	)
	message, err := scanChatMessage(row)
	if err != nil {
		return message, err
	}
	err = s.enrichChatMessage(ctx, &message)
	return message, err
}

func (s *Server) enrichChatMessage(ctx context.Context, message *ChatMessage) error {
	attachments, err := s.listChatAttachments(ctx, message.ID)
	if err != nil {
		return err
	}
	message.Attachments = attachments

	location, err := s.getChatMessageLocation(ctx, message.ID)
	if err != nil {
		return err
	}
	message.Location = location

	preview, err := s.getChatLinkPreview(ctx, message.ID)
	if err != nil {
		return err
	}
	message.LinkPreview = preview

	reactions, err := s.listChatReactions(ctx, message.ID)
	if err != nil {
		return err
	}
	message.Reactions = reactions

	if message.replyToMessageID != "" {
		reference, err := s.getChatMessageReference(ctx, message.replyToMessageID)
		if err != nil && !errors.Is(err, pgx.ErrNoRows) {
			return err
		}
		if err == nil {
			message.ReplyTo = &reference
		}
	}

	if message.forwardedFromMessageID != "" {
		reference, err := s.getChatMessageReference(ctx, message.forwardedFromMessageID)
		if err != nil && !errors.Is(err, pgx.ErrNoRows) {
			return err
		}
		if err == nil {
			message.ForwardedFrom = &reference
		}
	}

	return nil
}

func (s *Server) listChatReactions(
	ctx context.Context,
	messageID string,
) ([]ChatReaction, error) {
	rows, err := s.db.Query(
		ctx,
		`
		SELECT
			mr.emoji,
			mr.user_id::TEXT,
			COALESCE(NULLIF(u.display_name, ''), NULLIF(u.username, ''), u.email, '')
		FROM message_reactions mr
		JOIN users u ON u.id = mr.user_id
		WHERE mr.message_id = $1
		ORDER BY mr.created_at ASC, mr.user_id ASC
		`,
		messageID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	reactions := []ChatReaction{}
	reactionByEmoji := map[string]int{}

	for rows.Next() {
		var emoji string
		var reactionUser ChatReactionUser
		if err := rows.Scan(
			&emoji,
			&reactionUser.ID,
			&reactionUser.DisplayName,
		); err != nil {
			return nil, err
		}

		index, ok := reactionByEmoji[emoji]
		if !ok {
			reactionByEmoji[emoji] = len(reactions)
			reactions = append(reactions, ChatReaction{Emoji: emoji})
			index = len(reactions) - 1
		}

		reactions[index].Users = append(reactions[index].Users, reactionUser)
		reactions[index].Count = len(reactions[index].Users)
	}

	return reactions, rows.Err()
}

func (s *Server) getChatMessageLocation(
	ctx context.Context,
	messageID string,
) (*ChatLocation, error) {
	var lat, lng sql.NullFloat64
	var label string

	err := s.db.QueryRow(
		ctx,
		`SELECT location_lat, location_lng, COALESCE(location_label, '') FROM messages WHERE id = $1`,
		messageID,
	).Scan(&lat, &lng, &label)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}

	if !lat.Valid || !lng.Valid {
		return nil, nil
	}

	return &ChatLocation{Lat: lat.Float64, Lng: lng.Float64, Label: label}, nil
}

func (s *Server) listChatAttachments(
	ctx context.Context,
	messageID string,
) ([]ChatAttachment, error) {
	rows, err := s.db.Query(
		ctx,
		`
		SELECT id::TEXT, file_name, content_type, size_bytes
		FROM message_attachments
		WHERE message_id = $1
		ORDER BY created_at ASC
		`,
		messageID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	attachments := []ChatAttachment{}
	for rows.Next() {
		var attachment ChatAttachment
		if err := rows.Scan(
			&attachment.ID,
			&attachment.FileName,
			&attachment.ContentType,
			&attachment.SizeBytes,
		); err != nil {
			return nil, err
		}
		attachment.URL = "/chat/attachments/" + attachment.ID
		attachments = append(attachments, attachment)
	}

	return attachments, rows.Err()
}

func (s *Server) getChatMessageReference(
	ctx context.Context,
	messageID string,
) (ChatMessageReference, error) {
	var reference ChatMessageReference

	err := s.db.QueryRow(
		ctx,
		`
		SELECT
			m.id::TEXT,
			m.body,
			COALESCE(
				NULLIF(u.display_name, ''),
				NULLIF(u.username, ''),
				u.email,
				NULLIF(c.channel_source_name, ''),
				c.name,
				'Gelöschter Benutzer'
			),
			m.created_at
		FROM messages m
		JOIN conversations c ON c.id = m.conversation_id
		LEFT JOIN users u ON u.id = m.sender_id
		WHERE m.id = $1
		  AND m.deleted_at IS NULL
		`,
		messageID,
	).Scan(
		&reference.ID,
		&reference.Body,
		&reference.SenderDisplayName,
		&reference.CreatedAt,
	)

	return reference, err
}