using Dapper;
using FLSDAL.DTO.Bridge;
using FLSDAL.DTO.Event;
using FLSDAL.Query.Bridge;
using FLSDAL.Query.Event;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace FLSDAL.CustomCode.Event;

public class FlsEventDAL : IFlsEventDAL
{
    private readonly IQueryExecutor _qe;

    public FlsEventDAL(IQueryExecutor queryExecutor) => _qe = queryExecutor;

    public async Task<IEnumerable<EventCategoryPicklistDTO>> GetSelectListEventCategory(
        int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO login)
    {
        string sql = FlsEventQB.GET_SELECTLIST_EVENTCATEGORY;
        var parameters = new DynamicParameters();
        parameters.Add("firstnumber", firstNumber);
        parameters.Add("maxresult",   maxResult);

        ApplyEventCategoryCriteria(ref sql, parameters, criteriaDTO, suffix: "");

        sql = sql.Replace("{CRITERIA}", "");

        return await _qe.QueryAsync<EventCategoryPicklistDTO>(login, sql, parameters)
               ?? Enumerable.Empty<EventCategoryPicklistDTO>();
    }

    private static void ApplyEventCategoryCriteria(
        ref string sql, DynamicParameters parameters, CriteriaDTO? criteriaDTO, string suffix)
    {
        if (criteriaDTO?.SectionCriteriaList == null) return;

        int idx = 0;
        foreach (var section in criteriaDTO.SectionCriteriaList)
        {
            foreach (var attr in section.AttributesCriteriaList)
            {
                string? fieldValue = attr.FieldValue?.ToString();
                if (string.IsNullOrEmpty(fieldValue)) continue;

                string? column = attr.FieldName.ToLower() switch
                {
                    "id" or "eventcategoryid"     => "c.EVENTCATEGORYID",
                    "name" or "eventcategoryname" => "c.EVENTCATEGORYNAME",
                    _                              => null
                };
                if (column == null) continue;

                string paramName = $"{attr.FieldName.ToLower()}{suffix}_{idx++}";
                bool isText = column == "c.EVENTCATEGORYNAME";
                object val = isText ? fieldValue : Convert.ToInt32(fieldValue);

                string cond = attr.OperationType switch
                {
                    CriteriaDTO.OperationType.Equal => $"{column} = @{paramName}",
                    CriteriaDTO.OperationType.Like  => $"{column} LIKE @{paramName}",
                    _                                => $"{column} = @{paramName}"
                };
                if (attr.OperationType == CriteriaDTO.OperationType.Like)
                    val = $"%{fieldValue}%";

                parameters.Add(paramName, val);
                sql = sql.Replace("{CRITERIA}", $" AND {cond} {{CRITERIA}}");
            }
        }
    }

    public async Task<long> LogEventAsync(
        int flsInstanceId, int flsRespondentId, int bridgeConfigId,
        int eventType, string payloadJson, string? correlationId,
        LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteScalarAsync<long>(
            login,
            FlsEventQB.INSERT_EVENT_LOG,
            new
            {
                FlsInstanceId  = flsInstanceId,
                FlsRespondentId = flsRespondentId,
                BridgeConfigId = bridgeConfigId,
                EventType      = eventType,
                PayloadJson    = payloadJson,
                CorrelationId  = correlationId,
                TenantId       = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task UpdateEventStatusAsync(
        long flsEventLogId, int status, string? failureReason,
        bool incrementRetry, DateTime? nextRetryOn,
        LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            FlsEventQB.UPDATE_EVENT_STATUS,
            new
            {
                FlsEventLogId  = flsEventLogId,
                Status         = status,
                FailureReason  = failureReason,
                IncrementRetry = incrementRetry ? 1 : 0,
                NextRetryOn    = nextRetryOn
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<FlsEventSubscriptionRow>> GetSubscriptionsAsync(
        int flsRegistrationId, int eventType, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<FlsEventSubscriptionRow>(
            login,
            FlsEventQB.GET_SUBSCRIPTIONS,
            new { FlsRegistrationId = flsRegistrationId, EventType = eventType, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false)
           ?? Enumerable.Empty<FlsEventSubscriptionRow>();

    public async Task<IEnumerable<FlsBridgeConfigRow>> GetActiveBridgesAsync(LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<FlsBridgeConfigRow>(
            login,
            FlsBridgeQB.GET_ACTIVE_BRIDGES,
            new { },
            cancellationToken: ct).ConfigureAwait(false)
           ?? Enumerable.Empty<FlsBridgeConfigRow>();

    public async Task<IEnumerable<FlsEventLogRow>> GetPendingRetriesAsync(LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<FlsEventLogRow>(
            login,
            FlsEventQB.GET_PENDING_RETRIES,
            new { TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false)
           ?? Enumerable.Empty<FlsEventLogRow>();
}