Notification System Logic

public async Task RaiseEventAsync(string eventCode, string entityType, long entityId, object data)
{
    await _notificationService.ProcessEventAsync(new NotificationEvent
    {
        EventCode = eventCode,          // e.g., "PR_APPROVED"
        EntityType = entityType,        // e.g., "PR"
        EntityId = entityId,            // e.g., 12345
        EventData = data,               // Additional context data
        OccurredAt = DateTime.UtcNow
    });
}

SELECT nt.*, t.template_code, t.subject, t.body_html, t.variables
FROM config.notification_triggers nt
JOIN config.notification_templates t ON nt.template_id = t.id
WHERE nt.event_code = 'PR_APPROVED'
  AND nt.module_type = 'PR'
  AND nt.is_enabled = true
  AND t.is_active = true;

private async Task<List<User>> ResolveRecipientsAsync(string recipientType, NotificationEvent evt)
{
    return recipientType switch
    {
        "CREATOR" => await GetEntityCreator(evt.EntityType, evt.EntityId),
        "APPROVER" => await GetNextApprover(evt.EntityType, evt.EntityId),
        "ROLE" => await GetUsersByRole(evt.RoleId),
        "ALL_APPROVERS" => await GetAllPendingApprovers(evt.EntityType, evt.EntityId),
        "VENDOR" => await GetVendorContacts(evt.VendorId),
        _ => new List<User>()
    };
}

SELECT preference_value
FROM config.user_preferences
WHERE user_id = @userId
  AND preference_key = 'email_pr_approved';

-- Default to 'true' if no preference is set
-- Only skip if explicitly set to 'false'

private string RenderTemplate(string template, Dictionary<string, string> variables)
{
    foreach (var kvp in variables)
    {
        template = template.Replace("{{" + kvp.Key + "}}", kvp.Value);
    }
    return template;
}

// Variables example:
// {{pr_number}} -> "PR-2026-00123"
// {{approver_name}} -> "John Smith"
// {{amount}} -> "$15,000.00"

INSERT INTO config.notifications
    (user_id, title, message, notification_type, entity_type, entity_id, is_read, created_at)
VALUES
    (@userId, 'PR Approved', 'Your PR #PR-2026-00123 has been approved',
     'APPROVAL', 'PR', 12345, false, NOW());

// Send email
var result = await _emailService.SendAsync(recipient.Email, subject, bodyHtml);

// Log result
INSERT INTO config.email_logs
    (template_id, to_email, subject, status, error_message, sent_at)
VALUES
    (@templateId, @email, @subject,
     result.Success ? 'SENT' : 'FAILED',
     result.ErrorMessage, NOW());