using AmpDAL.DTO.ApiRequest;
using AMPDAL.Query.ApiRequest;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace AmpDAL.CustomCode.ApiRequest;

public class ApiRequestDAL(IQueryExecutor _qe) : IApiRequestDAL
{
    public async Task<ApiRequestDTO?> GetRequestAsync(
        int apiRequestId, int tenantId, LoginDTO login, CancellationToken ct)
        => await _qe.QuerySingleAsync<ApiRequestDTO>(
            login,
            ApiRequestQB.GET_REQUEST,
            new { ApiRequestId = apiRequestId, TenantId = tenantId },
            null,               // DbTransaction?
            false,              // useReadUncommitted
            ct)                 // CancellationToken
            .ConfigureAwait(false);

    public async Task<List<ApiRequestDTO>> GetRequestListAsync(
        LoginDTO login, CancellationToken ct)
    {
        var result = await _qe.QueryAsync<ApiRequestDTO>(
            login,
            ApiRequestQB.GET_REQUEST_LIST,
            new { TenantId = login.ClientId },
            null,
            false,
            ct).ConfigureAwait(false);
        return result.ToList();
    }

    public async Task<List<ApiRequestSaveDTO>> GetPagedListAsync(
        int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
    {
        var result = await _qe.QueryAsync<ApiRequestSaveDTO>(
            login,
            ApiRequestQB.GET_REQUEST_LIST_PAGED,
            new { FirstNumber = firstNumber, MaxResult = maxResult, TenantId = login.ClientId },
            null,
            false,
            ct).ConfigureAwait(false);
        return result.ToList();
    }

    // ✅ CriteriaDTO is a dynamic filter — use QueryWithCriteriaAsync
    public async Task<List<ApiRequestDTO>> GetSelectListAsync(
        CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
    {
        var result = await _qe.QueryWithCriteriaAsync<ApiRequestDTO>(
            login,
            ApiRequestQB.GET_REQUEST_SELECT_LIST,
            new { TenantId = login.ClientId },
            criteriaDTO,
            ct).ConfigureAwait(false);

        return result.ToList();
    }

    public async Task<int> SaveRequestAsync(
        ApiRequestSaveDTO dto, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteScalarAsync<int>(
            login,
            ApiRequestQB.SAVE_REQUEST,
            new
            {
                dto.ApiRequestId,
                TenantId = login.ClientId,
                dto.RequestCode,
                dto.RequestTitle,
                dto.RequestDescription,
                dto.BusinessJustification,
                dto.UseCases,
                dto.IntentKeywords,
                RequestedById = login.UserId,
                dto.RequestedOn,
                dto.TargetDomainId,
                dto.ProposedApiType,
                dto.ProposedApiKind,
                dto.Priority,
                RequestStatus = (byte)0,
                dto.AssignedToId,
                dto.LinkedApiId,
                dto.LinkedApiVersionId,
                dto.TargetDeliveryDate,
                dto.Remarks,
                dto.SortOrder,
                dto.Status,
                dto.Version,
                dto.SourceType,
                CreatedById = login.UserId,
                ModifiedById = login.UserId
            },
            null,               // DbTransaction?
            false,              // useReadUncommitted
            ct)                 // CancellationToken
            .ConfigureAwait(false);

    public async Task UpdateRequestAsync(
        ApiRequestSaveDTO dto, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            ApiRequestQB.UPDATE_REQUEST,
            new
            {
                dto.ApiRequestId,
                TenantId = login.ClientId,
                dto.RequestTitle,
                dto.RequestDescription,
                dto.BusinessJustification,
                dto.UseCases,
                dto.IntentKeywords,
                dto.TargetDomainId,
                dto.ProposedApiType,
                dto.ProposedApiKind,
                dto.Priority,
                dto.AssignedToId,
                dto.TargetDeliveryDate,
                dto.Remarks,
                dto.SortOrder,
                dto.Status,
                ModifiedById = login.UserId
            },
            null,               // DbTransaction?
            ct)                 // CancellationToken
            .ConfigureAwait(false);

    public async Task UpdateRequestStatusAsync(
        int apiRequestId, byte requestStatus, int modifiedById, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            ApiRequestQB.UPDATE_REQUEST_STATUS,
            new
            {
                ApiRequestId = apiRequestId,
                TenantId = login.ClientId,
                RequestStatus = requestStatus,
                ModifiedById = modifiedById
            },
            null,               // DbTransaction?
            ct)                 // CancellationToken
            .ConfigureAwait(false);

    public async Task LinkApiToRequestAsync(
        int apiRequestId, int linkedApiId, int linkedApiVersionId,
        int modifiedById, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            ApiRequestQB.LINK_API_TO_REQUEST,
            new
            {
                ApiRequestId = apiRequestId,
                TenantId = login.ClientId,
                LinkedApiId = linkedApiId,
                LinkedApiVersionId = linkedApiVersionId,
                ModifiedById = modifiedById
            },
            null,               // DbTransaction?
            ct)                 // CancellationToken
            .ConfigureAwait(false);

    public async Task DeleteRequestAsync(
        int apiRequestId, int modifiedById, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            ApiRequestQB.DELETE_REQUEST,
            new
            {
                ApiRequestId = apiRequestId,
                TenantId = login.ClientId,
                ModifiedById = modifiedById
            },
            null,               // DbTransaction?
            ct)                 // CancellationToken
            .ConfigureAwait(false);

    // ── Gate Check Helpers ────────────────────────────────────────

    public async Task<int> GetPendingDuplicateCountAsync(
        int apiRequestId, int tenantId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteScalarAsync<int>(
            login,
            ApiRequestQB.GET_PENDING_DUPLICATE_COUNT,
            new { ApiRequestId = apiRequestId, TenantId = tenantId },
            null, false, ct).ConfigureAwait(false);

    public async Task<int> GetInterfaceCountAsync(
        int apiId, int tenantId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteScalarAsync<int>(
            login,
            ApiRequestQB.GET_INTERFACE_COUNT,
            new { ApiId = apiId, TenantId = tenantId },
            null, false, ct).ConfigureAwait(false);

    public async Task<int> GetUnparsedInterfaceCountAsync(
        int apiId, int tenantId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteScalarAsync<int>(
            login,
            ApiRequestQB.GET_UNPARSED_INTERFACE_COUNT,
            new { ApiId = apiId, TenantId = tenantId },
            null, false, ct).ConfigureAwait(false);

    public async Task<int> GetSchemaWithContentCountAsync(
        int apiId, int tenantId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteScalarAsync<int>(
            login,
            ApiRequestQB.GET_SCHEMA_WITH_CONTENT_COUNT,
            new { ApiId = apiId, TenantId = tenantId },
            null, false, ct).ConfigureAwait(false);

    public async Task<int> GetUnsetParameterCountAsync(
        int apiId, int tenantId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteScalarAsync<int>(
            login,
            ApiRequestQB.GET_UNSET_PARAMETER_COUNT,
            new { ApiId = apiId, TenantId = tenantId },
            null, false, ct).ConfigureAwait(false);

    public async Task<int> GetApiContentCompletenessAsync(
        int apiId, int tenantId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteScalarAsync<int>(
            login,
            ApiRequestQB.GET_API_CONTENT_COMPLETENESS,
            new { ApiId = apiId, TenantId = tenantId },
            null, false, ct).ConfigureAwait(false);

    public async Task<int> GetArtifactCountAsync(
        int apiId, int tenantId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteScalarAsync<int>(
            login,
            ApiRequestQB.GET_ARTIFACT_COUNT,
            new { ApiId = apiId, TenantId = tenantId },
            null, false, ct).ConfigureAwait(false);

    public async Task<int> GetExposurePolicyCountAsync(
        int apiId, int tenantId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteScalarAsync<int>(
            login,
            ApiRequestQB.GET_EXPOSURE_POLICY_COUNT,
            new { ApiId = apiId, TenantId = tenantId },
            null, false, ct).ConfigureAwait(false);

    public async Task<byte?> GetVersionStatusAsync(
        int apiVersionId, int tenantId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteScalarAsync<byte?>(
            login,
            ApiRequestQB.GET_VERSION_STATUS,
            new { ApiVersionId = apiVersionId, TenantId = tenantId },
            null, false, ct).ConfigureAwait(false);

    public async Task<int> GetIncompleteChecklistCountAsync(
        int apiRequestId, byte stage, int tenantId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteScalarAsync<int>(
            login,
            ApiRequestQB.GET_INCOMPLETE_CHECKLIST_COUNT,
            new { ApiRequestId = apiRequestId, Stage = stage, TenantId = tenantId },
            null, false, ct).ConfigureAwait(false);

    public async Task<ReadinessComponentsDTO?> GetReadinessComponentsAsync(
        int apiId, int tenantId, LoginDTO login, CancellationToken ct)
        => await _qe.QuerySingleAsync<ReadinessComponentsDTO>(
            login,
            ApiRequestQB.GET_READINESS_COMPONENTS,
            new { ApiId = apiId, TenantId = tenantId },
            null,               // DbTransaction?
            false,              // useReadUncommitted
            ct)                 // CancellationToken
            .ConfigureAwait(false);
}