using System.Data.Common;
using System.Text.Json;
using AnalyticsDAL.DTO.RoleKPI;
using AnalyticsDAL.DTO.RoleKPIDetail;
using AnalyticsDAL.Query.RoleKPI;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace AnalyticsDAL.CustomCode.RoleKPI
{
    public class RoleKPIDAL : IRoleKPIDAL
    {
        private readonly IQueryExecutor _queryExecutor;

        public RoleKPIDAL(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }

        public async Task<string> GetRoleKPI(int roleKPIId, LoginDTO login, CancellationToken ct)
        {
            var dto = await _queryExecutor.QuerySingleAsync<RoleKPIDTO>(
                login,
                RoleKPIQB.GET_ROLEKPI,
                new { RoleKPIId = roleKPIId, TenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);

            if (dto != null)
            {
                var details = await _queryExecutor.QueryAsync<RoleKPIDetailDTO>(
                    login,
                    RoleKPIQB.GET_ROLEKPI_DETAILS,
                    new { RoleKPIId = roleKPIId, TenantId = login.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);

                dto.RoleKPIDetailArray = details?.ToList();
            }

            return JsonSerializer.Serialize(dto);
        }

        public async Task<string> GetSelectListRoleKPI(LoginDTO login, CancellationToken ct)
        {
            var list = await _queryExecutor.QueryAsync<RoleKPIDTO>(
                login,
                RoleKPIQB.GET_SELECT_LIST,
                new { TenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);

            return JsonSerializer.Serialize(list);
        }

        public async Task<int> SaveRoleKPI(RoleKPIDTO dto, LoginDTO login, CancellationToken ct)
        {
            await using var tx = await _queryExecutor.BeginTransactionAsync(login);
            try
            {
                var newId = await _queryExecutor.ExecuteIdentityAsync(
                    login,
                    RoleKPIQB.SAVE_ROLEKPI,
                    new
                    {
                        dto.RoleId,
                        dto.RoleKPISortOrder,
                        RoleKPISourceType = (byte)5,
                        TenantId          = login.ClientId,
                        CreatedById       = login.UserId,
                        ModifiedById      = login.UserId
                    }).ConfigureAwait(false);

                dto.RoleKPIId = newId;
                await SaveDetailRows(dto, login, tx, ct).ConfigureAwait(false);

                await tx.CommitAsync(ct);
                return newId;
            }
            catch
            {
                await tx.RollbackAsync(ct);
                throw;
            }
        }

        public async Task UpdateRoleKPI(RoleKPIDTO dto, LoginDTO login, CancellationToken ct)
        {
            await using var tx = await _queryExecutor.BeginTransactionAsync(login);
            try
            {
                await _queryExecutor.ExecuteAsync(
                    login,
                    RoleKPIQB.UPDATE_ROLEKPI,
                    new
                    {
                        dto.RoleKPIId,
                        dto.RoleId,
                        dto.RoleKPISortOrder,
                        dto.RoleKPIStatus,
                        TenantId     = login.ClientId,
                        ModifiedById = login.UserId
                    },
                    tx, ct).ConfigureAwait(false);

                await _queryExecutor.ExecuteAsync(
                    login,
                    RoleKPIQB.DELETE_DETAIL_ROWS,
                    new { RoleKPIId = dto.RoleKPIId, TenantId = login.ClientId },
                    tx, ct).ConfigureAwait(false);

                await SaveDetailRows(dto, login, tx, ct).ConfigureAwait(false);

                await tx.CommitAsync(ct);
            }
            catch
            {
                await tx.RollbackAsync(ct);
                throw;
            }
        }

        public async Task DeleteRoleKPI(int roleKPIId, LoginDTO login, CancellationToken ct)
        {
            await _queryExecutor.ExecuteAsync(
                login,
                RoleKPIQB.DELETE_ROLEKPI,
                new { RoleKPIId = roleKPIId, TenantId = login.ClientId, ModifiedById = login.UserId },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<bool> IsDuplicateRole(int roleId, int excludeId, LoginDTO login, CancellationToken ct)
        {
            var count = await _queryExecutor.ExecuteScalarAsync<int>(
                login,
                RoleKPIQB.CHECK_DUPLICATE_ROLE,
                new { RoleId = roleId, TenantId = login.ClientId, RoleKPIId = excludeId },
                cancellationToken: ct).ConfigureAwait(false);

            return count > 0;
        }

        private async Task SaveDetailRows(RoleKPIDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            if (dto.RoleKPIDetailArray == null || dto.RoleKPIDetailArray.Count == 0)
                return;

            foreach (var row in dto.RoleKPIDetailArray)
            {
                await _queryExecutor.ExecuteAsync(
                    login,
                    RoleKPIQB.SAVE_DETAIL_ROW,
                    new
                    {
                        RoleKPIId = dto.RoleKPIId,
                        row.KPIId,
                        Selected  = row.selected,
                        TenantId  = login.ClientId
                    },
                    tx, ct).ConfigureAwait(false);
            }
        }
    }
}