using AmpDAL.DTO.RegisterApi;
using AmpDAL.Query.RegisterApi;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Logging;

namespace ApiRegistrationTool;

/// <summary>
/// Direct SQL UPDATE service for refreshing existing MAPI rows.
/// Used when an endpoint is already registered but its DTO or description changed.
///
/// Bypasses the BLL (which only supports INSERT) and writes UPDATE SQL directly
/// via IQueryExecutor — the same infrastructure used by all GB5 DAL classes.
/// </summary>
public sealed class ApiUpdateService
{
    private readonly IQueryExecutor _qe;
    private readonly ILogger<ApiUpdateService> _log;

    public ApiUpdateService(IQueryExecutor qe, ILogger<ApiUpdateService> log)
    {
        _qe  = qe;
        _log = log;
    }

    // ── UPDATE MAPI master description + governance fields ────────────────
    private const string SQL_UPDATE_MAPI = @"
UPDATE MAPI SET
    DESCRIPTION      = @Description,
    ABOUTAPI         = @AboutApi,
    KEYFEATURES      = @KeyFeatures,
    USECASES         = @UseCases,
    QUICKSTART       = @QuickStart,
    OWNERTEAM        = @OwnerTeam,
    BUSINESSOWNER    = @BusinessOwner,
    REPOURL          = @RepoUrl,
    PIPELINEREF      = @PipelineRef,
    GEOGRAPHY        = @Geography,
    MODIFIEDBYID     = @UserId,
    MODIFIEDON       = GETDATE()
WHERE APIID    = @ApiId
AND   TENANTID = @TenantId";

    // ── UPDATE MAPIVERSION lifecycle dates ─────────────────────────────────
    private const string SQL_UPDATE_VERSION = @"
UPDATE MAPIVERSION SET
    TODATE       = @VersionToDate,
    SUNSETDATE   = @SunsetDate,
    MODIFIEDBYID = @UserId,
    MODIFIEDON   = GETDATE()
WHERE APIID    = @ApiId
AND   TENANTID = @TenantId";

    // ── UPDATE MAPIINTERFACE path, description and parse metadata ─────────
    private const string SQL_UPDATE_INTERFACE = @"
UPDATE MAPIINTERFACE SET
    INTERFACEDESCRIPTION = @InterfaceDescription,
    APIPATH              = @ApiPath,
    METHOD               = @InterfaceMethod,
    CONTENTHASH          = @ContentHash,
    LASTPARSEDAT         = @LastParsedAt,
    PARSEDSUCCESSFULLY   = @ParsedSuccessfully,
    MODIFIEDBYID         = @UserId,
    MODIFIEDON           = GETDATE()
WHERE APIID    = @ApiId
AND   TENANTID = @TenantId";

    // ── UPDATE MAPISCHEMA RequestJson + ResponseJson (core of every re-run) ─
    private const string SQL_UPDATE_SCHEMA = @"
UPDATE MAPISCHEMA SET
    REQUESTJSON      = @RequestJson,
    RESPONSEJSON     = @ResponseJson,
    SCHEMAJSON       = @SchemaJson,
    SCHEMADESCRIPTION= @SchemaDescription,
    MODIFIEDBYID     = @UserId,
    MODIFIEDON       = GETDATE()
WHERE APIID    = @ApiId
AND   TENANTID = @TenantId";

    // ── UPDATE MAPIOPERATION summary, path and remarks ─────────────────────
    private const string SQL_UPDATE_OPERATION = @"
UPDATE MAPIOPERATION SET
    RELATIVEPATH = @OperationPath,
    SUMMARY      = @OperationSummary,
    REMARKS      = @OperationRemarks,
    MODIFIEDBYID = @UserId,
    MODIFIEDON   = GETDATE()
WHERE APIID    = @ApiId
AND   TENANTID = @TenantId";

    // ── UPDATE MAPIPARAMETER remarks ────────────────────────────────────────
    private const string SQL_UPDATE_PARAMETER = @"
UPDATE MAPIPARAMETER SET
    REMARKS      = @ParameterRemarks,
    DATATYPE     = @ParameterDataType,
    INLOCATION   = @ParameterInLocation,
    MODIFIEDBYID = @UserId,
    MODIFIEDON   = GETDATE()
WHERE APIID    = @ApiId
AND   TENANTID = @TenantId";

    // ── Batched: MAPI + APIDOMAINID + MAPIVERSION + MAPIINTERFACE + MAPISCHEMA
    //    in a single round-trip (was 5 separate ExecuteAsync calls) ─────────
    private const string SQL_UPDATE_CORE_BATCH =
        SQL_UPDATE_MAPI + ";" +
        RegisterApiQB.UPDATE_API_DOMAIN_ID + ";" +
        SQL_UPDATE_VERSION + ";" +
        SQL_UPDATE_INTERFACE + ";" +
        SQL_UPDATE_SCHEMA;

    // ── Batched: MAPIOPERATION + MAPIPARAMETER (optional, may not exist) ──
    private const string SQL_UPDATE_OPTIONAL_BATCH =
        SQL_UPDATE_OPERATION + ";" +
        SQL_UPDATE_PARAMETER;

    // ── SELECT to check if this ApiPath already has a record ─────────────
    private const string SQL_GET_APIID_BY_PATH = @"
SELECT TOP 1 A.APIID
FROM   MAPI          A
JOIN   MAPIINTERFACE I ON I.APIID = A.APIID AND I.TENANTID = A.TENANTID
WHERE  I.APIPATH   = @ApiPath
AND    A.TENANTID  = @TenantId
AND    A.STATUS    = 1";

    // ═════════════════════════════════════════════════════════════════════
    // PUBLIC METHODS
    // ═════════════════════════════════════════════════════════════════════

    /// <summary>
    /// Looks up an existing APIID by ApiPath. Returns -1 if not found.
    /// </summary>
    public async Task<int> GetApiIdByPathAsync(string apiPath, int tenantId, LoginDTO login, CancellationToken ct)
    {
        try
        {
            var result = await _qe.QuerySingleAsync<int?>(
                login, SQL_GET_APIID_BY_PATH,
                new { ApiPath = apiPath, TenantId = tenantId },
                cancellationToken: ct).ConfigureAwait(false);
            return result ?? -1;
        }
        catch { return -1; }
    }

    /// <summary>
    /// Updates all description + schema + interface + version + parameter
    /// fields for an existing API, and repoints MAPI.APIDOMAINID at the
    /// (possibly shared) domain id resolved by the caller.
    /// </summary>
    public async Task UpdateAsync(
        RegisterApiSaveDTO dto, int apiId, int tenantId, int apiDomainId, LoginDTO login, CancellationToken ct)
    {
        int userId = login.UserId;

        // Core fields (MAPI, APIDOMAINID, MAPIVERSION, MAPIINTERFACE, MAPISCHEMA)
        // in one round-trip — all statements share compatible parameter names.
        await _qe.ExecuteAsync(login, SQL_UPDATE_CORE_BATCH, new
        {
            ApiId             = apiId,
            TenantId          = tenantId,
            dto.Description,
            dto.AboutApi,
            dto.KeyFeatures,
            dto.UseCases,
            dto.QuickStart,
            dto.OwnerTeam,
            dto.BusinessOwner,
            dto.RepoUrl,
            dto.PipelineRef,
            dto.Geography,
            ApiDomainId       = apiDomainId,
            dto.VersionToDate,
            dto.SunsetDate,
            dto.InterfaceDescription,
            dto.ApiPath,
            dto.InterfaceMethod,
            dto.ContentHash,
            dto.LastParsedAt,
            dto.ParsedSuccessfully,
            dto.RequestJson,
            dto.ResponseJson,
            dto.SchemaJson,
            SchemaDescription = dto.SchemaDescription,
            UserId            = userId
        }).ConfigureAwait(false);

        // Operation/Parameter rows are optional (only present when the endpoint
        // has query params or a request body) — log and continue if missing.
        try
        {
            await _qe.ExecuteAsync(login, SQL_UPDATE_OPTIONAL_BATCH, new
            {
                ApiId               = apiId,
                TenantId            = tenantId,
                dto.OperationPath,
                dto.OperationSummary,
                dto.OperationRemarks,
                dto.ParameterRemarks,
                dto.ParameterDataType,
                dto.ParameterInLocation,
                UserId              = userId
            }).ConfigureAwait(false);
        }
        catch (Exception ex)
        {
            _log.LogWarning(ex, "Operation/Parameter update failed for ApiId {ApiId}", apiId);
        }
    }
}