using FLSDAL.DTO.Respondent;
using FLSDAL.Query.Respondent;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace FLSDAL.CustomCode.Respondent;

public class FlsRespondentDAL : IFlsRespondentDAL
{
    private readonly IQueryExecutor _qe;

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

    public async Task<List<AddedRespondentResultRow>> AddRespondentsAsync(
        int flsInstanceId, int groupId, int stepNo,
        List<RespondentRequest> respondents, List<int> respondentIds,
        LoginDTO login, CancellationToken ct)
    {
        var results = new List<AddedRespondentResultRow>(respondents.Count);
        for (int i = 0; i < respondents.Count; i++)
        {
            var r = respondents[i];
            var inserted = await _qe.QuerySingleAsync<AddedRespondentResultRow>(
                login,
                FlsRespondentQB.INSERT_RESPONDENT,
                new
                {
                    FlsRespondentId = respondentIds[i],
                    FlsInstanceId   = flsInstanceId,
                    GroupId         = groupId,
                    StepNo          = stepNo,
                    r.RespondentType,
                    r.RespondentRefId,
                    r.RespondentName,
                    r.RespondentMail,
                    r.RespondentMobile,
                    r.SourceObjectTypeId,
                    r.SourceObjectId,
                    r.SourceBizTypeId,
                    r.SourceReference,
                    TenantId        = login.ClientId
                },
                cancellationToken: ct).ConfigureAwait(false);
            results.Add(inserted);
        }

        await _qe.ExecuteAsync(
            login,
            FlsRespondentQB.INCREMENT_GROUP_RESPONDENT_COUNT,
            new { GroupId = groupId, Count = respondents.Count, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);

        return results;
    }

    public async Task<FlsRespondentRow?> GetByTokenAsync(Guid accessToken, LoginDTO login, CancellationToken ct)
        => await _qe.QuerySingleAsync<FlsRespondentRow>(
            login,
            FlsRespondentQB.GET_RESPONDENT_BY_TOKEN,
            new { AccessToken = accessToken },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<FlsRespondentRow>> GetByInstanceAsync(
        int flsInstanceId, int? groupId, int? statusFilter,
        int pageNo, int pageSize, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<FlsRespondentRow>(
            login,
            FlsRespondentQB.GET_RESPONDENTS_PAGED,
            new
            {
                FlsInstanceId = flsInstanceId,
                GroupId       = groupId,
                StatusFilter  = statusFilter,
                TenantId      = login.ClientId,
                Offset        = (pageNo - 1) * pageSize,
                PageSize      = pageSize
            },
            cancellationToken: ct).ConfigureAwait(false)
           ?? Enumerable.Empty<FlsRespondentRow>();

    public async Task<int> GetByInstanceCountAsync(
        int flsInstanceId, int? groupId, int? statusFilter,
        LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteScalarAsync<int>(
            login,
            FlsRespondentQB.GET_RESPONDENTS_COUNT,
            new
            {
                FlsInstanceId = flsInstanceId,
                GroupId       = groupId,
                StatusFilter  = statusFilter,
                TenantId      = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<FlsPendingFormRow>> GetPendingFormsAsync(
        int respondentType, int respondentRefId, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<FlsPendingFormRow>(
            login,
            FlsRespondentQB.GET_PENDING_FORMS,
            new { RespondentType = respondentType, RespondentRefId = respondentRefId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false)
           ?? Enumerable.Empty<FlsPendingFormRow>();

    public async Task NudgeAsync(int flsRespondentId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            FlsRespondentQB.NUDGE_RESPONDENT,
            new { FlsRespondentId = flsRespondentId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task OptOutAsync(int flsRespondentId, LoginDTO login, CancellationToken ct)
    {
        const string sql = @"
            UPDATE MFLSRESPONDENT
            SET    RESPONDENTSTS = 5,
                   OPTEDOUTAT    = GETUTCDATE(),
                   MODIFIEDON    = GETUTCDATE()
            WHERE  FLSRESPONDENTID = @FlsRespondentId
            AND    TENANTID        = @TenantId
            AND    RESPONDENTSTS   NOT IN (3, 4, 5)";
        await _qe.ExecuteAsync(
            login, sql,
            new { FlsRespondentId = flsRespondentId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<FlsRespondentRow?> GetByIdAsync(int flsRespondentId, LoginDTO login, CancellationToken ct)
    {
        const string sql = @"
            SELECT FLSRESPONDENTID, FLSINSTANCEID, GROUPID, CAST(STEPNO AS INT) AS STEPNO,
                   CAST(RESPONDENTTYPE AS INT) AS RESPONDENTTYPE, RESPONDENTREFID,
                   CAST(RESPONDENTSTS AS INT) AS RESPONDENTSTS, CAST(DELIVERYSTATUS AS INT) AS DELIVERYSTATUS,
                   ACCESSTOKEN, TOKENEXPIRY, CAST(NUDGECOUNT AS INT) AS NUDGECOUNT, LASTNUDGEDAT, OPTEDOUTAT,
                   TENANTID
            FROM   MFLSRESPONDENT
            WHERE  FLSRESPONDENTID = @FlsRespondentId
            AND    TENANTID        = @TenantId";
        return await _qe.QuerySingleAsync<FlsRespondentRow>(
            login, sql,
            new { FlsRespondentId = flsRespondentId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
    }
}