using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AnalyticsDAL.DTO.AnalysisAggregation.V1;
using AnalyticsDAL.DTO.BICatalog;
using AnalyticsDAL.DTO.Warehouse;
using AnalyticsDAL.Query.Warehouse;
using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace AnalyticsDAL.CustomCode.Warehouse
{
    public class WarehouseDAL : IWarehouseDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        public async Task<WarehouseFactDTO?> GetFactById(int factId, LoginDTO login, CancellationToken ct)
        {
            return await _queryExecutor.QuerySingleAsync<WarehouseFactDTO>(
                login,
                WarehouseFactQB.GET_FACT_BY_ID,
                new { FactId = factId },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<List<WarehouseMeasureDTO>> GetMeasuresByFactId(int factId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<WarehouseMeasureDTO>(
                login,
                WarehouseFactQB.GET_MEASURES_BY_FACT_ID,
                new { FactId = factId },
                cancellationToken: ct).ConfigureAwait(false);

            return rows.ToList();
        }

        // ─────────────────────────────────────────────────────────────────────
        // Dynamic aggregation — allowlist-based column resolution only. A field
        // string from the client-supplied AnalysisQueryDefinition is NEVER spliced
        // directly into SQL; every dimension/measure/sort field must resolve through
        // ResolveDimensionColumn/ResolveMeasureColumn below or the request is rejected.
        // Values are always passed as Dapper DynamicParameters, never interpolated.
        //
        // The DIMOU/DIMDATE allowlists themselves now live in WarehouseDimensionCatalog
        // (shared with the config-UI dimension picker, GetDimensionsForDataset) — only DIMOU
        // and DIMDATE are joinable in this pass, since the ported facts (FSALE, FFINANCE) are
        // grained by OUID+DATEID only.
        // ─────────────────────────────────────────────────────────────────────

        public async Task<List<Dictionary<string, object?>>> ExecuteAggregationAsync(
            DatasetResolutionDTO resolution,
            AnalysisQueryDefinition definition,
            LoginDTO login,
            CancellationToken ct)
        {
            if (resolution.FactTableName is null || resolution.DateDimColumn is null || resolution.OuDimColumn is null)
                throw new InvalidOperationException("Warehouse dataset resolution is missing FactTableName/DateDimColumn/OuDimColumn.");

            var measuresByColumn = (resolution.Measures ?? new List<WarehouseMeasureDTO>())
                .ToDictionary(m => m.ColumnName, StringComparer.OrdinalIgnoreCase);

            var selectExpressions = new List<string>();
            var groupByExpressions = new List<string>();
            var parameters = new DynamicParameters();
            parameters.Add("TenantId", login.ClientId);

            foreach (var dim in definition.Dimensions)
            {
                var (expression, columnAlias) = ResolveDimensionColumn(dim.Field);
                selectExpressions.Add($"{expression} AS [{columnAlias}]");
                groupByExpressions.Add(expression);
            }

            foreach (var measure in definition.Measures)
            {
                if (!measuresByColumn.TryGetValue(measure.Field, out var measureDto))
                    throw new ArgumentException($"Measure '{measure.Field}' is not a registered measure for this warehouse fact.", nameof(definition));

                var aggFunction = AggregationSqlFunction(measure.Aggregation);
                var columnExpr = $"f.[{measureDto.ColumnName}]";
                selectExpressions.Add(aggFunction is null
                    ? $"{columnExpr} AS [{measure.Field}]"
                    : $"{aggFunction}({columnExpr}) AS [{measure.Field}]");
            }

            if (selectExpressions.Count == 0)
                throw new ArgumentException("AnalysisQueryDefinition must request at least one dimension or measure.", nameof(definition));

            // Live-tested 2026-07-29 against GB5DEMO: a caller-only filter (f.TENANTID = @TenantId)
            // silently returned zero rows for every ported fact, because all pre-existing legacy
            // FSALE/FFINANCE rows were defaulted to TENANTID = -1 (shared/global) when the column
            // was backfilled — the same "-1 = shared, visible to every tenant" sentinel used
            // throughout this module (MWORKSPACE, MDATASOURCE, MANALYSIS, ...). A tenant must see
            // both their own private rows (if any exist later) and the shared ones.
            var whereConditions = new List<string> { "f.[TENANTID] IN (@TenantId, -1)" };
            for (int i = 0; i < definition.Filters.Count; i++)
            {
                var filter = definition.Filters[i];
                var (expression, _) = ResolveDimensionColumn(filter.Field);
                whereConditions.Add(BuildFilterCondition(expression, filter, i, parameters));
            }

            var orderByExpressions = new List<string>();
            foreach (var sort in definition.Sort)
            {
                var (expression, _) = ResolveDimensionColumn(sort.Field);
                orderByExpressions.Add(sort.Descending ? $"{expression} DESC" : expression);
            }

            var topClause = definition.TopN is int topN and > 0 ? $"TOP ({topN}) " : string.Empty;

            var sql = new StringBuilder()
                .Append("SELECT ").Append(topClause).Append(string.Join(", ", selectExpressions))
                .Append(" FROM ").Append(resolution.FactTableName).Append(" f")
                .Append(" LEFT JOIN DIMOU ou ON ou.OUID = f.[").Append(resolution.OuDimColumn).Append(']')
                // DimDate's PK is DateKey, not DATEID — verified against the live table (see
                // DimDateColumns comment above). resolution.DateDimColumn is the FACT's own
                // column name (e.g. FSALE.DATEID); the DimDate-side join column is fixed.
                .Append(" LEFT JOIN DimDate dt ON dt.DateKey = f.[").Append(resolution.DateDimColumn).Append(']')
                .Append(" WHERE ").Append(string.Join(" AND ", whereConditions));

            if (groupByExpressions.Count > 0)
                sql.Append(" GROUP BY ").Append(string.Join(", ", groupByExpressions));

            if (orderByExpressions.Count > 0)
                sql.Append(" ORDER BY ").Append(string.Join(", ", orderByExpressions));

            var dynamicRows = (await _queryExecutor.QueryAsync<dynamic>(
                login, sql.ToString(), parameters, cancellationToken: ct).ConfigureAwait(false)).ToList();

            var result = new List<Dictionary<string, object?>>(dynamicRows.Count);
            foreach (IDictionary<string, object> rowDict in dynamicRows)
                result.Add(new Dictionary<string, object?>(rowDict));

            return result;
        }

        /// <summary>Resolves a client-supplied field name to a safe, allowlisted SQL expression.
        /// Throws for anything not on the allowlist — this is the one place that stands between
        /// client input and SQL text, so it must fail closed.</summary>
        private static (string Expression, string ColumnAlias) ResolveDimensionColumn(string field)
        {
            if (string.IsNullOrWhiteSpace(field))
                throw new ArgumentException("Field name is required.", nameof(field));

            if (field.StartsWith("DIMOU.", StringComparison.OrdinalIgnoreCase))
            {
                var column = field.Substring("DIMOU.".Length);
                if (!WarehouseDimensionCatalog.DimOuColumns.Contains(column))
                    throw new ArgumentException($"'{column}' is not an allowed DIMOU column.", nameof(field));
                return ($"ou.[{column}]", column);
            }

            if (field.StartsWith("DIMDATE.", StringComparison.OrdinalIgnoreCase))
            {
                var column = field.Substring("DIMDATE.".Length);
                if (!WarehouseDimensionCatalog.DimDateColumns.Contains(column))
                    throw new ArgumentException($"'{column}' is not an allowed DIMDATE column.", nameof(field));
                return ($"dt.[{column}]", column);
            }

            // Bare field — only the fact's own grain keys are exposed unqualified.
            if (string.Equals(field, "OUID", StringComparison.OrdinalIgnoreCase))
                return ("f.[OUID]", "OUID");
            if (string.Equals(field, "DATEID", StringComparison.OrdinalIgnoreCase))
                return ("f.[DATEID]", "DATEID");

            throw new ArgumentException(
                $"'{field}' is not a recognized dimension. Use a bare fact grain key (OUID, DATEID) " +
                "or a 'DIMOU.<Column>'/'DIMDATE.<Column>' reference.", nameof(field));
        }

        private static string BuildFilterCondition(
            string columnExpression, AnalysisFilterDTO filter, int index, DynamicParameters parameters)
        {
            var paramName = $"Filter{index}";
            var value = filter.Values.Length > 0 ? filter.Values[0] : null;

            switch (filter.Operator)
            {
                case CriteriaDTO.OperationType.Equal:
                    parameters.Add(paramName, value);
                    return $"{columnExpression} = @{paramName}";
                case CriteriaDTO.OperationType.NotEqual:
                    parameters.Add(paramName, value);
                    return $"{columnExpression} <> @{paramName}";
                case CriteriaDTO.OperationType.GreaterThan:
                    parameters.Add(paramName, value);
                    return $"{columnExpression} > @{paramName}";
                case CriteriaDTO.OperationType.LessThan:
                    parameters.Add(paramName, value);
                    return $"{columnExpression} < @{paramName}";
                case CriteriaDTO.OperationType.GreaterThanOrEqualTo:
                    parameters.Add(paramName, value);
                    return $"{columnExpression} >= @{paramName}";
                case CriteriaDTO.OperationType.LessThanOrEqualTo:
                    parameters.Add(paramName, value);
                    return $"{columnExpression} <= @{paramName}";
                case CriteriaDTO.OperationType.Like:
                    parameters.Add(paramName, $"%{value}%");
                    return $"{columnExpression} LIKE @{paramName}";
                case CriteriaDTO.OperationType.StartWith:
                    parameters.Add(paramName, $"{value}%");
                    return $"{columnExpression} LIKE @{paramName}";
                case CriteriaDTO.OperationType.EndsWith:
                    parameters.Add(paramName, $"%{value}");
                    return $"{columnExpression} LIKE @{paramName}";
                case CriteriaDTO.OperationType.In:
                    parameters.Add(paramName, filter.Values);
                    return $"{columnExpression} IN @{paramName}";
                case CriteriaDTO.OperationType.NotIn:
                    parameters.Add(paramName, filter.Values);
                    return $"{columnExpression} NOT IN @{paramName}";
                case CriteriaDTO.OperationType.Between when filter.Values.Length >= 2:
                    parameters.Add($"{paramName}From", filter.Values[0]);
                    parameters.Add($"{paramName}To", filter.Values[1]);
                    return $"{columnExpression} BETWEEN @{paramName}From AND @{paramName}To";
                default:
                    throw new ArgumentException($"Unsupported filter operator '{filter.Operator}' for field.", nameof(filter));
            }
        }

        private static string? AggregationSqlFunction(byte aggregation) => aggregation switch
        {
            0 => null,       // None — raw value, no aggregate function
            1 => "SUM",
            3 => "AVG",
            5 => "COUNT",
            7 => "MAX",
            8 => "MIN",
            _ => throw new ArgumentException($"Unknown aggregation code {aggregation}.", nameof(aggregation))
        };
    }
}