Read a Window's SQL (SQL Information)

// Read a P21 datawindow's SQL through the client's SQL Information tool.
// Usage: dotnet run -- [SERVICE DATAWINDOW] [tab=TAB] [field=FIELD] [row=N] [load=TAB.DATAWINDOW.FIELD=VALUE]
// 1. Open the window by service name (Interactive API). With load=, retrieve a
//    record first by changing its key field -- nothing is saved.
// 2. Select the tab that holds the datawindow, and run the m_sqlinformation tool
//    on it, the way Help > SQL Information does in the client.
// 3. Read the dialog it opens: the SQL INFO text, the column map
//    (label = table.column), and the datawindow's SELECT. The SELECT is a text
//    box on the dialog's SQL tab, which only the web client's ui/full surface returns.
// 4. Close the dialog, the window and the session.
using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;

// ---- EDIT THESE -----------------------------------------------------------
const string BaseUrl = "https://play.example.com";   // your P21 server
const string Username = "apiuser";
const string Password = "your-password";
var service = "Item";                                 // Interactive service name of the window
var datawindow = "document_link";                     // the datawindow to describe
var tab = "DOCUMENT_LINK";                            // tab page that holds it ("" = leave as opened)
var field = "";                                       // a column to describe ("" = none)
var row = 0;                                          // 1-based row for a grid field (0 = form or none)
var load = "";                                        // e.g. "TABPAGE_1.tp_1_dw_1.item_id=WIDGET-001" loads a record
// ---------------------------------------------------------------------------

// Command-line arguments override the values above.
var positional = args.Where(a => !a.Contains('=')).ToArray();
var options = args.Where(a => a.Contains('='))
    .ToDictionary(a => a[..a.IndexOf('=')], a => a[(a.IndexOf('=') + 1)..]);
if (positional.Length >= 2)
{
    (service, datawindow) = (positional[0], positional[1]);
    (tab, field, row, load) = ("", "", 0, "");
}
tab = options.GetValueOrDefault("tab", tab);
field = options.GetValueOrDefault("field", field);
row = int.Parse(options.GetValueOrDefault("row", row.ToString()));
load = options.GetValueOrDefault("load", load);

var handler = new HttpClientHandler
{
    // Test tenants often present a self-signed cert. Delete this line in production.
    ServerCertificateCustomValidationCallback =
        HttpClientHandler.DangerousAcceptAnyServerCertificateValidator,
};
using var client = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(2) };
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

var token = await GetTokenAsync(client);
var uiServer = await GetUiServerAsync(client, token);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);
var iapi = $"{uiServer}/api/ui/interactive";
var full = $"{uiServer}/ui/full";   // the web client's surface: no /api/ prefix

string title = "";
string sqlInfo = "(none)";
var columns = new List<string>();
string? select = null;

var session = await SendAsync(HttpMethod.Post, $"{iapi}/sessions/",
    new { ResponseWindowHandlingEnabled = true }, "session");
var sessionId = session.TryGetProperty("Id", out var id) ? id.GetString()
              : session.GetProperty("SessionId").GetString();
try
{
    // 1. Open the window, and load a record if asked to.
    var window = await SendAsync(HttpMethod.Post, $"{iapi}/v2/window",
        new { SessionId = sessionId, ServiceName = service }, $"open {service}");
    var windowId = window.GetProperty("WindowId").GetString()!;
    try
    {
        if (load != "")
        {
            var target = load[..load.IndexOf('=')];
            var value = load[(load.IndexOf('=') + 1)..];
            var parts = target.Split('.', 3);   // TAB.DATAWINDOW.FIELD
            await TabAsync(parts[0]);
            await SendAsync(HttpMethod.Put, $"{iapi}/v2/change",
                new { SessionId = sessionId, WindowId = windowId,
                      List = new[] { new { TabName = parts[0], DatawindowName = parts[1],
                                           FieldName = parts[2], Value = value } } },
                $"load {load}");
        }

        // 2. The tab that holds the datawindow, then Help > SQL Information.
        if (tab != "") await TabAsync(tab);
        var answer = await SendAsync(HttpMethod.Post, $"{iapi}/v2/tools",
            new { SessionId = sessionId, WindowId = windowId, ToolName = "m_sqlinformation",
                  DatawindowName = datawindow, FieldName = field == "" ? null : field,
                  Row = row, Text = (string?)null }, "m_sqlinformation");
        var dialog = OpenedWindow(answer)
            ?? throw new InvalidOperationException($"SQL Information opened no dialog: {answer}");

        // 3. Read the dialog. Its title names the tab and the window.
        try
        {
            using var dialogDoc = JsonDocument.Parse(
                await client.GetStringAsync($"{iapi}/v2/window?id={dialog}"));
            var definition = dialogDoc.RootElement.GetProperty("Definition");
            title = definition.GetProperty("Title").GetString() ?? "";
            var pages = definition.TryGetProperty("TabPageList", out var list) &&
                        list.ValueKind == JsonValueKind.Array
                ? list.EnumerateArray().Select(p => p.GetProperty("Name").GetString()).ToList()
                : new List<string?>();

            var info = await RowsAsync(dialog, "sqlinfo");
            if (info.Count > 0 && info[0].TryGetProperty("text", out var text)) sqlInfo = text.GetString() ?? "(none)";
            if (pages.Contains("SQL INFORMATION REPORT"))
            {
                await PageAsync(dialog, "SQL INFORMATION REPORT");
                foreach (var r in await RowsAsync(dialog, "showreport"))
                    if (r.TryGetProperty("info", out var line) && !string.IsNullOrEmpty(line.GetString()))
                        columns.Add(line.GetString()!);
            }
            if (pages.Contains("SQL"))
            {
                var sqlPage = await PageAsync(dialog, "SQL");
                if (sqlPage.TryGetProperty("TextBoxes", out var boxes) &&
                    boxes.ValueKind == JsonValueKind.Array)
                    select = boxes.EnumerateArray()
                        .Select(b => b.TryGetProperty("Text", out var t) ? t.GetString() : null)
                        .FirstOrDefault(t => !string.IsNullOrEmpty(t));
            }
        }
        finally
        {
            await client.DeleteAsync($"{iapi}/v2/window?id={dialog}");
        }
    }
    finally
    {
        await client.DeleteAsync($"{iapi}/v2/window?id={windowId}");
    }

    async Task TabAsync(string name) =>
        await SendAsync(HttpMethod.Put, $"{iapi}/v2/tab",
            new { SessionId = sessionId, WindowId = windowId, TabName = name, PageName = name },
            $"tab {name}");
}
finally
{
    await client.DeleteAsync($"{iapi}/sessions/");
}

Console.WriteLine(title);
Console.WriteLine("\n-- SQL INFO");
Console.WriteLine(sqlInfo);
Console.WriteLine("\n-- Columns (label = table.column)");
Console.WriteLine(columns.Count > 0 ? string.Join("\n", columns) : "(none)");
Console.WriteLine("\n-- SELECT");
// The text arrives whitespace-collapsed; break it where the client left column gaps.
Console.WriteLine(select is null ? "(none)"
    : Regex.Replace(select.Replace("\r\n", "\n"), " {3,}", "\n    ").Trim());

// --- helpers ---------------------------------------------------------------

// A datawindow's rows on the dialog. ui/full's data/data returns an empty grid
// until the client asks for its rows; elements/state does.
async Task<List<JsonElement>> RowsAsync(string window, string dw)
{
    var body = await FullOkAsync(await client.GetAsync(
        $"{full}/v1/grid/{window}/elements/state?dwName={dw}&activeRows=*"), dw);
    if (!body.TryGetProperty("Data", out var data) || data.ValueKind != JsonValueKind.Object)
        return new List<JsonElement>();
    foreach (var entry in data.EnumerateObject())
        return entry.Value.EnumerateArray().Select(e => e.Clone()).ToList();
    return new List<JsonElement>();
}

// Select a tab page of the dialog; the answer is its controls.
async Task<JsonElement> PageAsync(string window, string name)
{
    var body = JsonSerializer.Serialize(new { WindowId = window, PageName = name });
    var response = await client.PostAsync($"{full}/v2/window/page",
        new StringContent(body, Encoding.UTF8, "application/json"));
    return (await FullOkAsync(response, $"page {name}")).GetProperty("Result").GetProperty("TabDefinition");
}

// A ui/full answer: success there is Success + State "Normal", not HTTP 200.
static async Task<JsonElement> FullOkAsync(HttpResponseMessage response, string what)
{
    var text = await response.Content.ReadAsStringAsync();
    var body = text.Length == 0 ? default : JsonDocument.Parse(text).RootElement.Clone();
    if (!response.IsSuccessStatusCode || body.ValueKind != JsonValueKind.Object ||
        !body.TryGetProperty("Success", out var ok) || !ok.GetBoolean() ||
        body.GetProperty("State").GetString() != "Normal")
        throw new InvalidOperationException($"{what}: HTTP {(int)response.StatusCode} {text}");
    return body;
}

// One Interactive call; refuses HTTP errors and P21's Status 2 (failure).
async Task<JsonElement> SendAsync(HttpMethod method, string url, object body, string what)
{
    using var request = new HttpRequestMessage(method, url)
    {
        Content = new StringContent(JsonSerializer.Serialize(body), Encoding.UTF8,
            "application/json"),
    };
    var response = await client.SendAsync(request);
    var text = await response.Content.ReadAsStringAsync();
    if (!response.IsSuccessStatusCode)
        throw new InvalidOperationException($"{what}: HTTP {(int)response.StatusCode} {text}");
    var answer = text.Length == 0 ? default : JsonDocument.Parse(text).RootElement.Clone();
    if (answer.ValueKind == JsonValueKind.Object &&
        answer.TryGetProperty("Status", out var status) && status.GetInt32() == 2)
        throw new InvalidOperationException($"{what}: P21 refused it: {text}");
    return answer;
}

// The window id a tool opened (its windowopened event), or null.
static string? OpenedWindow(JsonElement answer)
{
    if (!answer.TryGetProperty("Events", out var events)) return null;
    foreach (var e in events.EnumerateArray())
        if (e.GetProperty("Name").GetString() == "windowopened")
            foreach (var pair in e.GetProperty("Data").EnumerateArray())
                if (pair.GetProperty("Key").GetString() == "windowid")
                    return pair.GetProperty("Value").GetString();
    return null;
}

// v2 token endpoint — credentials go in the body, never in headers.
static async Task<string> GetTokenAsync(HttpClient client)
{
    var payload = JsonSerializer.Serialize(new { username = Username, password = Password });
    var response = await client.PostAsync(
        $"{BaseUrl}/api/security/token/v2",
        new StringContent(payload, Encoding.UTF8, "application/json"));
    response.EnsureSuccessStatusCode();
    return ReadField(await response.Content.ReadAsStringAsync(), "AccessToken");
}

// Transaction and Interactive calls go to the UI server, not BaseUrl.
static async Task<string> GetUiServerAsync(HttpClient client, string token)
{
    using var request = new HttpRequestMessage(
        HttpMethod.Get, $"{BaseUrl}/api/ui/router/v1/?urlType=external");
    request.Headers.Authorization = new AuthenticationHeaderValue("Bearer", token);
    var response = await client.SendAsync(request);
    response.EnsureSuccessStatusCode();
    return ReadField(await response.Content.ReadAsStringAsync(), "Url").TrimEnd('/');
}

// Some middleware answers these two endpoints in XML even when asked for JSON.
static string ReadField(string payload, string field)
{
    try
    {
        var value = JsonDocument.Parse(payload).RootElement.GetProperty(field).GetString();
        if (!string.IsNullOrEmpty(value)) return value;
    }
    catch (Exception ex) when (ex is JsonException or KeyNotFoundException) { }

    var match = Regex.Match(payload, $"<{field}>([^<]+)</{field}>");
    if (!match.Success)
        throw new InvalidOperationException(
            $"No {field} in response: {payload[..Math.Min(200, payload.Length)]}");
    return match.Groups[1].Value;
}