using System.Net.Http.Json;
using System.Text.Json;
using System.Text.RegularExpressions;
using GB5Shared.DTO.Framework.Login;
using Microsoft.Extensions.Logging;

namespace EntitlementBLL.Onboarding;

public class SqlWorkbenchClient : ISqlWorkbenchClient
{
    // SqlWorkbench's own control-plane schema (SW.*) lives in the "Gb5System" database
    // (§31.6 of the Thread 6/Entitlement DB-provisioning plan) — every call here must carry
    // a Login header whose DatabaseName is "Gb5System", never the caller's own
    // EntitlementDb, or SqlWorkbench's IQueryExecutor resolves against the wrong database.
    private const string SqlWorkbenchDatabaseName = "Gb5System";

    private static readonly Regex TrailingIdPattern = new(@"(-?\d+)\s*$", RegexOptions.Compiled);

    private readonly IHttpClientFactory _HttpClientFactory;
    private readonly ILogger<SqlWorkbenchClient> _Logger;

    public SqlWorkbenchClient(IHttpClientFactory httpClientFactory, ILogger<SqlWorkbenchClient> logger)
    {
        _HttpClientFactory = httpClientFactory;
        _Logger            = logger;
    }

    public async Task<SwClientDatabaseInfoDTO?> GetClientDatabaseAsync(int clientDbId, LoginDTO swLogin, CancellationToken ct)
    {
        var client = CreateClient(swLogin);
        var response = await client.GetAsync($"/ClientDatabase/GetClientDatabaseById?ClientDbId={clientDbId}", ct)
            .ConfigureAwait(false);
        var raw = await ReadAndEnsureSuccessAsync(response, "GetClientDatabaseById", ct).ConfigureAwait(false);

        using var doc = JsonDocument.Parse(raw);
        if (!doc.RootElement.TryGetProperty("Body", out var body) || body.ValueKind != JsonValueKind.Object)
            return null;   // no such ClientDbId — GetById returned null

        return body.Deserialize<SwClientDatabaseInfoDTO>(new JsonSerializerOptions { PropertyNameCaseInsensitive = true });
    }

    public async Task<int> SaveClientDatabaseAsync(SwSaveClientDatabaseRequest req, LoginDTO swLogin, CancellationToken ct)
    {
        var client = CreateClient(swLogin);
        var response = await client.PostAsJsonAsync("/ClientDatabase/SaveClientDatabase", req, ct).ConfigureAwait(false);
        var raw = await ReadAndEnsureSuccessAsync(response, "SaveClientDatabase", ct).ConfigureAwait(false);
        return ExtractIdFromMessageBody(raw)
            ?? throw new InvalidOperationException($"SqlWorkbench SaveClientDatabase succeeded but no id could be parsed from: {raw}");
    }

    public async Task<int> SaveChangeRequestAsync(SwSaveChangeRequestRequest req, LoginDTO swLogin, CancellationToken ct)
    {
        var client = CreateClient(swLogin);
        var response = await client.PostAsJsonAsync("/ChangeRequest/SaveChangeRequest", req, ct).ConfigureAwait(false);
        var raw = await ReadAndEnsureSuccessAsync(response, "SaveChangeRequest", ct).ConfigureAwait(false);
        return ExtractIdFromMessageBody(raw)
            ?? throw new InvalidOperationException($"SqlWorkbench SaveChangeRequest succeeded but no id could be parsed from: {raw}");
    }

    public async Task SubmitChangeRequestAsync(int changeRequestId, LoginDTO swLogin, CancellationToken ct)
    {
        var client = CreateClient(swLogin);
        var response = await client.PutAsync($"/ChangeRequest/SubmitChangeRequest?ChangeRequestId={changeRequestId}", null, ct)
            .ConfigureAwait(false);
        await ReadAndEnsureSuccessAsync(response, "SubmitChangeRequest", ct).ConfigureAwait(false);
    }

    public async Task ApproveChangeRequestAsync(int changeRequestId, string? comment, LoginDTO swLogin, CancellationToken ct)
    {
        var client = CreateClient(swLogin);
        var url = $"/ChangeRequest/ApproveChangeRequest?ChangeRequestId={changeRequestId}"
            + (string.IsNullOrWhiteSpace(comment) ? "" : $"&Comment={Uri.EscapeDataString(comment)}");
        var response = await client.PutAsync(url, null, ct).ConfigureAwait(false);
        await ReadAndEnsureSuccessAsync(response, "ApproveChangeRequest", ct).ConfigureAwait(false);
    }

    public async Task ExecuteChangeRequestAsync(int changeRequestId, LoginDTO swLogin, CancellationToken ct)
    {
        var client = CreateClient(swLogin);
        var response = await client.PostAsync($"/ChangeRequest/ExecuteChangeRequest?ChangeRequestId={changeRequestId}", null, ct)
            .ConfigureAwait(false);
        await ReadAndEnsureSuccessAsync(response, "ExecuteChangeRequest", ct).ConfigureAwait(false);
    }

    public async Task QueueChangeRequestExecutionAsync(int changeRequestId, LoginDTO swLogin, CancellationToken ct)
    {
        var client = CreateClient(swLogin);
        var response = await client.PostAsync($"/ChangeRequest/QueueChangeRequestExecution?ChangeRequestId={changeRequestId}", null, ct)
            .ConfigureAwait(false);
        await ReadAndEnsureSuccessAsync(response, "QueueChangeRequestExecution", ct).ConfigureAwait(false);
    }

    // Thread 3 (Demo/DemoDB, tracker §49) — reapplies an already-Approved/Released package's
    // scripts to an already-existing client database (the real, already-built
    // ProvisioningBLL.ProvisionClientDatabase, exposed as-is — no new SqlWorkbench endpoint
    // needed). §36's reference-data scripts are idempotent (INSERT...WHERE NOT EXISTS), so this
    // is safe to call repeatedly — it restores missing baseline rows, but does NOT clear any
    // occupant-written mutable-table data first; see DemoSessionBLL's own reset-step comment.
    public async Task ReapplyUpgradePackageAsync(int clientDbId, int upgradePackageId, LoginDTO swLogin, CancellationToken ct)
    {
        var client = CreateClient(swLogin);
        var response = await client.PostAsync(
            $"/Provisioning/ProvisionClientDatabase?ClientDbId={clientDbId}&UpgradePackageId={upgradePackageId}", null, ct)
            .ConfigureAwait(false);
        await ReadAndEnsureSuccessAsync(response, "ProvisionClientDatabase", ct).ConfigureAwait(false);
    }

    public async Task<SwChangeRequestStatusDTO?> GetChangeRequestStatusAsync(int changeRequestId, LoginDTO swLogin, CancellationToken ct)
    {
        var client = CreateClient(swLogin);
        var response = await client.GetAsync($"/ChangeRequest/GetChangeRequestById?ChangeRequestId={changeRequestId}", ct)
            .ConfigureAwait(false);
        var raw = await ReadAndEnsureSuccessAsync(response, "GetChangeRequestById", ct).ConfigureAwait(false);

        using var doc = JsonDocument.Parse(raw);
        if (!doc.RootElement.TryGetProperty("Body", out var body) || body.ValueKind != JsonValueKind.Object)
            return null;   // no such ChangeRequestId — GetById returned null

        return body.Deserialize<SwChangeRequestStatusDTO>(new JsonSerializerOptions { PropertyNameCaseInsensitive = true });
    }

    public async Task<IReadOnlyList<SwPackageChainItemDTO>> GetProvisioningChainAsync(
        int dbModelId, SwPackageScopeFilterRequest? scope, LoginDTO swLogin, CancellationToken ct)
    {
        var client = CreateClient(swLogin);
        var response = await client.PostAsJsonAsync(
            $"/UpgradePackage/GetProvisioningChain?DbModelId={dbModelId}",
            scope ?? new SwPackageScopeFilterRequest(), ct).ConfigureAwait(false);
        var raw = await ReadAndEnsureSuccessAsync(response, "GetProvisioningChain", ct).ConfigureAwait(false);

        using var doc = JsonDocument.Parse(raw);
        if (!doc.RootElement.TryGetProperty("Body", out var body) || body.ValueKind != JsonValueKind.Array)
            return Array.Empty<SwPackageChainItemDTO>();

        return body.Deserialize<List<SwPackageChainItemDTO>>(new JsonSerializerOptions { PropertyNameCaseInsensitive = true })
            ?? new List<SwPackageChainItemDTO>();
    }

    // ── Helpers ──────────────────────────────────────────────────────────────

    private HttpClient CreateClient(LoginDTO callerLogin)
    {
        var client = _HttpClientFactory.CreateClient("SqlWorkbench");

        // Never forward the caller's own LoginDTO as-is — SqlWorkbench's own IQueryExecutor
        // resolves the DatabaseName from this header, and SW's schema lives in a different
        // physical database (Gb5System) from Entitlement's own (EntitlementDb). Only ClientId
        // (so MSWCLIENTDATABASE/MSWCHANGEREQUEST are tagged with the real new client, not -1)
        // and UserId carry over.
        var swLogin = new LoginDTO
        {
            ClientId     = callerLogin.ClientId,
            UserId       = callerLogin.UserId,
            DatabaseName = SqlWorkbenchDatabaseName,
        };

        client.DefaultRequestHeaders.Remove("Login");
        client.DefaultRequestHeaders.Add("Login", JsonSerializer.Serialize(swLogin));
        return client;
    }

    private async Task<string> ReadAndEnsureSuccessAsync(HttpResponseMessage response, string operation, CancellationToken ct)
    {
        var raw = await response.Content.ReadAsStringAsync(ct).ConfigureAwait(false);

        if (!response.IsSuccessStatusCode)
        {
            _Logger.LogError("SqlWorkbench {Operation} returned {StatusCode}: {Body}", operation, response.StatusCode, raw);
            throw new InvalidOperationException($"SqlWorkbench {operation} failed ({(int)response.StatusCode}): {ExtractErrorBody(raw) ?? raw}");
        }

        var errorBody = ExtractErrorBody(raw);
        if (!string.IsNullOrEmpty(errorBody))
        {
            _Logger.LogError("SqlWorkbench {Operation} returned 200 with an ErrorBody: {ErrorBody}", operation, errorBody);
            throw new InvalidOperationException($"SqlWorkbench {operation} failed: {errorBody}");
        }

        return raw;
    }

    private static string? ExtractErrorBody(string rawJson)
    {
        try
        {
            using var doc = JsonDocument.Parse(rawJson);
            if (doc.RootElement.TryGetProperty("ErrorBody", out var err) && err.ValueKind == JsonValueKind.String)
                return err.GetString();
        }
        catch { /* not JSON, or no ErrorBody property — treated as success by the caller */ }
        return null;
    }

    // SqlWorkbench's Save*() endpoints return a human-readable success message with the new
    // id as the trailing token (e.g. "Details have been saved successfully with id = 5") —
    // the same convention this engagement's own live-testing threads had to parse by hand.
    private static int? ExtractIdFromMessageBody(string rawJson)
    {
        try
        {
            using var doc = JsonDocument.Parse(rawJson);
            if (!doc.RootElement.TryGetProperty("Body", out var body) || body.ValueKind != JsonValueKind.String)
                return null;

            var match = TrailingIdPattern.Match(body.GetString() ?? string.Empty);
            return match.Success && int.TryParse(match.Groups[1].Value, out var id) ? id : null;
        }
        catch
        {
            return null;
        }
    }
}