using System.Data;
using Dapper;
using FLSDAL.DTO.Survey;
using FLSDAL.Query.Survey;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace FLSDAL.CustomCode.Survey;

public class SurveyInstrumentDAL : ISurveyInstrumentDAL
{
    private readonly IQueryExecutor _qe;

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

    public async Task<IEnumerable<SurveyInstrumentTypePicklistDTO>> GetSelectListSurveyInstrumentType(
        int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO login)
    {
        string sql = SurveyQB.GET_SELECTLIST_SURVEYINSTRUMENTTYPE;
        var parameters = new DynamicParameters();
        parameters.Add("firstnumber", firstNumber);
        parameters.Add("maxresult",   maxResult);

        ApplySelectListCriteria(ref sql, parameters, criteriaDTO,           suffix: "");
        ApplySelectListCriteria(ref sql, parameters, login.UserCriteriaDTO, suffix: "_u");

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

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

    public async Task<int?> GetInstrumentTypeStatusAsync(int instrumentTypeId, LoginDTO login, CancellationToken ct)
    {
        var result = await _qe.QueryAsync<int>(login, SurveyQB.GET_SURVEYINSTRUMENTTYPE_STATUS,
            new { InstrumentTypeId = instrumentTypeId }, cancellationToken: ct);
        return result?.Cast<int?>().FirstOrDefault();
    }

    // Maps CriteriaDTO field names to MSURVEYINSTRUMENTTYPE/MSURVEYQUESTIONBANK/MSURVEYQUESTIONTYPE
    // columns. Called twice: the caller's CriteriaDTO (suffix="") and LoginDTO.UserCriteriaDTO
    // (suffix="_u") for access scoping.
    private static void ApplySelectListCriteria(
        ref string sql, DynamicParameters parameters, CriteriaDTO? criteriaDTO, string suffix,
        bool isQuestion = false, bool isQuestionType = false, bool isSection = false, bool isInstrument = false)
    {
        if (criteriaDTO?.SectionCriteriaList == null) return;

        var conditions = new List<(string Cond, string Param, object Val, CriteriaDTO.AttributeJoinOperationType Join)>();

        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 = isQuestionType
                    ? GetQuestionTypeColumnForField(attr.FieldName.ToLower())
                    : isSection
                        ? GetSectionColumnForField(attr.FieldName.ToLower())
                        : isQuestion
                            ? GetQuestionColumnForField(attr.FieldName.ToLower())
                            : isInstrument
                                ? GetInstrumentColumnForField(attr.FieldName.ToLower())
                                : GetColumnForField(attr.FieldName.ToLower());
                if (column == null) continue;

                string paramName = $"{attr.FieldName.ToLower().Replace(".", "_")}{suffix}_{idx++}";

                if ((attr.OperationType == CriteriaDTO.OperationType.In ||
                     attr.OperationType == CriteriaDTO.OperationType.NotIn)
                    && attr.InArray?.Length > 0)
                {
                    string op = attr.OperationType == CriteriaDTO.OperationType.In ? "IN" : "NOT IN";
                    var vals = attr.InArray.Select(v => Convert.ToInt32(v?.ToString())).ToList();
                    conditions.Add(($"{column} {op} @{paramName}", paramName, vals, attr.JoinType));
                    continue;
                }

                var (condSql, condVal) = BuildCondition(column, paramName, attr.OperationType, fieldValue);
                if (condSql == null) continue;

                conditions.Add((condSql, paramName, condVal, attr.JoinType));
            }
        }

        int i = 0;
        while (i < conditions.Count)
        {
            var (cond, pname, pval, joinType) = conditions[i];

            if (joinType == CriteriaDTO.AttributeJoinOperationType.Or)
            {
                var orParts = new List<string> { cond };
                parameters.Add(pname, pval);
                i++;
                while (i < conditions.Count)
                {
                    var (nc, np, nv, nj) = conditions[i];
                    parameters.Add(np, nv);
                    orParts.Add(nc);
                    i++;
                    if (nj != CriteriaDTO.AttributeJoinOperationType.Or) break;
                }
                sql = sql.Replace("{CRITERIA}", $" AND ({string.Join(" OR ", orParts)}) {{CRITERIA}}");
            }
            else
            {
                parameters.Add(pname, pval);
                sql = sql.Replace("{CRITERIA}", $" AND {cond} {{CRITERIA}}");
                i++;
            }
        }
    }

    private static string? GetColumnForField(string fieldName) => fieldName switch
    {
        "id" or "instrumenttypeid"     => "t.INSTRUMENTTYPEID",
        "code" or "instrumenttypecode" => "t.INSTRUMENTTYPECODE",
        "name" or "instrumenttypename" => "t.INSTRUMENTTYPENAME",
        "status"                       => "t.STATUS",
        _                               => null
    };

    private static string? GetQuestionColumnForField(string fieldName) => fieldName switch
    {
        "id" or "questionid"     => "qb.QUESTIONID",
        "code" or "questioncode" => "qb.QUESTIONCODE",
        "questiontypeid"         => "qb.QUESTIONTYPEID",
        "isrequired"             => "qb.ISREQUIRED",
        "status"                 => "qb.STATUS",
        _                        => null
    };

    private static string? GetQuestionTypeColumnForField(string fieldName) => fieldName switch
    {
        "id" or "questiontypeid"       => "t.QUESTIONTYPEID",
        "code" or "questiontypecode"   => "t.QUESTIONTYPECODE",
        "name" or "questiontypename"   => "t.QUESTIONTYPENAME",
        "status"                       => "t.STATUS",
        _                               => null
    };

    private static string? GetSectionColumnForField(string fieldName) => fieldName switch
    {
        "id" or "instrumentsectionid" => "s.INSTRUMENTSECTIONID",
        "code" or "sectioncode"       => "s.SECTIONCODE",
        "name" or "sectionname"       => "s.SECTIONNAME",
        "instrumentconfigid"          => "s.INSTRUMENTCONFIGID",
        _                             => null
    };

    private static string? GetInstrumentColumnForField(string fieldName) => fieldName switch
    {
        "id" or "instrumentconfigid" => "c.INSTRUMENTCONFIGID",
        "name" or "instrumenttitle"  => "c.INSTRUMENTTITLE",
        "instrumenttypeid"           => "c.INSTRUMENTTYPEID",
        "flsregistrationid"          => "c.FLSREGISTRATIONID",
        "status"                     => "c.STATUS",
        _                            => null
    };

    private static readonly HashSet<string> _textColumns =
        new(StringComparer.OrdinalIgnoreCase)
        { "t.INSTRUMENTTYPECODE", "t.INSTRUMENTTYPENAME", "qb.QUESTIONCODE",
          "t.QUESTIONTYPECODE", "t.QUESTIONTYPENAME", "s.SECTIONCODE", "s.SECTIONNAME",
          "c.INSTRUMENTTITLE" };

    private static (string? Cond, object Val) BuildCondition(
        string column, string paramName, CriteriaDTO.OperationType opType, string fieldValue)
    {
        bool isText = _textColumns.Contains(column);
        object asVal = isText ? (object)fieldValue : (object)Convert.ToInt32(fieldValue);

        return opType switch
        {
            CriteriaDTO.OperationType.Equal    or
            CriteriaDTO.OperationType.In       => ($"{column} = @{paramName}",   asVal),
            CriteriaDTO.OperationType.NotEqual or
            CriteriaDTO.OperationType.NotIn    => ($"{column} <> @{paramName}",  asVal),
            CriteriaDTO.OperationType.Like      => ($"{column} LIKE @{paramName}", (object)$"%{fieldValue}%"),
            CriteriaDTO.OperationType.StartWith => ($"{column} LIKE @{paramName}", (object)$"{fieldValue}%"),
            CriteriaDTO.OperationType.EndsWith  => ($"{column} LIKE @{paramName}", (object)$"%{fieldValue}"),
            CriteriaDTO.OperationType.GreaterThan          => ($"{column} > @{paramName}",  asVal),
            CriteriaDTO.OperationType.LessThan             => ($"{column} < @{paramName}",  asVal),
            CriteriaDTO.OperationType.GreaterThanOrEqualTo => ($"{column} >= @{paramName}", asVal),
            CriteriaDTO.OperationType.LessThanOrEqualTo    => ($"{column} <= @{paramName}", asVal),
            _ => (null, (object)0)
        };
    }

    public async Task<IEnumerable<SurveyQuestionTypePicklistDTO>> GetSelectListSurveyQuestionType(
        int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO login)
    {
        string sql = SurveyQB.GET_SELECTLIST_SURVEYQUESTIONTYPE;
        var parameters = new DynamicParameters();
        parameters.Add("firstnumber", firstNumber);
        parameters.Add("maxresult",   maxResult);

        ApplySelectListCriteria(ref sql, parameters, criteriaDTO,           suffix: "",   isQuestionType: true);
        ApplySelectListCriteria(ref sql, parameters, login.UserCriteriaDTO, suffix: "_u", isQuestionType: true);

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

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

    public async Task<(SurveyInstrumentConfigRow? Config,
                       List<SurveySectionRow> Sections,
                       List<SurveyInstQuestionRow> Questions,
                       List<SurveyInstOptionRow> Options)>
        GetInstrumentAsync(int instrumentConfigId, string langCode, LoginDTO login, CancellationToken ct)
    {
        // Single round trip, 4 result sets (no stored proc — inline text batch):
        // 1=Config, 2=Sections, 3=Questions (inst+bank+trans JOIN), 4=Options (option+trans JOIN)
        using var multi = await _qe.QueryMultipleAsync(
            login,
            SurveyQB.GET_INSTRUMENT_MULTI,
            new { InstrumentConfigId = instrumentConfigId, LangCode = langCode, TenantId = login.ClientId },
            commandType: CommandType.Text).ConfigureAwait(false);

        var config    = await multi.ReadSingleOrDefaultAsync<SurveyInstrumentConfigRow>().ConfigureAwait(false);
        var sections  = (await multi.ReadAsync<SurveySectionRow>().ConfigureAwait(false)).ToList();
        var questions = (await multi.ReadAsync<SurveyInstQuestionRow>().ConfigureAwait(false)).ToList();
        var options   = (await multi.ReadAsync<SurveyInstOptionRow>().ConfigureAwait(false)).ToList();

        return (config, sections, questions, options);
    }

    public async Task<int> GetConfigIdByRegistrationAsync(int flsRegistrationId, LoginDTO login, CancellationToken ct)
    {
        var config = await _qe.QuerySingleAsync<SurveyInstrumentConfigRow>(
            login,
            SurveyQB.GET_CONFIG_BY_REGISTRATION,
            new { FlsRegistrationId = flsRegistrationId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return config?.InstrumentConfigId ?? -1;
    }

    public async Task LockInstrumentAsync(int instrumentConfigId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            SurveyQB.LOCK_INSTRUMENT,
            new { InstrumentConfigId = instrumentConfigId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task InsertInstrumentConfigAsync(int instrumentConfigId, SaveSurveyInstrumentRequest req, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            SurveyQB.INSERT_INSTRUMENT_CONFIG,
            new
            {
                InstrumentConfigId = instrumentConfigId,
                req.FlsRegistrationId,
                req.FormTemplateId,
                req.InstrumentTypeId,
                req.InstrumentTitle,
                req.IntroText,
                req.ThankYouMessage,
                req.ShowProgressBar,
                req.AllowPartialSubmit,
                req.LangCode,
                req.IsAnonymous,
                req.Status,
                TenantId           = login.ClientId,
                UserId             = login.UserId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<bool> UpdateInstrumentConfigAsync(int instrumentConfigId, SaveSurveyInstrumentRequest req, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            SurveyQB.UPDATE_INSTRUMENT_CONFIG,
            new
            {
                InstrumentConfigId = instrumentConfigId,
                req.FormTemplateId,
                req.InstrumentTypeId,
                req.InstrumentTitle,
                req.IntroText,
                req.ThankYouMessage,
                req.ShowProgressBar,
                req.AllowPartialSubmit,
                req.LangCode,
                req.IsAnonymous,
                req.Status,
                TenantId           = login.ClientId,
                UserId             = login.UserId
            },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task InsertSectionAsync(int instrumentSectionId, int instrumentConfigId, SaveSurveySectionRequest section, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            SurveyQB.INSERT_SECTION,
            new
            {
                InstrumentSectionId = instrumentSectionId,
                InstrumentConfigId  = instrumentConfigId,
                section.DisplayOrder,
                section.SectionCode,
                section.SectionName,
                TenantId            = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task UpdateSectionAsync(int instrumentSectionId, SaveSurveySectionRequest section, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            SurveyQB.UPDATE_SECTION,
            new
            {
                InstrumentSectionId = instrumentSectionId,
                section.DisplayOrder,
                section.SectionCode,
                section.SectionName,
                TenantId            = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task DeactivateSectionsNotInAsync(int instrumentConfigId, List<int> keepIds, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            keepIds.Count > 0 ? SurveyQB.DEACTIVATE_SECTIONS_NOT_IN : SurveyQB.DEACTIVATE_ALL_SECTIONS,
            new { InstrumentConfigId = instrumentConfigId, KeepIds = keepIds, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task InsertSectionOnlyAsync(int instrumentSectionId, SaveSurveySectionOnlyRequest req, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            SurveyQB.INSERT_SECTION,
            new
            {
                InstrumentSectionId = instrumentSectionId,
                req.InstrumentConfigId,
                req.DisplayOrder,
                req.SectionCode,
                req.SectionName,
                TenantId            = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<bool> UpdateSectionOnlyAsync(int instrumentSectionId, SaveSurveySectionOnlyRequest req, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            SurveyQB.UPDATE_SECTION,
            new
            {
                InstrumentSectionId = instrumentSectionId,
                req.DisplayOrder,
                req.SectionCode,
                req.SectionName,
                TenantId            = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task<bool> DeleteSectionAsync(int instrumentSectionId, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            SurveyQB.DELETE_SECTION,
            new { InstrumentSectionId = instrumentSectionId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task<SurveySectionRow?> GetSectionByIdAsync(int instrumentSectionId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<SurveySectionRow>(
            login,
            SurveyQB.GET_SECTION_BY_ID,
            new { InstrumentSectionId = instrumentSectionId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows?.FirstOrDefault();
    }

    public async Task<IEnumerable<SurveySectionRow>> GetSectionsByConfigAsync(int instrumentConfigId, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<SurveySectionRow>(
            login,
            SurveyQB.GET_SECTIONS_BY_CONFIG,
            new { InstrumentConfigId = instrumentConfigId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false)
           ?? Enumerable.Empty<SurveySectionRow>();

    public async Task<IEnumerable<SurveySectionPicklistDTO>> GetSelectListSurveySection(
        int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO login)
    {
        string sql = SurveyQB.GET_SELECTLIST_SURVEYSECTION;
        var parameters = new DynamicParameters();
        parameters.Add("firstnumber", firstNumber);
        parameters.Add("maxresult",   maxResult);
        parameters.Add("TenantId",    login.ClientId);

        ApplySelectListCriteria(ref sql, parameters, criteriaDTO,           suffix: "",   isSection: true);
        ApplySelectListCriteria(ref sql, parameters, login.UserCriteriaDTO, suffix: "_u", isSection: true);

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

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

    public async Task InsertInstQuestionAsync(int instQuestionId, int instrumentSectionId, int instrumentConfigId, SaveSurveyInstQuestionRequest question, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            SurveyQB.INSERT_INSTQUESTION,
            new
            {
                InstQuestionId      = instQuestionId,
                InstrumentSectionId = instrumentSectionId,
                InstrumentConfigId  = instrumentConfigId,
                question.QuestionId,
                question.DisplayOrder,
                question.IsRequiredOverride,
                TenantId            = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task UpdateInstQuestionAsync(int instQuestionId, SaveSurveyInstQuestionRequest question, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            SurveyQB.UPDATE_INSTQUESTION,
            new
            {
                InstQuestionId = instQuestionId,
                question.QuestionId,
                question.DisplayOrder,
                question.IsRequiredOverride,
                TenantId       = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task DeactivateInstQuestionsNotInAsync(int instrumentSectionId, List<int> keepIds, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            keepIds.Count > 0 ? SurveyQB.DEACTIVATE_INSTQUESTIONS_NOT_IN : SurveyQB.DEACTIVATE_ALL_INSTQUESTIONS_IN_SECTION,
            new { InstrumentSectionId = instrumentSectionId, KeepIds = keepIds, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task InsertQuestionBankAsync(int questionId, SaveSurveyQuestionRequest req, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            SurveyQB.INSERT_QUESTION_BANK,
            new
            {
                QuestionId = questionId,
                req.QuestionCode,
                req.QuestionTypeId,
                req.RatingMin,
                req.RatingMax,
                req.RatingLabelLow,
                req.RatingLabelHigh,
                req.TextMaxLength,
                req.TextMinLength,
                req.YesLabel,
                req.NoLabel,
                req.IsRequired,
                req.SortOrder,
                TenantId   = login.ClientId,
                UserId     = login.UserId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<bool> UpdateQuestionBankAsync(int questionId, SaveSurveyQuestionRequest req, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            SurveyQB.UPDATE_QUESTION_BANK,
            new
            {
                QuestionId = questionId,
                req.QuestionCode,
                req.QuestionTypeId,
                req.RatingMin,
                req.RatingMax,
                req.RatingLabelLow,
                req.RatingLabelHigh,
                req.TextMaxLength,
                req.TextMinLength,
                req.YesLabel,
                req.NoLabel,
                req.IsRequired,
                req.SortOrder,
                TenantId   = login.ClientId,
                UserId     = login.UserId
            },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task UpsertQuestionTransAsync(int questionId, int questionTransId, string langCode, string questionText, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            SurveyQB.UPSERT_QUESTION_TRANS,
            new
            {
                QuestionId      = questionId,
                QuestionTransId = questionTransId,
                LangCode        = langCode,
                QuestionText    = questionText,
                TenantId        = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task InsertOptionAsync(int optionId, int questionId, SaveSurveyOptionRequest option, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            SurveyQB.INSERT_OPTION,
            new
            {
                OptionId   = optionId,
                QuestionId = questionId,
                option.DisplayOrder,
                option.OptionCode,
                TenantId   = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task UpdateOptionAsync(int optionId, SaveSurveyOptionRequest option, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            SurveyQB.UPDATE_OPTION,
            new
            {
                OptionId = optionId,
                option.DisplayOrder,
                option.OptionCode,
                TenantId = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task DeactivateOptionsNotInAsync(int questionId, List<int> keepIds, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            keepIds.Count > 0 ? SurveyQB.DEACTIVATE_OPTIONS_NOT_IN : SurveyQB.DEACTIVATE_ALL_OPTIONS,
            new { QuestionId = questionId, KeepIds = keepIds, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task UpsertOptionTransAsync(int optionId, int optionTransId, string langCode, string optionText, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            SurveyQB.UPSERT_OPTION_TRANS,
            new
            {
                OptionId      = optionId,
                OptionTransId = optionTransId,
                LangCode      = langCode,
                OptionText    = optionText,
                TenantId      = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<SurveyQuestionBankListRow>> GetQuestionBankListAsync(
        string searchText, string langCode, int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
    {
        int skip = Math.Max(0, firstNumber - 1);
        return await _qe.QueryAsync<SurveyQuestionBankListRow>(
            login,
            SurveyQB.GET_QUESTION_BANK_LIST,
            new
            {
                SearchText = searchText ?? string.Empty,
                LangCode   = langCode,
                Skip       = skip,
                Take       = maxResult,
                TenantId   = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false)
               ?? Enumerable.Empty<SurveyQuestionBankListRow>();
    }

    public async Task<(SurveyQuestionCoreRow? Question, List<SurveyQuestionOptionDetailDto> Options)> GetQuestionByIdAsync(
        int questionId, string langCode, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<SurveyQuestionCoreRow>(
            login,
            SurveyQB.GET_QUESTION_BY_ID,
            new { QuestionId = questionId, LangCode = langCode, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        var question = rows?.FirstOrDefault();

        if (question is null)
            return (null, new List<SurveyQuestionOptionDetailDto>());

        var options = (await _qe.QueryAsync<SurveyQuestionOptionDetailDto>(
            login,
            SurveyQB.GET_OPTIONS_BY_QUESTION,
            new { QuestionId = questionId, LangCode = langCode, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false) ?? Enumerable.Empty<SurveyQuestionOptionDetailDto>()).ToList();

        return (question, options);
    }

    public async Task<IEnumerable<SurveyQuestionPicklistDTO>> GetSelectListSurveyQuestion(
        int firstNumber, int maxResult, string langCode, CriteriaDTO criteriaDTO, LoginDTO login)
    {
        string sql = SurveyQB.GET_SELECTLIST_SURVEYQUESTION;
        var parameters = new DynamicParameters();
        parameters.Add("firstnumber", firstNumber);
        parameters.Add("maxresult",   maxResult);
        parameters.Add("LangCode",    langCode);
        parameters.Add("TenantId",    login.ClientId);

        ApplySelectListCriteria(ref sql, parameters, criteriaDTO,           suffix: "",   isQuestion: true);
        ApplySelectListCriteria(ref sql, parameters, login.UserCriteriaDTO, suffix: "_u", isQuestion: true);

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

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

    public async Task<IEnumerable<SurveyInstrumentPicklistDTO>> GetSelectListSurveyInstrument(
        int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO login)
    {
        string sql = SurveyQB.GET_SELECTLIST_SURVEYINSTRUMENT;
        var parameters = new DynamicParameters();
        parameters.Add("firstnumber", firstNumber);
        parameters.Add("maxresult",   maxResult);
        parameters.Add("TenantId",    login.ClientId);

        ApplySelectListCriteria(ref sql, parameters, criteriaDTO,           suffix: "",   isInstrument: true);
        ApplySelectListCriteria(ref sql, parameters, login.UserCriteriaDTO, suffix: "_u", isInstrument: true);

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

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

    public async Task<bool> DeactivateQuestionBankAsync(int questionId, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            SurveyQB.DEACTIVATE_QUESTION_BANK,
            new { QuestionId = questionId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }
}