using System.Text.Json;
using FoodLabeling.Application.Helpers;
using FoodLabeling.Application.Contracts.Dtos.Common;
using FoodLabeling.Application.Contracts.Dtos.Label;
using FoodLabeling.Application.Contracts.Dtos.LabelTemplate;
using FoodLabeling.Application.Contracts.IServices;
using FoodLabeling.Application.Services.DbModels;
using FoodLabeling.Domain.Entities;
using SqlSugar;
using Volo.Abp;
using Volo.Abp.Application.Services;
using Volo.Abp.Guids;
using Volo.Abp.Uow;
using Yi.Framework.SqlSugarCore.Abstractions;

namespace FoodLabeling.Application.Services;

/// <summary>
/// 标签管理（一个产品展示多个标签）
/// </summary>
public class LabelAppService : ApplicationService, ILabelAppService
{
    private readonly ISqlSugarDbContext _dbContext;
    private readonly IGuidGenerator _guidGenerator;

    public LabelAppService(ISqlSugarDbContext dbContext, IGuidGenerator guidGenerator)
    {
        _dbContext = dbContext;
        _guidGenerator = guidGenerator;
    }

    public async Task<PagedResultWithPageDto<LabelGetListOutputDto>> GetListAsync(LabelGetListInputVo input)
    {
        RefAsync<int> total = 0;

        var productId = input.ProductId?.Trim();
        var partnerId = input.PartnerId?.Trim();
        var groupId = input.GroupId?.Trim();
        var locationId = input.LocationId?.Trim();
        var keyword = input.Keyword?.Trim();
        var labelCategoryId = input.LabelCategoryId?.Trim();
        var labelTypeId = input.LabelTypeId?.Trim();
        var templateCode = input.TemplateCode?.Trim();

        // 目标：列表每行是“标签”，同一个标签下的 products 以 “,” 拼接展示
        // 因此需要按 label 维度分页（避免 label-product join 导致重复行与分页错乱）。

        var labelIdsQuery = _dbContext.SqlSugarClient.Queryable<FlLabelDbEntity>()
            .Where(l => !l.IsDeleted)
            .WhereIF(!string.IsNullOrWhiteSpace(labelCategoryId), l => l.LabelCategoryId == labelCategoryId)
            .WhereIF(!string.IsNullOrWhiteSpace(labelTypeId), l => l.LabelTypeId == labelTypeId)
            .WhereIF(input.State != null, l => l.State == input.State);

        if (!string.IsNullOrWhiteSpace(templateCode))
        {
            labelIdsQuery = labelIdsQuery
                .InnerJoin<FlLabelTemplateDbEntity>((l, tpl) => l.TemplateId == tpl.Id)
                .Where((l, tpl) => !tpl.IsDeleted && tpl.TemplateCode == templateCode)
                .Select((l, tpl) => l);
        }

        var regionSchema = await LabelRegionSchemaHelper.GetStatusAsync(_dbContext.SqlSugarClient);
        var scopedLocationIds = await LocationScopeBindingHelper.ResolveScopedLocationIdsAsync(
            _dbContext.SqlSugarClient, groupId, locationId);
        var filterGroupId = groupId?.Trim();
        if (!string.IsNullOrWhiteSpace(filterGroupId))
        {
            labelIdsQuery = scopedLocationIds is { Count: 0 }
                ? labelIdsQuery.Where(_ => false)
                : LabelRegionScopeHelper.ApplyLabelRegionListFilter(
                    labelIdsQuery, filterGroupId, scopedLocationIds, regionSchema);
        }
        else if (scopedLocationIds is not null)
        {
            labelIdsQuery = scopedLocationIds.Count == 0
                ? labelIdsQuery.Where(_ => false)
                : labelIdsQuery.Where(l => scopedLocationIds.Contains(l.LocationId));
        }
        else if (!string.IsNullOrWhiteSpace(groupId))
        {
            labelIdsQuery = LabelRegionScopeHelper.ApplyGroupIdListFilter(labelIdsQuery, groupId);
        }
        else if (!string.IsNullOrWhiteSpace(partnerId))
        {
            var partnerLocationIds = await LocationScopeBindingHelper.ResolveFilteredLocationIdsForListAsync(
                _dbContext.SqlSugarClient, partnerId, null, null);
            if (partnerLocationIds is not null)
            {
                labelIdsQuery = partnerLocationIds.Count == 0
                    ? labelIdsQuery.Where(_ => false)
                    : labelIdsQuery.Where(l => partnerLocationIds.Contains(l.LocationId));
            }
        }

        // 按产品筛选：存在 label-product 关联即可
        if (!string.IsNullOrWhiteSpace(productId))
        {
            labelIdsQuery = labelIdsQuery
                .InnerJoin<FlLabelProductDbEntity>((l, lp) => lp.LabelId == l.Id)
                .Where((l, lp) => lp.ProductId == productId)
                .Select((l, lp) => l);
        }

        // 关键字：匹配 labelName/categoryName/typeName/templateName/productName
        if (!string.IsNullOrWhiteSpace(keyword))
        {
            labelIdsQuery = labelIdsQuery
                .LeftJoin<FlLabelCategoryDbEntity>((l, c) => l.LabelCategoryId == c.Id)
                .LeftJoin<FlLabelTypeDbEntity>((l, c, t) => l.LabelTypeId == t.Id)
                .LeftJoin<FlLabelTemplateDbEntity>((l, c, t, tpl) => l.TemplateId == tpl.Id)
                .LeftJoin<FlLabelProductDbEntity>((l, c, t, tpl, lp) => lp.LabelId == l.Id)
                .LeftJoin<FlProductDbEntity>((l, c, t, tpl, lp, p) => lp.ProductId == p.Id)
                .Where((l, c, t, tpl, lp, p) =>
                    l.LabelName.Contains(keyword!) ||
                    (c.CategoryName != null && c.CategoryName.Contains(keyword!)) ||
                    (t.TypeName != null && t.TypeName.Contains(keyword!)) ||
                    (tpl.TemplateName != null && tpl.TemplateName.Contains(keyword!)) ||
                    (p.ProductName != null && p.ProductName.Contains(keyword!)))
                .Select((l, c, t, tpl, lp, p) => l);
        }

        // 排序（优先外部 Sorting，否则按最后编辑倒序）
        if (!string.IsNullOrWhiteSpace(input.Sorting))
        {
            labelIdsQuery = labelIdsQuery.OrderBy(input.Sorting);
        }
        else
        {
            labelIdsQuery = labelIdsQuery.OrderByDescending(l => l.LastModificationTime ?? l.CreationTime);
        }

        var pageLabelIds = await labelIdsQuery
            .Select(l => l.Id)
            .Distinct()
            .ToPageListAsync(input.SkipCount, input.MaxResultCount, total);

        if (pageLabelIds.Count == 0)
        {
            return new PagedResultWithPageDto<LabelGetListOutputDto>
            {
                PageIndex = 1,
                PageSize = input.MaxResultCount,
                TotalCount = total,
                TotalPages = 0,
                Items = new List<LabelGetListOutputDto>()
            };
        }

        // 查询标签基础信息（分类/类型/模板）
        var labelRows = await _dbContext.SqlSugarClient
            .Queryable<FlLabelDbEntity, FlLabelCategoryDbEntity, FlLabelTypeDbEntity, FlLabelTemplateDbEntity>(
                (l, c, t, tpl) => l.LabelCategoryId == c.Id && l.LabelTypeId == t.Id && l.TemplateId == tpl.Id)
            .Where((l, c, t, tpl) => pageLabelIds.Contains(l.Id))
            .Where((l, c, t, tpl) => !l.IsDeleted && !c.IsDeleted && !t.IsDeleted && !tpl.IsDeleted)
            .Select((l, c, t, tpl) => new
            {
                l.Id,
                l.LabelCode,
                l.LabelName,
                l.LocationId,
                l.AppliedRegionType,
                LabelCategoryName = c.CategoryName,
                LabelTypeName = t.TypeName,
                TemplateName = tpl.TemplateName,
                TemplateCode = tpl.TemplateCode,
                l.State,
                LastEdited = l.LastModificationTime ?? l.CreationTime
            })
            .ToListAsync();

        // 按分页顺序输出
        var labelMap = labelRows.ToDictionary(x => x.Id, x => x);
        var orderedLabels = pageLabelIds.Where(id => labelMap.ContainsKey(id)).Select(id => labelMap[id]).ToList();
        var appliedRegionTypeMap = await LabelRegionSchemaHelper.GetAppliedRegionTypesForLabelsAsync(
            _dbContext.SqlSugarClient, pageLabelIds);
        var regionScopeMap = new Dictionary<string, LabelRegionScopeHelper.LabelRegionScopeDisplay>(StringComparer.Ordinal);
        foreach (var lid in pageLabelIds)
        {
            if (!labelMap.TryGetValue(lid, out var row))
            {
                continue;
            }

            appliedRegionTypeMap.TryGetValue(lid, out var appliedType);
            regionScopeMap[lid] = await LabelRegionScopeHelper.BuildScopeDisplayAsync(
                _dbContext.SqlSugarClient,
                lid,
                appliedType,
                row.LocationId);
        }

        // 查询 products 并拼接
        var productRows = await _dbContext.SqlSugarClient
            .Queryable<FlLabelProductDbEntity>()
            .InnerJoin<FlLabelDbEntity>((lp, l) => lp.LabelId == l.Id)
            .InnerJoin<FlProductDbEntity>((lp, l, p) => lp.ProductId == p.Id)
            .LeftJoin<FlProductCategoryDbEntity>((lp, l, p, pc) => p.CategoryId == pc.Id)
            .Where((lp, l, p, pc) => pageLabelIds.Contains(lp.LabelId))
            .Where((lp, l, p, pc) => !l.IsDeleted && !p.IsDeleted)
            .Select((lp, l, p, pc) => new { lp.LabelId, p.ProductName, ProductCategoryName = pc.CategoryName })
            .ToListAsync();

        var productsMap = productRows
            .GroupBy(x => x.LabelId)
            .ToDictionary(
                g => g.Key,
                g => new
                {
                    Products = string.Join(",", g.Select(x => x.ProductName ?? string.Empty).Select(x => x.Trim()).Where(x => !string.IsNullOrWhiteSpace(x)).Distinct()),
                    ProductCategoryName = string.Join(",", g.Select(x => x.ProductCategoryName ?? string.Empty).Select(x => x.Trim()).Where(x => !string.IsNullOrWhiteSpace(x)).Distinct())
                });

        var locationIds = orderedLabels
            .Select(x => x.LocationId)
            .Where(x => !string.IsNullOrWhiteSpace(x))
            .Select(x => x!.Trim())
            .Distinct()
            .ToList();

        var locationMap = new Dictionary<string, LocationAggregateRoot>();
        if (locationIds.Count > 0)
        {
            var locGuids = locationIds.Where(x => Guid.TryParse(x, out _)).Select(Guid.Parse).ToList();
            if (locGuids.Count > 0)
            {
                var locs = await _dbContext.SqlSugarClient.Queryable<LocationAggregateRoot>()
                    .Where(x => !x.IsDeleted)
                    .Where(x => locGuids.Contains(x.Id))
                    .ToListAsync();
                locationMap = locs.ToDictionary(x => x.Id.ToString(), x => x);
            }
        }

        var regionScopeMap = await LabelRegionScopeHelper.BuildRegionScopeMapAsync(
            _dbContext.SqlSugarClient,
            orderedLabels.Select(x => (x.Id, x.AppliedRegionType ?? LabelRegionScopeHelper.AppliedTypeSpecified)).ToList());

        var items = orderedLabels.Select(x =>
        {
            var locationName = string.Empty;
            if (!string.IsNullOrWhiteSpace(x.LocationId) && locationMap.TryGetValue(x.LocationId!, out var loc))
            {
                locationName = loc.LocationName ?? loc.LocationCode;
            }
            var products = productsMap.TryGetValue(x.Id, out var prod) ? prod.Products : string.Empty;
            var productCategoryNameValue = productsMap.TryGetValue(x.Id, out var prod2) ? prod2.ProductCategoryName : string.Empty;
            regionScopeMap.TryGetValue(x.Id, out var regionScope);
            appliedRegionTypeMap.TryGetValue(x.Id, out var appliedRegionType);
            return new LabelGetListOutputDto
            {
                Id = x.LabelCode ?? string.Empty,
                LabelName = x.LabelName ?? string.Empty,
                LocationName = string.IsNullOrWhiteSpace(locationName) ? "无" : locationName,
                Region = regionScope?.Region ?? "无",
                RegionIds = regionScope?.RegionIds ?? new List<string>(),
                GroupIds = regionScope?.GroupIds ?? new List<string>(),
                AppliedRegionType = string.IsNullOrWhiteSpace(appliedRegionType)
                    ? LabelRegionScopeHelper.AppliedRegionSpecified
                    : appliedRegionType.Trim(),
                LabelCategoryName = x.LabelCategoryName ?? string.Empty,
                ProductCategoryName = string.IsNullOrWhiteSpace(productCategoryNameValue) ? "无" : productCategoryNameValue,
                Products = products,
                TemplateName = x.TemplateName ?? string.Empty,
                TemplateCode = x.TemplateCode ?? string.Empty,
                LabelTypeName = x.LabelTypeName ?? string.Empty,
                State = x.State,
                LastEdited = x.LastEdited,
                HasError = false
            };
        }).ToList();

        var pageSize = input.MaxResultCount <= 0 ? items.Count : input.MaxResultCount;
        var pageIndex = pageSize <= 0 ? 1 : PagedQueryConvention.PageIndexFromSkipCount(input.SkipCount);
        var totalPages = pageSize <= 0 ? 0 : (int)Math.Ceiling(total / (double)pageSize);

        return new PagedResultWithPageDto<LabelGetListOutputDto>
        {
            PageIndex = pageIndex,
            PageSize = pageSize,
            TotalCount = total,
            TotalPages = totalPages,
            Items = items
        };
    }

    public async Task<LabelGetOutputDto> GetAsync(string id)
    {
        var labelCode = id?.Trim();
        if (string.IsNullOrWhiteSpace(labelCode))
        {
            throw new UserFriendlyException("标签Code不能为空");
        }

        var label = await _dbContext.SqlSugarClient.Queryable<FlLabelDbEntity>()
            .FirstAsync(x => !x.IsDeleted && x.LabelCode == labelCode);
        if (label is null)
        {
            throw new UserFriendlyException("标签不存在");
        }

        var template = await LabelTemplateQueryHelper.ProjectListColumns(
                _dbContext.SqlSugarClient.Queryable<FlLabelTemplateDbEntity>())
            .FirstAsync(x => x.Id == label.TemplateId);
        var category = await _dbContext.SqlSugarClient.Queryable<FlLabelCategoryDbEntity>()
            .FirstAsync(x => x.Id == label.LabelCategoryId);
        var type = await _dbContext.SqlSugarClient.Queryable<FlLabelTypeDbEntity>()
            .FirstAsync(x => x.Id == label.LabelTypeId);

        LocationAggregateRoot? location = null;
        if (!string.IsNullOrWhiteSpace(label.LocationId) && Guid.TryParse(label.LocationId, out var locId))
        {
            location = await _dbContext.SqlSugarClient.Queryable<LocationAggregateRoot>()
                .FirstAsync(x => !x.IsDeleted && x.Id == locId);
        }

        var productIds = await _dbContext.SqlSugarClient.Queryable<FlLabelProductDbEntity>()
            .Where(x => x.LabelId == label.Id)
            .Select(x => x.ProductId)
            .ToListAsync();

        object? labelInfo = null;
        if (!string.IsNullOrWhiteSpace(label.LabelInfoJson))
        {
            labelInfo = JsonSerializer.Deserialize<object>(label.LabelInfoJson);
        }

        var locationId = label.LocationId ?? string.Empty;
        var locationIdList = string.IsNullOrWhiteSpace(locationId)
            ? new List<string>()
            : new List<string> { locationId.Trim() };
        var partnerIds = await LocationScopeBindingHelper.ResolvePartnerIdsFromLocationIdsAsync(
            _dbContext.SqlSugarClient, locationIdList);
        var appliedRegionType = await LabelRegionSchemaHelper.GetAppliedRegionTypeForLabelAsync(
            _dbContext.SqlSugarClient, label.Id);
        var regionScope = await LabelRegionScopeHelper.BuildScopeDisplayAsync(
            _dbContext.SqlSugarClient,
            label.Id,
            appliedRegionType,
            label.LocationId);

        return new LabelGetOutputDto
        {
            Id = label.LabelCode ?? string.Empty,
            LabelName = label.LabelName,
            LocationId = locationId,
            LocationName = location?.LocationName ?? location?.LocationCode ?? "无",
            AppliedRegionType = scope.AppliedRegionType,
            Region = scope.Region,
            PartnerId = partnerIds.Count > 0 ? partnerIds[0] : null,
            PartnerIds = partnerIds,
            AppliedRegionType = appliedRegionType,
            Region = regionScope.Region,
            RegionIds = regionScope.RegionIds,
            GroupIds = regionScope.GroupIds,
            LabelCategoryId = label.LabelCategoryId ?? string.Empty,
            LabelCategoryName = category?.CategoryName ?? "无",
            LabelTypeId = label.LabelTypeId ?? string.Empty,
            LabelTypeName = type?.TypeName ?? "无",
            TemplateCode = template?.TemplateCode ?? string.Empty,
            TemplateName = template?.TemplateName ?? string.Empty,
            State = label.State,
            LabelInfoJson = labelInfo,
            ProductIds = productIds
        };
    }

    [UnitOfWork]
    public async Task<LabelGetOutputDto> CreateAsync(LabelCreateInputVo input)
    {
        var labelCode = input.LabelCode?.Trim();
        if (string.IsNullOrWhiteSpace(labelCode))
        {
            labelCode = $"LBL_{_guidGenerator.Create():N}";
        }

        var labelName = input.LabelName?.Trim();
        if (string.IsNullOrWhiteSpace(labelName))
        {
            throw new UserFriendlyException("标签名称不能为空");
        }

        if (input.ProductIds is null || input.ProductIds.Count == 0)
        {
            throw new UserFriendlyException("标签至少需要绑定一个产品");
        }

        if (string.IsNullOrWhiteSpace(input.TemplateCode))
        {
            throw new UserFriendlyException("模板编码不能为空");
        }
        if (string.IsNullOrWhiteSpace(input.LabelCategoryId))
        {
            throw new UserFriendlyException("标签分类Id不能为空");
        }
        if (string.IsNullOrWhiteSpace(input.LabelTypeId))
        {
            throw new UserFriendlyException("标签类型Id不能为空");
        }

        var scope = await LabelRegionScopeHelper.ResolveScopeForSaveAsync(
            _dbContext.SqlSugarClient,
            input.AppliedRegionType,
            input.RegionIds,
            input.GroupIds,
            input.LocationId,
            input.LocationIds);

        var template = await LabelTemplateQueryHelper.ProjectListColumns(
                _dbContext.SqlSugarClient.Queryable<FlLabelTemplateDbEntity>())
            .FirstAsync(x => !x.IsDeleted && x.TemplateCode == input.TemplateCode.Trim());
        if (template is null)
        {
            throw new UserFriendlyException("模板不存在");
        }

        // 唯一性校验（LabelCode）
        var exists = await _dbContext.SqlSugarClient.Queryable<FlLabelDbEntity>()
            .AnyAsync(x => !x.IsDeleted && x.LabelCode == labelCode);
        if (exists)
        {
            throw new UserFriendlyException("标签编码已存在");
        }

        // 插入 fl_label
        var now = DateTime.Now;
        var labelId = _guidGenerator.Create().ToString();
        var labelEntity = new FlLabelDbEntity
        {
            Id = labelId,
            IsDeleted = false,
            CreationTime = now,
            CreatorId = CurrentUser?.Id?.ToString(),
            LastModifierId = CurrentUser?.Id?.ToString(),
            LastModificationTime = now,
            ConcurrencyStamp = string.Empty,
            LabelCode = labelCode,
            LabelName = labelName,
            TemplateId = template.Id,
            LocationId = scope.LocationId,
            LabelCategoryId = input.LabelCategoryId?.Trim(),
            LabelTypeId = input.LabelTypeId?.Trim(),
            State = input.State,
            LabelType = null,
            LabelInfoJson = input.LabelInfoJson == null ? null : JsonSerializer.Serialize(input.LabelInfoJson)
        };
        await _dbContext.SqlSugarClient.Insertable(labelEntity).ExecuteCommandAsync();
        await LabelRegionSchemaHelper.SetAppliedRegionTypeAsync(
            _dbContext.SqlSugarClient, labelId, scope.AppliedRegionType);
        await LabelRegionScopeHelper.SaveLabelRegionsAsync(
            _dbContext.SqlSugarClient,
            _guidGenerator,
            labelId,
            scope.AppliedRegionType,
            scope.RegionIds,
            CurrentUser?.Id?.ToString(),
            now);

        // 插入 fl_label_product
        var productIds = input.ProductIds.Where(x => !string.IsNullOrWhiteSpace(x)).Select(x => x.Trim()).Distinct().ToList();
        var rows = productIds.Select(pid => new FlLabelProductDbEntity
        {
            Id = _guidGenerator.Create().ToString(),
            LabelId = labelId,
            ProductId = pid
        }).ToList();
        await _dbContext.SqlSugarClient.Insertable(rows).ExecuteCommandAsync();

        await LabelRegionScopeHelper.SaveRegionIdsAsync(
            _dbContext.SqlSugarClient,
            labelId,
            resolvedScope.AppliedRegionType,
            resolvedScope.RegionIds,
            CurrentUser?.Id?.ToString(),
            now,
            () => _guidGenerator.Create().ToString());

        await LabelCategoryAppService.TouchLabelCategoryLastEditedAsync(
            _dbContext, labelEntity.LabelCategoryId, CurrentUser?.Id?.ToString());

        return await GetAsync(labelCode);
    }

    [UnitOfWork]
    public async Task<LabelGetOutputDto> UpdateAsync(string id, LabelUpdateInputVo input)
    {
        var labelCode = id?.Trim();
        if (string.IsNullOrWhiteSpace(labelCode))
        {
            throw new UserFriendlyException("标签Code不能为空");
        }

        var label = await _dbContext.SqlSugarClient.Queryable<FlLabelDbEntity>()
            .FirstAsync(x => !x.IsDeleted && x.LabelCode == labelCode);
        if (label is null)
        {
            throw new UserFriendlyException("标签不存在");
        }

        if (input.ProductIds is null || input.ProductIds.Count == 0)
        {
            throw new UserFriendlyException("标签至少需要绑定一个产品");
        }

        if (string.IsNullOrWhiteSpace(input.TemplateCode))
        {
            throw new UserFriendlyException("模板编码不能为空");
        }
        if (string.IsNullOrWhiteSpace(input.LabelCategoryId))
        {
            throw new UserFriendlyException("标签分类Id不能为空");
        }
        if (string.IsNullOrWhiteSpace(input.LabelTypeId))
        {
            throw new UserFriendlyException("标签类型Id不能为空");
        }

        var scope = await LabelRegionScopeHelper.ResolveScopeForSaveAsync(
            _dbContext.SqlSugarClient,
            input.AppliedRegionType,
            input.RegionIds,
            input.GroupIds,
            input.LocationId,
            input.LocationIds);

        var template = await LabelTemplateQueryHelper.ProjectListColumns(
                _dbContext.SqlSugarClient.Queryable<FlLabelTemplateDbEntity>())
            .FirstAsync(x => !x.IsDeleted && x.TemplateCode == input.TemplateCode.Trim());
        if (template is null)
        {
            throw new UserFriendlyException("模板不存在");
        }

        var oldCategoryId = label.LabelCategoryId;
        var now = DateTime.Now;
        label.LabelName = input.LabelName?.Trim() ?? label.LabelName;
        label.TemplateId = template.Id;
        label.LocationId = scope.LocationId;
        label.LabelCategoryId = input.LabelCategoryId?.Trim();
        label.LabelTypeId = input.LabelTypeId?.Trim();
        label.State = input.State;
        label.LastModifierId = CurrentUser?.Id?.ToString();
        label.LastModificationTime = now;
        label.LabelInfoJson = input.LabelInfoJson == null ? null : JsonSerializer.Serialize(input.LabelInfoJson);

        await _dbContext.SqlSugarClient.Updateable(label).ExecuteCommandAsync();
        await LabelRegionSchemaHelper.SetAppliedRegionTypeAsync(
            _dbContext.SqlSugarClient, label.Id, scope.AppliedRegionType);
        await LabelRegionScopeHelper.SaveLabelRegionsAsync(
            _dbContext.SqlSugarClient,
            _guidGenerator,
            label.Id,
            scope.AppliedRegionType,
            scope.RegionIds,
            CurrentUser?.Id?.ToString(),
            now);

        // 重建关联
        await _dbContext.SqlSugarClient.Deleteable<FlLabelProductDbEntity>()
            .Where(x => x.LabelId == label.Id)
            .ExecuteCommandAsync();

        var productIds = input.ProductIds.Where(x => !string.IsNullOrWhiteSpace(x)).Select(x => x.Trim()).Distinct().ToList();
        var rows = productIds.Select(pid => new FlLabelProductDbEntity
        {
            Id = _guidGenerator.Create().ToString(),
            LabelId = label.Id,
            ProductId = pid
        }).ToList();
        await _dbContext.SqlSugarClient.Insertable(rows).ExecuteCommandAsync();

        await LabelRegionScopeHelper.SaveRegionIdsAsync(
            _dbContext.SqlSugarClient,
            label.Id,
            resolvedScope.AppliedRegionType,
            resolvedScope.RegionIds,
            CurrentUser?.Id?.ToString(),
            now,
            () => _guidGenerator.Create().ToString());

        var newCategoryId = label.LabelCategoryId;
        await LabelCategoryAppService.TouchLabelCategoryLastEditedAsync(
            _dbContext, newCategoryId, CurrentUser?.Id?.ToString());
        if (!string.Equals(oldCategoryId, newCategoryId, StringComparison.Ordinal))
        {
            await LabelCategoryAppService.TouchLabelCategoryLastEditedAsync(
                _dbContext, oldCategoryId, CurrentUser?.Id?.ToString());
        }

        return await GetAsync(labelCode);
    }

    [UnitOfWork]
    public async Task DeleteAsync(string id)
    {
        var labelCode = id?.Trim();
        if (string.IsNullOrWhiteSpace(labelCode))
        {
            return;
        }

        var label = await _dbContext.SqlSugarClient.Queryable<FlLabelDbEntity>()
            .FirstAsync(x => !x.IsDeleted && x.LabelCode == labelCode);
        if (label is null)
        {
            return;
        }

        label.IsDeleted = true;
        label.LastModifierId = CurrentUser?.Id?.ToString();
        label.LastModificationTime = DateTime.Now;
        await _dbContext.SqlSugarClient.Updateable(label).ExecuteCommandAsync();

        var regionSchema = await LabelRegionSchemaHelper.GetStatusAsync(_dbContext.SqlSugarClient);
        if (regionSchema.HasLabelRegionTable)
        {
            await _dbContext.SqlSugarClient.Deleteable<FlLabelRegionDbEntity>()
                .Where(x => x.LabelId == label.Id)
                .ExecuteCommandAsync();
        }

        await _dbContext.SqlSugarClient.Deleteable<FlLabelProductDbEntity>()
            .Where(x => x.LabelId == label.Id)
            .ExecuteCommandAsync();

        await _dbContext.SqlSugarClient.Deleteable<FlLabelRegionDbEntity>()
            .Where(x => x.LabelId == label.Id)
            .ExecuteCommandAsync();

        await LabelCategoryAppService.TouchLabelCategoryLastEditedAsync(
            _dbContext, label.LabelCategoryId, CurrentUser?.Id?.ToString());
    }

    /// <summary>
    /// 标签预览：不落库，只把 template elements 的 AUTO_DB/PRINT_INPUT 渲染进 config
    /// </summary>
    [UnitOfWork]
    public async Task<LabelTemplatePreviewDto> PreviewAsync(LabelPreviewResolveInputVo input)
    {
        var labelCode = input?.LabelCode?.Trim();
        if (string.IsNullOrWhiteSpace(labelCode))
        {
            throw new UserFriendlyException("labelCode不能为空");
        }

        var baseTime = input?.BaseTime ?? DateTime.Now;

        var label = await _dbContext.SqlSugarClient.Queryable<FlLabelDbEntity>()
            .FirstAsync(x => !x.IsDeleted && x.LabelCode == labelCode);
        if (label is null)
        {
            throw new UserFriendlyException("标签不存在");
        }

        // 选择预览产品
        var productId = input?.ProductId?.Trim();
        if (string.IsNullOrWhiteSpace(productId))
        {
            productId = await _dbContext.SqlSugarClient.Queryable<FlLabelProductDbEntity>()
                .Where(x => x.LabelId == label.Id)
                .Select(x => x.ProductId)
                .FirstAsync();
        }

        if (string.IsNullOrWhiteSpace(productId))
        {
            throw new UserFriendlyException("该标签未绑定产品，无法预览");
        }

        var product = await _dbContext.SqlSugarClient.Queryable<FlProductDbEntity>()
            .FirstAsync(x => !x.IsDeleted && x.Id == productId);
        if (product is null)
        {
            throw new UserFriendlyException("预览产品不存在");
        }

        // 取模板头 & elements
        var template = await LabelTemplateQueryHelper.ProjectListColumns(
                _dbContext.SqlSugarClient.Queryable<FlLabelTemplateDbEntity>())
            .FirstAsync(x => !x.IsDeleted && x.Id == label.TemplateId);
        if (template is null)
        {
            throw new UserFriendlyException("模板不存在");
        }

        var elements = await _dbContext.SqlSugarClient.Queryable<FlLabelTemplateElementDbEntity>()
            .Where(x => x.TemplateId == template.Id)
            // SqlSugar 不提供 ThenBy，这里用组合排序键保证 OrderNum + ZIndex 的稳定顺序
            .OrderBy(x => x.OrderNum * 1000000 + x.ZIndex)
            .ToListAsync();

        // 解析 labelInfo（可用于补充一些数值）
        Dictionary<string, object?> labelInfoDict = new();
        if (!string.IsNullOrWhiteSpace(label.LabelInfoJson))
        {
            try
            {
                labelInfoDict = JsonSerializer.Deserialize<Dictionary<string, object?>>(label.LabelInfoJson)
                                 ?? new Dictionary<string, object?>();
            }
            catch
            {
                // ignore
            }
        }

        var printInputJson = input?.PrintInputJson ?? new Dictionary<string, object?>();

        static int TryGetInt(object? v, int defaultValue)
        {
            if (v is null) return defaultValue;
            if (v is int i) return i;
            if (v is long l) return (int)l;
            if (v is decimal d) return (int)d;
            if (v is double db) return (int)db;
            if (v is JsonElement je)
            {
                if (je.ValueKind == JsonValueKind.Number && je.TryGetInt32(out var ii)) return ii;
                if (je.ValueKind == JsonValueKind.String && int.TryParse(je.GetString(), out var si)) return si;
            }
            if (v is string s && int.TryParse(s, out var st)) return st;
            return defaultValue;
        }

        static string? TryToString(object? v)
        {
            if (v is null) return null;
            if (v is string s) return s;
            if (v is JsonElement je)
            {
                if (je.ValueKind == JsonValueKind.String) return je.GetString();
                if (je.ValueKind == JsonValueKind.Number || je.ValueKind == JsonValueKind.True || je.ValueKind == JsonValueKind.False)
                {
                    return je.ToString();
                }
            }
            return v.ToString();
        }

        static void UpsertConfigValue(Dictionary<string, object?> cfg, string key, object? value)
        {
            if (cfg.ContainsKey(key))
            {
                cfg[key] = value;
                return;
            }
            cfg.Add(key, value);
        }

        Dictionary<string, object?> ParseConfig(string? configJson)
        {
            if (string.IsNullOrWhiteSpace(configJson))
            {
                return new Dictionary<string, object?>();
            }

            try
            {
                return JsonSerializer.Deserialize<Dictionary<string, object?>>(configJson)
                       ?? new Dictionary<string, object?>();
            }
            catch
            {
                return new Dictionary<string, object?>();
            }
        }

        var locationId = input?.LocationId?.Trim();
        var hasCompanyAutoElement = elements.Any(PartnerCompanyDisplayHelper.IsCompanyAutoElement);
        FlPartnerDbEntity? partnerForCompany = null;
        if (hasCompanyAutoElement)
        {
            if (string.IsNullOrWhiteSpace(locationId))
            {
                throw new UserFriendlyException("预览/打印需要 locationId 以填充 Company 信息");
            }

            partnerForCompany = await PartnerCompanyDisplayHelper.ResolvePartnerByLocationIdAsync(
                _dbContext.SqlSugarClient, locationId);
        }

        var resolvedElements = elements.Select(el =>
        {
            var cfg = ParseConfig(el.ConfigJson);

            // 1) 先做 AUTO_DB：按类型兜底填充
            if (string.Equals(el.ValueSourceType, "AUTO_DB", StringComparison.OrdinalIgnoreCase))
            {
                switch (el.ElementType)
                {
                    case "TEXT_PRODUCT":
                        UpsertConfigValue(cfg, "text", product.ProductName);
                        break;
                    case "TEXT_PRICE":
                        {
                            // price 可能在 labelInfo 或 printInput 里
                            object? priceObj = null;
                            if (labelInfoDict.TryGetValue("price", out var p)) priceObj = p;
                            if (priceObj is null && printInputJson.TryGetValue("price", out var pp)) priceObj = pp;
                            var priceStr = TryToString(priceObj);
                            if (!string.IsNullOrWhiteSpace(priceStr))
                            {
                                UpsertConfigValue(cfg, "text", priceStr);
                            }
                        }
                        break;
                    case "BARCODE":
                        if (!string.IsNullOrWhiteSpace(product.ProductCode))
                        {
                            UpsertConfigValue(cfg, "data", product.ProductCode);
                        }
                        break;
                    case "QRCODE":
                        if (!string.IsNullOrWhiteSpace(product.ProductCode))
                        {
                            UpsertConfigValue(cfg, "data", product.ProductCode);
                        }
                        break;
                    case "DATE":
                    case "TIME":
                        // 日期/时间在 App/Web 端按 BaseTime 实时解析；勿把首屏时刻写入 config 以免预览/出纸过期。
                        break;
                    case "TEXT_STATIC":
                        if (PartnerCompanyDisplayHelper.IsCompanyAutoElement(el.ValueSourceType, el.TypeAdd, el.ElementType)
                            && partnerForCompany is not null)
                        {
                            var includes = PartnerCompanyDisplayHelper.ParseIncludeFields(cfg);
                            var companyText = PartnerCompanyDisplayHelper.FormatDisplayText(partnerForCompany, includes);
                            UpsertConfigValue(cfg, "text", companyText);
                        }

                        break;
                }
            }

            // 2) 再做 PRINT_INPUT：按 InputKey 覆盖（如果前端传了）
            if (string.Equals(el.ValueSourceType, "PRINT_INPUT", StringComparison.OrdinalIgnoreCase)
                && !string.IsNullOrWhiteSpace(el.InputKey)
                && printInputJson.TryGetValue(el.InputKey!, out var inputValue))
            {
                var s = TryToString(inputValue);
                if (!string.IsNullOrWhiteSpace(s))
                {
                    switch (el.ElementType)
                    {
                        case "TEXT_STATIC":
                        case "TEXT_PRODUCT":
                        case "TEXT_PRICE":
                            UpsertConfigValue(cfg, "text", s);
                            cfg.Remove("inputType");
                            break;
                        case "BARCODE":
                        case "QRCODE":
                            UpsertConfigValue(cfg, "data", s);
                            break;
                        case "DATE":
                        case "TIME":
                            UpsertConfigValue(cfg, "format", s);
                            cfg.Remove("inputType");
                            break;
                    }
                }
            }

            // 3) FIXED：不做任何覆盖，保持模板/标签设计时写入的固定值（由前端/设计器落库）

            return new LabelTemplateElementDto
            {
                Id = el.ElementKey,
                ElementType = el.ElementType,
                TypeAdd = el.TypeAdd,
                ElementName = el.ElementName,
                PosX = el.PosX,
                PosY = el.PosY,
                Width = el.Width,
                Height = el.Height,
                Rotation = string.IsNullOrWhiteSpace(el.Rotation) ? "horizontal" : el.Rotation,
                BorderType = string.IsNullOrWhiteSpace(el.BorderType) ? "none" : el.BorderType,
                ZIndex = el.ZIndex,
                OrderNum = el.OrderNum,
                ValueSourceType = el.ValueSourceType,
                BindingExpr = el.BindingExpr,
                AutoQueryKey = el.AutoQueryKey,
                InputKey = el.InputKey,
                IsRequiredInput = el.IsRequiredInput,
                ConfigJson = cfg
            };
        }).ToList();

        return new LabelTemplatePreviewDto
        {
            Id = template.TemplateCode,
            Name = template.TemplateName,
            LabelType = template.LabelType ?? string.Empty,
            Unit = template.Unit,
            Width = template.Width,
            Height = template.Height,
            AppliedLocation = template.AppliedLocationType,
            ShowRuler = template.ShowRuler,
            ShowGrid = template.ShowGrid,
            Border = NormalizeTemplateBorderType(template.BorderType),
            PrintOrientation = NormalizePrintOrientationType(template.PrintOrientationType),
            Elements = resolvedElements
        };
    }

    private static string NormalizeTemplateBorderType(string? raw)
    {
        var v = (raw ?? string.Empty).Trim().ToLowerInvariant();
        return v is "line" or "dotted" ? v : "none";
    }

    private static string NormalizePrintOrientationType(string? raw)
    {
        var v = (raw ?? string.Empty).Trim().ToLowerInvariant();
        return v == "horizontal" ? "horizontal" : "vertical";
    }

}