From dc0d050ad35973e1ae09e3c7e47bc1fb13eedcd2 Mon Sep 17 00:00:00 2001 From: Roy Ben Shabat Date: Tue, 2 Sep 2025 20:36:48 +0300 Subject: Portal AI --- .../Controllers/ChatController.cs | 127 ++++++ .../Controllers/HomeController.cs | 9 + .../Tango.Portal.Chat.Web/Data/schema.json | 464 +++++++++++++++++++++ .../Tango.Portal.Chat.Web/Models/Contracts.cs | 53 +++ .../Models/FlexibleStringListConverter.cs | 46 ++ .../Tango.Portal.Chat.Web/Models/Options.cs | 24 ++ .../Tango.Portal.Chat.Web/Program.cs | 30 ++ .../Tango.Portal.Chat.Web/Services/KqlGuard.cs | 45 ++ .../Services/KustoQueryService.cs | 54 +++ .../Tango.Portal.Chat.Web/Services/LlmClient.cs | 460 ++++++++++++++++++++ .../Services/SchemaRegistry.cs | 32 ++ .../Tango.Portal.Chat.Web.csproj | 11 + .../Tango.Portal.Chat.Web/Views/Home/Index.cshtml | 234 +++++++++++ .../Views/Shared/_Layout.cshtml | 14 + .../appsettings.Development.json | 18 + .../Tango.Portal.Chat.Web/appsettings.json | 20 + .../Tango.Portal.Chat.Web/wwwroot/site.css | 240 +++++++++++ Software/Visual_Studio_22/Tango.sln | 25 ++ 18 files changed, 1906 insertions(+) create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Controllers/ChatController.cs create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Controllers/HomeController.cs create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/schema.json create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/Contracts.cs create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/FlexibleStringListConverter.cs create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/Options.cs create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Program.cs create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/KqlGuard.cs create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/KustoQueryService.cs create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/LlmClient.cs create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/SchemaRegistry.cs create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Tango.Portal.Chat.Web.csproj create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Views/Home/Index.cshtml create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/Views/Shared/_Layout.cshtml create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/appsettings.Development.json create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/appsettings.json create mode 100644 Software/Visual_Studio_22/Tango.Portal.Chat.Web/wwwroot/site.css create mode 100644 Software/Visual_Studio_22/Tango.sln diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Controllers/ChatController.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Controllers/ChatController.cs new file mode 100644 index 000000000..35b170347 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Controllers/ChatController.cs @@ -0,0 +1,127 @@ +using System.Data; +using System.Text.Json; +using ChatADX.Web.Models; +using ChatADX.Web.Services; +using Kusto.Data.Data; +using Microsoft.AspNetCore.Mvc; + +namespace ChatADX.Web.Controllers +{ + [ApiController] + [Route("api/[controller]")] + public sealed class ChatController : ControllerBase + { + private readonly SchemaRegistry _schema; + private readonly KqlGuard _guard; + private readonly KustoQueryService _adx; + private readonly LlmClient _llm; + private static readonly string[] AllowTables = new[] { "JobRunsTable", "JobStatusTable", "TelemetryTable", "MachinesTable" }; + + public ChatController(SchemaRegistry schema, KqlGuard guard, KustoQueryService adx, LlmClient llm) + { + _schema = schema; + _guard = guard; + _adx = adx; + _llm = llm; + } + + [HttpPost("ask")] + public async Task> Ask([FromBody] ChatRequest req, CancellationToken ct) + { + try + { + var schemaJson = _schema.GetSchemaJson(); + + // 1) Ask the model for KQL + var plan = await _llm.ProposeKqlAsync(req.Question, schemaJson, req.History, ct); + + if (plan.Assistant == "data") + { + // 2) Guardrail validation + var val = _guard.Validate(plan.Kql); + if (!val.IsOk) return BadRequest(new { error = "Invalid KQL", details = val.Error, plan }); + + // 4) Execute in ADX + DataTable table; + try + { + table = await _adx.QueryAsync(plan.Kql, plan.Parameters, ct); + } + catch (Exception ex) + { + // Return error to the client so they can iterate + return new ChatResponse + { + Answer = $"Seems like my kusto query ran into some issue..\n{ex.Message}", + ThreadId = req.ThreadId, + UsedKql = plan.Kql + }; + } + + // 5) Build compact facts (limit rows/cols) + var preview = ToPreview(table, 200); + var facts = JsonSerializer.Serialize(preview); + + // 6) Ask model for final answer + //var answer = await _llm.AnswerFromFactsAsync(req.Question, facts, plan.Kql, ct); + + var run = await _llm.AnswerWithAssistantAsync( + LlmClient.AssistantType.Data, + req.Question, + facts, + plan.Kql, + req.ThreadId, // <-- reuse if provided + ct); + + return new ChatResponse + { + Answer = run.Answer, + UsedKql = plan.Kql, + Preview = preview, + ThreadId = run.ThreadId // <-- echo back the thread id used/created + }; + } + else + { + // AFTER + var run = await _llm.AnswerWithAssistantAsync( + LlmClient.AssistantType.Docs, + req.Question, + string.Empty, + plan.Kql, + req.ThreadId, // <-- reuse if provided + ct); + + return new ChatResponse + { + Answer = run.Answer, + ThreadId = run.ThreadId + }; + } + } + catch (Exception ex) + { + return new ChatResponse + { + Answer = $"Ooops something went wrong...\n{ex.Message}", + ThreadId = req.ThreadId + }; + } + } + + private static object ToPreview(DataTable dt, int maxRows) + { + var cols = dt.Columns.Cast().Select(c => c.ColumnName).ToArray(); + var rows = new List>(); + int count = 0; + foreach (DataRow r in dt.Rows) + { + if (count++ >= maxRows) break; + var d = new Dictionary(); + foreach (var c in cols) d[c] = r[c]; + rows.Add(d); + } + return new { columns = cols, rows }; + } + } +} diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Controllers/HomeController.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Controllers/HomeController.cs new file mode 100644 index 000000000..8b13afe10 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Controllers/HomeController.cs @@ -0,0 +1,9 @@ +using Microsoft.AspNetCore.Mvc; + +namespace ChatADX.Web.Controllers +{ + public sealed class HomeController : Controller + { + public IActionResult Index() => View(); + } +} diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/schema.json b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/schema.json new file mode 100644 index 000000000..41da6e63a --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/schema.json @@ -0,0 +1,464 @@ +{ + "tables": { + "JobRunsTable": { + "columns": { + "ID": "string", + "SerialNumber": "string", + "MachineType": "string", + "Environment": "string", + "Organization": "string", + "Site": "string", + "CreatedTime": "datetime", + "UploadTime": "datetime", + "JobName": "string", + "Kind": "string", + "Thread": "string", + "NumberOfUnits": "int", + "InterSegmentLength": "int", + "LubricationEnabled": "bool", + "SpoolTypeDistribution": "int", + "NumberOfSpools": "int", + "LogicalLength": "real", + "ActualLength": "real", + "TotalLength": "real", + "StartPosition": "real", + "EndPosition": "real", + "Distance": "real", + "StartTime": "datetime", + "EndTime": "datetime", + "Duration": "timespan", + "HeatingDuration": "timespan", + "Status": "string", + "OutputCyan": "long", + "OutputMagenta": "long", + "OutputYellow": "long", + "OutputBlack": "long", + "OutputLightCyan": "long", + "OutputLightMagenta": "long", + "OutputLightYellow": "long", + "OutputBlue": "long", + "OutputLightBlue": "long", + "OutputOrange": "long", + "OutputLightOrange": "long", + "OutputRubine": "long", + "OutputLightRubine": "long", + "OutputNavy": "long", + "OutputViolet": "long", + "OutputTransparent": "long", + "OutputLubricant": "long", + "FailureReason": "string", + "ApplicationVersion": "string", + "FirmwareVersion": "string", + "Segments": "dynamic", + "FineTuning": "dynamic", + "ProcessParameters": "dynamic" + }, + + "primaryTimeColumn": "StartTime", + "fallbackTimeColumns": [ "CreatedTime", "EndTime" ], + "defaultTimeWindowDays": 30, + + "keys": { + "primaryKey": [ "ID" ], + "machineKey": [ "SerialNumber" ], + "orgKeys": [ "Organization", "Site" ] + }, + + "enums": { + "Status": [ "Completed", "Failed", "Aborted" ], + "Kind": [ "Default", "FineTuning" ], + "Environment": [ "DEV", "TEST", "BETA", "PROD" ] + }, + + "units": { + "Distance": "meters", + "LogicalLength": "meters", + "ActualLength": "meters", + "TotalLength": "meters", + "StartPosition": "meters", + "EndPosition": "meters", + "Duration": "timespan", + "HeatingDuration": "timespan", + "Output*": "nanoliters" + }, + + "display": { + "rename": { + "Thread": "ThreadType" + }, + "preferPercentBreakdowns": [ "Status" ] + }, + + "joinHints": [ + { + "table": "MachinesTable", + "on": [ "SerialNumber" ], + "why": "Bring Organization/Site or versions from machine master data." + } + ], + + "notes": [ + "Each row is a single job run.", + "Use StartTime for most time filters. if not found use CreatedTime", + "Thread is the thread profile/type used for the job.", + "Ink outputs (Output*) are in nanoliters.", + "Distance = EndPosition - StartPosition.", + "FineTuning is only populated when Kind == 'FineTuning'.", + "FailureReason is only meaningful when Status == 'Failed'." + ], + + "guardrails": { + "requireTimeFilter": false, + "maxRowsSuggested": 200, + "encourageSummarizeOrTop": true + }, + + "fewShot": [ + { + "user": "What are the most used thread types by jobs in the last 7 days?", + "kql": "JobRunsTable\n| where EndTime between (from .. to)\n| summarize Jobs=count() by Thread\n| top 5 by Jobs desc" + }, + { + "user": "Failure rate per site last 30 days", + "kql": "JobRunsTable\n| where EndTime between (from .. to)\n| summarize Total=count(), Failed=countif(Status == 'Failed') by Site\n| extend FailureRate = 100.0 * todouble(Failed) / todouble(Total)\n| order by FailureRate desc" + }, + { + "user": "Total ink usage by color for Organization X this week", + "kql": "JobRunsTable\n| where EndTime between (from .. to) and Organization == 'Twine'\n| summarize Cyan=sum(OutputCyan), Magenta=sum(OutputMagenta), Yellow=sum(OutputYellow), Black=sum(OutputBlack), Transparent=sum(OutputTransparent), Lubricant=sum(OutputLubricant)" + }, + { + "user": "Average job duration by machine type yesterday", + "kql": "JobRunsTable\n| where EndTime between (from .. to)\n| summarize AvgDuration=avg(Duration) by MachineType\n| order by AvgDuration desc" + }, + { + "user": "Top failure reasons in the past 14 days", + "kql": "JobRunsTable\n| where EndTime between (from .. to) and Status == 'Failed' and isnotempty(FailureReason)\n| summarize Failures=count() by FailureReason\n| top 10 by Failures desc" + } + ] + }, + "JobStatusTable": { + "columns": { + "SerialNumber": "string", + "CreatedTime": "datetime", + "State": "string", + "OverallTemperature": "real", + "Status": "dynamic" + }, + "notes": [ + "High-volume time series of machine status snapshots. Short retention." + ] + }, + "MachinesTable": { + "columns": { + "ID": "int", + "GUID": "string", + "LAST_UPDATED": "datetime", + "SERIAL_NUMBER": "string", + "NAME": "string", + "PRODUCTION_DATE": "datetime", + "ORGANIZATION_GUID": "string", + "SITE_GUID": "string", + "MACHINE_VERSION_GUID": "string", + "CONFIGURATION_GUID": "string", + "DEFAULT_RML_GUID": "string", + "LOADED_RML_GUID": "string", + "TARGET_JOB_TYPES": "string", + "TARGET_COLOR_SPACE_CODES": "string", + "DEFAULT_COLOR_SPACE_GUID": "string", + "DEFAULT_SEGMENT_LENGTH": "real", + "DEFAULT_SPOOL_TYPE_GUID": "string", + "OS_KEY": "string", + "AUTO_LOGIN": "bool", + "AUTO_CHECK_FOR_UPDATES": "bool", + "SETUP_ACTIVATION": "bool", + "SETUP_REMOTE_ASSISTANCE": "bool", + "SETUP_UWF": "bool", + "SETUP_FIRMWARE": "bool", + "SETUP_FPGA": "bool", + "IS_DEMO": "bool", + "SUSPEND_VERSION_UPDATE": "bool", + "FORCE_VERSION_UPDATE": "bool", + "PERFORM_SCHEMA_UPDATE_ON_DATA_UPDATE": "bool", + "DEVICE_COM_PORT": "string", + "IS_DEVICE_REGISTERED": "bool", + "DEVICE_ID": "string", + "DEVICE_NAME": "string", + "HEAD_TYPE": "int", + "ACTIVATION_KEY": "string", + "LIGHT_INKS_INSTALLED": "bool", + "BTSR_INSTALLED": "bool", + "VERSION_TAG": "string", + "MACHINE_TYPE": "int", + "ALLOW_SMS_NOTIFICATIONS": "bool", + "ENVIRONMENT": "string" + }, + + "keys": { + "primaryKey": [ "GUID" ], + "machineKey": [ "SERIAL_NUMBER" ] + }, + + "display": { + "rename": { + "SERIAL_NUMBER": "SerialNumber", + "LAST_UPDATED": "LastUpdated", + "MACHINE_TYPE": "MachineType", + "ENVIRONMENT": "Environment" + } + }, + + "joinHints": [ + { + "table": "JobRunsTable", + "on": [ "SerialNumber == MachinesTable.SERIAL_NUMBER" ], + "why": "Enrich job runs with machine metadata (organization/site/version). Note: column casing differs." + }, + { + "table": "OrganizationsTable", + "on": [ "GUID == MachinesTable.ORGANIZATION_GUID" ], + "why": "Get the machine's organization name'." + }, + { + "table": "SitesTable", + "on": [ "GUID == MachinesTable.SITE_GUID" ], + "why": "Get the machine's site name'. If no site than the machine does not belong to any site." + } + ], + + "notes": [ + "Column names here are UPPER_SNAKE_CASE and are case-sensitive in Kusto.", + "Use GUID as the stable machine identifier; SERIAL_NUMBER is the human-facing ID used in JobRunsTable.", + "Map JobRunsTable.SerialNumber to MachinesTable.SERIAL_NUMBER for joins.", + "If duplicate rows arrive, use arg_max(LAST_UPDATED, *) by GUID to keep the latest." + ] + }, + "LogsTable": { + "columns": { + "SerialNumber": "string", + "MachineType": "string", + "Environment": "string", + "Organization": "string", + "Site": "string", + "CreatedTime": "datetime", + "UploadTime": "datetime", + "Source": "string", + "Category": "string", + "Class": "string", + "Method": "string", + "Line": "int", + "Message": "string" + }, + + "primaryTimeColumn": "CreatedTime", + "fallbackTimeColumns": [ "UploadTime" ], + "defaultTimeWindowDays": 7, + + "keys": { + "machineKey": [ "SerialNumber" ] + }, + + "enums": { + "Category": [ "Info", "Warning", "Error", "Critical", "Debug" ], + "Source": [ "Application", "Firmware" ] + }, + + "notes": [ + "Application logs emitted by services/apps running on or about the machines.", + "Use CreatedTime for time filters; UploadTime is the ingestion/upload timestamp.", + "Category is limited to Info, Warning, Error, Critical, Debug.", + "Source can be either Application or Firmware where Firmware represents logs generated by the embedded device and Application is everything else." + ], + + "guardrails": { + "requireTimeFilter": false, + "maxRowsSuggested": 200, + "encourageSummarizeOrTop": false + }, + + "joinHints": [ + { + "table": "JobRunsTable", + "on": [ "SerialNumber" ], + "why": "Correlate logs to jobs on the same machine in a time window." + } + ], + + "display": { + "rename": { + "CreatedTime": "Time" + } + } + }, + "EventTypesTable": { + "columns": { + "ID": "int", + "GUID": "string", + "LAST_UPDATED": "datetime", + "CODE": "int", + "NAME": "string", + "TITLE": "string", + "DESCRIPTION": "string", + "TECHNICAL_DESCRIPTION": "string", + "COMPONENT_INDEX": "int", + "EVENT_CATEGORY": "int", + "EVENT_GROUP": "int", + "EVENT_NOTIFICATION_TIME": "int", + "EVENT_ACTIONS": "string", + "REQUIRES_USER_INTERVENTION": "bool", + "GUIDANCE": "string", + "PERSISTENT": "bool" + }, + + "keys": { + "primaryKey": [ "GUID" ], + "codeKey": [ "CODE" ] + }, + + "display": { + "rename": { + "LAST_UPDATED": "LastUpdated", + "EVENT_CATEGORY": "EventCategory", + "EVENT_GROUP": "EventGroup", + "EVENT_NOTIFICATION_TIME": "NotificationTimeSec", + "REQUIRES_USER_INTERVENTION": "RequiresUserIntervention" + } + }, + + "notes": [ + "Lookup/dimension table describing event types.", + "Join to EventsTable on EventsTable.EventTypeGuid == EventTypesTable.GUID.", + "EVENT_CATEGORY/GROUP are numeric buckets; TITLE/NAME are human-friendly.", + "If duplicate rows arrive, use arg_max(LAST_UPDATED, *) by GUID to keep the latest." + ] + }, + "EventsTable": { + "columns": { + "SerialNumber": "string", + "MachineType": "string", + "Environment": "string", + "Organization": "string", + "Site": "string", + "CreatedTime": "datetime", + "UploadTime": "datetime", + "ID": "string", + "HostName": "string", + "EventTypeGuid": "string", + "Description": "string" + }, + + "primaryTimeColumn": "CreatedTime", + "fallbackTimeColumns": [ "UploadTime" ], + "defaultTimeWindowDays": 7, + + "keys": { + "eventKey": [ "ID" ], + "machineKey": [ "SerialNumber" ] + }, + + "joinHints": [ + { + "table": "EventTypesTable", + "on": [ "EventTypeGuid == EventTypesTable.GUID" ], + "why": "Bring title/category/code/technical description and flags like RequiresUserIntervention/Persistent." + }, + { + "table": "MachinesTable", + "on": [ "SerialNumber == MachinesTable.SERIAL_NUMBER" ], + "why": "Enrich events with machine master data (org/site/version)." + } + ], + + "guardrails": { + "requireTimeFilter": true, + "maxRowsSuggested": 200, + "encourageSummarizeOrTop": true + }, + + "notes": [ + "Each row is a concrete event occurrence on a machine.", + "Use CreatedTime for time range filters; UploadTime indicates ingestion.", + "EventTypeGuid maps to EventTypesTable.GUID for metadata (title, category, etc.)." + ] + }, + "OrganizationsTable": { + "columns": { + "ID": "int", + "GUID": "string", + "LAST_UPDATED": "datetime", + "NAME": "string", + "CONTACT_GUID": "string", + "ADDRESS_GUID": "string", + "ENVIRONMENT": "string" + }, + "keys": { + "primaryKey": [ "GUID" ], + "identityKey": [ "ID" ] + }, + "display": { + "rename": { + "NAME": "Name", + "LAST_UPDATED": "LastUpdated", + "ENVIRONMENT": "Environment" + } + }, + "joinHints": [ + { + "table": "SitesTable", + "on": [ "SitesTable.ORGANIZATION_GUID == OrganizationsTable.GUID" ], + "why": "Attach sites to their parent organization." + }, + { + "table": "MachinesTable", + "on": [ "MachinesTable.ORGANIZATION_GUID == OrganizationsTable.GUID" ], + "why": "Filter or aggregate machines by organization." + } + ], + "notes": [ + "Column names are UPPER_SNAKE_CASE and case-sensitive in Kusto.", + "ENVIRONMENT is populated by ADF at sink time (not present in source SQL).", + "Prefer joining by GUID; ID is a SQL identity surrogate and may differ across environments.", + "If duplicate rows arrive, use arg_max(LAST_UPDATED, *) by GUID to keep the latest." + ] + }, + "SitesTable": { + "columns": { + "ID": "int", + "GUID": "string", + "LAST_UPDATED": "datetime", + "ORGANIZATION_GUID": "string", + "NAME": "string", + "DESCRIPTION": "string", + "ENVIRONMENT": "string" + }, + "keys": { + "primaryKey": [ "GUID" ], + "identityKey": [ "ID" ] + }, + "display": { + "rename": { + "LAST_UPDATED": "LastUpdated", + "ORGANIZATION_GUID": "OrganizationGuid", + "ENVIRONMENT": "Environment" + } + }, + "joinHints": [ + { + "table": "OrganizationsTable", + "on": [ "SitesTable | join kind=inner (OrganizationsTable) on $left.ORGANIZATION_GUID == $right.GUID and $left.ENVIRONMENT == $right.ENVIRONMENT)" ], + "why": "Attach each site to its parent organization." + }, + { + "table": "MachinesTable", + "on": [ "MachinesTable.SITE_GUID == SitesTable.GUID" ], + "why": "List or aggregate machines that belong to a site." + } + ], + "notes": [ + "DESCRIPTION is nullable in SQL; ADX string accepts null/empty.", + "ENVIRONMENT is injected by ADF at sink time (not present in source SQL).", + "Prefer GUID-based joins; ID is a SQL identity and may vary across environments.", + "To de-duplicate mirrored rows, use arg_max(LAST_UPDATED, *) by GUID." + ] + } + } +} \ No newline at end of file diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/Contracts.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/Contracts.cs new file mode 100644 index 000000000..d968d0738 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/Contracts.cs @@ -0,0 +1,53 @@ +using System.Collections.Generic; +using System.Text.Json.Serialization; + +namespace ChatADX.Web.Models +{ + public sealed class ProposeKqlResult + { + [JsonPropertyName("kql")] + public string Kql { get; set; } = string.Empty; + + [JsonPropertyName("parameterTypes")] + public Dictionary? ParameterTypes { get; set; } + + [JsonPropertyName("parameters")] + public Dictionary Parameters { get; set; } = new(); + + // NEW: be flexible about shapes from the model + [JsonPropertyName("assumptions")] + [JsonConverter(typeof(FlexibleStringListConverter))] + public List? Assumptions { get; set; } + + [JsonPropertyName("why")] + public string? Why { get; set; } + + [JsonPropertyName("assistant")] + public string Assistant { get; set; } = string.Empty; + } + + public sealed class ChatMessage + { + // "user" | "assistant" + public string Role { get; set; } = "user"; + public string Content { get; set; } = string.Empty; + } + + public sealed class ChatRequest + { + public List? History { get; set; } + + public string? ThreadId { get; set; } // shared Assistants thread for the whole chat + public string Question { get; set; } = string.Empty; + public string? From { get; set; } + public string? To { get; set; } + } + + public sealed class ChatResponse + { + public string? ThreadId { get; set; } // shared Assistants thread for the whole chat + public string Answer { get; set; } = string.Empty; + public string UsedKql { get; set; } = string.Empty; + public object? Preview { get; set; } + } +} diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/FlexibleStringListConverter.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/FlexibleStringListConverter.cs new file mode 100644 index 000000000..92bfffe36 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/FlexibleStringListConverter.cs @@ -0,0 +1,46 @@ +using System; +using System.Collections.Generic; +using System.Text.Json; +using System.Text.Json.Serialization; + +namespace ChatADX.Web.Models +{ + public sealed class FlexibleStringListConverter : JsonConverter> + { + public override List Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options) + { + if (reader.TokenType == JsonTokenType.Null) + return new List(); + + if (reader.TokenType == JsonTokenType.String) + return new List { reader.GetString() ?? string.Empty }; + + if (reader.TokenType == JsonTokenType.StartArray) + { + var list = new List(); + while (reader.Read()) + { + if (reader.TokenType == JsonTokenType.EndArray) break; + if (reader.TokenType == JsonTokenType.String) + list.Add(reader.GetString() ?? string.Empty); + else + { + using var doc = JsonDocument.ParseValue(ref reader); + list.Add(doc.RootElement.GetRawText()); + } + } + return list; + } + + using var any = JsonDocument.ParseValue(ref reader); + return new List { any.RootElement.GetRawText() }; + } + + public override void Write(Utf8JsonWriter writer, List value, JsonSerializerOptions options) + { + writer.WriteStartArray(); + foreach (var s in value) writer.WriteStringValue(s); + writer.WriteEndArray(); + } + } +} 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 new file mode 100644 index 000000000..f59ffa8d7 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Models/Options.cs @@ -0,0 +1,24 @@ +namespace ChatADX.Web.Services +{ + public sealed class LlmOptions + { + // If using Azure OpenAI, set IsAzure = true and Endpoint = full chat completions URL (with api-version query). + // If using OpenAI, set IsAzure = false and Endpoint = https://api.openai.com/v1/chat/completions + public bool IsAzure { get; set; } = false; + public string Endpoint { get; set; } = string.Empty; + public string ApiKey { get; set; } = string.Empty; + public string Model { get; set; } = "gpt-4o-mini"; // or your Azure deployment name + public double Temperature { get; set; } = 0.2; + public string AnswererAssistantId { get; set; } = string.Empty; // NEW + public string DocsAssistantId { get; set; } = string.Empty; // NEW + } + + public sealed class AdxOptions + { + public string ClusterUri { get; set; } = string.Empty; + public string Database { get; set; } = string.Empty; + public string TenantId { get; set; } = string.Empty; + public string ClientId { get; set; } = string.Empty; + public string ClientSecret { 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 new file mode 100644 index 000000000..046b1fb11 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Program.cs @@ -0,0 +1,30 @@ +using Azure.Identity; +using ChatADX.Web.Services; + +var builder = WebApplication.CreateBuilder(args); + +// Config binding (appsettings.json + env vars) +builder.Services.AddControllersWithViews(); + +// OpenAI/Azure OpenAI config +builder.Services.Configure(builder.Configuration.GetSection("OpenAI")); + +// ADX config +builder.Services.Configure(builder.Configuration.GetSection("ADX")); +builder.Services.AddSingleton(); +builder.Services.AddSingleton(); +builder.Services.AddSingleton(); + +// Simple HTTP client for LLM +builder.Services.AddHttpClient(); + +var app = builder.Build(); + +app.UseStaticFiles(); +app.UseRouting(); + +app.MapControllerRoute( + name: "default", + pattern: "{controller=Home}/{action=Index}/{id?}"); + +app.Run(); diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/KqlGuard.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/KqlGuard.cs new file mode 100644 index 000000000..6ce4e44c9 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/KqlGuard.cs @@ -0,0 +1,45 @@ +using System.Text.RegularExpressions; + +namespace ChatADX.Web.Services +{ + public sealed class KqlGuard + { + private static readonly string[] Banned = new[] { + "externaldata", "evaluate", "cluster(", "database(", "ingest", "print", "datatable", "delete", "drop", "truncate", "update", "set", "declare", "let", "materializedview", "mv-merge", "alter", "create", "append", "ingestiontime()", ".show", ".set", ".clear", ".drop", ".alter" + }; + + public KqlValidationResult Validate(string kql) + { + var text = kql.ToLowerInvariant(); + + foreach (var token in Banned) + if (text.Contains(token)) + return KqlValidationResult.Fail($"Query uses banned token: {token}"); + + // Ensure only allowed tables are referenced (quick heuristic) + //var tableNames = new HashSet(allowTables.Select(t => t.ToLowerInvariant())); + //var tableRefs = Regex.Matches(text, @"\b([A-Za-z_][A-Za-z0-9_]*)\bTable").Cast().Select(m => m.Value.ToLowerInvariant().Replace("table", "")); + + //foreach (var tr in tableRefs) + // if (!tableNames.Contains(tr)) + // return KqlValidationResult.Fail($"Query references non-allowlisted table: {tr}"); + + // Encourage summarize/top/take to avoid huge result sets + //if (!(text.Contains("summarize") || text.Contains("| take ") || text.Contains("| top "))) + // return KqlValidationResult.Fail("Query must include summarize/top/take to limit results."); + + return KqlValidationResult.Ok(); + } + } + + public readonly struct KqlValidationResult + { + public bool IsOk { get; } + public string? Error { get; } + + private KqlValidationResult(bool ok, string? error) { IsOk = ok; Error = error; } + + public static KqlValidationResult Ok() => new(true, null); + public static KqlValidationResult Fail(string error) => new(false, error); + } +} diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/KustoQueryService.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/KustoQueryService.cs new file mode 100644 index 000000000..ecb89fcd6 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/KustoQueryService.cs @@ -0,0 +1,54 @@ +using Azure.Core; +using Azure.Identity; +using Kusto.Data; +using Kusto.Data.Common; +using Kusto.Data.Net.Client; +using Microsoft.Extensions.Options; +using System.Data; + +namespace ChatADX.Web.Services +{ + public sealed class KustoQueryService + { + private readonly ICslQueryProvider _query; + private readonly string _database; + + public KustoQueryService(IOptions opts) + { + var options = opts.Value; + _database = options.Database; + + // Use DefaultAzureCredential: works locally (Azure CLI / Visual Studio), and in Azure (Managed Identity) + var cred = new ClientSecretCredential( + opts.Value.TenantId, + opts.Value.ClientId, + opts.Value.ClientSecret); + + var kcsb = new KustoConnectionStringBuilder(options.ClusterUri) + .WithAadAzureTokenCredentialsAuthentication(cred); + + _query = KustoClientFactory.CreateCslQueryProvider(kcsb); + } + + public async Task QueryAsync(string kql, IDictionary parameters, CancellationToken ct = default) + { + var props = new ClientRequestProperties + { + ClientRequestId = $"chat_{Guid.NewGuid()}" + }; + + foreach (var kvp in parameters) + { + // Pass all as strings; let KQL cast via e.g., datetime({from}) if declared + props.SetParameter(kvp.Key, kvp.Value); + } + + props.SetOption("servertimeout", "00:00:12"); + + using var reader = await _query.ExecuteQueryAsync(_database, kql, props); + var table = new DataTable(); + table.Load(reader); + return table; + } + } +} diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/LlmClient.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/LlmClient.cs new file mode 100644 index 000000000..fb2226fb8 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/LlmClient.cs @@ -0,0 +1,460 @@ +using System.Net.Http.Headers; +using System.Text; +using System.Text.Json; +using System.Text.Json.Nodes; +using Microsoft.Extensions.Options; +using ChatADX.Web.Models; +using System.Text.RegularExpressions; + +namespace ChatADX.Web.Services +{ + public sealed class LlmClient + { + private readonly HttpClient _http; + private readonly LlmOptions _opt; + + public sealed class AssistantRunResult + { + public string Answer { get; set; } = ""; + public string ThreadId { get; set; } = ""; + } + + public enum AssistantType + { + Data, + Docs + } + + public LlmClient(HttpClient http, IOptions opt) + { + _http = http; + _opt = opt.Value; + } + + public async Task ProposeKqlAsync(string question, string schemaJson, CancellationToken ct = default) + { + var system = string.Join("\n", new[] { + "You are a Kusto (KQL) assistant for Azure Data Explorer.", + "Use ONLY the tables/columns provided in the SCHEMA JSON that follows.", + "ALWAYS try to query for the least amount of data neccessary to answer the question.", + "Return a JSON object with fields: assistant, kql, parameters, parameterTypes (optional), assumptions, why.", + "When asked to query by months ago, convert number of months to days (e.g last to months = StartTime >= ago(60d))", + "When joining tables, this example for correct syntax: EventsTable | join kind=inner (EventTypesTable) on $left.EventTypeGuid == $right.GUID.", + "When querying MachinesTable you can fetch the machine's Organization and Site by joining the latest record from JobRunsTable by SerialNumber.", + "Output raw JSON ONLY (no code fences).", + @"Classify the user's question into exactly one of: + - ""data"": requires querying telemetry via KQL (numbers, trends, counts, rates, top-N, timelines). + - ""docs"": architectural/how-to/design/definitions/“what is/how do we” that do not require live data. place data or docs in the assistant field you return.", + "If the question is classified as 'docs', set kql to an empty string and parameters to an empty object.", + + + }); + + var schemaBlock = $"SCHEMA:\n{schemaJson}"; + var user = $"Question: {question}\n\n{schemaBlock}"; + + var payload = new + { + model = _opt.Model, + temperature = _opt.Temperature, + response_format = new { type = "json_object" }, + messages = new object[] { + new { role = "system", content = system }, + new { role = "user", content = user } + } + }; + + using var req = new HttpRequestMessage(HttpMethod.Post, _opt.Endpoint); + var json = JsonSerializer.Serialize(payload); + req.Content = new StringContent(json, Encoding.UTF8, "application/json"); + + if (_opt.IsAzure) req.Headers.Add("api-key", _opt.ApiKey); + else req.Headers.Authorization = new AuthenticationHeaderValue("Bearer", _opt.ApiKey); + + using var resp = await _http.SendAsync(req, ct); + resp.EnsureSuccessStatusCode(); + var body = await resp.Content.ReadAsStringAsync(ct); + + var root = JsonNode.Parse(body)!.AsObject(); + var content = root["choices"]![0]!["message"]!["content"]?.ToString() ?? "{}"; + content = StripCodeFences(content); + + var opts = new JsonSerializerOptions { PropertyNameCaseInsensitive = true }; + opts.Converters.Add(new FlexibleStringListConverter()); + try + { + var result = JsonSerializer.Deserialize(content, opts); + if (result != null) return result; + } + catch (JsonException) + { + // fall back + } + + // Lenient mapping + var node = JsonNode.Parse(content) as JsonObject ?? new JsonObject(); + var kql = node["kql"]?.ToString() ?? string.Empty; + + var parameters = new Dictionary(); + if (node["parameters"] is JsonObject pObj) + { + foreach (var kv in pObj) + parameters[kv.Key] = kv.Value?.ToString() ?? string.Empty; + } + + List? assumptions = null; + if (node.TryGetPropertyValue("assumptions", out var aNode) && aNode is not null) + { + assumptions = new List(); + if (aNode is JsonArray arr) + { + foreach (var el in arr) assumptions.Add(el?.ToString() ?? string.Empty); + } + else + { + assumptions.Add(aNode.ToString()); + } + } + + return new ProposeKqlResult + { + Kql = kql, + Parameters = parameters, + Assumptions = assumptions, + Why = node["why"]?.ToString() + }; + } + + public async Task ProposeKqlAsync( + string question, string schemaJson, IEnumerable? history, CancellationToken ct = default) + { + var system = string.Join("\n", new[] { + "You are a Kusto (KQL) assistant for Azure Data Explorer.", + "Use ONLY the tables/columns provided in the SCHEMA JSON that follows.", + "ALWAYS follow upper case column names when specified by the schema.", + "ALWAYS try to query for the least amount of data neccessary to answer the question.", + "Return a JSON object with fields: assistant, kql, parameters, parameterTypes (optional), assumptions, why.", + "When asked to query by months ago, convert number of months to days (e.g last to months = StartTime >= ago(60d))", + "When joining tables, this example for correct syntax: EventsTable | join kind=inner (EventTypesTable) on $left.EventTypeGuid == $right.GUID.", + "If you are joining and want to project two columns with the same name append '1' to the end of the second table name. (e.g: SitesTable | join kind=inner (OrganizationsTable) on $left.ORGANIZATION_GUID == $right.GUID | project Site = NAME, Organization = NAME1).", + "When joining tables, make sure to also join by environment (e.g: SitesTable | join kind=inner (OrganizationsTable) on $left.ORGANIZATION_GUID == $right.GUID and $left.ENVIRONMENT == $right.ENVIRONMENT)).", + "Output raw JSON ONLY (no code fences).", + // 👇 tiny but important instruction for routing: + @"Use conversational context from prior turns. If the current question refers to a previous result (e.g., 'that', 'those', 'the same one'), + keep the same 'assistant' classification as the previous turn unless the user clearly asks for documentation.", + @"Classify into exactly one of: + - ""data"": requires querying telemetry via KQL (numbers, trends, counts, rates, top-N, timelines). + - ""docs"": architectural/how-to/design/definitions not requiring live data." + }); + + var messages = new List { new { role = "system", content = system } }; + + if (history != null) + { + foreach (var m in history.TakeLast(6)) + messages.Add(new { role = m.Role, content = m.Content }); + } + + var schemaBlock = $"SCHEMA:\n{schemaJson}"; + messages.Add(new { role = "user", content = $"Question: {question}\n\n{schemaBlock}" }); + + var payload = new + { + model = _opt.Model, + temperature = _opt.Temperature, + response_format = new { type = "json_object" }, + messages = messages + }; + + using var req = new HttpRequestMessage(HttpMethod.Post, _opt.Endpoint); + req.Content = new StringContent(JsonSerializer.Serialize(payload), Encoding.UTF8, "application/json"); + if (_opt.IsAzure) req.Headers.Add("api-key", _opt.ApiKey); + else req.Headers.Authorization = new AuthenticationHeaderValue("Bearer", _opt.ApiKey); + + using var resp = await _http.SendAsync(req, ct); + resp.EnsureSuccessStatusCode(); + var body = await resp.Content.ReadAsStringAsync(ct); + + var root = JsonNode.Parse(body)!.AsObject(); + var content = root["choices"]![0]!["message"]!["content"]?.ToString() ?? "{}"; + content = StripCodeFences(content); // your existing helper + + var opts = new JsonSerializerOptions { PropertyNameCaseInsensitive = true }; + opts.Converters.Add(new FlexibleStringListConverter()); + var result = JsonSerializer.Deserialize(content, opts) + ?? new ProposeKqlResult { Kql = "", Parameters = new() }; + return result; + } + + private static string StripCodeFences(string s) + { + if (string.IsNullOrWhiteSpace(s)) return s ?? string.Empty; + var t = s.Trim(); + + if (t.StartsWith("```")) + { + // Remove first line (the ```json or ``` block) + var firstNl = t.IndexOf('\n'); + if (firstNl >= 0 && firstNl + 1 < t.Length) + { + var inner = t.Substring(firstNl + 1); + + // Remove trailing fence + var fence = inner.LastIndexOf("```", StringComparison.Ordinal); + if (fence >= 0) + inner = inner.Substring(0, fence); + + return inner.Trim(); + } + } + return t; + } + + + public async Task AnswerFromFactsAsync(string question, string factsJson, string kqlForDisplay, CancellationToken ct = default) + { + var system = string.Join("\n", new[] { + "You are a precise analyst.", + "Answer ONLY from the provided ADX facts. If insufficient, say so.", + "Be explicit about the time range and columns used.", + "Ink quantities are stored as nanoliters. Make them humanly readable by converting them to milliliters or liters depending on what makes more sense." + }); + + var user = $"Question: {question}\n\nFacts(JSON):\n{factsJson}\n\nKQL used:\n{kqlForDisplay}"; + + var payload = new + { + model = _opt.Model, + temperature = 0.2, + messages = new object[] { + new { role = "system", content = system }, + new { role = "user", content = user } + } + }; + + using var req = new HttpRequestMessage(HttpMethod.Post, _opt.Endpoint); + var json = JsonSerializer.Serialize(payload); + req.Content = new StringContent(json, Encoding.UTF8, "application/json"); + + if (_opt.IsAzure) req.Headers.Add("api-key", _opt.ApiKey); + else req.Headers.Authorization = new AuthenticationHeaderValue("Bearer", _opt.ApiKey); + + using var resp = await _http.SendAsync(req, ct); + resp.EnsureSuccessStatusCode(); + var body = await resp.Content.ReadAsStringAsync(ct); + + var root = JsonNode.Parse(body)!.AsObject(); + var content = root["choices"]![0]!["message"]!["content"]!.ToString(); + return content; + } + + // Add once in your class + private void AddOpenAIHeaders(HttpRequestMessage req) + { + // MUST be a standard OpenAI key (sk-...), not an Azure key + req.Headers.Authorization = + new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", _opt.ApiKey); + + // REQUIRED for Assistants v2 + req.Headers.Add("OpenAI-Beta", "assistants=v2"); + + // If your org enforces projects or you want to scope, uncomment: + // req.Headers.Add("OpenAI-Organization", ""); + // req.Headers.Add("OpenAI-Project", ""); + } + + private static async Task ReadBodyOrThrowAsync(HttpResponseMessage res, CancellationToken ct) + { + var body = await res.Content.ReadAsStringAsync(ct); + if (!res.IsSuccessStatusCode) + throw new HttpRequestException($"{(int)res.StatusCode} {res.ReasonPhrase}: {body}"); + return body; + } + + + public async Task AnswerWithAssistantAsync( + AssistantType assistant, + string question, + string factsJson, + string kql, + CancellationToken ct = default) + { + // 1) Create a thread (empty is fine) + using var tReq = new HttpRequestMessage(HttpMethod.Post, "https://api.openai.com/v1/threads") + { + Content = new StringContent("{}", System.Text.Encoding.UTF8, "application/json") + }; + AddOpenAIHeaders(tReq); + var tBody = await ReadBodyOrThrowAsync(await _http.SendAsync(tReq, ct), ct); + var threadId = System.Text.Json.JsonDocument.Parse(tBody).RootElement.GetProperty("id").GetString(); + + // 2) Add a single user message with the three sections + + String messageText = String.Empty; + + if (assistant == AssistantType.Data) + { + messageText = $"Question:\n{question}\n\nFacts(JSON):\n{factsJson}\n\nKQL used:\n{kql}"; + } + else + { + messageText = $"Question:\n{question}"; + } + + using var mReq = new HttpRequestMessage(HttpMethod.Post, $"https://api.openai.com/v1/threads/{threadId}/messages") + { + // v2 expects "content" to be an array of content parts + Content = JsonContent.Create(new + { + role = "user", + content = new object[] { new { type = "text", text = messageText } } + }) + }; + AddOpenAIHeaders(mReq); + await ReadBodyOrThrowAsync(await _http.SendAsync(mReq, ct), ct); + + // 3) Create a run targeting your Assistant (must have File Search enabled & schema.json attached) + using var rReq = new HttpRequestMessage(HttpMethod.Post, $"https://api.openai.com/v1/threads/{threadId}/runs") + { + Content = JsonContent.Create(new + { + assistant_id = assistant == AssistantType.Data ? _opt.AnswererAssistantId : _opt.DocsAssistantId, + // You can override instructions here if you ever need to: + // instructions = "..." + }) + }; + AddOpenAIHeaders(rReq); + var rBody = await ReadBodyOrThrowAsync(await _http.SendAsync(rReq, ct), ct); + var runId = System.Text.Json.JsonDocument.Parse(rBody).RootElement.GetProperty("id").GetString(); + + // 4) Poll run until completed + while (true) + { + await Task.Delay(600, ct); + using var gReq = new HttpRequestMessage(HttpMethod.Get, $"https://api.openai.com/v1/threads/{threadId}/runs/{runId}"); + AddOpenAIHeaders(gReq); + var gBody = await ReadBodyOrThrowAsync(await _http.SendAsync(gReq, ct), ct); + var root = System.Text.Json.JsonDocument.Parse(gBody).RootElement; + var status = root.GetProperty("status").GetString(); + if (status == "completed") break; + if (status == "failed" || status == "cancelled" || status == "expired") + { + var lastError = root.TryGetProperty("last_error", out var le) ? le.ToString() : "unknown"; + throw new Exception($"Assistant run {status}: {lastError}"); + } + } + + // 5) Fetch messages and return the latest assistant text + using var lReq = new HttpRequestMessage(HttpMethod.Get, $"https://api.openai.com/v1/threads/{threadId}/messages"); + AddOpenAIHeaders(lReq); + var lBody = await ReadBodyOrThrowAsync(await _http.SendAsync(lReq, ct), ct); + using var doc = System.Text.Json.JsonDocument.Parse(lBody); + + // Messages are returned most-recent-first; take the first assistant message + foreach (var msg in doc.RootElement.GetProperty("data").EnumerateArray()) + { + var role = msg.GetProperty("role").GetString(); + if (role == "assistant") + { + foreach (var part in msg.GetProperty("content").EnumerateArray()) + { + if (part.GetProperty("type").GetString() == "text") + return part.GetProperty("text").GetProperty("value").GetString() ?? ""; + } + } + } + + return "(no assistant message found)"; + } + + public async Task AnswerWithAssistantAsync( + AssistantType assistant, + string question, + string factsJson, + string kql, + string? threadId, + CancellationToken ct = default) + { + // 1) Use existing thread or create a new one + if (string.IsNullOrEmpty(threadId)) + { + using var tReq = new HttpRequestMessage(HttpMethod.Post, "https://api.openai.com/v1/threads") + { Content = new StringContent("{}", Encoding.UTF8, "application/json") }; + AddOpenAIHeaders(tReq); + var tBody = await ReadBodyOrThrowAsync(await _http.SendAsync(tReq, ct), ct); + threadId = System.Text.Json.JsonDocument.Parse(tBody).RootElement.GetProperty("id").GetString(); + } + + // 2) Add the user message (same text you build today) + var messageText = assistant == AssistantType.Data + ? $"Question:\n{question}\n\nFacts(JSON):\n{factsJson}\n\nKQL used:\n{kql}" + : $"Question:\n{question}"; + + using (var mReq = new HttpRequestMessage(HttpMethod.Post, $"https://api.openai.com/v1/threads/{threadId}/messages") + { Content = JsonContent.Create(new { role = "user", content = new object[] { new { type = "text", text = messageText } } }) }) + { + AddOpenAIHeaders(mReq); + await ReadBodyOrThrowAsync(await _http.SendAsync(mReq, ct), ct); + } + + // 3) Run with the correct assistant id (unchanged logic) + using (var rReq = new HttpRequestMessage(HttpMethod.Post, $"https://api.openai.com/v1/threads/{threadId}/runs") + { Content = JsonContent.Create(new { assistant_id = assistant == AssistantType.Data ? _opt.AnswererAssistantId : _opt.DocsAssistantId }) }) + { + AddOpenAIHeaders(rReq); + var rBody = await ReadBodyOrThrowAsync(await _http.SendAsync(rReq, ct), ct); + var runId = System.Text.Json.JsonDocument.Parse(rBody).RootElement.GetProperty("id").GetString(); + + // Poll until completed (same as your existing loop) + while (true) + { + await Task.Delay(600, ct); + using var gReq = new HttpRequestMessage(HttpMethod.Get, $"https://api.openai.com/v1/threads/{threadId}/runs/{runId}"); + AddOpenAIHeaders(gReq); + var gBody = await ReadBodyOrThrowAsync(await _http.SendAsync(gReq, ct), ct); + var root = System.Text.Json.JsonDocument.Parse(gBody).RootElement; + var status = root.GetProperty("status").GetString(); + if (status == "completed") break; + if (status == "failed" || status == "cancelled" || status == "expired") + { + var lastError = root.TryGetProperty("last_error", out var le) ? le.ToString() : "unknown"; + throw new Exception($"Assistant run {status}: {lastError}"); + } + } + } + + // 4) Fetch the latest assistant text and return it with the thread id + using var lReq = new HttpRequestMessage(HttpMethod.Get, $"https://api.openai.com/v1/threads/{threadId}/messages"); + AddOpenAIHeaders(lReq); + var lBody = await ReadBodyOrThrowAsync(await _http.SendAsync(lReq, ct), ct); + using var doc = System.Text.Json.JsonDocument.Parse(lBody); + foreach (var msg in doc.RootElement.GetProperty("data").EnumerateArray()) + { + if (msg.GetProperty("role").GetString() == "assistant") + { + foreach (var part in msg.GetProperty("content").EnumerateArray()) + if (part.GetProperty("type").GetString() == "text") + { + var raw = part.GetProperty("text").GetProperty("value").GetString() ?? ""; + var cleaned = assistant == AssistantType.Docs ? StripCitations(raw) : raw; + return new AssistantRunResult + { + Answer = cleaned, + ThreadId = threadId! + }; + } + } + } + return new AssistantRunResult { Answer = "(no assistant message found)", ThreadId = threadId! }; + } + + private static string StripCitations(string s) + { + if (string.IsNullOrWhiteSpace(s)) return s ?? string.Empty; + // Remove any inline citation markers like: or + return Regex.Replace(s, @"\s*【[^】]*】", string.Empty); + } + + + } +} diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/SchemaRegistry.cs b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/SchemaRegistry.cs new file mode 100644 index 000000000..da6868bc5 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Services/SchemaRegistry.cs @@ -0,0 +1,32 @@ +using System.Text.Json; + +namespace ChatADX.Web.Services +{ + public sealed class SchemaRegistry + { + private readonly IWebHostEnvironment _env; + private readonly ILogger _log; + private string? _cached; + + public SchemaRegistry(IWebHostEnvironment env, ILogger log) + { + _env = env; _log = log; + } + + public string GetSchemaJson() + { + if (!string.IsNullOrEmpty(_cached)) return _cached!; + var path = Path.Combine(_env.ContentRootPath, "Data", "schema.json"); + if (!File.Exists(path)) + { + _log.LogWarning("Schema file not found at {Path}. Returning empty schema.", path); + _cached = "{\"tables\":{}}"; + return _cached!; + } + _cached = File.ReadAllText(path); + // Basic sanity check + JsonDocument.Parse(_cached); + return _cached!; + } + } +} diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Tango.Portal.Chat.Web.csproj b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Tango.Portal.Chat.Web.csproj new file mode 100644 index 000000000..5e0868487 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Tango.Portal.Chat.Web.csproj @@ -0,0 +1,11 @@ + + + net8.0 + enable + enable + + + + + + diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Views/Home/Index.cshtml b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Views/Home/Index.cshtml new file mode 100644 index 000000000..602e38678 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Views/Home/Index.cshtml @@ -0,0 +1,234 @@ +@{ + Layout = "~/Views/Shared/_Layout.cshtml"; +} + +@{ + ViewData["Title"] = "Chat"; +} + +
+
+

ChatGPT ⇄ ADX (Ephemeral Chat)

+
+ +
+
+ + +
+
+
+
+
+ + + + + diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Views/Shared/_Layout.cshtml b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Views/Shared/_Layout.cshtml new file mode 100644 index 000000000..77721b648 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Views/Shared/_Layout.cshtml @@ -0,0 +1,14 @@ + + + + + + Chat ADX + + + +
+ @RenderBody() +
+ + 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 new file mode 100644 index 000000000..aa4ed5184 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/appsettings.Development.json @@ -0,0 +1,18 @@ +{ + "OpenAI": { + "IsAzure": false, + "Endpoint": "https://api.openai.com/v1/chat/completions", + "ApiKey": "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", + "Model": "gpt-4o-mini", + "Temperature": 0.2, + "AnswererAssistantId": "asst_JRKGFqWUYG2rP6CptUgyVcJk", + "DocsAssistantId": "asst_HQ0C8tsdtzjENITM4qq6kFpz" + }, + "ADX": { + "ClusterUri": "https://adx-twine.westeurope.kusto.windows.net/", + "Database": "twine-dev", + "TenantId": "2ebd63a5-bc2f-41dc-9066-4409ed5e5dd4", + "ClientId": "ec612854-7abc-457b-808a-5d0c5ba80c57", + "ClientSecret": "C6n8Q~-NgsAQ6yYJwoNABkcVUNSm2~8-8xNgaa32" + } +} \ 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 new file mode 100644 index 000000000..c5bebc38f --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/appsettings.json @@ -0,0 +1,20 @@ +{ + "Logging": { + "LogLevel": { + "Default": "Information", + "Microsoft.AspNetCore": "Warning" + } + }, + "OpenAI": { + "IsAzure": false, + "Endpoint": "https://api.openai.com/v1/chat/completions", + "ApiKey": "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", + "Model": "gpt-4o-mini", + "Temperature": 0.2 + }, + "ADX": { + "ClusterUri": "https://adx-twine.westeurope.kusto.windows.net/", + "Database": "twine-dev" + }, + "AllowedHosts": "*" +} \ No newline at end of file diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/wwwroot/site.css b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/wwwroot/site.css new file mode 100644 index 000000000..8830753d5 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/wwwroot/site.css @@ -0,0 +1,240 @@ +/* --- Base layout --- */ +:root { + --bg: #0b0f14; + --panel: #0e1621; + --panel-2: #0b1220; + --border: #1f2a37; + --border-2: #2b3b4f; + --text: #e6edf3; + --muted: #a7b0c0; + --accent: #6ea8fe; +} + +* { + box-sizing: border-box; +} + +body { + margin: 0; + font-family: system-ui, -apple-system, Segoe UI, Roboto, Helvetica, Arial, sans-serif; + background: var(--bg); + color: var(--text); +} + +/* Container helpers */ +.container { + max-width: 920px; + margin: 3rem auto; + padding: 0 1rem; +} + +.card { + background: var(--panel); + border: 1px solid var(--border); + border-radius: 12px; + padding: 1rem 1.25rem; + box-shadow: 0 10px 20px rgba(0,0,0,.25); +} + +.row { + display: flex; + gap: .5rem; + margin-top: .5rem; +} + +/* Inputs */ +input, textarea { + flex: 1; + padding: .6rem .8rem; + border-radius: 10px; + border: 1px solid var(--border-2); + background: var(--panel-2); + color: var(--text); + outline: none; +} + +textarea { + resize: none; + min-height: 44px; + max-height: 140px; +} + + textarea::placeholder { + color: #7d8aa5; + } + +button { + padding: .65rem .95rem; + border-radius: 10px; + border: 1px solid var(--border-2); + background: #162334; + color: var(--text); + cursor: pointer; + font-weight: 600; +} + + button:hover { + background: #1b2a40; + } + + button[disabled] { + opacity: .6; + cursor: not-allowed; + } + +/* Code-ish blocks */ +pre { + white-space: pre-wrap; + word-wrap: break-word; + background: var(--panel-2); + border: 1px solid var(--border-2); + padding: .75rem; + border-radius: 8px; + overflow: auto; /* allow horizontal scroll for long code/KQL */ +} + +details { + margin-top: .75rem; +} + +summary { + cursor: pointer; +} + +code { + background: var(--panel-2); + padding: .1rem .35rem; + border-radius: 6px; +} + +/* --- Chat UI --- */ +.chat { + display: flex; + flex-direction: column; + gap: .65rem; + /* REMOVED max-height/overflow so the page grows instead of inner scrolling */ + padding: .5rem .25rem .75rem; +} + +.msg { + display: flex; + gap: .5rem; +} + + .msg.user { + flex-direction: row-reverse; + } + +.avatar { + flex: 0 0 28px; + height: 28px; + border-radius: 50%; + background: #162334; + border: 1px solid var(--border-2); + color: var(--text); + display: grid; + place-items: center; + font-size: 12px; + font-weight: 600; +} + + .avatar.user { + background: #1a2b44; + } + +/* Assistant bubbles render Markdown: let block elements flow normally */ +.bubble { + background: var(--panel-2); + border: 1px solid var(--border-2); + border-radius: 10px; + padding: .55rem .7rem; + word-wrap: break-word; + max-width: 760px; + white-space: normal; /* no pre-wrap here */ +} +/* Keep pre-wrap for user's plain text so their newlines are respected */ +.msg.user .bubble { + white-space: pre-wrap; +} + +.meta { + color: var(--muted); + font-size: 12px; + margin-top: .2rem; +} + +.err { + color: #ff6b6b; + background: #2a1216; + border: 1px solid #5d1a22; + padding: 8px 10px; + border-radius: 10px; + margin: 8px 0 0; + display: none; +} + +/* Thinking indicator */ +.thinking { + opacity: .9; + display: inline-flex; + align-items: center; + gap: .35rem; +} + +.dots { + display: inline-grid; + grid-auto-flow: column; + gap: 4px; +} + + .dots span { + width: 6px; + height: 6px; + border-radius: 50%; + background: var(--accent); + opacity: .8; + animation: jump 1.1s infinite ease-in-out; + } + + .dots span:nth-child(2) { + animation-delay: .15s + } + + .dots span:nth-child(3) { + animation-delay: .3s + } + +@keyframes jump { + 0%,80%,100% { + transform: translateY(0) + } + + 40% { + transform: translateY(-4px) + } +} + +/* Markdown tweaks inside assistant bubbles */ +.bubble .md ol, +.bubble .md ul { + margin: .4rem 0 .6rem 1.25rem; +} + +.bubble .md li { + margin: .15rem 0; +} + +.bubble .md h1, .bubble .md h2, .bubble .md h3, +.bubble .md h4, .bubble .md h5, .bubble .md h6 { + margin: .4rem 0 .35rem; + line-height: 1.25; +} + +.bubble .md p { + margin: .35rem 0; +} + +/* Footer composer */ +.composer { + margin-top: .75rem; +} diff --git a/Software/Visual_Studio_22/Tango.sln b/Software/Visual_Studio_22/Tango.sln new file mode 100644 index 000000000..b946fd342 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.sln @@ -0,0 +1,25 @@ + +Microsoft Visual Studio Solution File, Format Version 12.00 +# Visual Studio Version 17 +VisualStudioVersion = 17.13.35818.85 +MinimumVisualStudioVersion = 10.0.40219.1 +Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "Tango.Portal.Chat.Web", "Tango.Portal.Chat.Web\Tango.Portal.Chat.Web.csproj", "{F044E4A1-1D9C-B93C-28C5-E9E5A44A038D}" +EndProject +Global + GlobalSection(SolutionConfigurationPlatforms) = preSolution + Debug|Any CPU = Debug|Any CPU + Release|Any CPU = Release|Any CPU + EndGlobalSection + GlobalSection(ProjectConfigurationPlatforms) = postSolution + {F044E4A1-1D9C-B93C-28C5-E9E5A44A038D}.Debug|Any CPU.ActiveCfg = Debug|Any CPU + {F044E4A1-1D9C-B93C-28C5-E9E5A44A038D}.Debug|Any CPU.Build.0 = Debug|Any CPU + {F044E4A1-1D9C-B93C-28C5-E9E5A44A038D}.Release|Any CPU.ActiveCfg = Release|Any CPU + {F044E4A1-1D9C-B93C-28C5-E9E5A44A038D}.Release|Any CPU.Build.0 = Release|Any CPU + EndGlobalSection + GlobalSection(SolutionProperties) = preSolution + HideSolutionNode = FALSE + EndGlobalSection + GlobalSection(ExtensibilityGlobals) = postSolution + SolutionGuid = {03E37932-B461-400E-BEF9-3C74CBD14154} + EndGlobalSection +EndGlobal -- cgit v1.3.1