relay-server/database/messages.go

package database

import (
	"database/sql"
	"fmt"
	"github.com/google/uuid"
	"relay-server/model"
	"relay-server/utils"
)

func GetMessages(db *sql.DB, userID uuid.UUID, conversationID uuid.UUID, limit int, cursor int) ([]*model.Message, error) {
	_, err := checkMembership(db, userID, conversationID)
	if err != nil {
		return []*model.Message{}, err
	}
	var query string
	var messages []*model.Message

	if cursor != 0 {
		query = `
        SELECT 
            m.message_id, 
            m.content AS message, 
            m.sent_at, 
            m.attachment_urls, 
            a.username AS sender
        FROM Messages m
        JOIN Accounts a ON m.user_id = a.user_id
        WHERE m.conversation_id = $1
        AND m.message_id < $2
        ORDER BY m.message_id DESC
        LIMIT $3;
      `
		rows, err := db.Query(query, conversationID, cursor, limit)
		if err != nil {
			return []*model.Message{}, utils.NewError(utils.ErrInternal, "Failed to get messages", fmt.Errorf("failed to get messages: %w", err))
		}
		defer rows.Close()

		for rows.Next() {
			message := &model.Message{}
			err = rows.Scan(&message.ID, &message.Message, &message.SentAt, &message.AttachmentUrl, &message.Sender)
			if err != nil {
				return []*model.Message{}, utils.NewError(utils.ErrInternal, "Failed to get messages", fmt.Errorf("failed to scan message: %w", err))
			}
			messages = append(messages, message)
		}
		if err = rows.Err(); err != nil {
			return []*model.Message{}, utils.NewError(utils.ErrInternal, "internal server error", fmt.Errorf("failed to process messages: %w", err))
		}

	} else {
		query = `
        SELECT 
            m.message_id, 
            m.content AS message, 
            m.sent_at, 
            m.attachment_urls, 
            a.username AS sender
        FROM Messages m
        JOIN Accounts a ON m.user_id = a.user_id
        WHERE m.conversation_id = $1
        ORDER BY m.message_id DESC
        LIMIT $2;
      `
		rows, err := db.Query(query, conversationID, cursor, limit)
		if err != nil {
			return []*model.Message{}, utils.NewError(utils.ErrInternal, "Failed to get messages", fmt.Errorf("failed to get messages: %w", err))
		}
		defer rows.Close()

		for rows.Next() {
			message := &model.Message{}
			err = rows.Scan(&message.ID, &message.Message, &message.SentAt, &message.AttachmentUrl, &message.Sender)
			if err != nil {
				return []*model.Message{}, utils.NewError(utils.ErrInternal, "Failed to get messages", fmt.Errorf("failed to scan message: %w", err))
			}
			messages = append(messages, message)
		}
		if err = rows.Err(); err != nil {
			return []*model.Message{}, utils.NewError(utils.ErrInternal, "internal server error", fmt.Errorf("failed to process messages: %w", err))
		}
	}
	if cursor != 0 {
		// Reverse first messages
		for i, j := 0, len(messages)-1; i < j; i, j = i+1, j-1 {
			messages[i], messages[j] = messages[j], messages[i]
		}
	}
	return messages, nil
}

func checkMembership(db *sql.DB, userID uuid.UUID, conversationID uuid.UUID) (bool, error) {
	query := `
    SELECT EXISTS (
      SELECT 1 
      FROM Memberships 
      WHERE user_id = $1 
      AND conversation_id = $2
    ) AS is_member;
  `
	var isMember bool
	err := db.QueryRow(query, userID, conversationID).Scan(&isMember)
	if err != nil {
		return false, utils.NewError(utils.ErrInternal, "internal server error", fmt.Errorf("failed to check membership: %w", err))
	}
	if !isMember {
		return false, utils.NewError(utils.ErrForbidden, "You are member of the conversation", nil)
	}
	return isMember, nil
}

func DeleteMessage(db *sql.DB, userID uuid.UUID, conversationID uuid.UUID, messageID int) error {
	checkMessageOwnershipQuery := `
    SELECT user_id FROM Messages WHERE message_id = $1;
  `
	deleteMessageQuery := `
    DELETE FROM Messages WHERE message_id = $1;
  `

	var messageOwnerID uuid.UUID
	err := db.QueryRow(checkMessageOwnershipQuery, messageID).Scan(&messageOwnerID)
	if err != nil {
		return utils.NewError(utils.ErrInternal, "Failed to delete message", fmt.Errorf("failed to check message ownership: %w", err))
	}

	var isSelfMessage bool
	if messageOwnerID == userID {
		isSelfMessage = true
	}

	isAdmin, err := IsAdmin(db, userID, conversationID)
	if err != nil {
		return err
	}
	if !isSelfMessage && !isAdmin {
		return utils.NewError(utils.ErrForbidden, "You don't have permissions to delete that message ", nil)
	}

	row, err := db.Exec(deleteMessageQuery, messageID)
	if err != nil {
		return utils.NewError(utils.ErrInternal, "Failed to delete message", fmt.Errorf("failed to delete message: %w", err))
	}
	rowsAffected, err := row.RowsAffected()
	if err != nil {
		return utils.NewError(utils.ErrInternal, "Failed to delete message", fmt.Errorf("failed to get rows affected: %w", err))
	}
	if rowsAffected == 0 {
		return utils.NewError(utils.ErrNotFound, "Message not found", nil)
	}
	return nil

}

func InsertMessage(db *sql.DB, senderID uuid.UUID, conversationID uuid.UUID, message string, attachmentUrls []string) (*model.Message, error) {
	isMember, err := checkMembership(db, senderID, conversationID)
	if err != nil {
		return &model.Message{}, fmt.Errorf("failed to check membership: %w", err)
	}
	if !isMember {
		return &model.Message{}, fmt.Errorf("user is not a member of the conversation")
	}

	query := `
  	  INSERT INTO Messages (conversation_id, user_id, content, attachment_urls)
  	  VALUES ($1, $2, $3, $4)
  	  RETURNING message_id, content AS message, sent_at, attachment_urls, user_id AS sender_id, conversation_id;
  	`

	var msg model.Message

	err = db.QueryRow(query, conversationID, senderID, message, attachmentUrls).Scan(
		&msg.ID,
		&msg.Message,
		&msg.SentAt,
		&msg.AttachmentUrl,
		&msg.SenderID,
		&msg.ConversationID,
	)
	if err != nil {
		return &model.Message{}, fmt.Errorf("failed to insert message: %w", err)
	}
	return &msg, nil
}