using System.Data.Common;
using System.Text.Json;
using AnalyticsDAL.DTO.KPILIST;
using AnalyticsDAL.DTO.KPILISTDETAIL;
using AnalyticsDAL.Query.KPILIST;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;

namespace AnalyticsDAL.CustomCode.KPILIST
{
    public class KPILISTDAL : IKPILISTDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly AutoNumber _autoNumber;

        public KPILISTDAL(IQueryExecutor queryExecutor, AutoNumber autoNumber)
        {
            _queryExecutor = queryExecutor;
            _autoNumber = autoNumber;
        }

        public async Task<string> GetKPIList(int kpiListId, LoginDTO login, CancellationToken ct)
        {
            var dto = await _queryExecutor.QuerySingleAsync<KPILISTDTO>(
                login,
                KPILISTQB.GET_KPILIST,
                new { KPILISTId = kpiListId, TenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);

            if (dto != null)
            {
                var details = await _queryExecutor.QueryAsync<KPILISTDETAILDTO>(
                    login,
                    KPILISTQB.GET_KPILIST_DETAILS,
                    new { KPILISTId = kpiListId, TenantId = login.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);

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

            return JsonSerializer.Serialize(dto);
        }

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

            return JsonSerializer.Serialize(list);
        }

        public async Task<int> SaveKPIList(KPILISTDTO dto, LoginDTO login, CancellationToken ct)
        {
            await using var tx = await _queryExecutor.BeginTransactionAsync(login);
            try
            {
                var autoNumber = await _autoNumber.GetNumberAsync(1, "KPILIST", login).ConfigureAwait(false);
                var newId = autoNumber.StartNumber;

                await _queryExecutor.ExecuteAsync(
                    login,
                    KPILISTQB.SAVE_KPILIST,
                    new
                    {
                        KPILISTId = newId,
                        dto.KPILISTName, dto.KPILISTDescription, dto.KPILISTListType,
                        dto.RoleId, dto.UserId, dto.UserGroupId, dto.KPILISTRemarks,
                        dto.KPILISTSortOrder,
                        KPILISTSourceType = (byte)5,
                        TenantId          = login.ClientId,
                        CreatedById       = login.UserId,
                        ModifiedById      = login.UserId
                    },
                    tx, ct).ConfigureAwait(false);

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

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

        public async Task UpdateKPIList(KPILISTDTO dto, LoginDTO login, CancellationToken ct)
        {
            await using var tx = await _queryExecutor.BeginTransactionAsync(login);
            try
            {
                await _queryExecutor.ExecuteAsync(
                    login,
                    KPILISTQB.UPDATE_KPILIST,
                    new
                    {
                        dto.KPILISTId, dto.KPILISTName, dto.KPILISTDescription, dto.KPILISTListType,
                        dto.RoleId, dto.UserId, dto.UserGroupId, dto.KPILISTRemarks,
                        dto.KPILISTSortOrder, dto.KPILISTStatus,
                        TenantId     = login.ClientId,
                        ModifiedById = login.UserId
                    },
                    tx, ct).ConfigureAwait(false);

                await _queryExecutor.ExecuteAsync(
                    login,
                    KPILISTQB.DELETE_DETAIL_ROWS,
                    new { KPILISTId = dto.KPILISTId, 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 DeleteKPIList(int kpiListId, LoginDTO login, CancellationToken ct)
        {
            await _queryExecutor.ExecuteAsync(
                login,
                KPILISTQB.DELETE_KPILIST,
                new { KPILISTId = kpiListId, TenantId = login.ClientId, ModifiedById = login.UserId },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<bool> IsDuplicateName(string name, int excludeId, LoginDTO login, CancellationToken ct)
        {
            var count = await _queryExecutor.ExecuteScalarAsync<int>(
                login,
                KPILISTQB.CHECK_DUPLICATE_NAME,
                new { KPILISTName = name, TenantId = login.ClientId, KPILISTId = excludeId },
                cancellationToken: ct).ConfigureAwait(false);

            return count > 0;
        }

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

            short slNo = 1;
            foreach (var row in dto.KPILISTDETAILArray)
            {
                var autoNumber = await _autoNumber.GetNumberAsync(1, "KPILISTDETAIL", login).ConfigureAwait(false);

                await _queryExecutor.ExecuteAsync(
                    login,
                    KPILISTQB.SAVE_DETAIL_ROW,
                    new
                    {
                        KPILISTDETAILId             = autoNumber.StartNumber,
                        KPILISTDETAILSLNo          = slNo++,
                        KPILISTId                   = dto.KPILISTId,
                        row.KPIId,
                        KPILISTDETAILName           = row.KPILISTDETAILName,
                        KPILISTDETAILPeriodFilter   = row.KPILISTDETAILPeriodFilter,
                        KPILISTDETAILPeriodType     = row.KPILISTDETAILPeriodType,
                        GBPeriodDetailId            = row.GBPeriodDetailId,
                        KPILISTDETAILDIMOuType      = row.KPILISTDETAILDIMOuType,
                        LEVELOUId                   = row.LEVELOUId,
                        ValueOuId                   = row.ValueOuId,
                        KPILISTDETAILDIM1Type       = row.KPILISTDETAILDIM1Type,
                        LEVEL1Id                    = row.LEVEL1Id,
                        Value1Id                    = row.Value1Id,
                        KPILISTDETAILDIM2Type       = row.KPILISTDETAILDIM2Type,
                        LEVEL2Id                    = row.LEVEL2Id,
                        Value2Id                    = row.Value2Id,
                        KPILISTDETAILDIM3Type       = row.KPILISTDETAILDIM3Type,
                        LEVEL3Id                    = row.LEVEL3Id,
                        Value3Id                    = row.Value3Id,
                        KPILISTDETAILDIM4Type       = row.KPILISTDETAILDIM4Type,
                        LEVEL4Id                    = row.LEVEL4Id,
                        Value4Id                    = row.Value4Id,
                        KPILISTDETAILDIM5Type       = row.KPILISTDETAILDIM5Type,
                        LEVEL5Id                    = row.LEVEL5Id,
                        Value5Id                    = row.Value5Id,
                        TenantId                    = login.ClientId
                    },
                    tx, ct).ConfigureAwait(false);
            }
        }
    }
}