ERP_Distribution/Distribution/Features/Warehouse/WarehouseDashboardRepository.cs

using Distribution.Data;
using Distribution.Models;
using Microsoft.EntityFrameworkCore;

namespace Distribution.Features.Warehouse;

public class WarehouseDashboardRepository(AppDBContext db) : IWarehouseDashboardRepository
{
    public Task<List<WarehouseBalanceRow>> GetBalanceRowsAsync(Guid warehouseId) =>
        db.StockBalances.AsNoTracking()
            .Where(b => b.LocationType == LocationType.Warehouse && b.LocationId == warehouseId)
            .Select(b => new WarehouseBalanceRow(
                b.FK_ItemId, b.Item.Sku, b.Item.Name, b.Item.Category.Name,
                b.Item.Brand != null ? b.Item.Brand.Name : null, b.Item.BaseUom.Name,
                b.Item.ReorderLevel, b.Item.CostPrice, b.Item.SalePrice,
                b.FK_ItemBatchId,
                b.ItemBatch != null ? b.ItemBatch.BatchNumber : null,
                b.ItemBatch != null ? b.ItemBatch.ExpiryDate : null,
                b.Quantity, b.UpdatedAt))
            .ToListAsync();

    public Task<List<WarehouseMovementRow>> GetMovementsAsync(Guid warehouseId, DateTime from, DateTime toExclusive) =>
        db.StockMovements.AsNoTracking()
            .Where(m => (m.FromLocationId == warehouseId || m.ToLocationId == warehouseId)
                && m.CreatedAt >= from && m.CreatedAt < toExclusive)
            .OrderByDescending(m => m.CreatedAt)
            .Select(m => new WarehouseMovementRow(
                m.Id, m.FK_ItemId, m.Item.Name, m.Item.Sku,
                m.FromLocationId, m.FromLocationType, m.ToLocationId, m.ToLocationType,
                m.Quantity, m.Type, m.ReasonCode, m.ReferenceId, m.CreatedAt, m.CreatedBy))
            .ToListAsync();

    public Task<List<WarehouseLastOutboundRow>> GetLastOutboundAsync(Guid warehouseId) =>
        db.StockMovements.AsNoTracking()
            .Where(m => m.FromLocationId == warehouseId && m.ToLocationId != warehouseId)
            .GroupBy(m => m.FK_ItemId)
            .Select(g => new WarehouseLastOutboundRow(g.Key, g.Max(x => x.CreatedAt)))
            .ToListAsync();

    public async Task<WarehouseGrnTotals> GetGrnTotalsAsync(Guid warehouseId, DateTime from, DateTime toExclusive)
    {
        var receipts = db.GoodsReceipts.AsNoTracking()
            .Where(g => g.FK_WarehouseId == warehouseId && g.ReceivedAt >= from && g.ReceivedAt < toExclusive);
        var count = await receipts.CountAsync();
        var accepted = await receipts.SelectMany(g => g.Items).SumAsync(i => (int?)i.AcceptedQuantity) ?? 0;
        var rejected = await receipts.SelectMany(g => g.Items).SumAsync(i => (int?)i.RejectedQuantity) ?? 0;
        return new WarehouseGrnTotals(count, accepted, rejected);
    }

    public Task<List<DashboardGrnRow>> GetRecentGrnsAsync(Guid warehouseId, DateTime from, DateTime toExclusive, int take) =>
        db.GoodsReceipts.AsNoTracking()
            .Where(g => g.FK_WarehouseId == warehouseId && g.ReceivedAt >= from && g.ReceivedAt < toExclusive)
            .OrderByDescending(g => g.ReceivedAt)
            .Take(take)
            .Select(g => new DashboardGrnRow(
                g.Id, g.ReceivedAt, g.FK_PurchaseOrderId,
                g.PurchaseOrder != null ? g.PurchaseOrder.Supplier.Name : (g.Supplier != null ? g.Supplier.Name : null),
                g.Items.Sum(i => i.AcceptedQuantity),
                g.Items.Sum(i => i.RejectedQuantity),
                g.Items.Sum(i => i.AcceptedQuantity * i.UnitCost)))
            .ToListAsync();

    public Task<List<DashboardLoadingSheetRow>> GetRecentLoadingSheetsAsync(Guid warehouseId, DateTime from, DateTime toExclusive, int take) =>
        db.LoadingSheets.AsNoTracking()
            .Where(s => s.PopulatedAt >= from && s.PopulatedAt < toExclusive
                && s.Items.Any(i => i.FK_WarehouseId == warehouseId))
            .OrderByDescending(s => s.PopulatedAt)
            .Take(take)
            .Select(s => new DashboardLoadingSheetRow(
                s.Id, s.FK_RideId, s.Ride.Vehicle.NumberPlate, s.Status, s.PopulatedAt, s.ConfirmedAt,
                s.Items.Count(i => i.FK_WarehouseId == warehouseId),
                s.Items.Where(i => i.FK_WarehouseId == warehouseId).Sum(i => i.QuantityDispatched)))
            .ToListAsync();

    public async Task<decimal> GetPendingDispatchUnitsAsync(Guid warehouseId) =>
        await db.LoadingSheetItems.AsNoTracking()
            .Where(i => i.FK_WarehouseId == warehouseId && i.LoadingSheet.Status == LoadingSheetStatus.Planned)
            .SumAsync(i => (decimal?)i.QuantityDispatched) ?? 0m;

    public async Task<List<WarehouseVehicleStockRow>> GetVehicleStockAsync(Guid warehouseId, Guid? primaryVehicleId)
    {
        var loadedFromHere = db.LoadingSheetItems
            .Where(i => i.FK_WarehouseId == warehouseId)
            .Select(i => i.LoadingSheet.Ride.FK_VehicleId);

        var vehicles = await db.Vehicles.AsNoTracking()
            .Where(v => v.IsActive
                && (v.DefaultWarehouseId == warehouseId
                    || (primaryVehicleId != null && v.Id == primaryVehicleId)
                    || loadedFromHere.Contains(v.Id)))
            .Select(v => new
            {
                v.Id, v.RegNumber, v.NumberPlate,
                IsLinked = v.DefaultWarehouseId == warehouseId || (primaryVehicleId != null && v.Id == primaryVehicleId),
            })
            .ToListAsync();

        var ids = vehicles.Select(v => v.Id).ToList();
        var balances = await db.StockBalances.AsNoTracking()
            .Where(b => b.LocationType == LocationType.Vehicle && ids.Contains(b.LocationId) && b.Quantity > 0)
            .GroupBy(b => b.LocationId)
            .Select(g => new { VehicleId = g.Key, Units = g.Sum(x => x.Quantity), Skus = g.Select(x => x.FK_ItemId).Distinct().Count() })
            .ToListAsync();

        return vehicles
            .Select(v =>
            {
                var bal = balances.FirstOrDefault(b => b.VehicleId == v.Id);
                return new WarehouseVehicleStockRow(v.Id, v.RegNumber, v.NumberPlate, v.IsLinked, bal?.Units ?? 0m, bal?.Skus ?? 0);
            })
            .OrderByDescending(v => v.Units)
            .ToList();
    }

    public Task<List<DashboardLinkedVehicle>> GetDefaultVehiclesAsync(Guid warehouseId, Guid? primaryVehicleId) =>
        db.Vehicles.AsNoTracking()
            .Where(v => v.IsActive
                && (v.DefaultWarehouseId == warehouseId || (primaryVehicleId != null && v.Id == primaryVehicleId)))
            .Select(v => new DashboardLinkedVehicle(
                v.Id, v.RegNumber, v.NumberPlate, primaryVehicleId != null && v.Id == primaryVehicleId))
            .ToListAsync();

    public Task<int> GetOpenClaimCountAsync(Guid warehouseId) =>
        db.SupplierClaims.AsNoTracking()
            .CountAsync(c => c.Status == SupplierClaimStatus.Open
                && c.GoodsReceipt != null && c.GoodsReceipt.FK_WarehouseId == warehouseId);

    public Task<List<DashboardClaimRow>> GetOpenClaimsAsync(Guid warehouseId, int take) =>
        db.SupplierClaims.AsNoTracking()
            .Where(c => c.Status == SupplierClaimStatus.Open
                && c.GoodsReceipt != null && c.GoodsReceipt.FK_WarehouseId == warehouseId)
            .OrderByDescending(c => c.CreatedAt)
            .Take(take)
            .Select(c => new DashboardClaimRow(
                c.Id, c.CreatedAt, c.PurchaseOrder.Supplier.Name, c.Details,
                c.Items.Sum(i => i.Quantity)))
            .ToListAsync();

    public async Task<Dictionary<Guid, string>> GetLocationNamesAsync(IEnumerable<Guid> ids)
    {
        var idList = ids.Distinct().ToList();
        var names = new Dictionary<Guid, string>();
        if (idList.Count == 0) return names;

        foreach (var w in await db.Warehouses.AsNoTracking().Where(w => idList.Contains(w.Id)).Select(w => new { w.Id, w.Name }).ToListAsync())
            names[w.Id] = w.Name;
        foreach (var v in await db.Vehicles.AsNoTracking().Where(v => idList.Contains(v.Id)).Select(v => new { v.Id, v.RegNumber }).ToListAsync())
            names[v.Id] = v.RegNumber;
        return names;
    }

    public async Task<Dictionary<Guid, string>> GetUserNamesAsync(IEnumerable<Guid> ids)
    {
        var idList = ids.Distinct().ToList();
        if (idList.Count == 0) return [];
        return await db.Users.AsNoTracking()
            .Where(u => idList.Contains(u.Id))
            .ToDictionaryAsync(u => u.Id, u => u.FullName);
    }
}