Alerts Worker.

9 files changed, 336 insertions, 8 deletions

diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Alerts/AlertsWorker.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Alerts/AlertsWorker.cs
new file mode 100644
index 000000000..c9ad1263a
--- /dev/null
+++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Alerts/AlertsWorker.cs
@@ -0,0 +1,131 @@
+using System.Data;
+using Tango.Portal.Chat.Web.Models;
+using Tango.Portal.Chat.Web.Services;
+
+namespace Tango.Portal.Chat.Web.Alerts
+{
+    public sealed class AlertsWorker : BackgroundService
+    {
+        private readonly ILogger<AlertsWorker> _logger;
+        private readonly IServiceScopeFactory _serviceScopeFactory;
+
+        public AlertsWorker(ILogger<AlertsWorker> logger, IServiceScopeFactory serviceScopeFactory)
+        {
+            _logger = logger;
+            _serviceScopeFactory = serviceScopeFactory;
+        }
+
+        protected override async Task ExecuteAsync(CancellationToken stoppingToken)
+        {
+            _logger.LogInformation("AlertsWorker service started");
+
+            // Wait for other services to initialize
+            await Task.Delay(TimeSpan.FromSeconds(10), stoppingToken);
+
+            while (!stoppingToken.IsCancellationRequested)
+            {
+                try
+                {
+                    await ProcessAlertsAsync(stoppingToken);
+                }
+                catch (Exception ex)
+                {
+                    _logger.LogError(ex, "Error occurred during alerts processing");
+                }
+
+                // Wait for 1 minute before next cycle
+                await Task.Delay(TimeSpan.FromMinutes(1), stoppingToken);
+            }
+
+            _logger.LogInformation("AlertsWorker service stopped");
+        }
+
+        private async Task ProcessAlertsAsync(CancellationToken cancellationToken)
+        {
+            using var scope = _serviceScopeFactory.CreateScope();
+            var alertsQueryService = scope.ServiceProvider.GetRequiredService<AlertsQueryService>();
+            var kustoQueryService = scope.ServiceProvider.GetRequiredService<KustoQueryService>();
+            var n8nService = scope.ServiceProvider.GetRequiredService<N8NService>();
+
+            try
+            {
+                // Ensure the AlertsQueries table exists
+                await alertsQueryService.EnsureTableExistsAsync(cancellationToken);
+
+                // Get enabled queries that are ready to execute
+                var queries = await alertsQueryService.GetEnabledQueriesAsync(cancellationToken);
+
+                _logger.LogInformation("Found {Count} queries ready for execution", queries.Count);
+
+                foreach (var query in queries)
+                {
+                    await ProcessSingleQueryAsync(query, alertsQueryService, kustoQueryService, n8nService, cancellationToken);
+                }
+            }
+            catch (Exception ex)
+            {
+                _logger.LogError(ex, "Error occurred while processing alerts");
+            }
+        }
+
+        private async Task ProcessSingleQueryAsync(
+            AlertsQuery query,
+            AlertsQueryService alertsQueryService,
+            KustoQueryService kustoQueryService,
+            N8NService n8nService,
+            CancellationToken cancellationToken)
+        {
+            try
+            {
+                _logger.LogInformation("Processing query: {Name}", query.Name);
+
+                // Execute the Kusto query
+                var dataTable = await kustoQueryService.QueryAsync(query.Query, new Dictionary<string, string>(), cancellationToken);
+
+                // Convert DataTable to array of objects
+                var rows = ConvertDataTableToObjectArray(dataTable);
+
+                _logger.LogInformation("Query {Name} returned {RowCount} rows", query.Name, rows.Length);
+
+                // Post to n8n
+                var success = await n8nService.PostAlertDataAsync(query.Name, query.Type, rows, cancellationToken);
+
+                if (success)
+                {
+                    // Update the execution time for next run
+                    await alertsQueryService.UpdateExecutionTimeAsync(query, cancellationToken);
+                    _logger.LogInformation("Successfully processed and updated query: {Name}", query.Name);
+                }
+                else
+                {
+                    _logger.LogWarning("Failed to post data to n8n for query: {Name}", query.Name);
+                }
+            }
+            catch (Exception ex)
+            {
+                _logger.LogError(ex, "Error processing query: {Name}", query.Name);
+            }
+        }
+
+        private static object[] ConvertDataTableToObjectArray(DataTable dataTable)
+        {
+            var rows = new object[dataTable.Rows.Count];
+
+            for (int i = 0; i < dataTable.Rows.Count; i++)
+            {
+                var row = dataTable.Rows[i];
+                var rowData = new Dictionary<string, object?>();
+
+                foreach (DataColumn column in dataTable.Columns)
+                {
+                    var value = row[column];
+                    rowData[column.ColumnName] = value == DBNull.Value ? null : value;
+                }
+
+                rows[i] = rowData;
+            }
+
+            return rows;
+        }
+    }
+}
\ No newline at end of file
diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Alerts/AzureAlertsHandler.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Alerts/AzureAlertsHandler.cs
new file mode 100644
index 000000000..82d079bb5
--- /dev/null
+++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Alerts/AzureAlertsHandler.cs
@@ -0,0 +1,29 @@
+namespace Tango.Portal.Chat.Web.Alerts
+{
+    public class AzureAlertsHandler
+    {
+        private const string SecretHeader = "X-Alerts-Secret";
+        private readonly string? _secret;
+
+        public AzureAlertsHandler(String? secret)
+        {
+            _secret = secret;
+        }
+
+        public async Task<IResult> HandleAlert(HttpRequest req)
+        {
+            if (string.IsNullOrEmpty(_secret))
+                return Results.StatusCode(StatusCodes.Status500InternalServerError);
+
+            if (!req.Headers.TryGetValue(SecretHeader, out var provided) || provided.Count == 0 || provided[0] != _secret)
+                return Results.Unauthorized();
+
+            using var reader = new StreamReader(req.Body);
+            var body = await reader.ReadToEndAsync();
+
+            // TODO: we’ll validate auth + forward to n8n in next steps
+            Console.WriteLine($"Alert received: {body.Length} bytes");
+            return Results.Ok(new { ok = true });
+        }
+    }
+}
diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/AlertsQuery.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/AlertsQuery.cs
new file mode 100644
index 000000000..9ab787104
--- /dev/null
+++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/AlertsQuery.cs
@@ -0,0 +1,20 @@
+using Azure;
+using Azure.Data.Tables;
+
+namespace Tango.Portal.Chat.Web.Models
+{
+    public sealed class AlertsQuery : ITableEntity
+    {
+        public string PartitionKey { get; set; } = "Queries";
+        public string RowKey { get; set; } = string.Empty;
+        public DateTimeOffset? Timestamp { get; set; }
+        public ETag ETag { get; set; }
+        
+        public string Name { get; set; } = string.Empty;
+        public string Type { get; set; } = string.Empty;
+        public string Query { get; set; } = string.Empty;
+        public int IntervalMinutes { get; set; }
+        public bool Enable { get; set; }
+        public DateTime ExecutesOn { get; set; }
+    }
+}
\ No newline at end of file
diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/Options.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/Options.cs
index 9ae05da3d..556bc59ba 100644
--- a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/Options.cs
+++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/Options.cs
@@ -40,4 +40,11 @@ namespace Tango.Portal.Chat.Web.Services
     {
         public string ConnectionString { get; set; } = string.Empty;
     }
+
+    public sealed class N8NOptions
+    {
+        public string URL { get; set; } = string.Empty;
+        public string User { get; set; } = string.Empty;
+        public string Password { get; set; } = string.Empty;
+    }
 }
diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Program.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Program.cs
index 380a9607b..dd063b2f6 100644
--- a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Program.cs
+++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Program.cs
@@ -1,4 +1,5 @@
 using Azure.Identity;
+using Tango.Portal.Chat.Web.Alerts;
 using Tango.Portal.Chat.Web.Services;
 
 var builder = WebApplication.CreateBuilder(args);
@@ -19,7 +20,22 @@ builder.Services.AddSingleton<KqlGuard>();
 // Azure Storage config
 builder.Services.Configure<AzureStorageOptions>(builder.Configuration.GetSection("AzureStorage"));
 builder.Services.AddSingleton<AIInstructionService>();
-builder.Services.AddSession();
+builder.Services.AddSingleton<AlertsQueryService>();
+
+// N8N config
+builder.Services.Configure<N8NOptions>(builder.Configuration.GetSection("N8N"));
+builder.Services.AddHttpClient<N8NService>();
+
+// Background service for alerts
+builder.Services.AddHostedService<AlertsWorker>();
+
+// Session configuration with 1 hour timeout
+builder.Services.AddSession(options =>
+{
+    options.IdleTimeout = TimeSpan.FromHours(1);
+    options.Cookie.HttpOnly = true;
+    options.Cookie.IsEssential = true;
+});
 
 // Simple HTTP client for LLM
 builder.Services.AddHttpClient<LlmClient>();
@@ -30,6 +46,13 @@ app.UseStaticFiles();
 app.UseRouting();
 app.UseSession();
 
+var AazureAlertsHandler = new AzureAlertsHandler(builder.Configuration["ALERTS_SHARED_SECRET"]);
+
+app.MapPost("/api/monitor-alert", (HttpRequest req) =>
+{
+    return AazureAlertsHandler.HandleAlert(req);
+});
+
 app.MapControllerRoute(
     name: "default",
     pattern: "{controller=Home}/{action=Index}/{id?}");
diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/AlertsQueryService.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/AlertsQueryService.cs
new file mode 100644
index 000000000..6ecff5be9
--- /dev/null
+++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/AlertsQueryService.cs
@@ -0,0 +1,43 @@
+using Azure.Data.Tables;
+using Microsoft.Extensions.Options;
+using Tango.Portal.Chat.Web.Models;
+
+namespace Tango.Portal.Chat.Web.Services
+{
+    public sealed class AlertsQueryService
+    {
+        private readonly TableClient _tableClient;
+
+        public AlertsQueryService(IOptions<AzureStorageOptions> options)
+        {
+            var tableServiceClient = new TableServiceClient(options.Value.ConnectionString);
+            _tableClient = tableServiceClient.GetTableClient("AlertsQueries");
+        }
+
+        public async Task EnsureTableExistsAsync(CancellationToken cancellationToken = default)
+        {
+            await _tableClient.CreateIfNotExistsAsync(cancellationToken);
+        }
+
+        public async Task<List<AlertsQuery>> GetEnabledQueriesAsync(CancellationToken cancellationToken = default)
+        {
+            var queries = new List<AlertsQuery>();
+            var now = DateTime.UtcNow;
+
+            await foreach (var entity in _tableClient.QueryAsync<AlertsQuery>(
+                filter: $"PartitionKey eq 'Queries' and Enable eq true and ExecutesOn le datetime'{now:yyyy-MM-ddTHH:mm:ss.fffZ}'",
+                cancellationToken: cancellationToken))
+            {
+                queries.Add(entity);
+            }
+
+            return queries;
+        }
+
+        public async Task UpdateExecutionTimeAsync(AlertsQuery query, CancellationToken cancellationToken = default)
+        {
+            query.ExecutesOn = DateTime.UtcNow.AddMinutes(query.IntervalMinutes);
+            await _tableClient.UpdateEntityAsync(query, query.ETag, cancellationToken: cancellationToken);
+        }
+    }
+}
\ No newline at end of file
diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/N8NService.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/N8NService.cs
new file mode 100644
index 000000000..8b4b2edde
--- /dev/null
+++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/N8NService.cs
@@ -0,0 +1,60 @@
+using Microsoft.Extensions.Options;
+using System.Text;
+using System.Text.Json;
+using Tango.Portal.Chat.Web.Models;
+
+namespace Tango.Portal.Chat.Web.Services
+{
+    public sealed class N8NService
+    {
+        private readonly HttpClient _httpClient;
+        private readonly N8NOptions _options;
+        private readonly ILogger<N8NService> _logger;
+
+        public N8NService(HttpClient httpClient, IOptions<N8NOptions> options, ILogger<N8NService> logger)
+        {
+            _httpClient = httpClient;
+            _options = options.Value;
+            _logger = logger;
+
+            // Set up basic authentication
+            var credentials = Convert.ToBase64String(Encoding.ASCII.GetBytes($"{_options.User}:{_options.Password}"));
+            _httpClient.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Basic", credentials);
+        }
+
+        public async Task<bool> PostAlertDataAsync(string name, string type, object[] rows, CancellationToken cancellationToken = default)
+        {
+            try
+            {
+                var payload = new
+                {
+                    Name = name,
+                    Type = type,
+                    Rows = rows
+                };
+
+                var json = JsonSerializer.Serialize(payload);
+                var content = new StringContent(json, Encoding.UTF8, "application/json");
+
+                var response = await _httpClient.PostAsync(_options.URL, content, cancellationToken);
+
+                if (response.IsSuccessStatusCode)
+                {
+                    _logger.LogInformation("Successfully posted alert data for {Name} to n8n", name);
+                    return true;
+                }
+                else
+                {
+                    _logger.LogWarning("Failed to post alert data for {Name} to n8n. Status: {StatusCode}, Response: {Response}", 
+                        name, response.StatusCode, await response.Content.ReadAsStringAsync(cancellationToken));
+                    return false;
+                }
+            }
+            catch (Exception ex)
+            {
+                _logger.LogError(ex, "Exception occurred while posting alert data for {Name} to n8n", name);
+                return false;
+            }
+        }
+    }
+}
\ No newline at end of file
diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/appsettings.Development.json b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/appsettings.Development.json
index b5e4ef0fd..23d154520 100644
--- a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/appsettings.Development.json
+++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/appsettings.Development.json
@@ -18,5 +18,14 @@
     "TenantId": "2ebd63a5-bc2f-41dc-9066-4409ed5e5dd4",
     "ClientId": "ec612854-7abc-457b-808a-5d0c5ba80c57",
     "ClientSecret": "C6n8Q~-NgsAQ6yYJwoNABkcVUNSm2~8-8xNgaa32"
-  }
+  },
+  "AzureStorage": {
+    "ConnectionString": "DefaultEndpointsProtocol=https;AccountName=tangostorage;AccountKey=S4z/D+Yg6mwMis+bs/VpcDLA9yE1iZaYq23shQlRIi2KmM9E7JY8zdZjeAPOPdG3gONHoNDEpsgH6D4cqQ/bsA==;EndpointSuffix=core.windows.net"
+  },
+  "N8N": {
+    "URL": "https://n8n.srv995254.hstgr.cloud/webhook-test/twine",
+    "User": "twine",
+    "Password": "11qq22ww33ee"
+  },
+  "ALERTS_SHARED_SECRET": "SECRET_KEY"
 } 
\ No newline at end of file
diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/appsettings.json b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/appsettings.json
index 260d4b834..b906f2bfd 100644
--- a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/appsettings.json
+++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/appsettings.json
@@ -1,10 +1,4 @@
 {
-  "Logging": {
-    "LogLevel": {
-      "Default": "Information",
-      "Microsoft.AspNetCore": "Warning"
-    }
-  },
   "OpenAI": {
     "Provider": "OpenAI",
     "Endpoint": "https://api.openai.com/v1/chat/completions",
@@ -28,5 +22,17 @@
   "AzureStorage": {
     "ConnectionString": "DefaultEndpointsProtocol=https;AccountName=tangostorage;AccountKey=S4z/D+Yg6mwMis+bs/VpcDLA9yE1iZaYq23shQlRIi2KmM9E7JY8zdZjeAPOPdG3gONHoNDEpsgH6D4cqQ/bsA==;EndpointSuffix=core.windows.net"
   },
+  "N8N": {
+    "URL": "https://n8n.srv995254.hstgr.cloud/webhook/twine",
+    "User": "twine",
+    "Password": "11qq22ww33ee"
+  },
+  "ALERTS_SHARED_SECRET": "SECRET_KEY",
+  "Logging": {
+    "LogLevel": {
+      "Default": "Information",
+      "Microsoft.AspNetCore": "Warning"
+    }
+  },
   "AllowedHosts": "*"
 }
\ No newline at end of file