using Elwig.Helpers;
using Elwig.Models.Entities;
using Elwig.ViewModels;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Controls;
using System.Windows;

namespace Elwig.Services {
    public static class AreaComService {

        public static async Task InitInputs(this AreaComAdminViewModel vm) {
            using var ctx = new AppDbContext();
            vm.FbNr = await ctx.NextFbNr();
            vm.MgNr = vm.FilterMember.MgNr;
            vm.YearFrom = Utils.CurrentYear;
            vm.WineCult = null;
        }

        public static void ClearInputs(this AreaComAdminViewModel vm) {
        }

        public static void FillInputs(this AreaComAdminViewModel vm, AreaCom a) {
            vm.FbNr = a.FbNr;
            vm.MgNr = a.MgNr;
            vm.YearFrom = a.YearFrom;
            vm.YearTo = a.YearTo;
            vm.AreaComType = ControlUtils.GetItemFromSourceWithPk(vm.AreaComTypeSource, a.VtrgId) as AreaComType;
            vm.WineCult = ControlUtils.GetItemFromSourceWithPk(vm.WineCultSource, a.CultId) as WineCult;
            vm.Comment = a.Comment;
            vm.Kg = ControlUtils.GetItemFromSourceWithPk(vm.KgSource, a.KgNr) as AT_Kg;
            vm.Rd = ControlUtils.GetItemFromSourceWithPk(vm.RdSource, a.KgNr, a.RdNr) as WbRd;
            vm.GstNr = a.GstNr;
            vm.Area = a.Area;
        }

        public static async Task<(List<string>, IQueryable<AreaCom>, List<string>)> GetFilters(this AreaComAdminViewModel vm, AppDbContext ctx) {
            List<string> filterNames = [];
            IQueryable<AreaCom> areaComQuery = ctx.AreaCommitments.Where(a => a.MgNr == vm.FilterMember.MgNr).OrderBy(a => a.FbNr);
            if (vm.ShowOnlyActiveAreaComs) {
                areaComQuery = Utils.ActiveAreaCommitments(areaComQuery, Utils.CurrentLastSeason);
                filterNames.Add($"laufend {Utils.CurrentLastSeason}");
            }

            var filterVar = new List<string>();
            var filterNotVar = new List<string>();
            var filterAttr = new List<string>();
            var filterNotAttr = new List<string>();
            var filterSeasons = new List<int>();

            var filter = vm.TextFilter;
            if (filter.Count > 0) {
                var var = await ctx.WineVarieties.ToDictionaryAsync(v => v.SortId, v => v);
                var attr = await ctx.WineAttributes.ToDictionaryAsync(a => a.Name.ToLower().Split(" ")[0], a => a);
                var attrId = await ctx.WineAttributes.ToDictionaryAsync(a => a.AttrId, a => a);

                for (int i = 0; i < filter.Count; i++) {
                    var e = filter[i];
                    if (e.Length == 2 && var.ContainsKey(e.ToUpper())) {
                        filterVar.Add(e.ToUpper());
                        filter.RemoveAt(i--);
                        filterNames.Add(var[e.ToUpper()].Name);
                    } else if (e.Length == 3 && e[0] == '!' && var.ContainsKey(e[1..].ToUpper())) {
                        filterNotVar.Add(e[1..].ToUpper());
                        filter.RemoveAt(i--);
                        filterNames.Add($"ohne {var[e.ToUpper()].Name}");
                    } else if (attr.ContainsKey(e.ToLower())) {
                        var a = attr[e.ToLower()];
                        filterAttr.Add(a.AttrId);
                        filter.RemoveAt(i--);
                        filterNames.Add($"Attribut {a.Name}");
                    } else if (e[0] == '!' && attr.ContainsKey(e[1..].ToLower())) {
                        var a = attr[e[1..].ToLower()];
                        filterNotAttr.Add(a.AttrId);
                        filter.RemoveAt(i--);
                        filterNames.Add($"ohne Attribut {a.Name}");
                    } else if (e.Length > 2 && var.ContainsKey(e.ToUpper()[..2]) && attrId.ContainsKey(e[2..].ToUpper())) {
                        filterVar.Add(e[..2].ToUpper());
                        filterAttr.Add(e[2..].ToUpper());
                        filter.RemoveAt(i--);
                        filterNames.Add(var[e[..2].ToUpper()].Name);
                        filterNames.Add($"Attribut {attrId[e[2..].ToUpper()].Name}");
                    } else if (e[0] == '!' && e.Length > 3 && var.ContainsKey(e.ToUpper()[1..3]) && attrId.ContainsKey(e[3..].ToUpper())) {
                        filterNotVar.Add(e[1..3].ToUpper());
                        filterNotAttr.Add(e[3..].ToUpper());
                        filter.RemoveAt(i--);
                        filterNames.Add($"ohne {var[e[1..3].ToUpper()].Name}");
                        filterNames.Add($"ohne Attribut {attrId[e[3..].ToUpper()].Name}");
                    } else if (e.Length == 4 && int.TryParse(e, out var year)) {
                        filterSeasons.Add(year);
                        filter.RemoveAt(i--);
                        filterNames.Add($"laufend {e}");
                    }
                }

                if (filterVar.Count > 0) areaComQuery = areaComQuery.Where(a => filterVar.Contains(a.AreaComType.WineVar.SortId));
                if (filterNotVar.Count > 0) areaComQuery = areaComQuery.Where(a => !filterNotVar.Contains(a.AreaComType.WineVar.SortId));
                if (filterAttr.Count > 0) areaComQuery = areaComQuery.Where(a => a.AreaComType.WineAttr!.AttrId != null && filterAttr.Contains(a.AreaComType.WineAttr.AttrId));
                if (filterNotAttr.Count > 0) areaComQuery = areaComQuery.Where(a => a.AreaComType.WineAttr!.AttrId == null || !filterNotAttr.Contains(a.AreaComType.WineAttr.AttrId));
                foreach (var year in filterSeasons) areaComQuery = Utils.ActiveAreaCommitments(areaComQuery, year);
            }

            return (filterNames, areaComQuery, filter);
        }

        public static async Task<int> UpdateAreaCommitment(this AreaComAdminViewModel vm, int? oldFbNr) {
            int newFbNr = (int)vm.FbNr!;

            using (var ctx = new AppDbContext()) {
                var a = new AreaCom {
                    FbNr = oldFbNr ?? newFbNr,
                    MgNr = (int)vm.MgNr!,
                    YearFrom = vm.YearFrom,
                    YearTo = vm.YearTo,
                    VtrgId = vm.AreaComType!.VtrgId,
                    CultId = vm.WineCult?.CultId,
                    Comment = string.IsNullOrEmpty(vm.Comment) ? null : vm.Comment,
                    KgNr = vm.Kg!.KgNr,
                    RdNr = vm.Rd?.RdNr,
                    GstNr = vm.GstNr!.Trim(),
                    Area = (int)vm.Area!,
                };

                if (vm.Rd?.RdNr == 0) {
                    vm.Rd.RdNr = await ctx.NextRdNr(a.KgNr);
                    a.RdNr = vm.Rd.RdNr;
                    ctx.Add(vm.Rd);
                }

                if (oldFbNr != null) {
                    ctx.Update(a);
                } else {
                    ctx.Add(a);
                }

                await ctx.SaveChangesAsync();

                if (newFbNr != a.FbNr) {
                    await ctx.Database.ExecuteSqlAsync($"UPDATE area_commitment SET fbnr = {newFbNr} WHERE fbnr = {oldFbNr}");
                }
            }

            App.HintContextChange();

            return newFbNr;
        }

        private static void AddToolTipCell(Grid grid, string text, int row, int col, int colSpan = 1, bool bold = false, bool alignRight = false, bool alignCenter = false) {
            var tb = new TextBlock() {
                Text = text,
                TextAlignment = alignRight ? TextAlignment.Right : alignCenter ? TextAlignment.Center : TextAlignment.Left,
                Margin = new(0, 12 * row, 0, 0),
                FontWeight = bold ? FontWeights.Bold : FontWeights.Normal,
            };
            tb.SetValue(Grid.ColumnProperty, col);
            tb.SetValue(Grid.ColumnSpanProperty, colSpan);
            grid.Children.Add(tb);
        }

        private static void AddToolTipRow(Grid grid, int row, string? h1, string? h2, int area, int? min, int? max) {
            var bold = h2 == null;
            if (h1 != null) AddToolTipCell(grid, h1 + ":", row, 0, (h2 == null) ? 2 : 1, bold);
            if (h2 != null) AddToolTipCell(grid, h2 + ":", row, 1, 1, bold);
            AddToolTipCell(grid, $"{area:N0} m²", row, 2, 1, bold, true);
            AddToolTipCell(grid, min == null ? "" : $"{min:N0} kg", row, 3, 1, bold, true);
            AddToolTipCell(grid, max == null ? "" : $"{max:N0} kg", row, 4, 1, bold, true);
        }

        public static async Task<(string, (string?, string?, int, int?, int?)[])> GenerateToolTipData(IQueryable<AreaCom> areaComs, int maxKgPerHa) {
            var grid = new List<(string?, string?, int, int?, int?)>();
            var text = "-";

            var area = await areaComs.SumAsync(p => p.Area);
            text = $"{area:N0} m²";
            grid.Add(("Geb. Fläche", null, area, null, null));

            if (await areaComs.AnyAsync()) {
                var attrGroups = await areaComs
                    .Where(c => c.AreaComType.WineAttr != null)
                    .GroupBy(c => c.AreaComType.WineAttr!.Name)
                    .Select(g => new {
                        Attr = g.Key,
                        Area = g.Sum(c => c.Area),
                        Min = g.Sum(c => c.Area * (c.AreaComType.MinKgPerHa ?? 0) / 10_000),
                        Max = g.Sum(c => c.Area * (c.AreaComType.WineAttr!.MaxKgPerHa ?? maxKgPerHa) / 10_000),
                    })
                    .OrderByDescending(g => g.Area)
                    .ThenBy(g => g.Attr)
                    .ToListAsync();
                var groups = await areaComs
                    .Where(c => c.AreaComType.WineAttr != null)
                    .GroupBy(c => new {
                        Attr = c.AreaComType.WineAttr!.Name,
                        c.AreaComType.SortId,
                    })
                    .Select(g => new {
                        g.Key.Attr,
                        g.Key.SortId,
                        Area = g.Sum(c => c.Area),
                        Min = g.Sum(c => c.Area * (c.AreaComType.MinKgPerHa ?? 0) / 10_000),
                        Max = g.Sum(c => c.Area * (c.AreaComType.WineAttr!.MaxKgPerHa ?? maxKgPerHa) / 10_000),
                    })
                    .OrderByDescending(g => g.Area)
                    .ThenBy(g => g.Attr)
                    .ThenBy(g => g.SortId)
                    .ToListAsync();

                var noAttr = await areaComs
                    .Where(c => c.AreaComType.WineAttr == null || !c.AreaComType.WineAttr.IsStrict)
                    .GroupBy(c => c.AreaComType.SortId)
                    .Select(g => new {
                        SortId = g.Key,
                        Area = g.Sum(c => c.Area),
                        Min = g.Sum(c => c.Area * (c.AreaComType.MinKgPerHa ?? 0) / 10_000),
                        Max = g.Sum(c => c.Area * (c.AreaComType.WineAttr!.MaxKgPerHa ?? maxKgPerHa) / 10_000),
                    })
                    .OrderByDescending(g => g.Area)
                    .ThenBy(g => g.SortId)
                    .ToListAsync();

                if (noAttr.Count > 0) {
                    grid.Add((null, null, noAttr.Sum(g => g.Area), noAttr.Sum(g => g.Min), noAttr.Sum(g => g.Max)));
                    foreach (var g in noAttr) {
                        grid.Add((null, g.SortId, g.Area, g.Min, g.Max));
                    }
                }
                foreach (var attrG in attrGroups) {
                    grid.Add((attrG.Attr, null, attrG.Area, attrG.Min, attrG.Max));
                    foreach (var g in groups.Where(g => g.Attr == attrG.Attr).OrderByDescending(g => g.Area).ThenBy(g => g.SortId)) {
                        grid.Add((null, g.SortId, g.Area, g.Min, g.Max));
                    }
                }
            }

            return (text, grid.ToArray());
        }

        public static Grid GenerateToolTip((string?, string?, int, int?, int?)[] data) {
            var grid = new Grid();
            grid.ColumnDefinitions.Add(new() { Width = new(10) });
            grid.ColumnDefinitions.Add(new() { Width = new(60) });
            grid.ColumnDefinitions.Add(new() { Width = new(80) });
            grid.ColumnDefinitions.Add(new() { Width = new(80) });
            grid.ColumnDefinitions.Add(new() { Width = new(80) });
            AddToolTipCell(grid, "Lieferpflicht", 0, 3, 1, false, false, true);
            AddToolTipCell(grid, "Lieferrecht", 0, 4, 1, false, false, true);
            int rowNum = 1;
            foreach (var row in data) {
                if (rowNum == 2 || (rowNum != 1 && row.Item1 != null)) rowNum++;
                AddToolTipRow(grid, rowNum++, row.Item1, row.Item2, row.Item3, row.Item4, row.Item5);
            }
            return grid;
        }
    }
}