using System.Data;
using System.Text.RegularExpressions;
using AnalyticsBLL.AnalysisAggregation;
using AnalyticsDAL.CustomCode.KPIEvaluation;
using AnalyticsDAL.DTO.AnalysisAggregation.V1;
using AnalyticsDAL.DTO.KPIEvaluation;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.Resource.Response;
using GB5Shared.Telemetry;
using Microsoft.Extensions.Logging;

namespace AnalyticsBLL.KPIEvaluation
{
    // Posts MKPI's ACTUALTYPE=0/3 rows into TKPIVALUE for a given (PeriodDetailId, OUId) — a
    // scheduled/JobEngine-triggered batch (mirrors WarehouseFactPostingBLL's own per-OU scoped
    // DELETE-then-INSERT discipline), never a compute-on-read path. GetKPIValues (the interactive
    // read) just reads the resulting snapshot.
    //
    // Legacy's own KPI engine (GB4Solution) proved this two-pass shape works — non-composite KPIs
    // posted first, composite (bracket-referencing sibling KPI names) posted second, substituting
    // already-posted sibling values — but legacy's own execution mechanism was string-splicing
    // ACTUALVALUEEXPRESSION into dynamic SQL (including a PIVOT/UNPIVOT `execute(@query)` step for
    // the composite case). This engine keeps the two-pass SHAPE but replaces the execution
    // mechanism with the same safe, in-memory DataColumn.Expression evaluator already proven by the
    // Ratio Analysis Dashboard — expression text never reaches SQL.
    public class KPIEvaluationBLL : IKPIEvaluationBLL
    {
        // The production FFINANCE BI catalog row (MBICATALOG.BICATALOGID), promoted from the pilot
        // FFINANCE_TEST entry in Phase 2. NOT read from MKPI.ANALYSISID — that column has a real FK
        // to MANALYSIS (confirmed live via FK_MKPI_ANALYSISID; the 19 real KPI rows' ANALYSISID
        // already correctly points to a genuine, if minimally-configured, legacy MANALYSIS
        // "FFINANCE Analysis" row — a naming/categorization link unrelated to the modern BI
        // Platform). Hardcoded here, same precedent already established by
        // RatioAnalysisReportQB.GET_MEASURE_ADDITIVITY's own hardcoded FACTID=2 — this MVP pass is
        // FFINANCE-scoped only.
        private const int FFinanceBiCatalogId = 2;

        // Bare "TABLE.MEASURE" token — currently only FFINANCE-prefixed expressions are supported
        // (ANALYSISLINKTYPE=0 rows), matching this pass's MVP scope (the 19 real Finance KPIs).
        private static readonly Regex MeasureToken = new(@"\bFFINANCE\.([A-Za-z_][A-Za-z0-9_]*)\b", RegexOptions.IgnoreCase | RegexOptions.Compiled);

        // Inline 'case when X=0 then A else B end' guards baked into several real MKPI rows'
        // ACTUALVALUEEXPRESSION (e.g. 'FFINANCE.RECEIVABLE/case when FFINANCE.SALES=0 then 1 else
        // FFINANCE.SALES end') — DataColumn.Expression has no CASE WHEN syntax of its own (only
        // IIF(condition, true, false)), so this rewrites the SQL-shaped guard into the equivalent
        // IIF() call rather than silently dropping it or letting it fail to parse.
        private static readonly Regex CaseWhenInline = new(
            @"case\s+when\s+(?<cond>.+?)\s*=\s*0\s+then\s+(?<then>.+?)\s+else\s+(?<else>.+?)\s+end",
            RegexOptions.IgnoreCase | RegexOptions.Singleline | RegexOptions.Compiled);

        // Composite KPI (ACTUALTYPE=3) bracket references to OTHER KPIs' own KPINAME, e.g.
        // '[AverageInventory]/[AverageCOGS]' — matches legacy's own bracket-token convention.
        private static readonly Regex BracketToken = new(@"\[([^\]]+)\]", RegexOptions.Compiled);

        private readonly IKPIEvaluationDAL _dal;
        private readonly IAnalysisAggregationService _aggregationService;
        private readonly ILogger<KPIEvaluationBLL> _logger;

        public KPIEvaluationBLL(
            IKPIEvaluationDAL dal,
            IAnalysisAggregationService aggregationService,
            ILogger<KPIEvaluationBLL> logger)
        {
            _dal = dal;
            _aggregationService = aggregationService;
            _logger = logger;
        }

        public async Task<string> PostKPIValues(int periodDetailId, int ouId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                GB5Trace.Step("validate-kpi-posting", new { periodDetailId, ouId });

                var range = await _dal.GetPeriodRange(periodDetailId, login, ct).ConfigureAwait(false);
                if (range is null)
                    throw new ArgumentException($"PeriodDetailId {periodDetailId} does not resolve to a real MGBPERIODDETAIL row.");

                var fromDateKey = await _dal.ResolveDateKey(range.Value.PeriodFromDate, login, ct).ConfigureAwait(false);
                var toDateKey = await _dal.ResolveDateKey(range.Value.PeriodToDate, login, ct).ConfigureAwait(false);

                var allKpiIds = await _dal.GetAllComputableKpiIds(login, ct).ConfigureAwait(false);
                if (allKpiIds.Count == 0)
                    return "No computable KPIs found (ACTUALTYPE 0/3).";

                var kpis = await _dal.GetComputableKpis(allKpiIds, login, ct).ConfigureAwait(false);
                var pass1All = kpis.Where(k => k.ActualType == 0).ToList();
                var pass2 = kpis.Where(k => k.ActualType == 3).ToList();

                // A KPI referencing a measure never registered in MWAREHOUSEMEASURE for this fact
                // (e.g. RECEIVABLE/PAYABLE/AVERAGEASSETS — real MKPI rows exist for these, but their
                // measures were explicitly deferred out of the FFINANCE MVP posting scope) is SKIPPED
                // -- not fatal, and not allowed to abort the batched fetch for every OTHER KPI in the
                // same run. Same "skip uncataloged, don't fail the whole batch" discipline the Ratio
                // Analysis Dashboard already established for this identical situation.
                var additivity = await _dal.GetMeasureAdditivity(login, ct).ConfigureAwait(false);
                var pass1 = new List<KpiDefinitionDTO>();
                var uncomputablePass1 = new List<KpiDefinitionDTO>();
                foreach (var kpi in pass1All)
                {
                    var codes = MeasureToken.Matches(kpi.ActualValueExpression ?? string.Empty)
                        .Select(m => m.Groups[1].Value).Distinct(StringComparer.OrdinalIgnoreCase);
                    if (codes.All(c => additivity.ContainsKey(c)))
                        pass1.Add(kpi);
                    else
                        uncomputablePass1.Add(kpi);
                }

                GB5Trace.Step("post-kpi-pass1", new { periodDetailId, ouId, count = pass1.Count, skipped = uncomputablePass1.Count });
                var operandValues = await FetchOperandValuesAsync(pass1, additivity, ouId, fromDateKey, toDateKey, login, ct).ConfigureAwait(false);

                await _dal.DeleteExistingValues(periodDetailId, ouId, login, ct).ConfigureAwait(false);

                var postedActuals = new Dictionary<string, decimal?>(StringComparer.OrdinalIgnoreCase);
                var posted = 0;

                foreach (var kpi in pass1)
                {
                    var actual = EvaluateExpression(kpi.ActualValueExpression, operandValues);
                    postedActuals[kpi.KPIName] = actual;
                    await PostOneKpiAsync(kpi, periodDetailId, ouId, actual, login, ct).ConfigureAwait(false);
                    posted++;
                }

                foreach (var kpi in uncomputablePass1)
                {
                    postedActuals[kpi.KPIName] = null;
                    await PostOneKpiAsync(kpi, periodDetailId, ouId, null, login, ct).ConfigureAwait(false);
                    posted++;
                }

                GB5Trace.Step("post-kpi-pass2-composite", new { periodDetailId, ouId, count = pass2.Count });
                foreach (var kpi in pass2)
                {
                    // Substitute [SiblingKpiName] bracket tokens with that sibling's just-posted
                    // Pass 1 actual value — this only resolves one level deep (a composite
                    // referencing another composite is out of this pass's scope; today's live
                    // catalog has exactly one composite row referencing two non-composite
                    // siblings, matching this).
                    var missingSibling = false;
                    var expr = BracketToken.Replace(kpi.ActualValueExpression ?? string.Empty, m =>
                    {
                        var siblingName = m.Groups[1].Value;
                        if (!postedActuals.TryGetValue(siblingName, out var value) || value is null)
                        {
                            // Sibling not found in Pass 1 (not a real KPI name, not ACTUALTYPE=0, or
                            // itself non-calculable this period) — leave this composite non-
                            // calculable too rather than failing the whole posting run.
                            missingSibling = true;
                            return "0";
                        }
                        return value.Value.ToString(System.Globalization.CultureInfo.InvariantCulture);
                    });

                    decimal? actual = null;
                    if (!missingSibling)
                    try
                    {
                        using var dt = new DataTable();
                        dt.Columns.Add(new DataColumn("RESULT", typeof(decimal), expr));
                        var dr = dt.NewRow();
                        dt.Rows.Add(dr);
                        var computed = dr["RESULT"];
                        if (computed is not null and not DBNull)
                            actual = Convert.ToDecimal(computed);
                    }
                    catch (Exception)
                    {
                        actual = null;
                    }

                    await PostOneKpiAsync(kpi, periodDetailId, ouId, actual, login, ct).ConfigureAwait(false);
                    posted++;
                }

                return $"{SuccessResponse.SaveSuccessMessage} {posted} KPI value(s) posted for PeriodDetailId {periodDetailId}, OU {ouId}.";
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("post-kpi-values-failed", ex);
                _logger.LogError(ex, "PostKPIValues failed for PeriodDetailId {PeriodDetailId}, OUId {OUId}", periodDetailId, ouId);
                throw;
            }
        }

        private async Task PostOneKpiAsync(
            KpiDefinitionDTO kpi, int periodDetailId, int ouId, decimal? actual, LoginDTO login, CancellationToken ct)
        {
            // TKPIVALUE.STATUSID is NOT NULL (DB default -1, matching MKPISTATUS's own -1/NONE
            // sentinel row) — a bound NULL parameter overrides the column default at INSERT time,
            // so "no band matched" must be represented as the real sentinel id, never a null param.
            var bands = await _dal.GetStatusBands(new List<int> { kpi.KPIId }, login, ct).ConfigureAwait(false);
            var statusId = actual is null
                ? -1
                : bands.FirstOrDefault(b => actual.Value >= (decimal)b.ValueFrom && actual.Value <= (decimal)b.ValueTo)?.KPIStatusId ?? -1;

            byte? trend = null;
            if (actual is not null)
            {
                var previousPeriodDetailId = await _dal.GetPreviousPeriodDetailId(periodDetailId, login, ct).ConfigureAwait(false);
                if (previousPeriodDetailId is not null)
                {
                    var previousActual = await _dal.GetPreviousActual(kpi.KPIId, previousPeriodDetailId.Value, ouId, login, ct).ConfigureAwait(false);
                    if (previousActual is not null)
                        trend = actual.Value > previousActual.Value ? (byte)1 : actual.Value < previousActual.Value ? (byte)2 : (byte)0;
                }
            }

            await _dal.InsertValue(kpi.KPIId, periodDetailId, ouId, kpi.DimOuId, actual, null, trend, statusId, login, ct)
                .ConfigureAwait(false);
        }

        private async Task<Dictionary<string, decimal>> FetchOperandValuesAsync(
            List<KpiDefinitionDTO> pass1Kpis, Dictionary<string, byte> additivity,
            int ouId, int fromDateKey, int toDateKey, LoginDTO login, CancellationToken ct)
        {
            var operandValues = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);
            if (pass1Kpis.Count == 0)
                return operandValues;

            var neededCodes = pass1Kpis
                .SelectMany(k => MeasureToken.Matches(k.ActualValueExpression ?? string.Empty).Select(m => m.Groups[1].Value))
                .Distinct(StringComparer.OrdinalIgnoreCase)
                .ToList();
            if (neededCodes.Count == 0)
                return operandValues;

            var flowCodes = neededCodes.Where(c => additivity.TryGetValue(c, out var a) && a == 0).ToList();
            var asOfCodes = neededCodes.Where(c => additivity.TryGetValue(c, out var a) && a != 0).ToList();

            if (flowCodes.Count > 0)
            {
                var definition = new AnalysisQueryDefinition
                {
                    DatasetId = FFinanceBiCatalogId,
                    Measures = flowCodes.Select(c => new AnalysisMeasureDefinitionDTO { Field = c, Aggregation = 1 }).ToList(),
                    Filters = new List<AnalysisFilterDTO>
                    {
                        new() { Field = "OUID", Operator = CriteriaDTO.OperationType.Equal, Values = new object?[] { ouId } },
                        new() { Field = "DATEID", Operator = CriteriaDTO.OperationType.Between, Values = new object?[] { fromDateKey, toDateKey } }
                    }
                };
                var result = await _aggregationService.ExecuteAsync(definition, login, ct).ConfigureAwait(false);
                MergeOperandRow(result.Data.FirstOrDefault(), flowCodes, operandValues);
            }

            if (asOfCodes.Count > 0)
            {
                // A balance/snapshot measure is only posted on the days FFINANCE's own posting job
                // ran — reading "as of period end" means the most recently posted value ON OR
                // BEFORE that date, never an exact-date match (which would silently resolve to 0
                // whenever the period's last calendar day itself has no posted row — the normal
                // case for FFINANCE's real posting cadence). Resolved directly against FFINANCE
                // (not through the BI engine) purely to pick WHICH date to filter on; the actual
                // operand values are still always fetched through IAnalysisAggregationService.
                var asOfDateKey = await _dal.ResolveLatestFFinanceDateKeyOnOrBefore(ouId, toDateKey, login, ct).ConfigureAwait(false);
                if (asOfDateKey is not null)
                {
                    var definition = new AnalysisQueryDefinition
                    {
                        DatasetId = FFinanceBiCatalogId,
                        // MAX over a single (OUID, DATEID) row is a safe passthrough — never SUM
                        // here, since SUM across dates is exactly what ADDITIVITYTYPE=SemiAdditive
                        // forbids for a balance-sheet-style snapshot measure.
                        Measures = asOfCodes.Select(c => new AnalysisMeasureDefinitionDTO { Field = c, Aggregation = 7 }).ToList(),
                        Filters = new List<AnalysisFilterDTO>
                        {
                            new() { Field = "OUID", Operator = CriteriaDTO.OperationType.Equal, Values = new object?[] { ouId } },
                            new() { Field = "DATEID", Operator = CriteriaDTO.OperationType.Equal, Values = new object?[] { asOfDateKey } }
                        }
                    };
                    var result = await _aggregationService.ExecuteAsync(definition, login, ct).ConfigureAwait(false);
                    MergeOperandRow(result.Data.FirstOrDefault(), asOfCodes, operandValues);
                }
            }

            return operandValues;
        }

        private static void MergeOperandRow(Dictionary<string, object?>? row, List<string> codes, Dictionary<string, decimal> target)
        {
            foreach (var code in codes)
            {
                target[code] = row is not null && row.TryGetValue(code, out var value) && value is not null
                    ? Convert.ToDecimal(value)
                    : 0m;
            }
        }

        // NOTE: DataTable.Compute(expr, filter) only accepts AGGREGATE expressions (Sum/Avg/...) —
        // it throws on plain per-row arithmetic like 'A/B'. A computed DataColumn (DataColumn.
        // Expression) is the correct API here, same fix already proven by the Ratio Analysis
        // Dashboard — and it resolves column names case-insensitively by default.
        private static decimal? EvaluateExpression(string? rawExpression, Dictionary<string, decimal> operandValues)
        {
            if (string.IsNullOrWhiteSpace(rawExpression))
                return null;

            var withIif = CaseWhenInline.Replace(rawExpression, m =>
                $"IIF({m.Groups["cond"].Value}=0,{m.Groups["then"].Value},{m.Groups["else"].Value})");
            var expr = MeasureToken.Replace(withIif, m => m.Groups[1].Value.ToUpperInvariant());

            var codes = MeasureToken.Matches(rawExpression).Select(m => m.Groups[1].Value)
                .Distinct(StringComparer.OrdinalIgnoreCase).ToList();

            try
            {
                using var dt = new DataTable();
                foreach (var code in codes)
                    dt.Columns.Add(code.ToUpperInvariant(), typeof(decimal));
                var dr = dt.NewRow();
                foreach (var code in codes)
                    dr[code.ToUpperInvariant()] = operandValues.TryGetValue(code, out var v) ? v : 0m;
                dt.Rows.Add(dr);
                dt.Columns.Add(new DataColumn("RESULT", typeof(decimal), expr));
                var computed = dr["RESULT"];
                return computed is not null and not DBNull ? Convert.ToDecimal(computed) : null;
            }
            catch (Exception)
            {
                // Divide-by-zero or an otherwise non-calculable formula for this OU/period — leave
                // null rather than failing the whole posting run for one KPI.
                return null;
            }
        }

        // Mirrors TMSBLL.KpiPosting.TmsKpiIds.WholeOrgOuId (not referenced directly -- Analytics
        // has no project dependency on TMS): TmsKpiPostingBLL posts several KPIs once per tenant
        // with TKPIVALUE.MEMBEROUID = -1, since those KPIs are company-wide, not OU-scoped. This
        // is unrelated to MUSERACCESSRIGHTS' own use of -1 (there it marks an OU-group-based
        // grant row), so it can never appear in GetAccessibleOuIds' result and must be
        // special-cased before the per-OU membership check below.
        private const int WholeOrganizationOuId = -1;

        public async Task<List<KpiValueResultDTO>> GetKPIValues(
            List<int>? kpiIds, int periodDetailId, int ouId, LoginDTO login, CancellationToken ct)
        {
            var accessibleOuIds = await _dal.GetAccessibleOuIds(login.UserId, login, ct).ConfigureAwait(false);
            var hasAccess = ouId == WholeOrganizationOuId
                ? accessibleOuIds.Count > 0
                : accessibleOuIds.Contains(ouId);
            if (!hasAccess)
                throw new InvalidOperationException("You do not have access to the requested organizational unit.");

            var effectiveKpiIds = kpiIds is { Count: > 0 }
                ? kpiIds
                : await _dal.GetAllComputableKpiIds(login, ct).ConfigureAwait(false);

            if (effectiveKpiIds.Count == 0)
                return new List<KpiValueResultDTO>();

            return await _dal.GetKpiValues(effectiveKpiIds, periodDetailId, ouId, login, ct).ConfigureAwait(false);
        }

        // Portlet Integration (Phase 5) — a dashboard card never pins a static PeriodDetailId in
        // its own config; it always resolves to whatever was most recently posted for the
        // portlet's OU, so the card reflects current data without needing reconfiguration after
        // every posting run. OU itself IS required on the portlet (no "resolve from user session"
        // fallback — LoginDTO carries no default-OU concept to resolve that from; a portlet with
        // no OU configured is a real configuration error, not something to silently guess at).
        public async Task<List<KpiValueResultDTO>> GetKPIValuesForPortlet(
            List<int>? kpiIds, int ouId, LoginDTO login, CancellationToken ct)
        {
            var periodDetailId = await _dal.GetLatestPostedPeriodDetailId(ouId, login, ct).ConfigureAwait(false);
            if (periodDetailId is null)
                return new List<KpiValueResultDTO>();

            return await GetKPIValues(kpiIds, periodDetailId.Value, ouId, login, ct).ConfigureAwait(false);
        }
    }
}