[FEATURE]: Automate competition notification

Does a duplicate issue exist?

[X] I have searched the existing issues

🤔 Is feature request related to a problem? Please describe the problem.

No response

📝 Feature Request Description

We will configure the first type of notification sent to users on the platform. Refers to a notification that is sent to the participants of the competition when they win a prize.

Type of notification:

[ ] Push mobile, tablet, or desktop;
[ ] General notification (shown in the notification center with the corresponding alert on the notification icon);

Since this is the first implementation, we need to add the server configuration that allows Firebase Cloud messaging to send messages.

1. Firebase Cloud Messaging (FCM) Configuration

Ensure that Firebase Cloud Messaging (FCM) is configured on the server. This involves:

Adding the Firebase SDK and setting up credentials in your C# backend. Configuring FCM to allow the backend to send messages via the Firebase API.

2. Fetching the Message from Hasura

To retrieve the message and its translations:

Query the message_template table for the message with id = 1. Query the translation table for the corresponding translations based on the user's language preference.

3. Sending Notifications

Create a method in the backend to send notifications:

public async Task SendCompetitionWinNotification(string userId, decimal prizeAmount, string competitionTitle, string userLanguage)
{
    // Fetch the message template and translations
    var messageTemplate = await FetchMessageTemplate(1); // Replace 1 with the correct ID
    var messageTranslation = await FetchMessageTranslation(1, userLanguage); // Fetch based on user language

    // Replace placeholders with actual values
    string messageTitle = messageTranslation.Title;
    string messageBody = messageTranslation.Body
        .Replace("{BTA}", prizeAmount.ToString())
        .Replace("{competition_title}", competitionTitle);

    // Construct the notification payload
    var messagePayload = new
    {
        to = "/topics/" + userId, // assuming topics are used for targeting
        notification = new
        {
            title = messageTitle,
            body = messageBody
        },
        data = new
        {
            type = "competition_win",
            competitionTitle,
            prizeAmount
        }
    };

    // Send the notification via Firebase
    await SendMessageToFirebase(messagePayload);

    // Log the message in the notifications table
    LogNotification(userId, messageBody);
}

4. Log Notification to Database

Ensure every message sent is logged:

public void LogNotification(string userId, string message)
{
    using (var connection = new NpgsqlConnection(connectionString))
    {
        connection.Open();
        var command = new NpgsqlCommand(
            "INSERT INTO notifications.message (type, media, uid, message, date) VALUES (@type, @media, @uid, @message, @date)",
            connection);
        command.Parameters.AddWithValue("@type", "competition_win");
        command.Parameters.AddWithValue("@media", JsonConvert.SerializeObject(new { media_id = 2 })); // Assuming media ID 2 for push
        command.Parameters.AddWithValue("@uid", userId);
        command.Parameters.AddWithValue("@message", message);
        command.Parameters.AddWithValue("@date", DateTime.UtcNow);

        command.ExecuteNonQuery();
    }
}

Table where the messages are logged:

betarena_prod notifications message

5. Subscription Management

For the initial subscription setup:

public void InitializeUserSubscriptions()
{
    using (var connection = new NpgsqlConnection(connectionString))
    {
        connection.Open();
        var command = new NpgsqlCommand(
            "UPDATE notifications.message_template SET subscribers = array_append(subscribers, json_build_object('id', user_id, 'media_ids', '{1,2,3}')) FROM users WHERE NOT EXISTS (SELECT 1 FROM json_array_elements(subscribers) WHERE value->>'id' = users.user_id)",
            connection);

        command.ExecuteNonQuery();
    }
}

For new users, ensure that the default subscription is active:

public void AddNewUserSubscription(string userId)
{
    using (var connection = new NpgsqlConnection(connectionString))
    {
        connection.Open();
        var command = new NpgsqlCommand(
            "UPDATE notifications.message_template SET subscribers = array_append(subscribers, json_build_object('id', @userId, 'media_ids', '{1,2,3}')) WHERE id = 1",
            connection);
        command.Parameters.AddWithValue("@userId", userId);

        command.ExecuteNonQuery();
    }
}

6. Trigger Update for Competition Winners

Ensure the trigger for calculating competition winners includes sending notifications:

CREATE OR REPLACE FUNCTION notify_winners() RETURNS TRIGGER AS $$
BEGIN
    PERFORM SendCompetitionWinNotification(NEW.user_id, NEW.prize_amount, NEW.competition_title, NEW.user_language);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER winner_notification
AFTER INSERT ON competition_winners
FOR EACH ROW EXECUTE FUNCTION notify_winners();

7. Unsubscribe Handling

When a user unsubscribed:

public void UnsubscribeUser(string userId, int[] mediaIds)
{
    using (var connection = new NpgsqlConnection(connectionString))
    {
        connection.Open();
        var command = new NpgsqlCommand(
            "UPDATE notifications.message_template SET subscribers = jsonb_set(subscribers, '{id}', jsonb_build_object('id', @userId, 'media_ids', @mediaIds)) WHERE id = 1",
            connection);
        command.Parameters.AddWithValue("@userId", userId);
        command.Parameters.AddWithValue("@mediaIds", JsonConvert.SerializeObject(mediaIds));

        command.ExecuteNonQuery();
    }
}

ACCEPTANCE CRITERIA:

[ ] Firebase Server SDK working;
[ ] All users added to the subscription for the competition victory message template;
[ ] New users automatically added to the subscribers' list in the specific media;
[ ] Message being sent in the correct language;
[ ] Message being sent when calculating winners trigger;

➕ Further context and resources (cummulative)

No response

Betarena / boilerplate