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

namespace AnalyticsDAL.CustomCode.KPI
{
    public class KPIDAL : IKPIDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        public async Task<string> GetKPI(int kpiId, LoginDTO login, CancellationToken ct)
        {
            var dto = await _queryExecutor.QuerySingleAsync<KPIDTO>(
                login,
                KPIQB.GET_KPI,
                new { KPIId = kpiId, TenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);

            if (dto != null)
            {
                var statusRows = await _queryExecutor.QueryAsync<KPISTATUSDTO>(
                    login,
                    KPIQB.GET_KPI_STATUS,
                    new { KPIId = kpiId, TenantId = login.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);

                dto.KPISTATUSArray = statusRows?.ToList();
            }

            return JsonSerializer.Serialize(dto);
        }

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

            return JsonSerializer.Serialize(list);
        }

        public async Task<string> GetKPIList(byte? measureType, int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
        {
            int offset = firstNumber > 0 ? firstNumber : 0;
            int pageSize = maxResult > 0 ? maxResult : 10;

            var result = await _queryExecutor.QueryPagedAsync<KPIPagedListDTO>(
                login,
                KPIQB.GET_KPI_LIST_PAGED,
                new { MeasureType = measureType, Offset = offset, PageSize = pageSize }).ConfigureAwait(false);

            return JsonSerializer.Serialize(result);
        }

        public async Task<int> SaveKPI(KPIDTO dto, LoginDTO login, CancellationToken ct)
        {
            await using var tx = await _queryExecutor.BeginTransactionAsync(login);
            try
            {
                await _queryExecutor.ExecuteAsync(
                    login,
                    KPIQB.SAVE_KPI,
                    new
                    {
                        dto.KPIId, dto.KPIName, dto.KPIDescription, dto.KPISection,
                        dto.ModuleId, dto.AnalysisId, dto.AnalysisQueryId,
                        dto.KPIMeasureType, dto.KPIDesireDirection, dto.KPIAnalysisLinkType,
                        dto.KPIActualType, dto.KPIActualValueExpression, dto.KPIActualAggregation,
                        dto.KPITargetType, dto.KPITargetValueExpession, dto.KPITargetAggregation,
                        dto.KPICalculationFrequency, dto.OwnerId,
                        dto.KPIDataFormat, dto.KPIAggregation, dto.KPISecurityLevel, dto.KPILevelPeriod,
                        dto.DIMOuId, dto.DIM1Id, dto.DIM2Id, dto.DIM3Id, dto.DIM4Id, dto.DIM5Id,
                        dto.KPISortOrder,
                        KPISourceType = (byte)5,
                        TenantId      = login.ClientId,
                        CreatedById   = login.UserId,
                        ModifiedById  = login.UserId
                    },
                    tx, ct).ConfigureAwait(false);

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

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

        public async Task UpdateKPI(KPIDTO dto, LoginDTO login, CancellationToken ct)
        {
            await using var tx = await _queryExecutor.BeginTransactionAsync(login);
            try
            {
                await _queryExecutor.ExecuteAsync(
                    login,
                    KPIQB.UPDATE_KPI,
                    new
                    {
                        dto.KPIId, dto.KPIName, dto.KPIDescription, dto.KPISection,
                        dto.ModuleId, dto.AnalysisId, dto.AnalysisQueryId,
                        dto.KPIMeasureType, dto.KPIDesireDirection, dto.KPIAnalysisLinkType,
                        dto.KPIActualType, dto.KPIActualValueExpression, dto.KPIActualAggregation,
                        dto.KPITargetType, dto.KPITargetValueExpession, dto.KPITargetAggregation,
                        dto.KPICalculationFrequency, dto.OwnerId,
                        dto.KPIDataFormat, dto.KPIAggregation, dto.KPISecurityLevel, dto.KPILevelPeriod,
                        dto.DIMOuId, dto.DIM1Id, dto.DIM2Id, dto.DIM3Id, dto.DIM4Id, dto.DIM5Id,
                        dto.KPISortOrder, dto.KPIStatus,
                        TenantId     = login.ClientId,
                        ModifiedById = login.UserId
                    },
                    tx, ct).ConfigureAwait(false);

                // Replace status rows: delete all then re-insert
                await _queryExecutor.ExecuteAsync(
                    login,
                    KPIQB.DELETE_STATUS_ROWS,
                    new { KPIId = dto.KPIId, TenantId = login.ClientId },
                    tx, ct).ConfigureAwait(false);

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

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

        public async Task DeleteKPI(int kpiId, LoginDTO login, CancellationToken ct)
        {
            await _queryExecutor.ExecuteAsync(
                login,
                KPIQB.DELETE_KPI,
                new { KPIId = kpiId, 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,
                KPIQB.CHECK_DUPLICATE_NAME,
                new { KPIName = name, TenantId = login.ClientId, KPIId = excludeId },
                cancellationToken: ct).ConfigureAwait(false);

            return count > 0;
        }

        private async Task SaveStatusRows(KPIDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            if (dto.KPISTATUSArray == null || dto.KPISTATUSArray.Count == 0)
                return;

            short slNo = 1;
            foreach (var row in dto.KPISTATUSArray)
            {
                await _queryExecutor.ExecuteAsync(
                    login,
                    KPIQB.SAVE_STATUS_ROW,
                    new
                    {
                        KPISTATUSId       = row.KPISTATUSId,
                        KPISTATUSSLNo     = slNo++,
                        KPISTATUSValueFrom = row.KPISTATUSValueFrom,
                        KPISTATUSValueTo   = row.KPISTATUSValueTo,
                        KPISTATUSScore    = row.KPISTATUSScore,
                        KPISTATUSColor    = row.KPISTATUSColor,
                        KPIId             = dto.KPIId,
                        TenantId          = login.ClientId,
                        CreatedById       = login.UserId
                    },
                    tx, ct).ConfigureAwait(false);
            }
        }
    }
}