using System.Security.Claims;
using System.Text.Json;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using MiGu.DB.Abstractions.Entities;
using MiGu.DB.Abstractions.Persistence;
using MiGu.Server.Persistence;

namespace MiGu.Server.Wms;

public sealed class WmsService
{
    private readonly PlatformDbContext _db;
    private readonly IUnitOfWork _uow;
    private readonly IServiceProvider _services;
    private readonly WmsReferenceValidator _refs;
    private readonly JsonSerializerOptions _json = new(JsonSerializerDefaults.Web);

    public WmsService(
        PlatformDbContext db,
        IUnitOfWork uow,
        IServiceProvider services,
        WmsReferenceValidator refs)
    {
        _db = db;
        _uow = uow;
        _services = services;
        _refs = refs;
    }

    public Task<List<Warehouse>> Warehouses(string? q = null) =>
        FilterByKeyword(_db.Warehouses.AsNoTracking().OrderBy(x => x.SortOrder).ThenBy(x => x.Code), q).ToListAsync();

    public Task<List<WarehouseArea>> Areas(string? q = null, Guid? warehouseId = null)
    {
        var query = _db.WarehouseAreas.AsNoTracking().AsQueryable();
        if (warehouseId.HasValue) query = query.Where(x => x.WarehouseId == warehouseId.Value);
        return FilterByKeyword(query.OrderBy(x => x.SortOrder).ThenBy(x => x.Code), q).ToListAsync();
    }

    public Task<List<Storage>> Storages(string? q = null, Guid? areaId = null, string? locationKind = null)
    {
        var query = _db.Storages.AsNoTracking().AsQueryable();
        if (areaId.HasValue) query = query.Where(x => x.AreaId == areaId.Value);
        if (!string.IsNullOrWhiteSpace(locationKind) &&
            Enum.TryParse<LocationKind>(locationKind.Trim(), true, out var kind) &&
            LocationKinds.All.Contains(kind))
            query = query.Where(x => x.LocationKind == kind);
        return FilterByKeyword(query.OrderBy(x => x.Code), q).ToListAsync();
    }

    public Task<List<Container>> Containers(string? q = null) =>
        FilterByKeyword(_db.Containers.AsNoTracking().OrderBy(x => x.Code), q).ToListAsync();

    public Task<List<MaterialType>> MaterialTypes(string? q = null) =>
        FilterByKeyword(_db.MaterialTypes.AsNoTracking().OrderBy(x => x.Code), q).ToListAsync();

    public Task<List<Material>> Materials(string? q = null, string? lifecycle = null, bool? onlyUnbound = null, bool? onlyBound = null)
    {
        var query = _db.Materials.AsNoTracking().AsQueryable();
        var life = MaterialLifecycles.ParseOr(lifecycle);
        query = query.Where(x => x.LifecycleStatus == life);
        if (onlyUnbound == true || onlyBound == true)
        {
            var boundIds = _db.ContainerMaterials.AsNoTracking().Select(x => x.MaterialId);
            if (onlyUnbound == true) query = query.Where(x => !boundIds.Contains(x.Id));
            if (onlyBound == true) query = query.Where(x => boundIds.Contains(x.Id));
        }
        return FilterByKeyword(query.OrderBy(x => x.Code), q).ToListAsync();
    }

    public Task<List<ContainerLocation>> ContainerLocations(string? locationType = null, string? q = null)
    {
        var query = _db.ContainerLocations.AsNoTracking().OrderBy(x => x.ContainerId).AsQueryable();
        if (!string.IsNullOrWhiteSpace(locationType) && ContainerLocationTypes.IsDefined(locationType))
        {
            var lt = ContainerLocationTypes.ParseOr(locationType);
            query = query.Where(x => x.LocationType == lt);
        }
        return FilterByKeyword(query, q).ToListAsync();
    }

    public Task<List<ContainerMaterial>> ContainerMaterials(string? q = null, Guid? containerId = null)
    {
        var query = _db.ContainerMaterials.AsNoTracking().AsQueryable();
        if (containerId.HasValue) query = query.Where(x => x.ContainerId == containerId.Value);
        return FilterByKeyword(query.OrderBy(x => x.ContainerId), q).ToListAsync();
    }

    /// <summary>
    /// 库存物料列表：筛选/排序下推到 SQL。
    /// 库位：优先 ContainerLocation.StorageId；未回填时用 LocationId 与库位 Id 的存储字符串（Guid "D"）匹配。
    /// </summary>
    public async Task<List<InventoryMaterialRow>> InventoryMaterials(Guid? areaId = null, Guid? storageId = null, string? q = null)
    {
        var query =
            from b in _db.ContainerMaterials.AsNoTracking()
            join mat in _db.Materials.AsNoTracking() on b.MaterialId equals mat.Id
            from ctn in _db.Containers.AsNoTracking().Where(c => c.Id == b.ContainerId).DefaultIfEmpty()
            from loc in _db.ContainerLocations.AsNoTracking().Where(l => l.ContainerId == b.ContainerId).DefaultIfEmpty()
            from st in _db.Storages.AsNoTracking().Where(s =>
                loc != null &&
                loc.LocationType == ContainerLocationType.Storage &&
                (loc.StorageId == s.Id ||
                 (loc.StorageId == null && loc.LocationId == EF.Property<string>(s, nameof(Storage.Id))))).DefaultIfEmpty()
            from area in _db.WarehouseAreas.AsNoTracking().Where(a => st != null && a.Id == st.AreaId).DefaultIfEmpty()
            select new { b, mat, ctn, loc, st, area };

        if (storageId.HasValue)
            query = query.Where(x => x.st != null && x.st.Id == storageId.Value);
        if (areaId.HasValue)
            query = query.Where(x => x.area != null && x.area.Id == areaId.Value);
        if (!string.IsNullOrWhiteSpace(q))
        {
            var s = q.Trim();
            query = query.Where(x =>
                x.mat.Code.Contains(s) || x.mat.Name.Contains(s) ||
                (x.ctn != null && (x.ctn.Code.Contains(s) || x.ctn.Name.Contains(s))) ||
                (x.st != null && x.st.Code.Contains(s)));
        }

        return await query
            .OrderBy(x => x.mat.Code)
            .Select(x => new InventoryMaterialRow(
                x.mat.Id, x.mat.Code, x.mat.Name, x.mat.Barcode, x.mat.TypeCode,
                x.b.ContainerId, x.ctn != null ? x.ctn.Code : "", x.ctn != null ? x.ctn.Name : "",
                x.st != null ? x.st.Id : null, x.st != null ? x.st.Code : "", x.st != null ? x.st.Name : "",
                x.area != null ? x.area.Id : null, x.area != null ? x.area.Code : "", x.area != null ? x.area.Name : "",
                x.loc != null ? x.loc.LocationType.ToString() : "", x.b.BoundAt))
            .ToListAsync();
    }

    /// <summary>
    /// 库存事件：条件与 OrderBy/Take(500) 均下推；非法 eventType 直接返回空，避免全表拉取后再过滤。
    /// </summary>
    public async Task<List<StockEvent>> StockEvents(string? eventType = null, Guid? materialId = null, Guid? containerId = null)
    {
        var query = _db.StockEvents.AsNoTracking().AsQueryable();
        if (!string.IsNullOrWhiteSpace(eventType))
        {
            if (Enum.TryParse<StockEventType>(eventType.Trim(), true, out var et))
                query = query.Where(x => x.EventType == et);
            else
                return [];
        }
        if (materialId.HasValue) query = query.Where(x => x.MaterialId == materialId.Value);
        if (containerId.HasValue) query = query.Where(x => x.ContainerId == containerId.Value);
        return await query.OrderByDescending(x => x.OperatedAt).Take(500).ToListAsync();
    }

    public async Task<Warehouse> SaveWarehouse(MasterDataRequest req, string actor)
    {
        Warehouse entity;
        if (req.Id.HasValue) entity = await FindEditable<Warehouse>(req.Id.Value, req.Version);
        else
        {
            entity = new Warehouse();
            StampCreate(entity, actor);
            _db.Warehouses.Add(entity);
        }
        await EnsureUnique(_db.Warehouses, x => x.Code == req.Code && x.Id != entity.Id, "仓库编码已存在");
        entity.Code = req.Code.Trim();
        entity.Name = req.Name.Trim();
        entity.Type = req.Type.TrimOr("Default");
        entity.Enabled = req.Enabled;
        entity.SortOrder = req.SortOrder;
        ApplyCommon(entity, req, actor);
        await _uow.SaveChangesAsync();
        return entity;
    }

    public async Task<WarehouseArea> SaveArea(AreaRequest req, string actor)
    {
        var warehouseId = req.WarehouseId ?? Guid.Empty;
        if (warehouseId == Guid.Empty)
            warehouseId = (await EnsureDefaultWarehouseAsync(actor)).Id;
        else
            await _refs.EnsureWarehouseAsync(warehouseId);

        WarehouseArea entity;
        if (req.Id.HasValue) entity = await FindEditable<WarehouseArea>(req.Id.Value, req.Version);
        else
        {
            entity = new WarehouseArea();
            StampCreate(entity, actor);
            _db.WarehouseAreas.Add(entity);
        }
        await EnsureUnique(_db.WarehouseAreas, x => x.Code == req.Code && x.Id != entity.Id, "库区编码已存在");
        entity.WarehouseId = warehouseId;
        entity.Code = req.Code.Trim();
        entity.Name = req.Name.Trim();
        entity.Type = req.Type.TrimOr("Storage");
        entity.LayoutMode = AreaLayoutModes.ParseOr(req.LayoutMode);
        entity.State = req.State.TrimOr("Default");
        entity.Enabled = req.Enabled;
        entity.SortOrder = req.SortOrder;
        ApplyCommon(entity, req, actor);
        await _uow.SaveChangesAsync();
        return entity;
    }

    public async Task<Storage> SaveStorage(StorageRequest req, string actor)
    {
        await _refs.EnsureAreaAsync(req.AreaId);
        var kind = LocationKinds.ParseOr(req.LocationKind);
        if (kind == LocationKinds.Grid)
            await EnsureGridCoordUnique(req.AreaId, req.ColumnNo, req.LevelNo, req.DepthNo, req.Id);

        Storage entity;
        if (req.Id.HasValue) entity = await FindEditable<Storage>(req.Id.Value, req.Version);
        else
        {
            entity = new Storage();
            StampCreate(entity, actor);
            _db.Storages.Add(entity);
        }
        await EnsureUnique(_db.Storages, x => x.Code == req.Code && x.Id != entity.Id, "库位编码已存在");
        entity.AreaId = req.AreaId;
        entity.Code = req.Code.Trim();
        entity.Name = req.Name.Trim();
        entity.StorageType = req.StorageType.TrimOr("Storage");
        entity.LocationKind = kind;
        entity.ColumnNo = req.ColumnNo;
        entity.LevelNo = req.LevelNo <= 0 ? 1 : req.LevelNo;
        entity.DepthNo = req.DepthNo <= 0 ? 1 : req.DepthNo;
        entity.SiteId = req.SiteId.TrimOr("");
        entity.SiteCode = req.SiteCode.TrimOr(entity.SiteId);
        entity.Barcode = req.Barcode.TrimOr("");
        entity.Capacity = 1;
        var status = StorageStatuses.Normalize(req.Status);
        if (status == StorageStatuses.Disabled || req.Enabled == false)
            entity.Status = req.Enabled ? status : StorageStatuses.Disabled;
        else if (!string.IsNullOrWhiteSpace(req.Status) && StorageStatuses.All.Contains(status))
            entity.Status = status;
        entity.Usage = req.Usage.TrimOr("");
        entity.Priority = req.Priority;
        entity.ZoneCode = req.ZoneCode.TrimOr("");
        entity.AllowInbound = req.AllowInbound;
        entity.AllowOutbound = req.AllowOutbound;
        entity.Enabled = req.Enabled;
        ApplyCommon(entity, req, actor);
        await _uow.SaveChangesAsync();
        if (entity.Status != StorageStatuses.Disabled)
            await SyncOccupancyStatus(storageId: entity.Id);
        return entity;
    }

    public async Task<int> GenerateBins(Guid areaId, GenerateBinsRequest req, string actor)
    {
        await _refs.EnsureAreaAsync(areaId);
        if (req.ColumnFrom > req.ColumnTo || req.LevelFrom > req.LevelTo || req.DepthFrom > req.DepthTo)
            throw new InvalidOperationException("生成范围无效");
        var area = await _db.WarehouseAreas.AsNoTracking().FirstAsync(x => x.Id == areaId);
        var existing = await _db.Storages.AsNoTracking()
            .Where(x => x.AreaId == areaId && x.LocationKind == LocationKinds.Grid)
            .Select(x => new { x.ColumnNo, x.LevelNo, x.DepthNo })
            .ToListAsync();
        var set = existing.Select(x => (x.ColumnNo, x.LevelNo, x.DepthNo)).ToHashSet();
        var created = 0;
        for (var col = req.ColumnFrom; col <= req.ColumnTo; col++)
        for (var level = req.LevelFrom; level <= req.LevelTo; level++)
        for (var depth = req.DepthFrom; depth <= req.DepthTo; depth++)
        {
            if (set.Contains((col, level, depth))) continue;
            var code = string.IsNullOrWhiteSpace(req.CodePattern)
                ? $"{area.Code}-{level:D2}{col:D2}" + (req.DepthFrom == req.DepthTo && depth == 1 ? "" : $"-{depth}")
                : req.CodePattern
                    .Replace("{Area}", area.Code, StringComparison.OrdinalIgnoreCase)
                    .Replace("{Level}", level.ToString("D2"), StringComparison.OrdinalIgnoreCase)
                    .Replace("{Column}", col.ToString("D2"), StringComparison.OrdinalIgnoreCase)
                    .Replace("{Depth}", depth.ToString(), StringComparison.OrdinalIgnoreCase);
            if (await _db.Storages.AnyAsync(x => x.Code == code))
                code = $"{code}-{Guid.NewGuid().ToString("N")[..4]}";
            var entity = new Storage
            {
                AreaId = areaId,
                Code = code,
                Name = code,
                LocationKind = LocationKinds.Grid,
                ColumnNo = col,
                LevelNo = level,
                DepthNo = depth,
                Status = StorageStatuses.Empty,
                Capacity = 1,
                Enabled = true,
                AllowInbound = true,
                AllowOutbound = true
            };
            StampCreate(entity, actor);
            _db.Storages.Add(entity);
            created++;
        }
        await _uow.SaveChangesAsync();
        return created;
    }

    public async Task<Storage> SetStorageLock(Guid id, bool isLock, long? version, string actor)
    {
        var entity = await FindEditable<Storage>(id, version);
        entity.IsLock = isLock;
        entity.UpdatedBy = actor;
        await _uow.SaveChangesAsync();
        return entity;
    }

    public async Task<Storage> SetStorageEnabled(Guid id, bool enabled, long? version, string actor)
    {
        var entity = await FindEditable<Storage>(id, version);
        entity.Enabled = enabled;
        entity.Status = enabled
            ? (entity.Status == StorageStatuses.Disabled ? StorageStatuses.Empty : entity.Status)
            : StorageStatuses.Disabled;
        entity.UpdatedBy = actor;
        await _uow.SaveChangesAsync();
        if (enabled) await SyncOccupancyStatus(storageId: id);
        return entity;
    }

    public async Task<Container> SaveContainer(ContainerRequest req, string actor)
    {
        Container entity;
        if (req.Id.HasValue) entity = await FindEditable<Container>(req.Id.Value, req.Version);
        else
        {
            entity = new Container();
            StampCreate(entity, actor);
            _db.Containers.Add(entity);
        }
        await EnsureUnique(_db.Containers, x => x.Code == req.Code && x.Id != entity.Id, "容器编码已存在");
        entity.AreaId = req.AreaId;
        entity.Code = req.Code.Trim();
        entity.Name = req.Name.Trim();
        entity.ContainerType = req.Type.TrimOr("Box");
        entity.Status = ContainerStatuses.ParseOr(req.Status);
        entity.Barcode = req.Barcode.TrimOr("");
        entity.Length = req.Length;
        entity.Width = req.Width;
        entity.Height = req.Height;
        entity.Enabled = req.Enabled;
        ApplyCommon(entity, req, actor);
        await _uow.SaveChangesAsync();
        await SyncOccupancyStatus(containerId: entity.Id);
        return entity;
    }

    public async Task<MaterialType> SaveMaterialType(MaterialTypeRequest req, string actor)
    {
        MaterialType entity;
        if (req.Id.HasValue) entity = await FindEditable<MaterialType>(req.Id.Value, req.Version);
        else
        {
            entity = new MaterialType();
            StampCreate(entity, actor);
            _db.MaterialTypes.Add(entity);
        }
        await EnsureUnique(_db.MaterialTypes, x => x.Code == req.Code && x.Id != entity.Id, "物料类型编码已存在");
        entity.Code = req.Code.Trim();
        entity.Name = req.Name.Trim();
        entity.Spec = req.Spec.TrimOr("");
        entity.Unit = req.Unit.TrimOr("pcs");
        entity.Category = req.Category.TrimOr("");
        entity.BarcodePrefix = req.BarcodePrefix.TrimOr("");
        entity.Enabled = req.Enabled;
        ApplyCommon(entity, req, actor);
        await _uow.SaveChangesAsync();
        return entity;
    }

    public async Task<Material> SaveMaterial(MaterialRequest req, string actor)
    {
        if (!string.IsNullOrWhiteSpace(req.TypeCode))
            await _refs.EnsureMaterialTypeCodeAsync(req.TypeCode);
        Material entity;
        if (req.Id.HasValue) entity = await FindEditable<Material>(req.Id.Value, req.Version);
        else
        {
            entity = new Material();
            StampCreate(entity, actor);
            _db.Materials.Add(entity);
        }
        await EnsureUnique(_db.Materials, x => x.Code == req.Code && x.Id != entity.Id, "物料编码已存在");
        if (!string.IsNullOrWhiteSpace(req.Barcode))
            await EnsureUnique(_db.Materials, x => x.Barcode == req.Barcode && x.Id != entity.Id, "物料条码已存在");
        entity.Code = req.Code.Trim();
        entity.Name = req.Name.Trim();
        entity.TypeCode = req.TypeCode.TrimOr("");
        entity.Barcode = req.Barcode.TrimOr("");
        entity.Spec = req.Spec.TrimOr("");
        entity.Unit = req.Unit.TrimOr("pcs");
        entity.Category = req.Category.TrimOr("");
        entity.LifecycleStatus = MaterialLifecycles.ParseOr(req.LifecycleStatus);
        entity.Enabled = req.Enabled;
        ApplyCommon(entity, req, actor);
        await _uow.SaveChangesAsync();
        return entity;
    }

    public async Task<Material> ArchiveMaterial(Guid id, long? version, string actor)
    {
        var entity = await FindEditable<Material>(id, version);
        if (await _db.ContainerMaterials.AnyAsync(x => x.MaterialId == id))
            throw new InvalidOperationException("物料仍在绑定中，不能归档");
        entity.LifecycleStatus = MaterialLifecycles.Archived;
        entity.UnboundAt ??= DateTimeOffset.UtcNow;
        entity.UpdatedBy = actor;
        await _uow.SaveChangesAsync();
        return entity;
    }

    public async Task PurgeArchivedMaterials(string actor, int take = 200)
    {
        var rows = await _db.Materials
            .Where(x => x.LifecycleStatus == MaterialLifecycles.Archived)
            .OrderBy(x => x.UpdatedAt)
            .Take(take)
            .ToListAsync();
        foreach (var entity in rows)
        {
            if (await _db.ContainerMaterials.AnyAsync(x => x.MaterialId == entity.Id)) continue;
            entity.IsDeleted = true;
            entity.DeletedAt = DateTimeOffset.UtcNow;
            entity.DeletedBy = actor;
            entity.UpdatedBy = actor;
        }
        await _uow.SaveChangesAsync();
    }

    public async Task DeleteEntity<T>(Guid id, long? version, string actor) where T : EntityBase
    {
        var entity = await FindEditable<T>(id, version);
        if (typeof(T) == typeof(WarehouseArea))
        {
            if (await _db.Storages.AnyAsync(x => x.AreaId == id))
                throw new InvalidOperationException("库区下仍有货位，不能删除");
        }
        if (typeof(T) == typeof(Storage))
        {
            var sid = id.ToString("D");
            if (await _db.ContainerLocations.AnyAsync(x => x.LocationType == ContainerLocationTypes.Storage && x.LocationId == sid))
                throw new InvalidOperationException("货位仍被容器占用，不能删除");
        }
        if (typeof(T) == typeof(Material))
        {
            if (await _db.ContainerMaterials.AnyAsync(x => x.MaterialId == id))
                throw new InvalidOperationException("物料仍在绑定中，不能删除");
        }
        entity.IsDeleted = true;
        entity.DeletedAt = DateTimeOffset.UtcNow;
        entity.DeletedBy = actor;
        entity.UpdatedBy = actor;
        await _uow.SaveChangesAsync();
    }

    public async Task<ContainerLocation> BindOrTransferLocation(ContainerLocationRequest req, string actor)
    {
        await _refs.EnsureContainerAsync(req.ContainerId);
        var (code, name) = await _refs.ResolveLocationSnapshotAsync(req.LocationType, req.LocationId);
        if (!ContainerLocationStatuses.IsDefined(req.Status))
            throw new InvalidOperationException("容器位置状态无效");

        var locationType = ContainerLocationTypes.ParseOr(req.LocationType);
        var locationStatus = ContainerLocationStatuses.ParseOr(req.Status);

        Guid? fromStorageId = null;
        Guid? toStorageId = null;
        if (locationType == ContainerLocationTypes.Storage &&
            Guid.TryParse(req.LocationId, out var targetStorageId))
        {
            toStorageId = targetStorageId;
            var storage = await _db.Storages.FirstOrDefaultAsync(x => x.Id == targetStorageId)
                ?? throw new InvalidOperationException("库位不存在");
            if (!storage.Enabled || storage.Status == StorageStatuses.Disabled || storage.IsLock)
                throw new InvalidOperationException("目标库位已停用或锁定");
            if (!storage.AllowInbound)
                throw new InvalidOperationException("目标库位不允许入库");
            var occupied = await _db.ContainerLocations.AsNoTracking()
                .AnyAsync(x => x.LocationType == ContainerLocationTypes.Storage &&
                               x.LocationId == req.LocationId &&
                               x.ContainerId != req.ContainerId);
            if (occupied)
                throw new InvalidOperationException("目标库位已被其他容器占用，库位与容器为 1 对 1");
        }

        var current = await _db.ContainerLocations.FirstOrDefaultAsync(x => x.ContainerId == req.ContainerId);
        var before = current == null ? null : Snapshot(current);
        if (before != null &&
            ContainerLocationTypes.EqualsString(ContainerLocationTypes.Storage, before.LocationType) &&
            Guid.TryParse(before.LocationId, out var fs))
            fromStorageId = fs;
        var now = DateTimeOffset.UtcNow;

        if (current == null)
        {
            current = new ContainerLocation { ContainerId = req.ContainerId, EnteredAt = now };
            StampCreate(current, actor);
            _db.ContainerLocations.Add(current);
        }
        else
        {
            EnsureVersion(current, req.Version);
            EnsureUnlocked(current);
        }

        current.LocationType = locationType;
        current.LocationId = req.LocationId.Trim();
        current.StorageId = toStorageId;
        current.LocationCode = code;
        current.LocationName = name;
        current.Status = locationStatus;
        current.EnteredAt = req.EnteredAt ?? now;
        ApplyCommon(current, req, actor);

        _db.ContainerLocationHistories.Add(new ContainerLocationHistory
        {
            RelationId = current.Id,
            ContainerId = current.ContainerId,
            EventType = before == null ? "Bind" : "Transfer",
            FromLocationType = before?.LocationType ?? "",
            FromLocationId = before?.LocationId ?? "",
            ToLocationType = current.LocationType.ToString(),
            ToLocationId = current.LocationId,
            BeforeJson = before == null ? "{}" : JsonSerializer.Serialize(before, _json),
            AfterJson = JsonSerializer.Serialize(Snapshot(current), _json),
            Operator = actor,
            OperatedAt = now,
            Source = req.Source.TrimOr("Manual"),
            Reason = req.Reason.TrimOr(""),
            Remark = req.Remark.TrimOr(""),
            Extend = NormalizeExtend(req.Extend)
        });

        await AddContainerMoveEventAsync(req.ContainerId, fromStorageId, toStorageId, actor, req.Reason.TrimOr(""), now);
        await _uow.SaveChangesAsync();
        await SyncOccupancyStatus(containerId: req.ContainerId, storageId: fromStorageId);
        await SyncOccupancyStatus(storageId: toStorageId);
        return current;
    }

    public async Task UnbindLocation(Guid containerId, string actor, string reason = "")
    {
        var current = await _db.ContainerLocations.FirstOrDefaultAsync(x => x.ContainerId == containerId)
            ?? throw new InvalidOperationException("容器当前位置不存在");
        EnsureUnlocked(current);
        Guid? fromStorageId = null;
        if (current.LocationType == ContainerLocationTypes.Storage && Guid.TryParse(current.LocationId, out var fs))
            fromStorageId = fs;
        var before = Snapshot(current);
        var now = DateTimeOffset.UtcNow;
        _db.ContainerLocationHistories.Add(new ContainerLocationHistory
        {
            RelationId = current.Id,
            ContainerId = current.ContainerId,
            EventType = "Unbind",
            FromLocationType = current.LocationType.ToString(),
            FromLocationId = current.LocationId,
            BeforeJson = JsonSerializer.Serialize(before, _json),
            AfterJson = "{}",
            Operator = actor,
            OperatedAt = now,
            Source = "Manual",
            Reason = reason
        });
        await AddContainerMoveEventAsync(containerId, fromStorageId, null, actor, reason, now);
        _db.ContainerLocations.Remove(current);
        await _uow.SaveChangesAsync();
        await SyncOccupancyStatus(containerId: containerId, storageId: fromStorageId);
    }

    public async Task<ContainerMaterial> BindMaterial(BindMaterialRequest req, string actor)
    {
        await _refs.EnsureContainerAsync(req.ContainerId);
        var material = await _db.Materials.FirstOrDefaultAsync(x => x.Id == req.MaterialId)
            ?? throw new InvalidOperationException("物料不存在");
        if (material.LifecycleStatus != MaterialLifecycles.Active)
            throw new InvalidOperationException("仅 Active 物料可绑定");
        if (await _db.ContainerMaterials.AnyAsync(x => x.MaterialId == req.MaterialId))
            throw new InvalidOperationException("物料已绑定其他容器");

        var now = DateTimeOffset.UtcNow;
        var current = new ContainerMaterial
        {
            ContainerId = req.ContainerId,
            MaterialId = req.MaterialId,
            Quantity = 1,
            Status = ContainerMaterialStatuses.Bound,
            BoundAt = now,
            LoadedAt = now
        };
        StampCreate(current, actor);
        ApplyCommon(current, req, actor);
        _db.ContainerMaterials.Add(current);

        _db.ContainerMaterialHistories.Add(new ContainerMaterialHistory
        {
            RelationId = current.Id,
            ContainerId = current.ContainerId,
            MaterialId = current.MaterialId,
            EventType = "Bind",
            QuantityDelta = 0,
            BeforeJson = "{}",
            AfterJson = JsonSerializer.Serialize(Snapshot(current), _json),
            Operator = actor,
            OperatedAt = now,
            Source = req.Source.TrimOr("Manual"),
            Reason = req.Reason.TrimOr(""),
            Remark = req.Remark.TrimOr(""),
            Extend = NormalizeExtend(req.Extend)
        });

        material.UnboundAt = null;
        await AddBindUnbindEventAsync(StockEventTypes.Bind, material, req.ContainerId, actor, req.Reason.TrimOr(""), now);
        await _uow.SaveChangesAsync();
        await SyncOccupancyStatus(containerId: req.ContainerId);
        return current;
    }

    /// <summary>兼容旧接口：忽略数量，按实体绑定。</summary>
    public Task<ContainerMaterial> SaveContainerMaterial(ContainerMaterialRequest req, string actor) =>
        BindMaterial(new BindMaterialRequest(req.Id, req.Version, req.ContainerId, req.MaterialId, req.Source, req.Reason, req.IsLock, req.Remark, req.Extend), actor);

    public async Task UnbindMaterial(Guid bindingId, string actor, string reason = "", bool archive = false)
    {
        var current = await _db.ContainerMaterials.FirstOrDefaultAsync(x => x.Id == bindingId)
            ?? throw new InvalidOperationException("绑定不存在");
        EnsureUnlocked(current);
        var material = await _db.Materials.FirstOrDefaultAsync(x => x.Id == current.MaterialId);
        var before = Snapshot(current);
        var now = DateTimeOffset.UtcNow;
        var containerId = current.ContainerId;

        _db.ContainerMaterialHistories.Add(new ContainerMaterialHistory
        {
            RelationId = current.Id,
            ContainerId = current.ContainerId,
            MaterialId = current.MaterialId,
            EventType = "Unbind",
            QuantityDelta = 0,
            BeforeJson = JsonSerializer.Serialize(before, _json),
            AfterJson = "{}",
            Operator = actor,
            OperatedAt = now,
            Source = "Manual",
            Reason = reason
        });

        if (material != null)
        {
            material.UnboundAt = now;
            if (archive) material.LifecycleStatus = MaterialLifecycles.Archived;
            await AddBindUnbindEventAsync(StockEventTypes.Unbind, material, containerId, actor, reason, now);
        }

        _db.ContainerMaterials.Remove(current);
        await _uow.SaveChangesAsync();
        await SyncOccupancyStatus(containerId: containerId);
    }

    public Task UnloadMaterial(Guid id, string actor, string reason = "") => UnbindMaterial(id, actor, reason);

    public async Task SyncOccupancyStatus(Guid? storageId = null, Guid? containerId = null)
    {
        var containerIds = new HashSet<Guid>();
        if (containerId.HasValue) containerIds.Add(containerId.Value);

        if (storageId.HasValue)
        {
            var sid = storageId.Value.ToString("D");
            var loc = await _db.ContainerLocations.FirstOrDefaultAsync(x =>
                x.LocationType == ContainerLocationTypes.Storage && x.LocationId == sid);
            if (loc != null) containerIds.Add(loc.ContainerId);

            var storage = await _db.Storages.FirstOrDefaultAsync(x => x.Id == storageId.Value);
            if (storage != null && storage.Status != StorageStatuses.Disabled)
            {
                if (loc == null) storage.Status = StorageStatuses.Empty;
                else
                {
                    var hasMat = await _db.ContainerMaterials.AnyAsync(x => x.ContainerId == loc.ContainerId);
                    storage.Status = hasMat ? StorageStatuses.FullContainer : StorageStatuses.EmptyContainer;
                }
            }
        }

        foreach (var cid in containerIds)
        {
            var ctn = await _db.Containers.FirstOrDefaultAsync(x => x.Id == cid);
            if (ctn == null) continue;
            var hasMat = await _db.ContainerMaterials.AnyAsync(x => x.ContainerId == cid);
            ctn.Status = hasMat ? ContainerStatuses.FullMaterial : ContainerStatuses.EmptyMaterial;

            var loc = await _db.ContainerLocations.FirstOrDefaultAsync(x => x.ContainerId == cid);
            if (loc != null && loc.LocationType == ContainerLocationTypes.Storage && Guid.TryParse(loc.LocationId, out var sid2))
            {
                var storage = await _db.Storages.FirstOrDefaultAsync(x => x.Id == sid2);
                if (storage != null && storage.Status != StorageStatuses.Disabled)
                    storage.Status = hasMat ? StorageStatuses.FullContainer : StorageStatuses.EmptyContainer;
            }
        }

        await _uow.SaveChangesAsync();
    }

    public async Task<Warehouse> EnsureDefaultWarehouseAsync(string actor = "system")
    {
        var existing = await _db.Warehouses.FirstOrDefaultAsync(x => x.Code == WmsDefaults.DefaultWarehouseCode);
        if (existing != null) return existing;
        var wh = new Warehouse
        {
            Code = WmsDefaults.DefaultWarehouseCode,
            Name = WmsDefaults.DefaultWarehouseName,
            Type = "Default",
            Enabled = true,
            SortOrder = 0
        };
        StampCreate(wh, actor);
        _db.Warehouses.Add(wh);
        await _uow.SaveChangesAsync();
        return wh;
    }

    public async Task MigrateLegacyAsync(string actor = "system")
    {
        var wh = await EnsureDefaultWarehouseAsync(actor);
        var areas = await _db.WarehouseAreas.Where(x => x.WarehouseId == Guid.Empty).ToListAsync();
        foreach (var a in areas)
            a.WarehouseId = wh.Id;

        var storages = await _db.Storages.ToListAsync();
        foreach (var s in storages)
        {
            if (s.LevelNo <= 0) s.LevelNo = 1;
            if (s.DepthNo <= 0) s.DepthNo = 1;
            if (string.IsNullOrWhiteSpace(s.SiteCode)) s.SiteCode = s.SiteId;
            if (!StorageStatuses.All.Contains(s.Status))
                s.Status = StorageStatuses.Empty;
            if (!s.Enabled) s.Status = StorageStatuses.Disabled;
        }

        var containers = await _db.Containers.ToListAsync();
        foreach (var c in containers)
        {
            if (!ContainerStatuses.All.Contains(c.Status))
                c.Status = ContainerStatuses.EmptyMaterial;
        }

        var binds = await _db.ContainerMaterials.ToListAsync();
        foreach (var b in binds)
        {
            b.Quantity = 1;
            if (b.BoundAt == default) b.BoundAt = b.LoadedAt == default ? DateTimeOffset.UtcNow : b.LoadedAt;
            if (!ContainerMaterialStatuses.ActiveBind.Contains(b.Status))
                b.Status = ContainerMaterialStatuses.Bound;
        }

        await _uow.SaveChangesAsync();

        foreach (var s in storages.Where(x => x.Status != StorageStatuses.Disabled))
            await SyncOccupancyStatus(storageId: s.Id);
    }

    public async Task<List<ContainerLocationHistory>> LocationHistory(Guid? containerId = null)
    {
        var rows = await (containerId.HasValue
            ? _db.ContainerLocationHistories.AsNoTracking().Where(x => x.ContainerId == containerId.Value)
            : _db.ContainerLocationHistories.AsNoTracking())
            .ToListAsync();
        return rows.OrderByDescending(x => x.OperatedAt).Take(500).ToList();
    }

    public async Task<List<ContainerMaterialHistory>> MaterialHistory(Guid? containerId = null, Guid? materialId = null)
    {
        var query = _db.ContainerMaterialHistories.AsNoTracking().AsQueryable();
        if (containerId.HasValue) query = query.Where(x => x.ContainerId == containerId.Value);
        if (materialId.HasValue) query = query.Where(x => x.MaterialId == materialId.Value);
        var rows = await query.ToListAsync();
        return rows.OrderByDescending(x => x.OperatedAt).Take(500).ToList();
    }

    private async Task EnsureGridCoordUnique(Guid areaId, int column, int level, int depth, Guid? excludeId)
    {
        var exists = await _db.Storages.AnyAsync(x =>
            x.AreaId == areaId &&
            x.LocationKind == LocationKinds.Grid &&
            x.ColumnNo == column && x.LevelNo == level && x.DepthNo == depth &&
            (!excludeId.HasValue || x.Id != excludeId.Value));
        if (exists) throw new InvalidOperationException("同库区网格坐标已存在");
    }

    private async Task AddBindUnbindEventAsync(StockEventType eventType, Material material, Guid containerId, string actor, string reason, DateTimeOffset now)
    {
        var ctn = await _db.Containers.AsNoTracking().FirstOrDefaultAsync(x => x.Id == containerId);
        var loc = await _db.ContainerLocations.AsNoTracking().FirstOrDefaultAsync(x => x.ContainerId == containerId);
        Storage? storage = null;
        WarehouseArea? area = null;
        if (loc is { LocationType: ContainerLocationType.Storage } &&
            (loc.StorageId is { } sid || Guid.TryParse(loc.LocationId, out sid)))
        {
            storage = await _db.Storages.AsNoTracking().FirstOrDefaultAsync(x => x.Id == sid);
            if (storage != null)
                area = await _db.WarehouseAreas.AsNoTracking().FirstOrDefaultAsync(x => x.Id == storage.AreaId);
        }

        if (string.IsNullOrWhiteSpace(material.Code))
            throw new InvalidOperationException("物料编码快照不能为空");

        _db.StockEvents.Add(new StockEvent
        {
            EventType = eventType,
            MaterialId = material.Id,
            MaterialCode = material.Code,
            MaterialName = material.Name,
            MaterialBarcode = material.Barcode,
            MaterialTypeCode = material.TypeCode,
            ContainerId = containerId,
            ContainerCode = ctn?.Code ?? "",
            ContainerName = ctn?.Name ?? "",
            StorageId = storage?.Id,
            StorageCode = storage?.Code ?? loc?.LocationCode ?? "",
            StorageName = storage?.Name ?? loc?.LocationName ?? "",
            AreaCode = area?.Code ?? "",
            RefType = "Manual",
            Operator = actor,
            OperatedAt = now,
            Reason = reason
        });
    }

    private async Task AddContainerMoveEventAsync(Guid containerId, Guid? fromStorageId, Guid? toStorageId, string actor, string reason, DateTimeOffset now)
    {
        var ctn = await _db.Containers.AsNoTracking().FirstOrDefaultAsync(x => x.Id == containerId)
            ?? throw new InvalidOperationException("容器不存在");
        Storage? from = null, to = null;
        if (fromStorageId.HasValue)
            from = await _db.Storages.AsNoTracking().FirstOrDefaultAsync(x => x.Id == fromStorageId.Value);
        if (toStorageId.HasValue)
            to = await _db.Storages.AsNoTracking().FirstOrDefaultAsync(x => x.Id == toStorageId.Value);

        _db.StockEvents.Add(new StockEvent
        {
            EventType = StockEventTypes.ContainerMove,
            ContainerId = containerId,
            ContainerCode = ctn.Code,
            ContainerName = ctn.Name,
            FromStorageId = from?.Id,
            FromStorageCode = from?.Code ?? "",
            FromStorageName = from?.Name ?? "",
            ToStorageId = to?.Id,
            ToStorageCode = to?.Code ?? "",
            ToStorageName = to?.Name ?? "",
            StorageId = to?.Id ?? from?.Id,
            StorageCode = to?.Code ?? from?.Code ?? "",
            StorageName = to?.Name ?? from?.Name ?? "",
            RefType = "Manual",
            Operator = actor,
            OperatedAt = now,
            Reason = reason
        });
    }

    private Task<T> FindEditable<T>(Guid id, long? version)
        where T : class, IEntity<Guid>, ISoftDeletable, IVersioned, ILockable
        => _services.GetRequiredService<IEditableRepository<T>>().GetEditableAsync(id, version);

    private static void EnsureVersion(EntityBase entity, long? version)
    {
        if (version.HasValue && entity.Version != version.Value)
            throw new MiGu.DB.Abstractions.Exceptions.ConcurrencyConflictException(
                entity.GetType().Name, entity.Id, version);
    }

    private static bool IsUniqueConstraintViolation(DbUpdateException ex)
    {
        for (Exception? e = ex; e != null; e = e.InnerException)
        {
            var msg = e.Message;
            if (msg.Contains("UNIQUE constraint failed", StringComparison.OrdinalIgnoreCase)
                || msg.Contains("unique index", StringComparison.OrdinalIgnoreCase)
                || msg.Contains("duplicate key", StringComparison.OrdinalIgnoreCase))
                return true;
        }

        return false;
    }

    private static void EnsureUnlocked(EntityBase entity)
    {
        if (entity.IsLock)
            throw new MiGu.DB.Abstractions.Exceptions.EntityLockedException(entity.GetType().Name, entity.Id);
    }

    private static void StampCreate(EntityBase entity, string actor)
    {
        entity.CreatedBy = actor;
        entity.UpdatedBy = actor;
    }

    private void ApplyCommon(EntityBase entity, CommonRequest req, string actor)
    {
        entity.IsLock = req.IsLock;
        entity.Remark = req.Remark.TrimOr("");
        entity.Extend = NormalizeExtend(req.Extend);
        entity.UpdatedBy = actor;
    }

    private static async Task EnsureUnique<T>(IQueryable<T> query, System.Linq.Expressions.Expression<Func<T, bool>> predicate, string message)
    {
        if (await query.AnyAsync(predicate)) throw new InvalidOperationException(message);
    }

    private string NormalizeExtend(string? extend)
    {
        if (string.IsNullOrWhiteSpace(extend)) return "{}";
        if (extend.Length > 4000) throw new InvalidOperationException("扩展字段过长");
        using var doc = JsonDocument.Parse(extend);
        if (doc.RootElement.ValueKind != JsonValueKind.Object) throw new InvalidOperationException("扩展字段必须是 JSON object");
        return extend;
    }

    private static IQueryable<T> FilterByKeyword<T>(IQueryable<T> query, string? q)
    {
        if (string.IsNullOrWhiteSpace(q)) return query;
        var s = q.Trim();
        return typeof(T).Name switch
        {
            nameof(Warehouse) => (IQueryable<T>)((IQueryable<Warehouse>)query).Where(x => x.Code.Contains(s) || x.Name.Contains(s)),
            nameof(WarehouseArea) => (IQueryable<T>)((IQueryable<WarehouseArea>)query).Where(x => x.Code.Contains(s) || x.Name.Contains(s)),
            nameof(Storage) => (IQueryable<T>)((IQueryable<Storage>)query).Where(x => x.Code.Contains(s) || x.Name.Contains(s) || x.SiteId.Contains(s) || x.Barcode.Contains(s)),
            nameof(Container) => (IQueryable<T>)((IQueryable<Container>)query).Where(x => x.Code.Contains(s) || x.Name.Contains(s) || x.Barcode.Contains(s)),
            nameof(MaterialType) => (IQueryable<T>)((IQueryable<MaterialType>)query).Where(x => x.Code.Contains(s) || x.Name.Contains(s)),
            nameof(Material) => (IQueryable<T>)((IQueryable<Material>)query).Where(x => x.Code.Contains(s) || x.Name.Contains(s) || x.Spec.Contains(s) || x.Barcode.Contains(s)),
            nameof(ContainerLocation) => (IQueryable<T>)((IQueryable<ContainerLocation>)query).Where(x => x.LocationCode.Contains(s) || x.LocationName.Contains(s)),
            nameof(ContainerMaterial) => query,
            _ => query
        };
    }

    private static ContainerLocationSnapshot Snapshot(ContainerLocation x) => new(
        x.Id, x.ContainerId, x.LocationType.ToString(), x.LocationId, x.LocationCode, x.LocationName,
        x.Status.ToString(), x.EnteredAt, x.Version);

    private static ContainerMaterialSnapshot Snapshot(ContainerMaterial x) => new(
        x.Id, x.ContainerId, x.MaterialId, x.Quantity, x.BatchNo, x.SerialNo, x.Status.ToString(),
        x.BoundAt, x.LoadedAt, x.UnloadedAt, x.Version);
}

public static class WmsStringExtensions
{
    public static string TrimOr(this string? value, string fallback) =>
        string.IsNullOrWhiteSpace(value) ? fallback : value.Trim();

    public static string ActorName(this ClaimsPrincipal user) =>
        user.FindFirst(System.IdentityModel.Tokens.Jwt.JwtRegisteredClaimNames.UniqueName)?.Value
        ?? user.Identity?.Name
        ?? "system";
}
