using GB5Shared.DTO.Report;
using GB5Shared.Export;

namespace GB5Shared.Export.Pivot
{
    public sealed class DataPivotEngine : IDataPivotEngine
    {
        public async Task<PivotResultDTO> TransformAsync(
            IAsyncEnumerable<Dictionary<string, object?>> rows,
            IReadOnlyList<PivotFieldConfigDTO>             fieldConfig,
            int                                            maxRows = 100_000,
            CancellationToken                              ct      = default)
        {
            var rowFields = fieldConfig.Where(f => f.DisplayType == 0 || f.DisplayType == 2).ToList();
            var colFields = fieldConfig.Where(f => f.DisplayType == 1).ToList();
            var sumFields = fieldConfig.Where(f => f.DisplayType == 3).ToList();

            var fieldNames = new HashSet<string>(fieldConfig.Select(f => f.FieldName), StringComparer.OrdinalIgnoreCase);

            // Step 2: stream and project rows to only fieldConfig keys
            var allRows = new List<Dictionary<string, object?>>();
            await foreach (var row in rows.WithCancellation(ct).ConfigureAwait(false))
            {
                var projected = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);
                foreach (var fn in fieldNames)
                {
                    row.TryGetValue(fn, out var val);
                    projected[fn] = val;
                }
                allRows.Add(projected);

                if (allRows.Count > maxRows)
                    throw new InvalidOperationException(
                        $"{allRows.Count} rows exceeds pivot limit of {maxRows}. Refine your criteria or increase the limit.");
            }

            int originalRowCount = allRows.Count;

            // Step 3: discover distinct column dimension values (ordered)
            var colDistinctValues = new Dictionary<string, List<string>>(StringComparer.OrdinalIgnoreCase);
            foreach (var colField in colFields)
            {
                var distinct = allRows
                    .Select(r => r.TryGetValue(colField.FieldName, out var v) ? v?.ToString() ?? "" : "")
                    .Distinct(StringComparer.OrdinalIgnoreCase)
                    .OrderBy(x => x)
                    .ToList();
                colDistinctValues[colField.FieldName] = distinct;
            }

            // Build pivot column header names: "{colValue}_{sumField.FieldTitle}"
            var pivotColumns = new List<string>();
            foreach (var colField in colFields)
            {
                foreach (var colVal in colDistinctValues[colField.FieldName])
                {
                    foreach (var sumField in sumFields)
                        pivotColumns.Add($"{colVal}_{sumField.FieldTitle}");
                }
            }

            var rowHeaders = rowFields.Select(f => f.FieldTitle).ToList();
            var summaryFieldTitles = sumFields.Select(f => f.FieldTitle).ToList();

            // Step 4: group by row dimension keys and aggregate
            var grouped = allRows
                .GroupBy(r => string.Join("|||", rowFields.Select(rf =>
                    r.TryGetValue(rf.FieldName, out var v) ? v?.ToString() ?? "" : "")))
                .ToList();

            var pivotRows = new List<Dictionary<string, object?>>();
            foreach (var group in grouped)
            {
                var pivotRow = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);

                // populate row dimension values
                var firstRow = group.First();
                foreach (var rf in rowFields)
                {
                    firstRow.TryGetValue(rf.FieldName, out var rv);
                    pivotRow[rf.FieldTitle] = rv;
                }

                // populate aggregated values per (colValue × sumField)
                foreach (var colField in colFields)
                {
                    foreach (var colVal in colDistinctValues[colField.FieldName])
                    {
                        var matchingRows = group
                            .Where(r => string.Equals(
                                r.TryGetValue(colField.FieldName, out var cv) ? cv?.ToString() : "",
                                colVal, StringComparison.OrdinalIgnoreCase))
                            .ToList();

                        foreach (var sumField in sumFields)
                        {
                            var colKey = $"{colVal}_{sumField.FieldTitle}";
                            pivotRow[colKey] = Aggregate(matchingRows, sumField);
                        }
                    }
                }

                pivotRows.Add(pivotRow);
            }

            // Step 5: grand total row
            Dictionary<string, object?>? grandTotal = null;
            if (sumFields.Any(sf => sf.IsGrandTotal == 0))
            {
                grandTotal = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);
                grandTotal["__grandTotal"] = true;

                foreach (var colField in colFields)
                {
                    foreach (var colVal in colDistinctValues[colField.FieldName])
                    {
                        var matchingRows = allRows
                            .Where(r => string.Equals(
                                r.TryGetValue(colField.FieldName, out var cv) ? cv?.ToString() : "",
                                colVal, StringComparison.OrdinalIgnoreCase))
                            .ToList();

                        foreach (var sumField in sumFields)
                        {
                            if (sumField.IsGrandTotal == 0)
                            {
                                var colKey = $"{colVal}_{sumField.FieldTitle}";
                                grandTotal[colKey] = Aggregate(matchingRows, sumField);
                            }
                        }
                    }
                }
            }

            return new PivotResultDTO
            {
                RowHeaders       = rowHeaders,
                PivotColumns     = pivotColumns,
                SummaryFields    = summaryFieldTitles,
                Rows             = pivotRows,
                GrandTotal       = grandTotal,
                OriginalRowCount = originalRowCount
            };
        }

        private static object? Aggregate(List<Dictionary<string, object?>> rows, PivotFieldConfigDTO sumField)
        {
            if (!rows.Any()) return null;

            var aggregator = new ColumnAggregator(sumField.AggregationType, sumField.FieldType);
            foreach (var r in rows)
            {
                r.TryGetValue(sumField.FieldName, out var v);
                aggregator.Accumulate(v);
            }
            return aggregator.Result;
        }
    }
}