using AmpDAL.CustomCode.RegisterApi;
using AmpDAL.DTO.RegisterApi;
using GB5Shared.DaprCache;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.EventLogPublish;
using GB5Shared.GB5Constant;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Microsoft.Extensions.Logging;
using Microsoft.OpenApi;
using System.Text;
using System.Text.RegularExpressions;

namespace AMPBLL.RegisterApi;

public class RegisterApiBLL(
    IRegisterApiDAL   _dal,
    IValidation       _validation,
    KeyInvalidate     _keyInvalidate,
    AutoNumber        _autoNumber,
    EventLogPublish   _eventLog,
    ILogger<RegisterApiBLL> _logger
) : IRegisterApiBLL
{
    // ─────────────────────────────────────────────────────────────
    // SAVE — inserts all 8 tables in sequence inside one transaction
    // Auto-numbers: API, VERSION, INTERFACE, SCHEMA,
    //               DOMAIN, CAPABILITY, CAPABILITYLINK, POLICY
    // ─────────────────────────────────────────────────────────────
    public async Task<RegisterApiResultDTO> SaveAsync(
        RegisterApiSaveDTO dto, LoginDTO login, CancellationToken ct)
    {
        await _validation.NotEmpty(dto.ApiKey,          nameof(dto.ApiKey));
        await _validation.NotEmpty(dto.ApiName,         nameof(dto.ApiName));
        await _validation.NotEmpty(dto.VersionNumber,   nameof(dto.VersionNumber));
        await _validation.NotEmpty(dto.InterfaceName,   nameof(dto.InterfaceName));
        await _validation.NotEmpty(dto.SchemaName,      nameof(dto.SchemaName));
        await _validation.NotEmpty(dto.DomainName,      nameof(dto.DomainName));
        await _validation.NotEmpty(dto.CapabilityName,  nameof(dto.CapabilityName));

        // 1. Determine which optional tables the frontend supplied data for
        bool hasArtifact     = !string.IsNullOrWhiteSpace(dto.ArtifactTitle);
        bool hasOperation    = !string.IsNullOrWhiteSpace(dto.OperationKey);
        bool hasParameter    = hasOperation && !string.IsNullOrWhiteSpace(dto.ParameterName);
        bool hasPolicy       = !string.IsNullOrWhiteSpace(dto.BillingCategory)
                            || !string.IsNullOrWhiteSpace(dto.PolicySlaReference);

        // 2. Generate required auto-numbers
        var apiNo     = await _autoNumber.GetAutoNumber(1, "API",               login);
        var verNo     = await _autoNumber.GetAutoNumber(1, "APIVERSION",        login);
        var intNo     = await _autoNumber.GetAutoNumber(1, "APIINTERFACE",      login);
        var schNo     = await _autoNumber.GetAutoNumber(1, "APISCHEMA",         login);
        var capNo     = await _autoNumber.GetAutoNumber(1, "APICAPABILITY",     login);
        var linkNo    = await _autoNumber.GetAutoNumber(1, "APICAPABILITYLINK", login);

        int apiId               = apiNo.StartNumber;
        int apiVersionId        = verNo.StartNumber;
        int apiInterfaceId      = intNo.StartNumber;
        int apiSchemaId         = schNo.StartNumber;
        int apiCapabilityId     = capNo.StartNumber;
        int apiCapabilityLinkId = linkNo.StartNumber;

        int apiDomainId;

        // 3. Generate optional auto-numbers only when data is provided
        int? apiArtifactId       = null;
        int? apiOperationId      = null;
        int? parameterId         = null;
        int? apiExposurePolicyId = null;

        if (hasArtifact)
        {
            var artNo        = await _autoNumber.GetAutoNumber(1, "APIARTIFACT",       login);
            apiArtifactId    = artNo.StartNumber;
        }
        if (hasOperation)
        {
            var operNo       = await _autoNumber.GetAutoNumber(1, "APIOPERATION",      login);
            apiOperationId   = operNo.StartNumber;
        }
        if (hasParameter)
        {
            var paramNo      = await _autoNumber.GetAutoNumber(1, "APIPARAMETER",      login);
            parameterId      = paramNo.StartNumber;
        }
        if (hasPolicy)
        {
            var polNo        = await _autoNumber.GetAutoNumber(1, "APIEXPOSUREPOLICY", login);
            apiExposurePolicyId = polNo.StartNumber;
        }

        dto.ApiId = apiId;

        // Insert order respects FK chain:
        // API first (APIDOMAINID = -1 placeholder) → DOMAIN (FK → MAPI) →
        // patch MAPI.APIDOMAINID → VERSION → INTERFACE → SCHEMA →
        // CAPABILITY → CAPABILITYLINK → (optional) OPERATION → PARAMETER → POLICY

        // 2. API Master — inserted first with placeholder APIDOMAINID = -1
        await _dal.InsertApiAsync(dto, -1, login, ct).ConfigureAwait(false);

        // 3. Domain — reuse the existing module domain row, or create it if this
        //    is the first API registered for that domain name
        apiDomainId = await ResolveDomainIdAsync(dto, apiId, login, ct).ConfigureAwait(false);

        // 4. Patch API.APIDOMAINID with the real domain id
        await _dal.UpdateApiDomainIdAsync(apiId, apiDomainId, login, ct).ConfigureAwait(false);

        // 5. Version
        await _dal.InsertVersionAsync(dto, apiVersionId, apiId, login, ct).ConfigureAwait(false);

        // 6. Interface
        await _dal.InsertInterfaceAsync(dto, apiInterfaceId, apiId, apiVersionId, login, ct).ConfigureAwait(false);

        // 7. Schema
        await _dal.InsertSchemaAsync(dto, apiSchemaId, apiId, apiInterfaceId, login, ct).ConfigureAwait(false);

        // 8. Capability
        await _dal.InsertCapabilityAsync(dto, apiCapabilityId, apiId, apiDomainId, login, ct).ConfigureAwait(false);

        // 9. Capability Link
        await _dal.InsertCapabilityLinkAsync(apiCapabilityLinkId, apiId, apiCapabilityId, login, ct).ConfigureAwait(false);

        // 10. Artifact (optional)
        if (hasArtifact && apiArtifactId.HasValue)
            await _dal.InsertArtifactAsync(dto, apiArtifactId.Value, apiId, login, ct).ConfigureAwait(false);

        // 11. Operation (optional)
        if (hasOperation && apiOperationId.HasValue)
        {
            await _dal.InsertOperationAsync(dto, apiOperationId.Value, apiId, apiInterfaceId, login, ct).ConfigureAwait(false);

            // 11. Parameter (optional — requires Operation)
            if (hasParameter && parameterId.HasValue)
                await _dal.InsertParameterAsync(dto, parameterId.Value, apiOperationId.Value, apiId, login, ct).ConfigureAwait(false);
        }

        // 12. Exposure Policy (optional)
        if (hasPolicy && apiExposurePolicyId.HasValue)
            await _dal.InsertExposurePolicyAsync(dto, apiExposurePolicyId.Value, apiId, login, ct).ConfigureAwait(false);

        // Invalidate cache
        var cacheKey = new CacheKeyGeneration().KeyGeneration(
            login.ClientId.ToString(),
            Constant.EntityConstant.OBJECTAMPAPI,
            Constant.CacheKeyLevel.CLIENT_LEVEL,
            login);

        try { await _keyInvalidate.InvalidateCache(cacheKey); }
        catch (Exception ex) { _logger.LogWarning(ex, "Cache invalidation failed for RegisterApi {ApiId}", apiId); }

        // 12. Audit event
        try
        {
            await _eventLog.PublishEventLogAsync(
                "AMP API Registered (Full)",
                new { dto.ApiKey, dto.ApiName, apiId },
                Constant.EventTypeConstant.SAVEDOCUMENTSET,
                0,
                login);
        }
        catch (Exception ex) { _logger.LogWarning(ex, "Event publish failed for RegisterApi {ApiId}", apiId); }

        return new RegisterApiResultDTO
        {
            ApiId               = apiId,
            ApiVersionId        = apiVersionId,
            ApiInterfaceId      = apiInterfaceId,
            ApiSchemaId         = apiSchemaId,
            ApiDomainId         = apiDomainId,
            ApiCapabilityId     = apiCapabilityId,
            ApiCapabilityLinkId = apiCapabilityLinkId,
            ApiArtifactId       = apiArtifactId,
            ApiOperationId      = apiOperationId,
            ParameterId         = parameterId,
            ApiExposurePolicyId = apiExposurePolicyId
        };
    }

    // ─────────────────────────────────────────────────────────────
    // RESOLVE DOMAIN — reuse an existing MAPIDOMAIN row by name
    // (e.g. one shared per module) or create it on first use
    // ─────────────────────────────────────────────────────────────
    public async Task<int> ResolveDomainIdAsync(
        RegisterApiSaveDTO dto, int apiId, LoginDTO login, CancellationToken ct)
    {
        int? existingDomainId = await _dal.GetDomainIdByNameAsync(dto.DomainName, login.ClientId, login, ct)
                                           .ConfigureAwait(false);
        if (existingDomainId.HasValue)
            return existingDomainId.Value;

        var domNo    = await _autoNumber.GetAutoNumber(1, "APIDOMAIN", login);
        int domainId = domNo.StartNumber;
        await _dal.InsertDomainAsync(dto, domainId, apiId, login, ct).ConfigureAwait(false);
        return domainId;
    }

    public async Task RepointCapabilityDomainAsync(int apiId, int apiDomainId, LoginDTO login, CancellationToken ct)
        => await _dal.UpdateCapabilityDomainIdAsync(apiId, apiDomainId, login, ct).ConfigureAwait(false);

    public async Task<int> CleanupOrphanedDomainsAsync(LoginDTO login, CancellationToken ct)
        => await _dal.DeleteOrphanedDomainsAsync(login.ClientId, login, ct).ConfigureAwait(false);

    // ─────────────────────────────────────────────────────────────
    // GET SINGLE
    // ─────────────────────────────────────────────────────────────
    public async Task<RegisterApiDTO?> GetAsync(int apiId, LoginDTO login, CancellationToken ct)
    {
        try
        {
            return await _dal.GetAsync(apiId, login.ClientId, login, ct).ConfigureAwait(false);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "GetRegisterApi failed for ApiId {ApiId}", apiId);
            throw;
        }
    }

    // ─────────────────────────────────────────────────────────────
    // LIST
    // ─────────────────────────────────────────────────────────────
    public async Task<List<RegisterApiDTO>> GetListAsync(LoginDTO login, CancellationToken ct)
    {
        try
        {
            return await _dal.GetListAsync(login.ClientId, login, ct).ConfigureAwait(false);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "GetRegisterApiList failed for TenantId {TenantId}", login.ClientId);
            throw;
        }
    }

    // ─────────────────────────────────────────────────────────────
    // PICKLIST
    // ─────────────────────────────────────────────────────────────
    public async Task<List<RegisterApiSelectDTO>> GetSelectListAsync(LoginDTO login, CancellationToken ct)
    {
        try
        {
            return await _dal.GetSelectListAsync(login.ClientId, login, ct).ConfigureAwait(false);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "GetSelectListRegisterApi failed for TenantId {TenantId}", login.ClientId);
            throw;
        }
    }

    // ─────────────────────────────────────────────────────────────
    // PARSE SPEC — decode Base64 → parse OpenAPI → return pre-filled
    // RegisterApiSaveDTO covering all 11 table fields (no DB write)
    // ─────────────────────────────────────────────────────────────
    public async Task<RegisterApiSaveDTO> ParseSpecAsync(string base64Content, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(base64Content))
            throw new ArgumentException("Base64Content is required.");

        byte[] bytes   = Convert.FromBase64String(base64Content);
        string rawText = Encoding.UTF8.GetString(bytes).Trim();

        // ── Route 1: simple key=value txt file ───────────────────────
        // ApiUrl=https://...
        // RequestJson=...  (optional)
        // ResponseJson=... (optional)
        if (rawText.StartsWith("ApiUrl=", StringComparison.OrdinalIgnoreCase)
            || rawText.Contains("\nApiUrl=", StringComparison.OrdinalIgnoreCase))
        {
            var lines       = rawText.Split('\n', StringSplitOptions.RemoveEmptyEntries);
            string? apiUrl  = ReadLine(lines, "ApiUrl");
            string? reqJson = ReadLine(lines, "RequestJson");
            string? resJson = ReadLine(lines, "ResponseJson");

            if (!string.IsNullOrWhiteSpace(apiUrl))
            {
                var parsed = await ParseUrlAsync(apiUrl, reqJson, resJson, ct);
                ApplyTxtOverrides(lines, parsed);
                return parsed;
            }
        }

        // Try to parse as OpenAPI; fall back to plain-text defaults if invalid
        var result = OpenApiDocument.Parse(rawText);
        var doc    = result.Document;
        bool isValid = result.Diagnostic.Errors.Count == 0 && doc?.Info != null;

        var dto = new RegisterApiSaveDTO();

        if (isValid)
        {
            var info = doc!.Info;

            // ── MAPI ─────────────────────────────────────────────
            dto.ApiName         = info.Title ?? "Unnamed API";
            dto.ApiKey          = Sanitize(info.Title ?? "UNNAMED_API");
            dto.Description     = info.Description;
            dto.AboutApi        = info.Description;
            dto.OwnerTeam       = info.Contact?.Name;
            dto.BusinessOwner   = info.Contact?.Email;
            dto.RepoUrl         = info.Contact?.Url?.ToString();
            dto.SlaReference    = info.TermsOfService?.ToString();
            dto.ApiKind         = 0;   // Internal
            dto.ApiType         = 0;   // REST
            dto.Criticality     = 1;   // Medium
            dto.LifecycleStatus = 1;   // Active
            dto.VisibilityLevel = 1;   // Internal
            dto.ApiProviderType = 0;
            dto.ExposureType    = 0;
            dto.RuntimeType     = 1;   // FastEndpoints
            dto.Technology      = 0;   // .NET
            dto.ApiSortOrder    = 9999;

            // ── MAPIVERSION ───────────────────────────────────────
            dto.VersionNumber      = string.IsNullOrWhiteSpace(info.Version) ? "1.0.0" : info.Version;
            dto.VersionStatus      = 0;   // Draft
            dto.IsBreaking         = 0;
            dto.VersionFromDate    = DateTime.UtcNow;
            dto.VersionDescription = $"Auto-parsed from spec: {info.Title}";
            dto.VersionSortOrder   = 9999;

            // ── First server → SpecLocation ───────────────────────
            string? firstServer = doc.Servers?.FirstOrDefault()?.Url;

            // ── MAPIINTERFACE — from first path+operation ─────────
            var firstPath = doc.Paths?.FirstOrDefault();
            var firstOp   = firstPath?.Value.Operations.FirstOrDefault();
            dto.InterfaceName        = firstOp.HasValue
                ? (firstOp.Value.Value.OperationId ?? $"{firstOp.Value.Key} {firstPath!.Value.Key}")
                : (info.Title + " Interface");
            dto.InterfaceType        = 0;   // REST
            dto.SpecType             = 0;   // OpenAPI
            dto.InterfaceMethod      = firstOp.HasValue ? HttpMethodByte(firstOp.Value.Key.ToString()) : (byte)0;
            dto.ApiPath              = firstPath?.Key ?? "/";
            dto.InterfaceDescription = firstOp?.Value.Summary ?? info.Description;
            dto.SpecLocation         = firstServer ?? "/";
            dto.InterfaceSortOrder   = 9999;

            // ── MAPISCHEMA — raw spec as RequestJson ──────────────
            dto.SchemaName          = info.Title + " Schema";
            dto.SchemaDescription   = "Auto-generated from OpenAPI spec";
            dto.SpecificationSource = 0;   // URL
            dto.SpecificationUrl    = firstServer;
            dto.RequestJson         = rawText.Length > 4000 ? rawText[..4000] : rawText;
            dto.ResponseJson        = BuildResponseJson(doc);
            dto.SchemaSortOrder     = 9999;

            // ── MAPIDOMAIN — from first tag ───────────────────────
            string? firstTag = doc.Tags?.FirstOrDefault()?.Name
                            ?? firstOp?.Value.Tags?.FirstOrDefault()?.Name;
            dto.DomainName    = firstTag ?? (info.Title + " Domain");
            dto.DomainRemarks = firstTag != null ? $"Tag: {firstTag}" : null;
            dto.DomainSortOrder = 9999;

            // ── MAPICAPABILITY — from first tag description ───────
            string? tagDesc = doc.Tags?.FirstOrDefault()?.Description;
            dto.CapabilityName        = firstTag ?? (info.Title + " Capability");
            dto.CapabilityDescription = tagDesc ?? info.Description;
            dto.CapabilitySortOrder   = 9999;

            // ── MAPIOPERATION — first operation ───────────────────
            if (firstOp.HasValue)
            {
                dto.OperationKey      = firstOp.Value.Value.OperationId ?? Sanitize(firstPath!.Value.Key);
                dto.OperationMethod   = firstOp.Value.Key.ToString().ToUpperInvariant();
                dto.OperationPath     = firstPath!.Value.Key;
                dto.OperationSummary  = firstOp.Value.Value.Summary;
                dto.OperationSortOrder = 1;

                // ── MAPIPARAMETER — first parameter of first operation ──
                var firstParam = firstOp.Value.Value.Parameters?.FirstOrDefault();
                if (firstParam != null)
                {
                    dto.ParameterName       = firstParam.Name;
                    dto.ParameterInLocation = firstParam.In?.ToString() ?? "Query";
                    dto.ParameterDataType   = firstParam.Schema?.Type?.ToString().ToLowerInvariant() ?? "string";
                    dto.ParameterIsRequired = (byte)(firstParam.Required ? 1 : 0);
                    dto.ParameterSortOrder  = 1;
                }
            }

            // ── MAPIEXPOSUREPOLICY — from security schemes ────────
            bool hasSecSchemes = doc.Components?.SecuritySchemes?.Count > 0;
            if (hasSecSchemes)
            {
                dto.BillingCategory    = doc.Components!.SecuritySchemes.Keys.First();
                dto.AllowPartnerAccess = 0;
                dto.AllowPublicAccess  = 0;
                dto.PolicySortOrder    = 9999;
            }

            // ── MAPIARTIFACT — spec file itself as artifact ────────
            dto.ArtifactTitle    = info.Title + " Specification";
            dto.ArtifactType     = 2;   // Specification
            dto.ArtifactLocation = firstServer ?? "uploaded";
            dto.ArtifactRemarks  = "OpenAPI spec";
            dto.ArtifactSortOrder = 9999;
        }
        else
        {
            // Plain text / code file — extract as much as possible using regex
            ExtractFromPlainText(rawText, dto);
        }

        return dto;
    }

    // ─────────────────────────────────────────────────────────────
    // PARSE URL — derive all 11 table fields from just:
    //   ApiUrl  +  optional RequestJson  +  optional ResponseJson
    // ─────────────────────────────────────────────────────────────
    public Task<RegisterApiSaveDTO> ParseUrlAsync(
        string apiUrl, string? requestJson, string? responseJson, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(apiUrl))
            throw new ArgumentException("ApiUrl is required.");

        var uri      = new Uri(apiUrl.TrimEnd('/').Split('?')[0]);   // strip query string
        var segments = uri.AbsolutePath.Trim('/').Split('/')
                          .Where(s => !string.IsNullOrWhiteSpace(s))
                          .ToArray();

        // e.g. /fws/Draft/GetDraftRows  →  ["fws","Draft","GetDraftRows"]
        string server    = $"{uri.Scheme}://{uri.Host}";
        string module    = segments.Length >= 1 ? segments[0] : "API";
        string entity    = segments.Length >= 2 ? segments[1] : module;
        string operation = segments.Length >= 3 ? segments[^1] : (segments.Length >= 1 ? segments[^1] : "Operation");
        string route     = uri.AbsolutePath.TrimEnd('/');

        // Infer HTTP method from operation name prefix
        string httpVerb = operation.StartsWith("Get",    StringComparison.OrdinalIgnoreCase) ? "GET"
                        : operation.StartsWith("Save",   StringComparison.OrdinalIgnoreCase) ? "POST"
                        : operation.StartsWith("Update", StringComparison.OrdinalIgnoreCase) ? "PUT"
                        : operation.StartsWith("Delete", StringComparison.OrdinalIgnoreCase) ? "DELETE"
                        : operation.StartsWith("List",   StringComparison.OrdinalIgnoreCase) ? "GET"
                        : "POST";

        // Parse query string params from original URL
        string? queryString = apiUrl.Contains('?') ? apiUrl.Split('?', 2)[1] : null;
        var queryParams = queryString?
            .Split('&')
            .Select(p => p.Split('='))
            .Where(p => p.Length >= 1 && !string.IsNullOrWhiteSpace(p[0]))
            .Select(p => p[0])
            .ToList() ?? new List<string>();

        string apiKey = Sanitize(operation);
        if (string.IsNullOrWhiteSpace(apiKey)) apiKey = Sanitize(entity);

        var dto = new RegisterApiSaveDTO
        {
            // ── MAPI ──────────────────────────────────────────────
            ApiName          = operation,
            ApiKey           = string.IsNullOrWhiteSpace(apiKey) ? "IMPORTED_API" : apiKey,
            Description      = $"{operation} — {entity} module API",
            AboutApi         = $"Endpoint hosted at {server}, module: {module}, entity: {entity}",
            KeyFeatures      = $"{httpVerb} {route}",
            UseCases         = $"Call {httpVerb} {apiUrl.Split('?')[0]}",
            QuickStart       = queryParams.Count > 0
                               ? $"Pass query params: {string.Join(", ", queryParams)}"
                               : $"POST to {route} with Login header",
            OwnerTeam        = module,
            BusinessOwner    = module,
            RepoUrl          = server,
            ApiKind          = 0,    // Internal
            ApiType          = 0,    // REST
            Criticality      = 1,    // Medium
            LifecycleStatus  = 1,    // Active
            VisibilityLevel  = 1,    // Internal
            ApiProviderType  = 0,
            ExposureType     = 0,
            RuntimeType      = 1,    // FastEndpoints
            Technology       = 0,    // .NET
            RegulatoryImpact = "None",
            RiskRating       = "Low",
            ApiSortOrder     = 9999,

            // ── MAPIVERSION ───────────────────────────────────────
            VersionNumber      = "1.0.0",
            VersionStatus      = 0,
            IsBreaking         = 0,
            VersionFromDate    = DateTime.UtcNow,
            VersionDescription = $"Initial version of {operation}",
            VersionNotes       = $"Auto-parsed from URL: {apiUrl.Split('?')[0]}",
            VersionRemarks     = "Review before activating",
            VersionSortOrder   = 9999,

            // ── MAPIINTERFACE ─────────────────────────────────────
            InterfaceName        = $"{operation} Interface",
            InterfaceType        = 0,    // REST
            SpecType             = 3,    // Manual
            InterfaceMethod      = HttpMethodByte(httpVerb),
            ApiPath              = route,
            InterfaceDescription = $"{httpVerb} {route}",
            SpecLocation         = server,
            InterfaceSource      = "URL import",
            InterfaceRemarks     = $"Server: {server}",
            InterfaceSortOrder   = 9999,

            // ── MAPISCHEMA ────────────────────────────────────────
            SchemaName           = $"{operation} Schema",
            SchemaDescription    = $"Schema for {httpVerb} {route}",
            SpecificationSource  = 0,    // URL
            SpecificationUrl     = $"{server}{route}",
            RequestJson          = string.IsNullOrWhiteSpace(requestJson)
                                   ? BuildDefaultRequest(queryParams)
                                   : requestJson,
            ResponseJson         = string.IsNullOrWhiteSpace(responseJson)
                                   ? $"{{\"Status\":\"Success\",\"Body\":{{\"Data\":null}}}}"
                                   : responseJson,
            SchemaRemarks        = "Auto-generated from URL import",
            SchemaSortOrder      = 9999,

            // ── MAPIDOMAIN ────────────────────────────────────────
            DomainName    = entity,
            DomainRemarks = $"Domain: {entity}, Module: {module}",
            DomainSortOrder = 9999,

            // ── MAPICAPABILITY ────────────────────────────────────
            CapabilityName        = $"{entity} Capability",
            CapabilityDescription = $"Manages {entity} operations in {module}",
            CapabilityRemarks     = $"Endpoint: {route}",
            CapabilitySortOrder   = 9999,

            // ── MAPIARTIFACT ──────────────────────────────────────
            ArtifactTitle    = $"{operation} API Specification",
            ArtifactType     = 2,    // Specification
            ArtifactLocation = $"{server}{route}",
            ArtifactRemarks  = $"Auto-imported from {server}",
            ArtifactSortOrder = 9999,

            // ── MAPIOPERATION ─────────────────────────────────────
            OperationKey      = operation,
            OperationMethod   = httpVerb,
            OperationPath     = route,
            OperationSummary  = $"{httpVerb} {route}",
            OperationSortOrder = 1,

            // ── MAPIEXPOSUREPOLICY ────────────────────────────────
            AllowPartnerAccess = 0,
            AllowPublicAccess  = 0,
            BillingCategory    = "Internal",
            PolicySlaReference = "Standard-SLA-99.9",
            PolicyRemarks      = $"Endpoint: {server}{route}",
            PolicySortOrder    = 9999
        };

        // ── MAPIPARAMETER — first query param (if any) ─────────────
        if (queryParams.Count > 0)
        {
            dto.ParameterName       = queryParams[0];
            dto.ParameterInLocation = "Query";
            dto.ParameterDataType   = InferParamType(queryParams[0], apiUrl);
            dto.ParameterIsRequired = 1;
            dto.ParameterSortOrder  = 1;
        }

        return Task.FromResult(dto);
    }

    private static string BuildDefaultRequest(List<string> queryParams)
    {
        if (queryParams.Count == 0) return "{}";
        var pairs = queryParams.Select(p => $"\"{p}\": \"\"");
        return "{" + string.Join(", ", pairs) + "}";
    }

    private static string InferParamType(string name, string url)
    {
        // Check actual value in URL for type hint
        string? val = url.Contains('?')
            ? url.Split('?', 2)[1].Split('&')
                 .Where(p => p.StartsWith(name + "=", StringComparison.OrdinalIgnoreCase))
                 .Select(p => p.Split('=', 2).ElementAtOrDefault(1))
                 .FirstOrDefault()
            : null;

        if (val != null && int.TryParse(val, out _))    return "integer";
        if (val != null && Guid.TryParse(val, out _))   return "string (guid)";

        string lower = name.ToLowerInvariant();
        if (lower.EndsWith("id") || lower.EndsWith("no") || lower.EndsWith("count")) return "integer";
        if (lower.Contains("date") || lower.Contains("from") || lower.Contains("to"))  return "string (date)";
        if (lower.Contains("guid") || lower.Contains("session") || lower.Contains("token")) return "string (guid)";
        return "string";
    }

    // ─────────────────────────────────────────────────────────────
    // Apply every key=value from the txt file onto the DTO.
    // If a key is present → use txt value.
    // If a key is absent  → keep the auto-generated value.
    // ─────────────────────────────────────────────────────────────
    private static void ApplyTxtOverrides(string[] lines, RegisterApiSaveDTO d)
    {
        // helpers
        string? S(params string[] keys)  { foreach (var k in keys) { var v = ReadLine(lines, k); if (v != null) return v; } return null; }
        byte?   B(params string[] keys)  { var v = S(keys); return v != null && byte.TryParse(v,  out var r) ? r : null; }
        short?  H(params string[] keys)  { var v = S(keys); return v != null && short.TryParse(v, out var r) ? r : null; }
        int?    I(params string[] keys)  { var v = S(keys); return v != null && int.TryParse(v,   out var r) ? r : null; }
        DateTime? D(params string[] keys){ var v = S(keys); return v != null && DateTime.TryParse(v, out var r) ? r : null; }

        // ── MAPI ──────────────────────────────────────────────────
        var apiKey = S("API Key","ApiKey","APIKEY");
        if (apiKey != null)            d.ApiKey            = Sanitize(apiKey);
        var apiName = S("ApiName","API Name","Name");
        if (apiName != null)           d.ApiName           = apiName;
        var desc = S("Description","Desc");
        if (desc != null)              d.Description       = desc;
        var about = S("About API","AboutApi","About");
        if (about != null)             d.AboutApi          = about;
        var feat = S("KeyFeatures","Key Features","Features");
        if (feat != null)              d.KeyFeatures       = feat;
        var uc = S("UseCases","Use Cases","UseCase");
        if (uc != null)                d.UseCases          = uc;
        var qs = S("QuickStart","Quick Start");
        if (qs != null)                d.QuickStart        = qs;
        var kind = B("ApiKind","Api Kind");
        if (kind != null)              d.ApiKind           = kind.Value;
        var atype = B("ApiType","Api Type");
        if (atype != null)             d.ApiType           = atype.Value;
        var crit = B("Criticality");
        if (crit != null)              d.Criticality       = crit.Value;
        var lc = B("LifecycleStatus","Lifecycle Status","Lifecycle");
        if (lc != null)                d.LifecycleStatus   = lc.Value;
        var vis = B("VisibilityLevel","Visibility Level","Visibility");
        if (vis != null)               d.VisibilityLevel   = vis.Value;
        var apt = B("ApiProviderType","Provider Type");
        if (apt != null)               d.ApiProviderType   = apt.Value;
        var et = B("ExposureType","Exposure Type","Exposure");
        if (et != null)                d.ExposureType      = et.Value;
        var hl = B("IsHeadlessEnabled","Headless");
        if (hl != null)                d.IsHeadlessEnabled = hl.Value;
        var ot = S("OwnerTeam","Owner Team","Owner");
        if (ot != null)                { d.OwnerTeam = ot; d.BusinessOwner = ot; }
        var bo = S("BusinessOwner","Business Owner");
        if (bo != null)                d.BusinessOwner     = bo;
        var rt = B("RuntimeType","Runtime Type","Runtime");
        if (rt != null)                d.RuntimeType       = rt.Value;
        var tech = B("Technology");
        if (tech != null)              d.Technology        = tech.Value;
        var repo = S("RepoUrl","Repo URL","Repo");
        if (repo != null)              d.RepoUrl           = repo;
        var pipe = S("PipelineRef","Pipeline");
        if (pipe != null)              d.PipelineRef       = pipe;
        var prov = I("ProviderId","Provider Id");
        if (prov != null)              d.ProviderId        = prov.Value;
        var cat = I("CategoryId","Category Id");
        if (cat != null)               d.CategoryId        = cat.Value;
        var geo = S("Geography","Geo");
        if (geo != null)               d.Geography         = geo;
        var reg = S("RegulatoryImpact","Regulatory Impact","Regulatory");
        if (reg != null)               d.RegulatoryImpact  = reg;
        var risk = S("RiskRating","Risk Rating","Risk");
        if (risk != null)              d.RiskRating        = risk;
        var sla = S("SlaReference","SLA Reference","SLA");
        if (sla != null)               d.SlaReference      = sla;
        var aremark = S("ApiRemarks","Api Remarks");
        if (aremark != null)           d.ApiRemarks        = aremark;
        var aso = H("ApiSortOrder","Api Sort Order");
        if (aso != null)               d.ApiSortOrder      = aso.Value;

        // ── MAPIVERSION ───────────────────────────────────────────
        var vn = S("VersionNumber","Version Number","Version");
        if (vn != null)                d.VersionNumber      = vn;
        var vs = B("VersionStatus","Version Status");
        if (vs != null)                d.VersionStatus      = vs.Value;
        var ib = B("IsBreaking","Breaking");
        if (ib != null)                d.IsBreaking         = ib.Value;
        var vfd = D("VersionFromDate","Version From Date","From Date");
        if (vfd != null)               d.VersionFromDate    = vfd.Value;
        var vtd = D("VersionToDate","Version To Date","To Date");
        if (vtd != null)               d.VersionToDate      = vtd;
        var sd = D("SunsetDate","Sunset Date","Sunset");
        if (sd != null)                d.SunsetDate         = sd;
        var vn2 = S("VersionNotes","Version Notes");
        if (vn2 != null)               d.VersionNotes       = vn2;
        var vd = S("VersionDescription","Version Description");
        if (vd != null)                d.VersionDescription = vd;
        var vr = S("VersionRemarks","Version Remarks");
        if (vr != null)                d.VersionRemarks     = vr;
        var vso = H("VersionSortOrder","Version Sort Order");
        if (vso != null)               d.VersionSortOrder   = vso.Value;

        // ── MAPIINTERFACE ─────────────────────────────────────────
        var iname = S("InterfaceName","Interface Name","Interface");
        if (iname != null)             d.InterfaceName        = iname;
        var itype = B("InterfaceType","Interface Type");
        if (itype != null)             d.InterfaceType        = itype.Value;
        var st = B("SpecType","Spec Type");
        if (st != null)                d.SpecType             = st.Value;
        var im = S("InterfaceMethod","Method","HTTP Method");
        if (im != null)                d.InterfaceMethod      = HttpMethodByte(im);
        var ap = S("ApiPath","Api Path","Path");
        if (ap != null)                d.ApiPath              = ap;
        var idesc = S("InterfaceDescription","Interface Description");
        if (idesc != null)             d.InterfaceDescription = idesc;
        var sl = S("SpecLocation","Spec Location","Spec URL");
        if (sl != null)                d.SpecLocation         = sl;
        var isrc = S("InterfaceSource","Interface Source");
        if (isrc != null)              d.InterfaceSource      = isrc;
        var iremark = S("InterfaceRemarks","Interface Remarks");
        if (iremark != null)           d.InterfaceRemarks     = iremark;
        var iso = H("InterfaceSortOrder","Interface Sort Order");
        if (iso != null)               d.InterfaceSortOrder   = iso.Value;

        // ── MAPISCHEMA ────────────────────────────────────────────
        var sname = S("SchemaName","Schema Name","Schema");
        if (sname != null)             d.SchemaName           = sname;
        var sdesc = S("SchemaDescription","Schema Description");
        if (sdesc != null)             d.SchemaDescription    = sdesc;
        var ss = B("SpecificationSource","Specification Source");
        if (ss != null)                d.SpecificationSource  = ss.Value;
        var surl = S("SpecificationUrl","Specification URL","Spec URL");
        if (surl != null)              d.SpecificationUrl     = surl;
        var rj = S("RequestJson","Request Json","Request");
        if (rj != null && rj != "{}")  d.RequestJson          = rj;
        var rsj = S("ResponseJson","Response Json","Response");
        if (rsj != null && rsj != "{}") d.ResponseJson        = rsj;
        var sremark = S("SchemaRemarks","Schema Remarks");
        if (sremark != null)           d.SchemaRemarks        = sremark;
        var sso = H("SchemaSortOrder","Schema Sort Order");
        if (sso != null)               d.SchemaSortOrder      = sso.Value;

        // ── MAPIDOMAIN ────────────────────────────────────────────
        var dn = S("DomainName","Domain Name","Domain");
        if (dn != null)                { d.DomainName = dn; d.CapabilityName = dn + " Capability"; }
        var dremark = S("DomainRemarks","Domain Remarks");
        if (dremark != null)           d.DomainRemarks        = dremark;
        var dso = H("DomainSortOrder","Domain Sort Order");
        if (dso != null)               d.DomainSortOrder      = dso.Value;

        // ── MAPICAPABILITY ────────────────────────────────────────
        var cn = S("CapabilityName","Capability Name","Capability");
        if (cn != null)                d.CapabilityName       = cn;
        var cdesc = S("CapabilityDescription","Capability Description");
        if (cdesc != null)             d.CapabilityDescription = cdesc;
        var cremark = S("CapabilityRemarks","Capability Remarks");
        if (cremark != null)           d.CapabilityRemarks    = cremark;
        var cso = H("CapabilitySortOrder","Capability Sort Order");
        if (cso != null)               d.CapabilitySortOrder  = cso.Value;

        // ── MAPIARTIFACT ──────────────────────────────────────────
        var at2 = S("ArtifactTitle","Artifact Title","Artifact");
        if (at2 != null)               d.ArtifactTitle        = at2;
        var aty = B("ArtifactType","Artifact Type");
        if (aty != null)               d.ArtifactType         = aty.Value;
        var aloc = S("ArtifactLocation","Artifact Location");
        if (aloc != null)              d.ArtifactLocation     = aloc;
        var aremark2 = S("ArtifactRemarks","Artifact Remarks");
        if (aremark2 != null)          d.ArtifactRemarks      = aremark2;
        var artso = H("ArtifactSortOrder","Artifact Sort Order");
        if (artso != null)             d.ArtifactSortOrder    = artso.Value;

        // ── MAPIOPERATION ─────────────────────────────────────────
        var ok = S("OperationKey","Operation Key","Operation");
        if (ok != null)                d.OperationKey         = ok;
        var om = S("OperationMethod","Operation Method");
        if (om != null)                d.OperationMethod      = om.ToUpperInvariant();
        var op2 = S("OperationPath","Operation Path");
        if (op2 != null)               d.OperationPath        = op2;
        var osum = S("OperationSummary","Operation Summary");
        if (osum != null)              d.OperationSummary     = osum;
        var oso = H("OperationSortOrder","Operation Sort Order");
        if (oso != null)               d.OperationSortOrder   = oso.Value;

        // ── MAPIPARAMETER ─────────────────────────────────────────
        var pname = S("ParameterName","Parameter Name","Parameter");
        if (pname != null)             d.ParameterName        = pname;
        var pin = S("ParameterInLocation","Parameter In","In Location");
        if (pin != null)               d.ParameterInLocation  = pin;
        var pdt = S("ParameterDataType","Parameter DataType","DataType","Data Type");
        if (pdt != null)               d.ParameterDataType    = pdt;
        var pir = B("ParameterIsRequired","Parameter Required","Required");
        if (pir != null)               d.ParameterIsRequired  = pir.Value;
        var pso = H("ParameterSortOrder","Parameter Sort Order");
        if (pso != null)               d.ParameterSortOrder   = pso.Value;

        // ── MAPIEXPOSUREPOLICY ────────────────────────────────────
        var apa = B("AllowPartnerAccess","Allow Partner","Partner Access");
        if (apa != null)               d.AllowPartnerAccess   = apa.Value;
        var apub = B("AllowPublicAccess","Allow Public","Public Access");
        if (apub != null)              d.AllowPublicAccess    = apub.Value;
        var bc = S("BillingCategory","Billing Category","Billing");
        if (bc != null)                d.BillingCategory      = bc;
        var psla = S("PolicySlaReference","Policy SLA","Policy Sla Reference");
        if (psla != null)              d.PolicySlaReference   = psla;
        var premark = S("PolicyRemarks","Policy Remarks");
        if (premark != null)           d.PolicyRemarks        = premark;
        var polso = H("PolicySortOrder","Policy Sort Order");
        if (polso != null)             d.PolicySortOrder      = polso.Value;
    }

    private static string? ReadLine(string[] lines, string key)
    {
        // Normalise key for comparison: lowercase, remove spaces and underscores
        string normKey = Normalise(key);

        var line = lines.FirstOrDefault(l =>
        {
            int eq = l.IndexOf('=');
            if (eq < 0) return false;
            return Normalise(l[..eq]) == normKey;
        });

        if (line == null) return null;
        var val = line[(line.IndexOf('=') + 1)..].Trim();
        return string.IsNullOrWhiteSpace(val) || val.Equals("null", StringComparison.OrdinalIgnoreCase)
            ? null : val;
    }

    private static string Normalise(string s)
        => s.Trim().ToLowerInvariant()
             .Replace(" ", "")
             .Replace("_", "")
             .Replace("-", "");

    private static void ExtractFromPlainText(string text, RegisterApiSaveDTO dto)
    {
        // ── Extract key identifiers from the file ─────────────────────

        // Namespace  →  "namespace Admin.OrganizationUnit" or "namespace AdminSL.EndPoints.OrganizationUnit"
        string? ns        = NE(Regex.Match(text, @"namespace\s+([\w\.]+)").Groups[1].Value);
        string? nsPart    = ns?.Split('.').LastOrDefault();
        string? nsModule  = ns?.Split('.').FirstOrDefault();

        string? className = NE(Regex.Match(text, @"public\s+class\s+(\w+)").Groups[1].Value);

        var routeMatch    = Regex.Match(text, @"(Get|Post|Put|Patch|Delete)\s*\(\s*""([^""]+)""");
        string? httpVerb  = NE(routeMatch.Groups[1].Value);
        string? route     = NE(routeMatch.Groups[2].Value);

        var paramMatches   = Regex.Matches(text, @"\[property:\s*(?:QueryParam|FromBody)\]\s+(\w+)\s+(\w+)");
        string? firstParamType = paramMatches.Count > 0 ? NE(paramMatches[0].Groups[1].Value) : null;
        string? firstParamName = paramMatches.Count > 0 ? NE(paramMatches[0].Groups[2].Value) : null;

        string? bllName   = NE(Regex.Match(text, @"I(\w+BLL)").Groups[1].Value);

        // Derive a clean title
        string title      = className ?? nsPart ?? "Imported API";
        string apiKey     = Sanitize(title);
        string module     = nsModule ?? "Imported";

        // ── MAPI ─────────────────────────────────────────────────────
        dto.ApiName          = title;
        dto.ApiKey           = string.IsNullOrWhiteSpace(apiKey) ? "IMPORTED_API" : apiKey;
        dto.Description      = $"{title} — auto-imported from source file";
        dto.AboutApi         = $"Source module: {ns ?? "unknown"}";
        dto.KeyFeatures      = bllName != null ? $"Business logic via {bllName}" : "Imported endpoint";
        dto.UseCases         = route   != null ? $"HTTP {httpVerb} {route}" : "See source file";
        dto.QuickStart       = route   != null ? $"Call {httpVerb?.ToUpper()} {route} with Login header" : "See source file";
        dto.OwnerTeam        = nsModule ?? "Imported";
        dto.BusinessOwner    = nsModule ?? "Imported";
        dto.ApiKind          = 0;    // Internal
        dto.ApiType          = 0;    // REST
        dto.Criticality      = 1;    // Medium
        dto.LifecycleStatus  = 1;    // Active
        dto.VisibilityLevel  = 1;    // Internal
        dto.ApiProviderType  = 0;
        dto.ExposureType     = 0;
        dto.IsHeadlessEnabled = 0;
        dto.RuntimeType      = 1;    // FastEndpoints
        dto.Technology       = 0;    // .NET
        dto.RepoUrl          = $"https://github.com/org/{module}";
        dto.RegulatoryImpact = "None";
        dto.RiskRating       = "Low";
        dto.ApiSortOrder     = 9999;

        // ── MAPIVERSION ───────────────────────────────────────────────
        dto.VersionNumber      = "1.0.0";
        dto.VersionStatus      = 0;    // Draft
        dto.IsBreaking         = 0;
        dto.VersionFromDate    = DateTime.UtcNow;
        dto.VersionDescription = $"Initial version of {title}";
        dto.VersionNotes       = "Auto-parsed from source file";
        dto.VersionRemarks     = "Review before activating";
        dto.VersionSortOrder   = 9999;

        // ── MAPIINTERFACE ─────────────────────────────────────────────
        dto.InterfaceName        = title + " Interface";
        dto.InterfaceType        = 0;    // REST
        dto.SpecType             = 3;    // Manual
        dto.InterfaceMethod      = HttpMethodByte(httpVerb ?? "GET");
        dto.ApiPath              = route ?? $"/{title}";
        dto.InterfaceDescription = $"{httpVerb?.ToUpper() ?? "GET"} endpoint for {title}";
        dto.SpecLocation         = route ?? $"/{title}";
        dto.InterfaceSource      = "Source file import";
        dto.InterfaceRemarks     = $"Parsed from {ns ?? "unknown namespace"}";
        dto.InterfaceSortOrder   = 9999;

        // ── MAPISCHEMA ────────────────────────────────────────────────
        dto.SchemaName           = title + " Schema";
        dto.SchemaDescription    = $"Request/response schema for {title}";
        dto.SpecificationSource  = 2;    // RepoPath
        dto.SpecificationUrl     = route ?? $"/{title}";
        dto.RequestJson          = text.Length > 4000 ? text[..4000] : text;
        dto.ResponseJson         = $"{{\"Status\":\"Success\",\"Body\":{{\"Data\":null}}}}";
        dto.SchemaRemarks        = "Auto-generated from source file import";
        dto.SchemaSortOrder      = 9999;

        // ── MAPIDOMAIN ────────────────────────────────────────────────
        dto.DomainName    = nsPart ?? (module + " Domain");
        dto.DomainRemarks = $"Domain extracted from namespace: {ns ?? "unknown"}";
        dto.DomainSortOrder = 9999;

        // ── MAPICAPABILITY ────────────────────────────────────────────
        dto.CapabilityName        = title + " Capability";
        dto.CapabilityDescription = $"Manages {title} operations";
        dto.CapabilityRemarks     = bllName != null ? $"Backed by {bllName}" : "Auto-generated";
        dto.CapabilitySortOrder   = 9999;

        // ── MAPIARTIFACT ──────────────────────────────────────────────
        dto.ArtifactTitle    = title + " Source File";
        dto.ArtifactType     = 2;    // Specification
        dto.ArtifactLocation = route ?? $"/{title}";
        dto.ArtifactRemarks  = $"Source: {ns ?? "imported file"}";
        dto.ArtifactSortOrder = 9999;

        // ── MAPIOPERATION (if route detected) ─────────────────────────
        if (route != null)
        {
            dto.OperationKey      = className ?? Sanitize(route);
            dto.OperationMethod   = httpVerb?.ToUpper() ?? "GET";
            dto.OperationPath     = route;
            dto.OperationSummary  = $"{httpVerb?.ToUpper() ?? "GET"} {route}";
            dto.OperationSortOrder = 1;
        }

        // ── MAPIPARAMETER (first QueryParam detected) ─────────────────
        if (firstParamName != null)
        {
            dto.ParameterName       = firstParamName;
            dto.ParameterInLocation = "Query";
            dto.ParameterDataType   = MapCsharpType(firstParamType ?? "string");
            dto.ParameterIsRequired = 1;
            dto.ParameterSortOrder  = 1;
        }

        // ── MAPIEXPOSUREPOLICY ────────────────────────────────────────
        bool hasAuth = text.Contains("Roles(") || text.Contains("[Authorize") || text.Contains("RequireAuthorization");
        dto.AllowPartnerAccess = 0;
        dto.AllowPublicAccess  = (byte)(text.Contains("AllowAnonymous") ? 1 : 0);
        dto.BillingCategory    = hasAuth ? "Authenticated" : "Public";
        dto.PolicySlaReference = "Standard-SLA-99.9";
        dto.PolicyRemarks      = hasAuth ? "Requires authentication" : "Public endpoint — no auth required";
        dto.PolicySortOrder    = 9999;
    }

    private static string? NE(string? s)
        => string.IsNullOrWhiteSpace(s) ? null : s;

    private static string MapCsharpType(string csType) => csType.ToLowerInvariant() switch
    {
        "int" or "int32" or "long" or "int64" => "integer",
        "bool" or "boolean"                   => "boolean",
        "decimal" or "double" or "float"      => "number",
        "datetime"                            => "string (date-time)",
        _                                     => "string"
    };

    private static string Sanitize(string input)
    {
        var chars = input.Where(c => char.IsLetterOrDigit(c) || c == '_')
                         .Select(char.ToUpperInvariant)
                         .ToArray();
        int start = 0;
        while (start < chars.Length && !char.IsLetter(chars[start])) start++;
        return new string(chars, start, chars.Length - start);
    }

    private static byte HttpMethodByte(string method) => method.ToUpperInvariant() switch
    {
        "GET"    => 0,
        "POST"   => 1,
        "PUT"    => 2,
        "PATCH"  => 3,
        "DELETE" => 4,
        _        => 0
    };

    private static string? BuildResponseJson(Microsoft.OpenApi.OpenApiDocument doc)
    {
        foreach (var path in doc.Paths)
            foreach (var op in path.Value.Operations)
                if (op.Value.Responses.TryGetValue("200", out var resp))
                    return resp.Description;
        return null;
    }

    // ─────────────────────────────────────────────────────────────
    // DELETE — cascades all 8 child tables then removes root
    // ─────────────────────────────────────────────────────────────
    public async Task<string> DeleteAsync(int apiId, LoginDTO login, CancellationToken ct)
    {
        await _dal.DeleteAsync(apiId, login.ClientId, login, ct).ConfigureAwait(false);

        var cacheKey = new CacheKeyGeneration().KeyGeneration(
            login.ClientId.ToString(),
            Constant.EntityConstant.OBJECTAMPAPI,
            Constant.CacheKeyLevel.CLIENT_LEVEL,
            login);

        try { await _keyInvalidate.InvalidateCache(cacheKey); }
        catch (Exception ex) { _logger.LogWarning(ex, "Cache invalidation failed after delete {ApiId}", apiId); }

        return SuccessResponse.DeleteSuccessMessage;
    }
}