using System.Data;
using System.Text.RegularExpressions;
using AccountsDAL.CustomCode.AccountReports;
using AccountsDAL.DTO.AccountsReports;
using AccountsDAL.Query.AccountReports;
using AccountsDAL.Query.Warehouse;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace AccountsDAL.CustomCode.AccountReports.RatioAnalysis
{
    // Ratio Analysis Dashboard — reads operand values from the already-posted FFINANCE fact table
    // (see RatioAnalysisReportQB.cs and the plan file's "Ratio Analysis Dashboard" section) and
    // evaluates each MRATIO row's (stripped) EXPRESSION via System.Data.DataTable.Compute — a safe,
    // in-memory arithmetic evaluator (supports + - * / and parentheses natively), never dynamic SQL.
    // This replaces legacy's own sp_executesql-per-ratio engine, which executed each admin-editable
    // expression as a live SQL statement against a #tempratio staging table.
    public class RatioAnalysisReportDAL : IRatioAnalysisReportDAL
    {
        private static readonly Regex SafeIdentifier = new(@"^[A-Z0-9_]+$", RegexOptions.Compiled);
        private static readonly Regex MeasureCodeToken = new(@"\b[A-Za-z_][A-Za-z0-9_]*\b", RegexOptions.Compiled);

        // Legacy's EXPRESSION column wraps every division-based ratio in a denominator-zero guard,
        // e.g. 'case when currentliabilities <> 0 then currentassets/CurrentLiabilities else 0 end'.
        // This engine already re-implements that guard in C# (the try/catch below), so the wrapper is
        // stripped down to the bare arithmetic ('currentassets/CurrentLiabilities') before evaluation
        // — DataTable.Compute has no CASE WHEN syntax of its own (it has its own Iif() function
        // instead, which the raw legacy text doesn't use). A handful of rows (e.g. RATIOID 120, "Free
        // Cash Flow") have no wrapper at all — the regex simply won't match and the raw text is used
        // as-is.
        private static readonly Regex CaseWhenGuard = new(
            @"^\s*case\s+when\s+.+?<>\s*0\s+then\s+(?<expr>.+?)\s+else\s+0\s+end\s*$",
            RegexOptions.IgnoreCase | RegexOptions.Singleline | RegexOptions.Compiled);

        private readonly IQueryExecutor _queryExecutor;

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

        private class RatioDefinitionDTO
        {
            public int RatioId { get; set; }
            public string Category { get; set; } = string.Empty;
            public string RatioName { get; set; } = string.Empty;
            public string? DisplayFormula { get; set; }
            public string RawExpression { get; set; } = string.Empty;
            public string? Explanation { get; set; }
            public short SortOrder { get; set; }
        }

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

        private class ComputableRatio
        {
            public required RatioDefinitionDTO Ratio { get; init; }
            public required string ComputeExpression { get; init; }
            public required List<string> CanonicalCodes { get; init; }
        }

        public async Task<List<RatioAnalysisDashBoardDTO>> GetRatioAnalysisDashboard(
            DateTime fromDate, DateTime toDate, List<int>? ouIds, int ouGroupId, LoginDTO loginDTO, CancellationToken ct)
        {
            var accessibleOuIds = (await _queryExecutor
                .QueryAsync<int>(loginDTO, RatioAnalysisReportQB.GET_ACCESSIBLE_OU_IDS,
                    new { UserId = loginDTO.UserId }, cancellationToken: ct)
                .ConfigureAwait(false)).ToHashSet();

            var requestedOuIds = await AccountReportFilterBuilder
                .ResolveOuIdsWithGroupAsync(_queryExecutor, loginDTO, ouIds, ouGroupId, ct)
                .ConfigureAwait(false);

            List<int> effectiveOuIds;
            if (requestedOuIds is { Count: > 0 })
            {
                effectiveOuIds = requestedOuIds.Where(accessibleOuIds.Contains).ToList();
                if (effectiveOuIds.Count == 0)
                    throw new InvalidOperationException(
                        "You do not have access to the requested organizational unit(s).");
            }
            else
            {
                effectiveOuIds = accessibleOuIds.ToList();
            }

            var ratios = (await _queryExecutor
                .QueryAsync<RatioDefinitionDTO>(loginDTO, RatioAnalysisReportQB.GET_ACTIVE_RATIOS,
                    new { TenantId = loginDTO.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false)).ToList();

            // Canonical (uppercase) MeasureCode -> AdditivityType, keyed case-insensitively so a
            // legacy EXPRESSION referencing 'currentliabilities' or 'CurrentLiabilities' both resolve
            // to FFINANCE's own 'CURRENTLIABILITIES' column.
            var catalog = (await _queryExecutor
                .QueryAsync<MeasureAdditivityDTO>(loginDTO, RatioAnalysisReportQB.GET_MEASURE_ADDITIVITY,
                    cancellationToken: ct)
                .ConfigureAwait(false))
                .ToDictionary(m => m.MeasureCode, m => m, StringComparer.OrdinalIgnoreCase);

            // A ratio referencing any measure not yet posted to FFINANCE (e.g. Quick Ratio needs
            // 'inventory', Market ratios need 'shareprice'/'eps' — concepts FFINANCE doesn't model at
            // all) is SKIPPED, not fatal — this real legacy catalog is broader than this session's
            // FFINANCE MVP measure set, and that gap is expected, not a bug.
            var computable = new List<ComputableRatio>();
            foreach (var ratio in ratios)
            {
                var match = CaseWhenGuard.Match(ratio.RawExpression);
                var computeExpression = match.Success ? match.Groups["expr"].Value : ratio.RawExpression;

                var rawTokens = MeasureCodeToken.Matches(computeExpression)
                    .Select(m => m.Value).Distinct(StringComparer.OrdinalIgnoreCase).ToList();

                var canonicalCodes = new List<string>();
                var allKnown = true;
                foreach (var token in rawTokens)
                {
                    if (!catalog.TryGetValue(token, out var info))
                    {
                        allKnown = false;
                        break;
                    }
                    canonicalCodes.Add(ValidateIdentifier(info.MeasureCode));
                }
                if (!allKnown) continue;

                computable.Add(new ComputableRatio
                {
                    Ratio = ratio,
                    ComputeExpression = computeExpression,
                    CanonicalCodes = canonicalCodes.Distinct(StringComparer.OrdinalIgnoreCase).ToList(),
                });
            }

            var neededCodes = computable
                .SelectMany(c => c.CanonicalCodes)
                .Distinct(StringComparer.OrdinalIgnoreCase)
                .ToList();

            var flowCodes = neededCodes.Where(c => catalog[c].AdditivityType == 0).ToList();
            var asOfCodes = neededCodes.Where(c => catalog[c].AdditivityType != 0).ToList();

            var fromDateKey = await ResolveDateKeyAsync(fromDate, loginDTO, ct).ConfigureAwait(false);
            var toDateKey = await ResolveDateKeyAsync(toDate, loginDTO, ct).ConfigureAwait(false);

            var operandValues = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);

            if (flowCodes.Count > 0)
            {
                var sql = RatioAnalysisReportQB.GET_OPERAND_VALUES
                    .Replace("{SelectColumns}", string.Join(",\n    ", flowCodes.Select(c => $"SUM({c}) AS {c}")))
                    .Replace("{DateFilter}", "DATEID BETWEEN @FromDateKey AND @ToDateKey");
                var row = await _queryExecutor
                    .QuerySingleAsync<dynamic>(loginDTO, sql,
                        new { OuIds = effectiveOuIds, FromDateKey = fromDateKey, ToDateKey = toDateKey, TenantId = loginDTO.ClientId },
                        cancellationToken: ct)
                    .ConfigureAwait(false);
                MergeRow(row, flowCodes, operandValues);
            }

            if (asOfCodes.Count > 0)
            {
                var sql = RatioAnalysisReportQB.GET_OPERAND_VALUES
                    .Replace("{SelectColumns}", string.Join(",\n    ", asOfCodes.Select(c => $"SUM({c}) AS {c}")))
                    .Replace("{DateFilter}", "DATEID = @ToDateKey");
                var row = await _queryExecutor
                    .QuerySingleAsync<dynamic>(loginDTO, sql,
                        new { OuIds = effectiveOuIds, ToDateKey = toDateKey, TenantId = loginDTO.ClientId },
                        cancellationToken: ct)
                    .ConfigureAwait(false);
                MergeRow(row, asOfCodes, operandValues);
            }

            var result = new List<RatioAnalysisDashBoardDTO>();
            foreach (var c in computable)
            {
                decimal? resultValue = null;
                try
                {
                    // NOTE: DataTable.Compute(expr, filter) only accepts AGGREGATE expressions
                    // (Sum/Avg/Count/... or a bare column) — it throws EvaluateException on plain
                    // per-row arithmetic like 'A/B'. A computed DataColumn (DataColumn.Expression)
                    // is the correct API for evaluating an arbitrary arithmetic expression against a
                    // row's own column values, and — confirmed live — resolves column names
                    // case-insensitively by default, so legacy's freely-mixed-case EXPRESSION text
                    // (e.g. 'currentassets/CurrentLiabilities') needs no rewriting.
                    using var dt = new DataTable();
                    foreach (var code in c.CanonicalCodes)
                        dt.Columns.Add(code, typeof(decimal));
                    var dr = dt.NewRow();
                    foreach (var code in c.CanonicalCodes)
                        dr[code] = operandValues.TryGetValue(code, out var v) ? v : 0m;
                    dt.Rows.Add(dr);
                    dt.Columns.Add(new DataColumn("RESULT", typeof(decimal), c.ComputeExpression));
                    var computed = dr["RESULT"];
                    if (computed is not null and not DBNull)
                        resultValue = Convert.ToDecimal(computed);
                }
                catch (Exception)
                {
                    // Divide-by-zero or an otherwise non-calculable formula for this OU/period —
                    // leave Result null and still surface the real element values below, so the
                    // reader can see exactly which operand made it non-calculable, without failing
                    // the rest of the dashboard.
                    resultValue = null;
                }

                var slNo = 0;
                foreach (var code in c.CanonicalCodes)
                {
                    slNo++;
                    result.Add(new RatioAnalysisDashBoardDTO
                    {
                        RatioId = c.Ratio.RatioId,
                        Category = c.Ratio.Category,
                        Ratio = c.Ratio.RatioName,
                        Formula = c.Ratio.DisplayFormula ?? c.ComputeExpression,
                        Explanation = c.Ratio.Explanation ?? string.Empty,
                        PeriodType = "Custom",
                        SlNo = c.Ratio.SortOrder,
                        FromDate = fromDate,
                        ToDate = toDate,
                        Result = resultValue,
                        Label = $"{c.Ratio.RatioName} - {code}",
                        ElementSlno = slNo,
                        ElementName = code,
                        ElementValue = operandValues.TryGetValue(code, out var ev) ? ev : 0m,
                    });
                }
            }

            return result;
        }

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

        private async Task<int> ResolveDateKeyAsync(DateTime date, LoginDTO loginDTO, CancellationToken ct)
        {
            var dateKey = await _queryExecutor
                .ExecuteScalarAsync<int?>(loginDTO, WarehouseFactPostingQB.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;
        }

        private static string ValidateIdentifier(string value)
        {
            if (!SafeIdentifier.IsMatch(value))
                throw new InvalidOperationException($"'{value}' is not a safe SQL identifier (expected [A-Z0-9_]+) — check the MWAREHOUSEMEASURE metadata.");
            return value;
        }
    }
}