using GB5Shared.DTO.Framework.Login;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using PERMDAL.DTOs;
using PERMDAL.Interfaces;
using PERMDAL.QueryBuilders;

namespace PERMDAL.Implementations;

public class AppraisalEvalDAL : IAppraisalEvalDAL
{
    private readonly IQueryExecutor _qe;
    private readonly AutoNumber     _autoNumber;

    public AppraisalEvalDAL(IQueryExecutor queryExecutor, AutoNumber autoNumber)
    {
        _qe         = queryExecutor;
        _autoNumber = autoNumber;
    }

    public async Task<AppraisalEvalDTO?> GetAppraisalEvalAsync(int evalId, LoginDTO login, CancellationToken ct)
        => await _qe.QuerySingleAsync<AppraisalEvalDTO>(
                login, AppraisalEvalQB.GET_BY_ID,
                new { AppraisalEvalId = evalId })
            .ConfigureAwait(false);

    public async Task<IEnumerable<AppraisalEvalDTO>> GetEvalListByAppraisalAsync(
        int appraisalId, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<AppraisalEvalDTO>(login, AppraisalEvalQB.GET_LIST_BY_APPRAISAL,
                new { AppraisalId = appraisalId },
                cancellationToken: ct)
            .ConfigureAwait(false) ?? Enumerable.Empty<AppraisalEvalDTO>();

    public async Task<IEnumerable<AppraisalEvalKRADTO>> GetEvalKRAsAsync(
        int evalId, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<AppraisalEvalKRADTO>(login, AppraisalEvalQB.GET_KRAS_BY_EVAL,
                new { AppraisalEvalId = evalId },
                cancellationToken: ct)
            .ConfigureAwait(false) ?? Enumerable.Empty<AppraisalEvalKRADTO>();

    public async Task<IEnumerable<AppraisalDetailDTO>> GetEvalDetailsAsync(
        int evalId, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<AppraisalDetailDTO>(login, AppraisalEvalQB.GET_DETAILS_BY_EVAL,
                new { AppraisalEvalId = evalId },
                cancellationToken: ct)
            .ConfigureAwait(false) ?? Enumerable.Empty<AppraisalDetailDTO>();

    public async Task<int> SaveAppraisalEvalAsync(AppraisalEvalDTO dto, bool isNew, LoginDTO login, CancellationToken ct)
    {
        await using var tx = await _qe.BeginTransactionAsync(login).ConfigureAwait(false);
        try
        {
            int evalId;
            if (isNew)
            {
                var evalAuto = await _autoNumber.GetAutoNumber(1, "APPRAISALEVAL", login).ConfigureAwait(false);
                dto.AppraisalEvalId = evalAuto.StartNumber;

                await _qe.ExecuteAsync(login, AppraisalEvalQB.INSERT_EVAL,
                    new { dto.AppraisalEvalId, dto.AppraisalId, dto.SlNo, dto.EvaluatorId, dto.EvalType,
                          dto.EvalRemarks, ModifiedById = login.UserId },
                    tx, ct).ConfigureAwait(false);
                evalId = dto.AppraisalEvalId;
            }
            else
            {
                await _qe.ExecuteAsync(login, AppraisalEvalQB.UPDATE_EVAL,
                    new { dto.AppraisalEvalId, dto.OverallScore, dto.EvalRemarks, dto.SubmittedOn,
                          ModifiedById = login.UserId },
                    tx, ct).ConfigureAwait(false);
                evalId = dto.AppraisalEvalId;
            }

            foreach (var kra in dto.KRAs ?? Enumerable.Empty<AppraisalEvalKRADTO>())
            {
                int kraRowId;
                if (kra.AppraisalEvalKRAId == 0)
                {
                    var kraAuto = await _autoNumber.GetAutoNumber(1, "APPRAISALEVALKRA", login).ConfigureAwait(false);
                    kraRowId = kraAuto.StartNumber;
                    kra.AppraisalEvalKRAId = kraRowId;

                    await _qe.ExecuteAsync(login, AppraisalEvalQB.INSERT_KRA,
                        new { AppraisalEvalId = evalId, kra.AppraisalEvalKRAId, kra.SlNo, kra.KRAId, kra.KRAScore, kra.Remarks },
                        tx, ct).ConfigureAwait(false);
                }
                else
                {
                    await _qe.ExecuteAsync(login, AppraisalEvalQB.UPDATE_KRA,
                        new { kra.AppraisalEvalKRAId, kra.KRAScore, kra.Remarks },
                        tx, ct).ConfigureAwait(false);
                    kraRowId = kra.AppraisalEvalKRAId;
                }

                foreach (var detail in kra.Details ?? Enumerable.Empty<AppraisalDetailDTO>())
                {
                    if (detail.AppraisalDetailId == 0)
                    {
                        var detailAuto = await _autoNumber.GetAutoNumber(1, "APPRAISALDETAIL", login).ConfigureAwait(false);
                        detail.AppraisalDetailId = detailAuto.StartNumber;

                        await _qe.ExecuteAsync(login, AppraisalEvalQB.INSERT_DETAIL,
                            new { detail.AppraisalDetailId, detail.SlNo, detail.MeasureId, detail.Particulars, detail.Score,
                                  AppraisalEvalKRAId = kraRowId, detail.TargetValue, detail.AchievedValue,
                                  AppraisalEvalId = evalId },
                            tx, ct).ConfigureAwait(false);
                    }
                    else
                    {
                        await _qe.ExecuteAsync(login, AppraisalEvalQB.UPDATE_DETAIL,
                            new { detail.AppraisalDetailId, detail.TargetValue, detail.AchievedValue, detail.Score },
                            tx, ct).ConfigureAwait(false);
                    }
                }
            }

            await tx.CommitAsync(ct).ConfigureAwait(false);
            return evalId;
        }
        catch
        {
            await tx.RollbackAsync(ct).ConfigureAwait(false);
            throw;
        }
    }

    public async Task<IEnumerable<EmployeeKRAScoreDTO>> GetLatestKRAScoresByEmployeeAsync(
        int employeeId, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<EmployeeKRAScoreDTO>(login,
                AppraisalEvalQB.GET_LATEST_KRA_SCORES_BY_EMPLOYEE,
                new { EmployeeId = employeeId, TenantId = login.ClientId },
                cancellationToken: ct)
            .ConfigureAwait(false) ?? Enumerable.Empty<EmployeeKRAScoreDTO>();
}