using Indotalent.Infrastructure.Database;
using Indotalent.Data.Entities;
using Indotalent.Data.Enums;
using MediatR;
using Microsoft.EntityFrameworkCore;

namespace Indotalent.Features.Root.Home.Cqrs;

public class ChartDataPoint
{
    public string? Label { get; set; }
    public double SalesAmount { get; set; }
    public double PurchaseAmount { get; set; }
}

public class ScmMetricDto
{
    public string? Label { get; set; }
    public string? Value { get; set; }
    public string? Color { get; set; }
    public double Percentage { get; set; }
}

public class ScmActivityDto
{
    public string? Module { get; set; }
    public string? Number { get; set; }
    public string? Partner { get; set; }
    public double Qty { get; set; }
    public string? Status { get; set; }
    public DateTime? Date { get; set; }
    public string? Description { get; set; }
}

public class RecentPurchaseOrderDto
{
    public DateTime? OrderDate { get; set; }
    public string? Number { get; set; }
    public string? VendorName { get; set; }
    public decimal Amount { get; set; }
    public string? Status { get; set; }
    public int ItemCount { get; set; }
}

public class RecentGoodsReceiveDto
{
    public DateTime? ReceiveDate { get; set; }
    public string? Number { get; set; }
    public string? PONumber { get; set; }
    public string? Status { get; set; }
}

public class StatusCountDto
{
    public string? Status { get; set; }
    public int Count { get; set; }
    public string? Color { get; set; }
}

public class DashboardScmResponse
{
    public int PendingPR { get; set; }
    public int PendingSQ { get; set; }
    public int PendingGR { get; set; }
    public int PendingDO { get; set; }
    public int ActiveCustomer { get; set; }
    public int ActiveVendor { get; set; }
    public double InboundQtyMTD { get; set; }
    public double OutboundQtyMTD { get; set; }
    public decimal SalesMTD { get; set; }
    public decimal PurchaseMTD { get; set; }
    public double ChainAccuracyIndex { get; set; }
    public decimal TotalReceivable { get; set; }
    public decimal TotalPayable { get; set; }
    public int TotalPR { get; set; }
    public int TotalPO { get; set; }
    public int TotalGR { get; set; }
    public int TotalDO { get; set; }

    public List<ChartDataPoint> MonthlySalesVsPurchase { get; set; } = new();
    public List<ChartDataPoint> WeeklySalesVsPurchase { get; set; } = new();

    public List<ScmMetricDto> DocGrid { get; set; } = new();
    public List<ScmActivityDto> GlobalFlowLog { get; set; } = new();
    public List<ScmMetricDto> TopProductsByQty { get; set; } = new();
    public List<ScmMetricDto> CustGroupStats { get; set; } = new();
    public List<ScmMetricDto> VendGroupStats { get; set; } = new();
    public List<ScmMetricDto> CustCategoryStats { get; set; } = new();
    public List<ScmMetricDto> VendCategoryStats { get; set; } = new();
    public List<ScmMetricDto> PayMethodStats { get; set; } = new();
    public List<ScmMetricDto> WhseInventoryLevel { get; set; } = new();
    public List<ScmMetricDto> CashFlowMTD { get; set; } = new();
    public List<ScmMetricDto> StockValuePerGroup { get; set; } = new();
    public List<ScmMetricDto> VendorPOTotals { get; set; } = new();
    public List<ScmMetricDto> ProductCategories { get; set; } = new();
    public List<StatusCountDto> POStatusBreakdown { get; set; } = new();
    public List<StatusCountDto> GRStatusBreakdown { get; set; } = new();
    public List<StatusCountDto> PRStatusBreakdown { get; set; } = new();
    public List<StatusCountDto> DOStatusBreakdown { get; set; } = new();
    public List<RecentPurchaseOrderDto> RecentPOs { get; set; } = new();
    public List<RecentGoodsReceiveDto> RecentGRs { get; set; } = new();

    public string FormattedSales => FormatCurrency(SalesMTD);
    public string FormattedPurchase => FormatCurrency(PurchaseMTD);
    public string FormattedReceivable => FormatCurrency(TotalReceivable);
    public string FormattedPayable => FormatCurrency(TotalPayable);
    private static string FormatCurrency(decimal v) => v >= 1000000 ? "$" + (v / 1000000m).ToString("F2") + "M" : "$" + v.ToString("N0");
}

public record GetScmDashboardQuery() : IRequest<DashboardScmResponse>;

public class DashboardHandler : IRequestHandler<GetScmDashboardQuery, DashboardScmResponse>
{
    private readonly AppDbContext _context;
    public DashboardHandler(AppDbContext context) => _context = context;

    public async Task<DashboardScmResponse> Handle(GetScmDashboardQuery request, CancellationToken ct)
    {
        var start = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);

        // --- Inventory Transactions (materialize with FK navigation via projection, no .Include) ---
        var txRaw = await _context.InventoryTransaction.AsNoTracking()
            .Where(x => x.MovementDate >= start)
            .Select(x => new
            {
                x.MovementDate,
                x.TransType,
                x.Status,
                x.Movement,
                x.QtySCSys,
                x.QtySCDelta,
                x.WarehouseId,
                x.ProductId,
                x.ModuleCode,
                x.ModuleName,
                x.ModuleNumber
            })
            .ToListAsync(ct);

        var warehouseIds = txRaw.Where(x => x.WarehouseId != null).Select(x => x.WarehouseId).Distinct().ToList();
        var productIds = txRaw.Where(x => x.ProductId != null).Select(x => x.ProductId).Distinct().ToList();

        var warehouses = await _context.Warehouse.AsNoTracking()
            .Where(w => warehouseIds.Contains(w.Id))
            .Select(w => new { w.Id, w.Name })
            .ToListAsync(ct);

        var products = await _context.Product.AsNoTracking()
            .Where(p => productIds.Contains(p.Id))
            .Select(p => new { p.Id, p.Name, p.ProductGroupId, p.UnitPrice })
            .ToListAsync(ct);

        var productGroupIds = products.Where(p => p.ProductGroupId != null).Select(p => p.ProductGroupId).Distinct().ToList();
        var productGroups = await _context.ProductGroup.AsNoTracking()
            .Where(g => productGroupIds.Contains(g.Id))
            .Select(g => new { g.Id, g.Name })
            .ToListAsync(ct);

        var warehouseMap = warehouses.ToDictionary(w => w.Id, w => w.Name ?? "Main");
        var productMap = products.ToDictionary(p => p.Id, p => p.Name ?? "N/A");
        var productPriceMap = products.ToDictionary(p => p.Id, p => p.UnitPrice ?? 0);
        var groupMap = productGroups.ToDictionary(g => g.Id, g => g.Name ?? "General");

        // Chain Accuracy Index
        var countData = txRaw.Where(x => x.ModuleCode == "COUNT").ToList();
        double accuracy = 100;
        if (countData.Count != 0)
        {
            var sys = countData.Sum(x => x.QtySCSys ?? 0);
            var delta = countData.Sum(x => Math.Abs(x.QtySCDelta ?? 0));
            accuracy = sys > 0 ? Math.Max(0, 100 - (delta / sys * 100)) : 100;
        }

        // Inbound / Outbound MTD
        var inboundQty = txRaw.Where(x => x.TransType == InventoryTransType.In && x.Status == InventoryTransactionStatus.Confirmed)
            .Sum(x => x.Movement ?? 0);
        var outboundQty = txRaw.Where(x => x.TransType == InventoryTransType.Out && x.Status == InventoryTransactionStatus.Confirmed)
            .Sum(x => x.Movement ?? 0);

        // Whse Inventory Level
        var whseLevel = txRaw
            .GroupBy(x => x.WarehouseId)
            .Select(g => new ScmMetricDto
            {
                Label = warehouseMap.GetValueOrDefault(g.Key ?? "", "Main"),
                Value = g.Sum(x => (x.Movement ?? 0) * (x.TransType == InventoryTransType.In ? 1 : -1)).ToString("N0")
            }).ToList();

        // Stock Value Per Group
        var stockValGroup = txRaw
            .GroupBy(x => productIds.Contains(x.ProductId ?? "") ? groupMap.GetValueOrDefault(
                products.FirstOrDefault(p => p.Id == x.ProductId)?.ProductGroupId ?? "", "General") : "General")
            .Select(g => new ScmMetricDto
            {
                Label = g.Key,
                Value = g.Sum(x =>
                {
                    var price = productPriceMap.GetValueOrDefault(x.ProductId ?? "", 0);
                    return (decimal)((x.Movement ?? 0) * (x.TransType == InventoryTransType.In ? 1 : -1)) * price;
                }).ToString("N0")
            }).ToList();

        // --- Customer Groups (no .Include - use CustomerGroupId FK) ---
        var custRaw = await _context.Customer.AsNoTracking()
            .Select(x => new { x.CustomerGroupId })
            .ToListAsync(ct);
        var custGroupIds = custRaw.Where(x => x.CustomerGroupId != null).Select(x => x.CustomerGroupId).Distinct().ToList();
        var custGroupsDb = await _context.CustomerGroup.AsNoTracking()
            .Where(g => custGroupIds.Contains(g.Id))
            .Select(g => new { g.Id, g.Name })
            .ToListAsync(ct);
        var custGroupMap = custGroupsDb.ToDictionary(g => g.Id, g => g.Name ?? "General");
        var custGroups = custRaw
            .GroupBy(x => custGroupMap.GetValueOrDefault(x.CustomerGroupId ?? "", "General"))
            .Select(g => new ScmMetricDto { Label = g.Key, Value = g.Count().ToString() })
            .ToList();

        // --- Vendor Groups (no .Include - use VendorGroupId FK) ---
        var vendRaw = await _context.Vendor.AsNoTracking()
            .Select(x => new { x.VendorGroupId })
            .ToListAsync(ct);
        var vendGroupIds = vendRaw.Where(x => x.VendorGroupId != null).Select(x => x.VendorGroupId).Distinct().ToList();
        var vendGroupsDb = await _context.VendorGroup.AsNoTracking()
            .Where(g => vendGroupIds.Contains(g.Id))
            .Select(g => new { g.Id, g.Name })
            .ToListAsync(ct);
        var vendGroupMap = vendGroupsDb.ToDictionary(g => g.Id, g => g.Name ?? "General");
        var vendGroups = vendRaw
            .GroupBy(x => vendGroupMap.GetValueOrDefault(x.VendorGroupId ?? "", "General"))
            .Select(g => new ScmMetricDto { Label = g.Key, Value = g.Count().ToString() })
            .ToList();

        // --- Customer Categories ---
        var custCatRaw = await _context.Customer.AsNoTracking()
            .Select(x => new { x.CustomerCategoryId })
            .ToListAsync(ct);
        var custCatIds = custCatRaw.Where(x => x.CustomerCategoryId != null).Select(x => x.CustomerCategoryId).Distinct().ToList();
        var custCatDb = await _context.CustomerCategory.AsNoTracking()
            .Where(c => custCatIds.Contains(c.Id))
            .Select(c => new { c.Id, c.Name })
            .ToListAsync(ct);
        var custCatMap = custCatDb.ToDictionary(c => c.Id, c => c.Name ?? "General");
        var custCats = custCatRaw
            .GroupBy(x => custCatMap.GetValueOrDefault(x.CustomerCategoryId ?? "", "General"))
            .Select(g => new ScmMetricDto { Label = g.Key, Value = g.Count().ToString() })
            .ToList();

        // --- Vendor Categories ---
        var vendCatRaw = await _context.Vendor.AsNoTracking()
            .Select(x => new { x.VendorCategoryId })
            .ToListAsync(ct);
        var vendCatIds = vendCatRaw.Where(x => x.VendorCategoryId != null).Select(x => x.VendorCategoryId).Distinct().ToList();
        var vendCatDb = await _context.VendorCategory.AsNoTracking()
            .Where(c => vendCatIds.Contains(c.Id))
            .Select(c => new { c.Id, c.Name })
            .ToListAsync(ct);
        var vendCatMap = vendCatDb.ToDictionary(c => c.Id, c => c.Name ?? "General");
        var vendCats = vendCatRaw
            .GroupBy(x => vendCatMap.GetValueOrDefault(x.VendorCategoryId ?? "", "General"))
            .Select(g => new ScmMetricDto { Label = g.Key, Value = g.Count().ToString() })
            .ToList();

        // --- Payment Methods (no .Include) ---
        var payRaw = await _context.PaymentReceive.AsNoTracking()
            .Select(x => new { x.PaymentMethodId })
            .ToListAsync(ct);
        var payMethodIds = payRaw.Where(x => x.PaymentMethodId != null).Select(x => x.PaymentMethodId).Distinct().ToList();
        var payMethodDb = await _context.PaymentMethod.AsNoTracking()
            .Where(m => payMethodIds.Contains(m.Id))
            .Select(m => new { m.Id, m.Name })
            .ToListAsync(ct);
        var payMethodMap = payMethodDb.ToDictionary(m => m.Id, m => m.Name ?? "Cash");
        var payMethods = payRaw
            .GroupBy(x => payMethodMap.GetValueOrDefault(x.PaymentMethodId ?? "", "Cash"))
            .Select(g => new ScmMetricDto { Label = g.Key, Value = g.Count().ToString() })
            .ToList();

        // --- Top Products by Qty (no .Include - use ProductId FK) ---
        var soiRaw = await _context.SalesOrderItem.AsNoTracking()
            .Select(x => new { x.ProductId, x.Quantity })
            .ToListAsync(ct);
        var topProdIds = soiRaw.Where(x => x.ProductId != null).Select(x => x.ProductId).Distinct().ToList();
        var topProdDb = await _context.Product.AsNoTracking()
            .Where(p => topProdIds.Contains(p.Id))
            .Select(p => new { p.Id, p.Name })
            .ToListAsync(ct);
        var topProdMap = topProdDb.ToDictionary(p => p.Id, p => p.Name ?? "N/A");
        var topProds = soiRaw
            .Where(x => x.ProductId != null)
            .GroupBy(x => x.ProductId!)
            .Select(g => new ScmMetricDto { Label = topProdMap.GetValueOrDefault(g.Key, "N/A"), Value = g.Sum(x => x.Quantity ?? 0).ToString("N0") })
            .OrderByDescending(x => double.Parse(x.Value ?? "0"))
            .Take(10)
            .ToList();

        // --- Global Flow Log ---
        var globalLogs = await _context.InventoryTransaction
            .AsNoTracking()
            .OrderByDescending(x => x.MovementDate)
            .Take(30)
            .Select(x => new ScmActivityDto
            {
                Module = x.ModuleName,
                Number = x.ModuleNumber,
                Partner = x.WarehouseId,
                Qty = x.Movement ?? 0,
                Status = x.Status.ToString(),
                Date = x.MovementDate,
                Description = x.ModuleCode
            }).ToListAsync(ct);
        var logWhIds = globalLogs.Where(x => x.Partner != null).Select(x => x.Partner).Distinct().ToList();
        var logWhDb = await _context.Warehouse.AsNoTracking()
            .Where(w => logWhIds.Contains(w.Id))
            .Select(w => new { w.Id, w.Name })
            .ToListAsync(ct);
        var logWhMap = logWhDb.ToDictionary(w => w.Id, w => w.Name ?? "N/A");
        foreach (var log in globalLogs)
        {
            if (log.Partner != null && logWhMap.TryGetValue(log.Partner, out var whName))
                log.Partner = whName;
        }

        // --- Doc Grid ---
        var sqCount = await _context.SalesQuotation.CountAsync(ct);
        var soCount = await _context.SalesOrder.CountAsync(ct);
        var prCount = await _context.PurchaseRequisition.CountAsync(ct);
        var poCount = await _context.PurchaseOrder.CountAsync(ct);
        var grCount = await _context.GoodsReceive.CountAsync(ct);
        var doCount = await _context.DeliveryOrder.CountAsync(ct);
        var srCount = await _context.SalesReturn.CountAsync(ct);
        var prRetCount = await _context.PurchaseReturn.CountAsync(ct);
        var invCount = await _context.Invoice.CountAsync(ct);
        var billCount = await _context.Bill.CountAsync(ct);
        var cnCount = await _context.CreditNote.CountAsync(ct);
        var dnCount = await _context.DebitNote.CountAsync(ct);
        var payRecCount = await _context.PaymentReceive.CountAsync(ct);
        var payDisbCount = await _context.PaymentDisburse.CountAsync(ct);

        var docGrid = new List<ScmMetricDto>
        {
            new() { Label = "Sales Quotation", Value = sqCount.ToString(), Color = "#3b82f6" },
            new() { Label = "Sales Order", Value = soCount.ToString(), Color = "#10b981" },
            new() { Label = "Purchase Req", Value = prCount.ToString(), Color = "#f59e0b" },
            new() { Label = "Purchase Order", Value = poCount.ToString(), Color = "#ef4444" },
            new() { Label = "Goods Receive", Value = grCount.ToString(), Color = "#64748b" },
            new() { Label = "Delivery Order", Value = doCount.ToString(), Color = "#06b6d4" },
            new() { Label = "Sales Return", Value = srCount.ToString(), Color = "#8b5cf6" },
            new() { Label = "Purch. Return", Value = prRetCount.ToString(), Color = "#f43f5e" },
            new() { Label = "Invoice", Value = invCount.ToString(), Color = "#14b8a6" },
            new() { Label = "Bill", Value = billCount.ToString(), Color = "#f97316" },
            new() { Label = "Credit Note", Value = cnCount.ToString(), Color = "#ec4899" },
            new() { Label = "Debit Note", Value = dnCount.ToString(), Color = "#0ea5e9" },
            new() { Label = "Pay Receive", Value = payRecCount.ToString(), Color = "#84cc16" },
            new() { Label = "Pay Disburse", Value = payDisbCount.ToString(), Color = "#d946ef" }
        };

        // --- Monthly Sales vs Purchase ---
        var monthlyLabels = new[] { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };
        var soMonths = await _context.SalesOrder
            .Where(x => x.OrderDate != null)
            .Select(x => x.OrderDate!.Value)
            .ToListAsync(ct);
        var poMonths = await _context.PurchaseOrder
            .Where(x => x.OrderDate != null)
            .Select(x => x.OrderDate!.Value)
            .ToListAsync(ct);
        var allDates = soMonths.Concat(poMonths)
            .Select(d => new { d.Year, d.Month })
            .Distinct()
            .OrderByDescending(x => x.Year).ThenByDescending(x => x.Month)
            .Take(6)
            .Reverse()
            .ToList();
        var monthlyData = new List<ChartDataPoint>();
        foreach (var ym in allDates)
        {
            var monthStartDt = new DateTime(ym.Year, ym.Month, 1);
            var monthEndDt = monthStartDt.AddMonths(1);
            var salesAmount = await _context.SalesOrder
                .Where(x => x.OrderDate >= monthStartDt && x.OrderDate < monthEndDt)
                .SumAsync(x => (double?)x.AfterTaxAmount ?? 0, ct);
            var purchaseAmount = await _context.PurchaseOrder
                .Where(x => x.OrderDate >= monthStartDt && x.OrderDate < monthEndDt)
                .SumAsync(x => (double?)x.AfterTaxAmount ?? 0, ct);
            monthlyData.Add(new ChartDataPoint
            {
                Label = monthlyLabels[ym.Month - 1],
                SalesAmount = Math.Round(salesAmount / 1000.0, 1),
                PurchaseAmount = Math.Round(purchaseAmount / 1000.0, 1)
            });
        }

        // --- Weekly Sales vs Purchase ---
        var soWeeks = await _context.SalesOrder
            .Where(x => x.OrderDate != null)
            .Select(x => x.OrderDate!.Value)
            .ToListAsync(ct);
        var poWeeks = await _context.PurchaseOrder
            .Where(x => x.OrderDate != null)
            .Select(x => x.OrderDate!.Value)
            .ToListAsync(ct);
        var allWeekStarts = soWeeks.Concat(poWeeks)
            .Select(d => d.AddDays(-(int)d.DayOfWeek))
            .Distinct()
            .OrderByDescending(w => w)
            .Take(6)
            .Reverse()
            .ToList();
        var weeklyData = new List<ChartDataPoint>();
        foreach (var weekStart in allWeekStarts)
        {
            var weekEnd = weekStart.AddDays(7);
            var salesAmount = await _context.SalesOrder
                .Where(x => x.OrderDate >= weekStart && x.OrderDate < weekEnd)
                .SumAsync(x => (double?)x.AfterTaxAmount ?? 0, ct);
            var purchaseAmount = await _context.PurchaseOrder
                .Where(x => x.OrderDate >= weekStart && x.OrderDate < weekEnd)
                .SumAsync(x => (double?)x.AfterTaxAmount ?? 0, ct);
            weeklyData.Add(new ChartDataPoint
            {
                Label = $"Wk {weekStart:MM/dd}",
                SalesAmount = Math.Round(salesAmount / 1000.0, 1),
                PurchaseAmount = Math.Round(purchaseAmount / 1000.0, 1)
            });
        }

        // --- Vendor PO Totals ---
        var poVendorRaw = await _context.PurchaseOrder.AsNoTracking()
            .Select(x => new { x.VendorId, x.AfterTaxAmount })
            .ToListAsync(ct);
        var poVendorIds = poVendorRaw.Where(x => x.VendorId != null).Select(x => x.VendorId).Distinct().ToList();
        var poVendorDb = await _context.Vendor.AsNoTracking()
            .Where(v => poVendorIds.Contains(v.Id))
            .Select(v => new { v.Id, v.Name })
            .ToListAsync(ct);
        var poVendorMap = poVendorDb.ToDictionary(v => v.Id, v => v.Name ?? "N/A");
        var vendorPOTotals = poVendorRaw
            .Where(x => x.VendorId != null)
            .GroupBy(x => x.VendorId!)
            .Select(g => new ScmMetricDto
            {
                Label = poVendorMap.GetValueOrDefault(g.Key, "N/A"),
                Value = g.Sum(x => x.AfterTaxAmount ?? 0).ToString("N0"),
                Color = "#6366f1"
            })
            .OrderByDescending(x => decimal.TryParse(x.Value, out var v) ? v : 0)
            .Take(15)
            .ToList();

        // --- PO Status Breakdown ---
        var poStatusRaw = await _context.PurchaseOrder.AsNoTracking()
            .Select(x => x.OrderStatus)
            .ToListAsync(ct);
        var poStatusBreakdown = poStatusRaw
            .GroupBy(x => x)
            .Select(g => new StatusCountDto
            {
                Status = g.Key.ToString(),
                Count = g.Count(),
                Color = GetPOStatusColor(g.Key.ToString())
            }).ToList();

        // --- GR Status Breakdown ---
        var grStatusRaw = await _context.GoodsReceive.AsNoTracking()
            .Select(x => x.Status)
            .ToListAsync(ct);
        var grStatusBreakdown = grStatusRaw
            .GroupBy(x => x)
            .Select(g => new StatusCountDto
            {
                Status = g.Key.ToString(),
                Count = g.Count(),
                Color = GetGRStatusColor(g.Key.ToString())
            }).ToList();

        // --- PR Status Breakdown ---
        var prStatusRaw = await _context.PurchaseRequisition.AsNoTracking()
            .Select(x => x.RequisitionStatus)
            .ToListAsync(ct);
        var prStatusBreakdown = prStatusRaw
            .GroupBy(x => x)
            .Select(g => new StatusCountDto
            {
                Status = g.Key.ToString(),
                Count = g.Count(),
                Color = GetPOStatusColor(g.Key.ToString())
            }).ToList();

        // --- DO Status Breakdown ---
        var doStatusRaw = await _context.DeliveryOrder.AsNoTracking()
            .Select(x => x.Status)
            .ToListAsync(ct);
        var doStatusBreakdown = doStatusRaw
            .GroupBy(x => x)
            .Select(g => new StatusCountDto
            {
                Status = g.Key.ToString(),
                Count = g.Count(),
                Color = GetGRStatusColor(g.Key.ToString())
            }).ToList();

        // --- Recent POs ---
        var recentPOsRaw = await _context.PurchaseOrder.AsNoTracking()
            .OrderByDescending(x => x.OrderDate)
            .Take(10)
            .Select(x => new
            {
                x.OrderDate,
                x.AutoNumber,
                x.VendorId,
                x.AfterTaxAmount,
                x.OrderStatus,
                ItemCount = x.PurchaseOrderItemList != null ? x.PurchaseOrderItemList.Count : 0
            })
            .ToListAsync(ct);
        var recentPOVendorIds = recentPOsRaw.Where(x => x.VendorId != null).Select(x => x.VendorId).Distinct().ToList();
        var recentPOVendorDb = await _context.Vendor.AsNoTracking()
            .Where(v => recentPOVendorIds.Contains(v.Id))
            .Select(v => new { v.Id, v.Name })
            .ToListAsync(ct);
        var recentPOVendorMap = recentPOVendorDb.ToDictionary(v => v.Id, v => v.Name ?? "N/A");
        var recentPOs = recentPOsRaw.Select(x => new RecentPurchaseOrderDto
        {
            OrderDate = x.OrderDate,
            Number = x.AutoNumber,
            VendorName = recentPOVendorMap.GetValueOrDefault(x.VendorId ?? "", "N/A"),
            Amount = x.AfterTaxAmount ?? 0,
            Status = x.OrderStatus.ToString(),
            ItemCount = x.ItemCount
        }).ToList();

        // --- Recent GRs ---
        var recentGRsRaw = await _context.GoodsReceive.AsNoTracking()
            .OrderByDescending(x => x.ReceiveDate)
            .Take(10)
            .Select(x => new
            {
                x.ReceiveDate,
                x.AutoNumber,
                x.PurchaseOrderId,
                x.Status
            })
            .ToListAsync(ct);
        var recentGRPOIds = recentGRsRaw.Where(x => x.PurchaseOrderId != null).Select(x => x.PurchaseOrderId).Distinct().ToList();
        var recentGRPODb = await _context.PurchaseOrder.AsNoTracking()
            .Where(po => recentGRPOIds.Contains(po.Id))
            .Select(po => new { po.Id, po.AutoNumber })
            .ToListAsync(ct);
        var recentGRPOMap = recentGRPODb.ToDictionary(po => po.Id, po => po.AutoNumber ?? "N/A");
        var recentGRs = recentGRsRaw.Select(x => new RecentGoodsReceiveDto
        {
            ReceiveDate = x.ReceiveDate,
            Number = x.AutoNumber,
            PONumber = recentGRPOMap.GetValueOrDefault(x.PurchaseOrderId ?? "", "N/A"),
            Status = x.Status.ToString()
        }).ToList();

        // --- Product Categories for donut ---
        var prodCatRaw = await _context.Product.AsNoTracking()
            .Select(x => new { x.ProductGroupId })
            .ToListAsync(ct);
        var prodCatIds = prodCatRaw.Where(x => x.ProductGroupId != null).Select(x => x.ProductGroupId).Distinct().ToList();
        var prodCatDb = await _context.ProductGroup.AsNoTracking()
            .Where(g => prodCatIds.Contains(g.Id))
            .Select(g => new { g.Id, g.Name })
            .ToListAsync(ct);
        var prodCatMap = prodCatDb.ToDictionary(g => g.Id, g => g.Name ?? "General");
        var productCategories = prodCatRaw
            .GroupBy(x => prodCatMap.GetValueOrDefault(x.ProductGroupId ?? "", "General"))
            .Select(g => new ScmMetricDto { Label = g.Key, Value = g.Count().ToString() })
            .ToList();

        // --- Invoice Receivable ---
        var totalReceivable = await _context.Invoice.AsNoTracking()
            .Where(x => x.InvoiceStatus == InvoiceStatus.Confirmed || x.InvoiceStatus == InvoiceStatus.PartialPaid)
            .SumAsync(x => x.SalesOrder != null ? (x.SalesOrder.AfterTaxAmount ?? 0) : 0, ct);

        // --- Bill Payable ---
        var totalPayable = await _context.Bill.AsNoTracking()
            .Where(x => x.BillStatus == BillStatus.Confirmed || x.BillStatus == BillStatus.PartialPaid)
            .SumAsync(x => x.PurchaseOrder != null ? (x.PurchaseOrder.AfterTaxAmount ?? 0) : 0, ct);

        var res = new DashboardScmResponse
        {
            PendingPR = await _context.PurchaseRequisition.CountAsync(x => x.RequisitionStatus == PurchaseRequisitionStatus.Draft, ct),
            PendingSQ = await _context.SalesQuotation.CountAsync(x => x.QuotationStatus == SalesQuotationStatus.Draft, ct),
            PendingGR = await _context.GoodsReceive.CountAsync(x => x.Status == GoodsReceiveStatus.Draft, ct),
            PendingDO = await _context.DeliveryOrder.CountAsync(x => x.Status == DeliveryOrderStatus.Draft, ct),
            ActiveCustomer = await _context.Customer.CountAsync(ct),
            ActiveVendor = await _context.Vendor.CountAsync(ct),
            InboundQtyMTD = inboundQty,
            OutboundQtyMTD = outboundQty,
            SalesMTD = await _context.SalesOrder.Where(x => x.OrderDate >= start).SumAsync(x => x.AfterTaxAmount ?? 0, ct),
            PurchaseMTD = await _context.PurchaseOrder.Where(x => x.OrderDate >= start).SumAsync(x => x.AfterTaxAmount ?? 0, ct),
            TotalReceivable = totalReceivable,
            TotalPayable = totalPayable,
            ChainAccuracyIndex = Math.Round(accuracy, 1),
            TotalPR = prCount,
            TotalPO = poCount,
            TotalGR = grCount,
            TotalDO = doCount,
            DocGrid = docGrid,
            GlobalFlowLog = globalLogs,
            TopProductsByQty = topProds,
            CustGroupStats = custGroups,
            VendGroupStats = vendGroups,
            CustCategoryStats = custCats,
            VendCategoryStats = vendCats,
            PayMethodStats = payMethods,
            WhseInventoryLevel = whseLevel,
            StockValuePerGroup = stockValGroup,
            CashFlowMTD = new List<ScmMetricDto>
            {
                new() { Label = "Inflow (Receive)", Value = (await _context.PaymentReceive.Where(x => x.PaymentDate >= start).SumAsync(x => x.PaymentAmount ?? 0, ct)).ToString("N0"), Color = "#10b981" },
                new() { Label = "Outflow (Disburse)", Value = (await _context.PaymentDisburse.Where(x => x.PaymentDate >= start).SumAsync(x => x.PaymentAmount ?? 0, ct)).ToString("N0"), Color = "#ef4444" }
            },
            MonthlySalesVsPurchase = monthlyData,
            WeeklySalesVsPurchase = weeklyData,
            VendorPOTotals = vendorPOTotals,
            ProductCategories = productCategories,
            POStatusBreakdown = poStatusBreakdown,
            GRStatusBreakdown = grStatusBreakdown,
            PRStatusBreakdown = prStatusBreakdown,
            DOStatusBreakdown = doStatusBreakdown,
            RecentPOs = recentPOs,
            RecentGRs = recentGRs
        };

        return res;
    }

    private static string GetPOStatusColor(string status) => status switch
    {
        "Draft" => "#f59e0b",
        "Confirmed" => "#2563eb",
        "Closed" => "#10b981",
        "Cancelled" => "#e11d48",
        _ => "#64748b"
    };

    private static string GetGRStatusColor(string status) => status switch
    {
        "Draft" => "#f59e0b",
        "Confirmed" => "#2563eb",
        "Closed" => "#10b981",
        "Cancelled" => "#e11d48",
        _ => "#64748b"
    };
}