using AnalyticsDAL.DTO.KPIEvaluation;
using AnalyticsDAL.Query.KPIEvaluation;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace AnalyticsDAL.CustomCode.KPIEvaluation
{
    public class KPIEvaluationDAL : IKPIEvaluationDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        public async Task<List<int>> GetAccessibleOuIds(int userId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<int>(
                login, KPIEvaluationQB.GET_ACCESSIBLE_OU_IDS, new { UserId = userId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<(DateTime PeriodFromDate, DateTime PeriodToDate)?> GetPeriodRange(
            int periodDetailId, LoginDTO login, CancellationToken ct)
        {
            var row = await _queryExecutor.QuerySingleAsync<PeriodRangeRow>(
                login, KPIEvaluationQB.GET_PERIOD_RANGE, new { PeriodDetailId = periodDetailId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return row is null ? null : (row.PeriodFromDate, row.PeriodToDate);
        }

        public async Task<int> ResolveDateKey(DateTime date, LoginDTO login, CancellationToken ct)
        {
            var dateKey = await _queryExecutor.ExecuteScalarAsync<int?>(
                login, KPIEvaluationQB.GET_DATE_KEY, new { TargetDate = date }, cancellationToken: ct)
                .ConfigureAwait(false);
            if (dateKey is null)
                throw new InvalidOperationException($"No DimDate row found for {date:yyyy-MM-dd} — DimDate needs extending.");
            return dateKey.Value;
        }

        public async Task<int?> ResolveLatestFFinanceDateKeyOnOrBefore(int ouId, int toDateKey, LoginDTO login, CancellationToken ct)
        {
            return await _queryExecutor.ExecuteScalarAsync<int?>(
                login, KPIEvaluationQB.GET_LATEST_DATE_KEY_ON_OR_BEFORE,
                new { OUId = ouId, ToDateKey = toDateKey }, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        public async Task<List<KpiDefinitionDTO>> GetComputableKpis(List<int> kpiIds, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<KpiDefinitionDTO>(
                login, KPIEvaluationQB.GET_COMPUTABLE_KPIS, new { KpiIds = kpiIds }, cancellationToken: ct)
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<List<int>> GetAllComputableKpiIds(LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<int>(
                login, KPIEvaluationQB.GET_ALL_COMPUTABLE_KPI_IDS, cancellationToken: ct)
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<Dictionary<string, byte>> GetMeasureAdditivity(LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<MeasureAdditivityRow>(
                login, KPIEvaluationQB.GET_MEASURE_ADDITIVITY, cancellationToken: ct)
                .ConfigureAwait(false);
            return rows.ToDictionary(r => r.MeasureCode, r => r.AdditivityType, StringComparer.OrdinalIgnoreCase);
        }

        public async Task<List<KpiStatusBandDTO>> GetStatusBands(List<int> kpiIds, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<KpiStatusBandDTO>(
                login, KPIEvaluationQB.GET_STATUS_BANDS, new { KpiIds = kpiIds }, cancellationToken: ct)
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<decimal?> GetPreviousActual(int kpiId, int periodDetailId, int ouId, LoginDTO login, CancellationToken ct)
        {
            return await _queryExecutor.ExecuteScalarAsync<decimal?>(
                login, KPIEvaluationQB.GET_PREVIOUS_ACTUAL,
                new { KPIId = kpiId, PeriodDetailId = periodDetailId, OUId = ouId }, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        public async Task<int?> GetPreviousPeriodDetailId(int periodDetailId, LoginDTO login, CancellationToken ct)
        {
            return await _queryExecutor.ExecuteScalarAsync<int?>(
                login, KPIEvaluationQB.GET_PREVIOUS_PERIOD_DETAIL_ID,
                new { PeriodDetailId = periodDetailId }, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        public async Task<int?> GetLatestPostedPeriodDetailId(int ouId, LoginDTO login, CancellationToken ct)
        {
            return await _queryExecutor.ExecuteScalarAsync<int?>(
                login, KPIEvaluationQB.GET_LATEST_POSTED_PERIOD_DETAIL_ID,
                new { OUId = ouId }, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        public async Task DeleteExistingValues(int periodDetailId, int ouId, LoginDTO login, CancellationToken ct)
        {
            await _queryExecutor.ExecuteAsync(
                login, KPIEvaluationQB.DELETE_EXISTING_VALUES,
                new { PeriodDetailId = periodDetailId, OUId = ouId }, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        public async Task InsertValue(
            int kpiId, int periodDetailId, int ouId, int dimOuId,
            decimal? actual, decimal? target, byte? trend, int? statusId,
            LoginDTO login, CancellationToken ct)
        {
            await _queryExecutor.ExecuteAsync(
                login, KPIEvaluationQB.INSERT_VALUE,
                new
                {
                    KPIId = kpiId, PeriodDetailId = periodDetailId, OUId = ouId, DimOuId = dimOuId,
                    Actual = actual, Target = target, Trend = trend, StatusId = statusId
                }, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<List<KpiValueResultDTO>> GetKpiValues(
            List<int> kpiIds, int periodDetailId, int ouId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<KpiValueRow>(
                login, KPIEvaluationQB.GET_KPI_VALUES,
                new { KpiIds = kpiIds, PeriodDetailId = periodDetailId, OUId = ouId }, cancellationToken: ct)
                .ConfigureAwait(false);

            return rows.Select(r => new KpiValueResultDTO
            {
                KPIId = r.KPIID,
                KPIName = r.KPINAME,
                Section = r.SECTION,
                // ActualType=2 (manual) KPIs never get a TKPIVALUE row — their value lives only in
                // TKPIMANUAL, matched by the same LEFT JOIN in GET_KPI_VALUES.
                ActualValue = r.ActualValue ?? r.ManualActualValue,
                TargetValue = r.TargetValue ?? r.ManualTargetValue,
                Trend = r.Trend,
                Score = r.Score,
                Color = r.Color,
                IsComputable = r.ActualValue is not null || r.ManualActualValue is not null
            }).ToList();
        }

        private class PeriodRangeRow
        {
            public DateTime PeriodFromDate { get; set; }
            public DateTime PeriodToDate { get; set; }
        }

        private class MeasureAdditivityRow
        {
            public string MeasureCode { get; set; } = string.Empty;
            public byte AdditivityType { get; set; }
        }

        private class KpiValueRow
        {
            public int KPIID { get; set; }
            public string KPINAME { get; set; } = string.Empty;
            public string? SECTION { get; set; }
            public decimal? ActualValue { get; set; }
            public decimal? TargetValue { get; set; }
            public byte? Trend { get; set; }
            public double? Score { get; set; }
            public byte? Color { get; set; }
            public decimal? ManualActualValue { get; set; }
            public decimal? ManualTargetValue { get; set; }
        }
    }
}