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 = LabelQueryHelper.ProjectListColumns(
            _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 db = _dbContext.SqlSugarClient;
        var regionSchema = await LabelRegionSchemaHelper.GetStatusAsync(db);
        var scopedLocationIds = await LocationScopeBindingHelper.ResolveScopedLocationIdsAsync(
            db, groupId, locationId);
        var filterGroupId = groupId?.Trim();
        if (!string.IsNullOrWhiteSpace(filterGroupId))
        {
            labelIdsQuery = scopedLocationIds is { Count: 0 }
                ? labelIdsQuery.Where(_ => false)
                : LabelRegionScopeHelper.ApplyLabelRegionListFilter(
                    db, labelIdsQuery, filterGroupId, scopedLocationIds, regionSchema);
        }
        else if (scopedLocationIds is not null)
        {
            labelIdsQuery = scopedLocationIds.Count == 0
                ? labelIdsQuery.Where(_ => false)
                : LabelRegionScopeHelper.ApplyLabelLocationListFilter(
                    db, labelIdsQuery, scopedLocationIds, regionSchema);
        }
        else if (!string.IsNullOrWhiteSpace(groupId))
        {
            labelIdsQuery = LabelRegionScopeHelper.ApplyGroupIdListFilter(
                db, labelIdsQuery, groupId, regionSchema);
        }
        else if (!string.IsNullOrWhiteSpace(partnerId))
        {
            var partnerLocationIds = await LocationScopeBindingHelper.ResolveFilteredLocationIdsForListAsync(
                db, partnerId, null, null);
            if (partnerLocationIds is not null)
            {
                labelIdsQuery = partnerLocationIds.Count == 0
                    ? labelIdsQuery.Where(_ => false)
                    : LabelRegionScopeHelper.ApplyLabelLocationListFilter(
                        db, labelIdsQuery, partnerLocationIds, regionSchema);
            }
        }

        // 按产品筛选：存在 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>()
            };
        }

        // 查询标签基础信息（分类/类型/模板）；类型 LeftJoin，允许未绑定 labelTypeId 的标签出现在列表
        var labelRows = await _dbContext.SqlSugarClient
            .Queryable<FlLabelDbEntity>()
            .InnerJoin<FlLabelCategoryDbEntity>((l, c) => l.LabelCategoryId == c.Id)
            .LeftJoin<FlLabelTypeDbEntity>((l, c, t) => l.LabelTypeId == t.Id)
            .InnerJoin<FlLabelTemplateDbEntity>((l, c, t, tpl) => l.TemplateId == tpl.Id)
            .Where((l, c, t, tpl) => pageLabelIds.Contains(l.Id))
            .Where((l, c, t, tpl) => !l.IsDeleted && !c.IsDeleted && !tpl.IsDeleted)
            .Where((l, c, t, tpl) => t.Id == null || !t.IsDeleted)
            .Select((l, c, t, tpl) => new
            {
                l.Id,
                l.LabelCode,
                l.LabelName,
                l.LocationId,
                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(
            db, pageLabelIds);
        var locationIdsMap = await LabelRegionScopeHelper.BuildLocationIdsMapAsync(
            db,
            pageLabelIds,
            orderedLabels.ToDictionary(x => x.Id, x => x.LocationId, StringComparer.Ordinal));
        var regionScopeMap = new Dictionary<string, LabelRegionScopeHelper.LabelRegionScopeDisplay>(StringComparer.Ordinal);
        var locationScopeMap = new Dictionary<string, LabelRegionScopeHelper.LabelLocationScopeDisplay>(StringComparer.Ordinal);
        foreach (var lid in pageLabelIds)
        {
            if (!labelMap.TryGetValue(lid, out var row))
            {
                continue;
            }

            appliedRegionTypeMap.TryGetValue(lid, out var appliedType);
            var applied = string.IsNullOrWhiteSpace(appliedType)
                ? LabelRegionScopeHelper.AppliedRegionSpecified
                : appliedType.Trim();
            locationIdsMap.TryGetValue(lid, out var locIds);
            locIds ??= new List<string>();
            regionScopeMap[lid] = await LabelRegionScopeHelper.BuildScopeDisplayAsync(
                db, lid, applied, locIds);
            locationScopeMap[lid] = await LabelRegionScopeHelper.BuildLocationDisplayAsync(
                db, applied, locIds);
        }

        // 查询 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 items = orderedLabels.Select(x =>
        {
            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);
            locationScopeMap.TryGetValue(x.Id, out var locationScope);
            locationIdsMap.TryGetValue(x.Id, out var locIds);
            appliedRegionTypeMap.TryGetValue(x.Id, out var appliedRegionType);
            return new LabelGetListOutputDto
            {
                Id = x.LabelCode ?? string.Empty,
                LabelName = x.LabelName ?? string.Empty,
                LocationName = string.IsNullOrWhiteSpace(locationScope?.Location) ? "无" : locationScope!.Location,
                LocationIds = locIds ?? new List<string>(),
                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 = string.IsNullOrWhiteSpace(x.LabelTypeName) ? "无" : x.LabelTypeName,
                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 db = _dbContext.SqlSugarClient;
        var label = await LabelQueryHelper.QueryProjected(db)
            .FirstAsync(x => !x.IsDeleted && x.LabelCode == labelCode);
        if (label is null)
        {
            throw new UserFriendlyException("标签不存在");
        }

        var template = await LabelTemplateQueryHelper.QueryProjected(db)
            .FirstAsync(x => x.Id == label.TemplateId);
        var category = await db.Queryable<FlLabelCategoryDbEntity>()
            .FirstAsync(x => x.Id == label.LabelCategoryId);
        FlLabelTypeDbEntity? type = null;
        if (!string.IsNullOrWhiteSpace(label.LabelTypeId))
        {
            type = await db.Queryable<FlLabelTypeDbEntity>()
                .FirstAsync(x => x.Id == label.LabelTypeId);
        }

        var productIds = await db.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 locationIdList = await LabelRegionScopeHelper.GetLocationIdsForLabelAsync(
            db, label.Id, label.LocationId);
        var partnerIds = await LocationScopeBindingHelper.ResolvePartnerIdsFromLocationIdsAsync(
            db, locationIdList);
        var appliedRegionType = await LabelRegionSchemaHelper.GetAppliedRegionTypeForLabelAsync(
            db, label.Id, label.AppliedRegionType);
        var regionScope = await LabelRegionScopeHelper.BuildScopeDisplayAsync(
            db, label.Id, appliedRegionType, locationIdList);
        var locationScope = await LabelRegionScopeHelper.BuildLocationDisplayAsync(
            db, appliedRegionType, locationIdList);

        return new LabelGetOutputDto
        {
            Id = label.LabelCode ?? string.Empty,
            LabelName = label.LabelName,
            LocationId = locationIdList.Count > 0 ? locationIdList[0] : string.Empty,
            LocationIds = locationIdList,
            Location = locationScope.Location,
            LocationName = locationScope.Location,
            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不能为空");
        }

        await EnsureLabelTypeExistsIfProvidedAsync(input.LabelTypeId);

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

        var template = await LabelTemplateQueryHelper.QueryProjected(_dbContext.SqlSugarClient)
            .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.PrimaryLocationId,
            LabelCategoryId = input.LabelCategoryId?.Trim(),
            LabelTypeId = NormalizeOptionalLabelTypeId(input.LabelTypeId),
            State = input.State,
            LabelType = null,
            LabelInfoJson = input.LabelInfoJson == null ? null : JsonSerializer.Serialize(input.LabelInfoJson)
        };
        await _dbContext.SqlSugarClient.Insertable(labelEntity)
            .IgnoreColumns(it => it.AppliedRegionType)
            .ExecuteCommandAsync();
        await LabelRegionSchemaHelper.SetAppliedRegionTypeAsync(
            _dbContext.SqlSugarClient, labelId, scope.AppliedRegionType);
        await LabelRegionScopeHelper.SaveLabelRegionsAsync(
            _dbContext.SqlSugarClient,
            _guidGenerator,
            labelId,
            scope.AppliedRegionType,
            scope.RegionIds,
            CurrentUser?.Id?.ToString(),
            now);
        await LabelRegionScopeHelper.SaveLabelLocationsAsync(
            _dbContext.SqlSugarClient,
            _guidGenerator,
            labelId,
            scope.LocationIds,
            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 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 LabelQueryHelper.QueryProjected(_dbContext.SqlSugarClient)
            .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不能为空");
        }

        await EnsureLabelTypeExistsIfProvidedAsync(input.LabelTypeId);

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

        var template = await LabelTemplateQueryHelper.QueryProjected(_dbContext.SqlSugarClient)
            .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.PrimaryLocationId;
        label.LabelCategoryId = input.LabelCategoryId?.Trim();
        label.LabelTypeId = NormalizeOptionalLabelTypeId(input.LabelTypeId);
        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)
            .IgnoreColumns(it => it.AppliedRegionType)
            .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 LabelRegionScopeHelper.SaveLabelLocationsAsync(
            _dbContext.SqlSugarClient,
            _guidGenerator,
            label.Id,
            scope.LocationIds,
            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();

        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 LabelQueryHelper.QueryProjected(_dbContext.SqlSugarClient)
            .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)
            .IgnoreColumns(it => it.AppliedRegionType)
            .ExecuteCommandAsync();

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

        if (regionSchema.HasLabelLocationTable)
        {
            await _dbContext.SqlSugarClient.Deleteable<FlLabelLocationDbEntity>()
                .Where(x => x.LabelId == label.Id)
                .ExecuteCommandAsync();
        }

        await _dbContext.SqlSugarClient.Deleteable<FlLabelProductDbEntity>()
            .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 LabelQueryHelper.QueryProjected(_dbContext.SqlSugarClient)
            .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.QueryProjected(_dbContext.SqlSugarClient)
            .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 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();

        var borderType = await LabelTemplateScopeSchemaHelper.GetBorderTypeForTemplateAsync(
            _dbContext.SqlSugarClient, template.Id);

        return new LabelTemplatePreviewDto
        {
            Id = template.TemplateCode,
            Name = template.TemplateName,
            LabelType = template.LabelType ?? string.Empty,
            Unit = template.Unit,
            Width = template.Width,
            Height = template.Height,
            PrintOrientation = LabelTemplatePrintOrientationHelper.Normalize(template.PrintOrientation),
            AppliedLocation = template.AppliedLocationType,
            ShowRuler = template.ShowRuler,
            ShowGrid = template.ShowGrid,
            Border = NormalizeTemplateBorderType(borderType),
            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? NormalizeOptionalLabelTypeId(string? labelTypeId)
    {
        var id = labelTypeId?.Trim();
        return string.IsNullOrWhiteSpace(id) ? null : id;
    }

    private async Task EnsureLabelTypeExistsIfProvidedAsync(string? labelTypeId)
    {
        var id = NormalizeOptionalLabelTypeId(labelTypeId);
        if (id is null)
        {
            return;
        }

        var exists = await _dbContext.SqlSugarClient.Queryable<FlLabelTypeDbEntity>()
            .AnyAsync(x => !x.IsDeleted && x.Id == id);
        if (!exists)
        {
            throw new UserFriendlyException("标签类型不存在");
        }
    }
}