using FoodLabeling.Application.Services.DbModels;
using FoodLabeling.Domain.Entities;
using SqlSugar;
using Volo.Abp;
using Volo.Abp.Guids;

namespace FoodLabeling.Application.Helpers;

/// <summary>
/// ?? Region?<c>fl_group.Id</c>????? <c>fl_label_location</c> ???????????????
/// </summary>
public static class LabelRegionScopeHelper
{
    public const string AppliedRegionAll = "ALL";

    public const string AppliedRegionSpecified = "SPECIFIED";

    public const string AllRegionsDisplay = FoodLabelingDisplayConsts.AllRegion;

    public const string AllLocationsDisplay = FoodLabelingDisplayConsts.AllLocation;

    public sealed class LabelRegionScopeSaveResult
    {
        public string AppliedRegionType { get; init; } = AppliedRegionSpecified;

        public List<string> RegionIds { get; init; } = new();

        /// <summary>???? Id ???<c>location.Id</c>???? <c>fl_label_location</c>?</summary>
        public List<string> LocationIds { get; init; } = new();

        /// <summary>?? <c>fl_label.LocationId</c>?? <see cref="LocationIds"/> ???</summary>
        public string? PrimaryLocationId => LocationIds.Count > 0 ? LocationIds[0] : null;
    }

    public sealed class LabelRegionScopeDisplay
    {
        public string Region { get; init; } = string.Empty;

        public List<string> RegionIds { get; init; } = new();

        public List<string> GroupIds { get; init; } = new();
    }

    public sealed class LabelLocationScopeDisplay
    {
        public string Location { get; init; } = string.Empty;

        public List<string> LocationIds { get; init; } = new();
    }

    /// <summary>
    /// 解析新增/编辑入参中的 Region / Location 范围。
    /// 前端 Select All 常传 SPECIFIED + 当前全量 Id：覆盖全集时归档为 ALL（或不写门店快照），后续新增 Region/Location 动态适用。
    /// </summary>
    public static async Task<LabelRegionScopeSaveResult> ResolveScopeForSaveAsync(
        ISqlSugarClient db,
        string? appliedRegionType,
        IReadOnlyList<string>? regionIds,
        IReadOnlyList<string>? groupIds,
        string? locationId,
        IReadOnlyList<string>? locationIds,
        IReadOnlyList<string>? partnerIdsForContext = null)
    {
        var mergedRegionIdsRaw = NormalizeRegionIds(regionIds, groupIds);
        var explicitLocationIdsRaw = MergeExplicitLocationIds(locationId, locationIds);
        var regionHasAll = LocationScopeBindingHelper.ContainsAllScopeSentinel(mergedRegionIdsRaw);
        var locationHasAll = LocationScopeBindingHelper.ContainsAllScopeSentinel(explicitLocationIdsRaw);
        var mergedRegionIds = LocationScopeBindingHelper.FilterConcreteScopeIds(mergedRegionIdsRaw);
        var explicitLocationIds = LocationScopeBindingHelper.FilterConcreteScopeIds(explicitLocationIdsRaw);
        var type = (appliedRegionType ?? AppliedRegionAll).Trim().ToUpperInvariant();
        var hasScopeArrays = regionIds is not null || groupIds is not null || locationIds is not null;
        var partnerContext = LocationScopeBindingHelper.NormalizeIds(partnerIdsForContext);

        // locationIds / regionIds 含 ALL 哨兵：不归档为 Guid 校验，按全选处理（Company 由 fl_label.PartnerId 落库）
        if (locationHasAll || (regionHasAll && explicitLocationIds.Count == 0))
        {
            return new LabelRegionScopeSaveResult
            {
                AppliedRegionType = AppliedRegionAll,
                RegionIds = new List<string>(),
                LocationIds = new List<string>()
            };
        }

        if (regionHasAll && explicitLocationIds.Count > 0)
        {
            await LocationScopeBindingHelper.ValidateLocationIdsExistAsync(db, explicitLocationIds);
            return new LabelRegionScopeSaveResult
            {
                AppliedRegionType = AppliedRegionAll,
                RegionIds = new List<string>(),
                LocationIds = explicitLocationIds
            };
        }

        if (mergedRegionIds.Count > 0)
        {
            type = AppliedRegionSpecified;
        }
        else if (hasScopeArrays && string.Equals(type, AppliedRegionAll, StringComparison.OrdinalIgnoreCase))
        {
            type = AppliedRegionAll;
        }

        if (type != AppliedRegionAll && type != AppliedRegionSpecified)
        {
            throw new UserFriendlyException("适用 Region 范围不合法（ALL/SPECIFIED）");
        }

        if (mergedRegionIds.Count == 0 && explicitLocationIds.Count == 0
            && !string.Equals(type, AppliedRegionAll, StringComparison.OrdinalIgnoreCase))
        {
            throw new UserFriendlyException("请指定 Region（regionIds）和/或门店（locationIds）");
        }

        if (mergedRegionIds.Count > 0)
        {
            await ValidateRegionIdsExistAsync(db, mergedRegionIds);
        }

        if (explicitLocationIds.Count > 0)
        {
            await LocationScopeBindingHelper.ValidateLocationIdsExistAsync(db, explicitLocationIds);
        }

        var resolvedPartnerContext = partnerContext.Count > 0
            ? partnerContext
            : await ResolvePartnerContextAsync(db, mergedRegionIds, explicitLocationIds);
        var allRegions = await AllScopeBindingHelper.ResolveAllRegionIdsAsync(db, resolvedPartnerContext);
        var regionsFull = mergedRegionIds.Count > 0
            && AllScopeBindingHelper.IsFullIdSelection(mergedRegionIds, allRegions);

        // 区域全集（或声明 ALL）+ 门店全选/未限门店 → AppliedRegionType=ALL，不写 Region/Location 快照
        var declaredOrFullAll =
            string.Equals(type, AppliedRegionAll, StringComparison.OrdinalIgnoreCase)
            || regionsFull;
        if (declaredOrFullAll)
        {
            var allLocationsForPartner = await AllScopeBindingHelper.ResolveAllLocationIdsAsync(
                db, resolvedPartnerContext, null);
            var locationsFullForPartner = explicitLocationIds.Count == 0
                || (allLocationsForPartner.Count > 0
                    && AllScopeBindingHelper.IsFullIdSelection(explicitLocationIds, allLocationsForPartner));

            if (locationsFullForPartner)
            {
                return new LabelRegionScopeSaveResult
                {
                    AppliedRegionType = AppliedRegionAll,
                    RegionIds = new List<string>(),
                    LocationIds = new List<string>()
                };
            }

            // 声明 ALL / 区域全选但仅勾选部分门店 → 保留门店快照（部分适用）
            return new LabelRegionScopeSaveResult
            {
                AppliedRegionType = AppliedRegionAll,
                RegionIds = new List<string>(),
                LocationIds = explicitLocationIds
            };
        }

        // 仅门店、无 Region：若覆盖 Company 下全部门店 → ALL
        if (mergedRegionIds.Count == 0 && explicitLocationIds.Count > 0)
        {
            var allLocations = await AllScopeBindingHelper.ResolveAllLocationIdsAsync(
                db, resolvedPartnerContext, null);
            if (allLocations.Count > 0
                && AllScopeBindingHelper.IsFullIdSelection(explicitLocationIds, allLocations))
            {
                return new LabelRegionScopeSaveResult
                {
                    AppliedRegionType = AppliedRegionAll,
                    RegionIds = new List<string>(),
                    LocationIds = new List<string>()
                };
            }

            return new LabelRegionScopeSaveResult
            {
                AppliedRegionType = AppliedRegionSpecified,
                RegionIds = new List<string>(),
                LocationIds = explicitLocationIds
            };
        }

        // 部分 Region：门店 Select All（空或覆盖该区域全集）→ 只落 Region，不写门店快照（新区店靠 Region 动态匹配）
        var allLocationsInRegions = await AllScopeBindingHelper.ResolveAllLocationIdsAsync(
            db, resolvedPartnerContext, mergedRegionIds);
        var locationsFullInRegions = explicitLocationIds.Count == 0
            || (allLocationsInRegions.Count > 0
                && AllScopeBindingHelper.IsFullIdSelection(explicitLocationIds, allLocationsInRegions));

        if (locationsFullInRegions)
        {
            if (allLocationsInRegions.Count == 0)
            {
                throw new UserFriendlyException("指定 Region 下未匹配到有效门店，请检查 Region 或 locationIds");
            }

            return new LabelRegionScopeSaveResult
            {
                AppliedRegionType = AppliedRegionSpecified,
                RegionIds = mergedRegionIds,
                LocationIds = new List<string>()
            };
        }

        // 部分 Region + 部分门店 → 校验门店属于所选 Region 后落快照
        var mergedLocations = await LocationScopeBindingHelper.MergeToLocationIdsAsync(
            db, resolvedPartnerContext, mergedRegionIds, explicitLocationIds);
        if (mergedLocations.Count == 0)
        {
            throw new UserFriendlyException("指定 Region 下未匹配到有效门店，请检查 Region 或 locationIds");
        }

        var invalid = explicitLocationIds
            .Where(id => !mergedLocations.Contains(id, StringComparer.Ordinal))
            .ToList();
        if (invalid.Count > 0)
        {
            throw new UserFriendlyException("所选门店必须属于所选 Region");
        }

        return new LabelRegionScopeSaveResult
        {
            AppliedRegionType = AppliedRegionSpecified,
            RegionIds = mergedRegionIds,
            LocationIds = explicitLocationIds
        };
    }

    /// <summary>由已选 Region/门店反推 Company 上下文，用于判断是否「全选」。</summary>
    private static async Task<List<string>?> ResolvePartnerContextAsync(
        ISqlSugarClient db,
        IReadOnlyList<string> regionIds,
        IReadOnlyList<string> locationIds)
    {
        var regionList = LocationScopeBindingHelper.NormalizeIds(regionIds);
        if (regionList.Count > 0)
        {
            var partnerIds = await db.Queryable<FlGroupDbEntity>()
                .Where(g => !g.IsDeleted && regionList.Contains(g.Id))
                .Select(g => g.PartnerId)
                .ToListAsync();
            var normalized = LocationScopeBindingHelper.NormalizeIds(partnerIds);
            return normalized.Count > 0 ? normalized : null;
        }

        var locList = LocationScopeBindingHelper.NormalizeIds(locationIds);
        if (locList.Count == 0)
        {
            return null;
        }

        var groupIds = await LocationScopeBindingHelper.ResolveGroupIdsFromLocationIdsAsync(db, locList);
        if (groupIds.Count == 0)
        {
            return null;
        }

        var fromLoc = await db.Queryable<FlGroupDbEntity>()
            .Where(g => !g.IsDeleted && groupIds.Contains(g.Id))
            .Select(g => g.PartnerId)
            .ToListAsync();
        var fromLocNormalized = LocationScopeBindingHelper.NormalizeIds(fromLoc);
        return fromLocNormalized.Count > 0 ? fromLocNormalized : null;
    }

    public static async Task SaveLabelRegionsAsync(
        ISqlSugarClient db,
        IGuidGenerator guidGenerator,
        string labelId,
        string appliedRegionType,
        IReadOnlyList<string> regionIds,
        string? currentUserId,
        DateTime now)
    {
        var schema = await LabelRegionSchemaHelper.GetStatusAsync(db);
        if (!schema.HasLabelRegionTable)
        {
            return;
        }

        await db.Deleteable<FlLabelRegionDbEntity>()
            .Where(x => x.LabelId == labelId)
            .ExecuteCommandAsync();

        if (!string.Equals(appliedRegionType, AppliedRegionSpecified, StringComparison.OrdinalIgnoreCase)
            || regionIds.Count == 0)
        {
            return;
        }

        var rows = regionIds.Select(gid => new FlLabelRegionDbEntity
        {
            Id = guidGenerator.Create().ToString(),
            LabelId = labelId,
            GroupId = gid,
            CreationTime = now,
            CreatorId = currentUserId
        }).ToList();

        await db.Insertable(rows).ExecuteCommandAsync();
    }

    /// <summary>????????? <c>fl_label_location</c>?</summary>
    public static async Task SaveLabelLocationsAsync(
        ISqlSugarClient db,
        IGuidGenerator guidGenerator,
        string labelId,
        IReadOnlyList<string> locationIds,
        string? currentUserId,
        DateTime now)
    {
        var schema = await LabelRegionSchemaHelper.GetStatusAsync(db);
        if (!schema.HasLabelLocationTable)
        {
            return;
        }

        await db.Deleteable<FlLabelLocationDbEntity>()
            .Where(x => x.LabelId == labelId)
            .ExecuteCommandAsync();

        if (locationIds.Count == 0)
        {
            return;
        }

        var rows = locationIds.Select(locId => new FlLabelLocationDbEntity
        {
            Id = guidGenerator.Create().ToString(),
            LabelId = labelId,
            LocationId = locId,
            CreationTime = now,
            CreatorId = currentUserId
        }).ToList();

        await db.Insertable(rows).ExecuteCommandAsync();
    }

    public static async Task<List<string>> GetRegionIdsForLabelAsync(ISqlSugarClient db, string labelId)
    {
        var schema = await LabelRegionSchemaHelper.GetStatusAsync(db);
        if (!schema.HasLabelRegionTable)
        {
            return new List<string>();
        }

        return LocationScopeBindingHelper.NormalizeIds(
            await db.Queryable<FlLabelRegionDbEntity>()
                .Where(x => x.LabelId == labelId)
                .Select(x => x.GroupId)
                .ToListAsync());
    }

    public static async Task<List<string>> GetLocationIdsForLabelAsync(
        ISqlSugarClient db,
        string labelId,
        string? fallbackLocationId)
    {
        var schema = await LabelRegionSchemaHelper.GetStatusAsync(db);
        if (schema.HasLabelLocationTable)
        {
            var ids = LocationScopeBindingHelper.NormalizeIds(
                await db.Queryable<FlLabelLocationDbEntity>()
                    .Where(x => x.LabelId == labelId)
                    .Select(x => x.LocationId)
                    .ToListAsync());
            if (ids.Count > 0)
            {
                return ids;
            }
        }

        var fallback = fallbackLocationId?.Trim();
        return string.IsNullOrWhiteSpace(fallback)
            ? new List<string>()
            : new List<string> { fallback };
    }

    public static async Task<Dictionary<string, List<string>>> BuildLocationIdsMapAsync(
        ISqlSugarClient db,
        IReadOnlyList<string> labelIds,
        IReadOnlyDictionary<string, string?> fallbackLocationIdByLabelId)
    {
        var result = new Dictionary<string, List<string>>(StringComparer.Ordinal);
        if (labelIds.Count == 0)
        {
            return result;
        }

        foreach (var labelId in labelIds)
        {
            fallbackLocationIdByLabelId.TryGetValue(labelId, out var fallback);
            result[labelId] = new List<string>();
            if (!string.IsNullOrWhiteSpace(fallback))
            {
                result[labelId].Add(fallback.Trim());
            }
        }

        var schema = await LabelRegionSchemaHelper.GetStatusAsync(db);
        if (!schema.HasLabelLocationTable)
        {
            return result;
        }

        var links = await db.Queryable<FlLabelLocationDbEntity>()
            .Where(x => labelIds.Contains(x.LabelId))
            .ToListAsync();

        foreach (var g in links.GroupBy(x => x.LabelId, StringComparer.Ordinal))
        {
            var ids = LocationScopeBindingHelper.NormalizeIds(g.Select(x => x.LocationId).ToList());
            if (ids.Count > 0)
            {
                result[g.Key] = ids;
            }
        }

        return result;
    }

    public static async Task<LabelRegionScopeDisplay> BuildScopeDisplayAsync(
        ISqlSugarClient db,
        string labelId,
        string? appliedRegionType,
        IReadOnlyList<string> locationIds)
    {
        if (string.Equals(appliedRegionType, AppliedRegionAll, StringComparison.OrdinalIgnoreCase))
        {
            return new LabelRegionScopeDisplay
            {
                Region = AllRegionsDisplay,
                RegionIds = new List<string> { AllScopeBindingHelper.ScopeAll },
                GroupIds = new List<string> { AllScopeBindingHelper.ScopeAll }
            };
        }

        var regionIds = await GetRegionIdsForLabelAsync(db, labelId);
        if (regionIds.Count == 0 && locationIds.Count > 0)
        {
            regionIds = await LocationScopeBindingHelper.ResolveGroupIdsFromLocationIdsAsync(db, locationIds.ToList());
        }

        if (regionIds.Count == 0)
        {
            return new LabelRegionScopeDisplay
            {
                Region = "?",
                RegionIds = new List<string>(),
                GroupIds = new List<string>()
            };
        }

        var names = await db.Queryable<FlGroupDbEntity>()
            .Where(g => !g.IsDeleted && regionIds.Contains(g.Id))
            .OrderBy(g => g.GroupName)
            .Select(g => g.GroupName)
            .ToListAsync();
        var regionText = names.Count > 0
            ? string.Join(", ", names.Where(n => !string.IsNullOrWhiteSpace(n)).Select(n => n!.Trim()).Distinct())
            : "?";

        var collapsed = await ScopeAllEchoHelper.CollapseScopeIdsToAllSentinelAsync(
            db,
            null,
            regionIds,
            locationIds,
            ScopeAllEchoHelper.ForLabelRegionScope(appliedRegionType, locationIds));

        return new LabelRegionScopeDisplay
        {
            Region = regionText,
            RegionIds = collapsed.RegionIds,
            GroupIds = collapsed.RegionIds
        };
    }

    public static async Task<LabelLocationScopeDisplay> BuildLocationDisplayAsync(
        ISqlSugarClient db,
        string? appliedRegionType,
        IReadOnlyList<string> locationIds)
    {
        if (string.Equals(appliedRegionType, AppliedRegionAll, StringComparison.OrdinalIgnoreCase)
            && locationIds.Count == 0)
        {
            return new LabelLocationScopeDisplay
            {
                Location = AllLocationsDisplay,
                LocationIds = new List<string> { AllScopeBindingHelper.ScopeAll }
            };
        }

        if (locationIds.Count == 0)
        {
            return new LabelLocationScopeDisplay
            {
                Location = "?",
                LocationIds = new List<string>()
            };
        }

        var guidList = locationIds.Where(x => Guid.TryParse(x, out _)).Select(Guid.Parse).ToList();
        var names = new List<string>();
        if (guidList.Count > 0)
        {
            names = await db.Queryable<LocationAggregateRoot>()
                .Where(x => !x.IsDeleted && guidList.Contains(x.Id))
                .OrderBy(x => x.LocationName)
                .Select(x => x.LocationName ?? x.LocationCode)
                .ToListAsync();
        }

        var locationText = names.Count > 0
            ? string.Join(", ", names.Where(n => !string.IsNullOrWhiteSpace(n)).Select(n => n!.Trim()).Distinct())
            : "?";

        var collapsed = await ScopeAllEchoHelper.CollapseScopeIdsToAllSentinelAsync(
            db,
            null,
            null,
            locationIds,
            ScopeAllEchoHelper.ForLabelRegionScope(appliedRegionType, locationIds));

        return new LabelLocationScopeDisplay
        {
            Location = locationText,
            LocationIds = collapsed.LocationIds
        };
    }

    /// <summary>
    /// ??? Region?<c>fl_group.Id</c>?? scoped ?????
    /// </summary>
    public static ISugarQueryable<FlLabelDbEntity> ApplyLabelRegionListFilter(
        ISqlSugarClient db,
        ISugarQueryable<FlLabelDbEntity> query,
        string groupId,
        List<string>? scopedLocationIds,
        LabelRegionSchemaHelper.LabelRegionSchemaStatus schema)
    {
        var gid = groupId.Trim();
        if (scopedLocationIds is not { Count: > 0 })
        {
            return query.Where(_ => false);
        }

        if (!schema.HasLabelRegionTable && !schema.HasAppliedRegionTypeColumn)
        {
            return ApplyLabelLocationMatchFilter(query, scopedLocationIds, schema);
        }

        if (!schema.HasLabelRegionTable)
        {
            return query.Where(
                "(AppliedRegionType = @all OR LocationId IN (@locs))",
                new { all = AppliedRegionAll, locs = scopedLocationIds });
        }

        if (!schema.HasAppliedRegionTypeColumn)
        {
            if (schema.HasLabelLocationTable)
            {
                return query.Where(
                    """
                    (EXISTS (SELECT 1 FROM fl_label_region lr WHERE lr.LabelId = fl_label.Id AND lr.GroupId = @gid)
                     OR LocationId IN (@locs)
                     OR EXISTS (SELECT 1 FROM fl_label_location ll WHERE ll.LabelId = fl_label.Id AND ll.LocationId IN (@locs)))
                    """,
                    new { gid, locs = scopedLocationIds });
            }

            return query.Where(
                """
                (EXISTS (SELECT 1 FROM fl_label_region lr WHERE lr.LabelId = fl_label.Id AND lr.GroupId = @gid)
                 OR LocationId IN (@locs))
                """,
                new { gid, locs = scopedLocationIds });
        }

        if (schema.HasLabelLocationTable)
        {
            return query.Where(
                """
                (AppliedRegionType = @all
                 OR EXISTS (SELECT 1 FROM fl_label_region lr WHERE lr.LabelId = fl_label.Id AND lr.GroupId = @gid)
                 OR LocationId IN (@locs)
                 OR EXISTS (SELECT 1 FROM fl_label_location ll WHERE ll.LabelId = fl_label.Id AND ll.LocationId IN (@locs)))
                """,
                new { all = AppliedRegionAll, gid, locs = scopedLocationIds });
        }

        return query.Where(
            """
            (AppliedRegionType = @all
             OR EXISTS (SELECT 1 FROM fl_label_region lr WHERE lr.LabelId = fl_label.Id AND lr.GroupId = @gid)
             OR LocationId IN (@locs))
            """,
            new { all = AppliedRegionAll, gid, locs = scopedLocationIds });
    }

    /// <summary>? Region Id ???? scoped ????</summary>
    public static ISugarQueryable<FlLabelDbEntity> ApplyGroupIdListFilter(
        ISqlSugarClient db,
        ISugarQueryable<FlLabelDbEntity> query,
        string groupId,
        LabelRegionSchemaHelper.LabelRegionSchemaStatus schema)
    {
        var gid = groupId.Trim();
        if (!schema.HasLabelRegionTable && !schema.HasAppliedRegionTypeColumn)
        {
            return query.Where(_ => false);
        }

        if (!schema.HasLabelRegionTable)
        {
            return query.Where("AppliedRegionType = @all", new { all = AppliedRegionAll });
        }

        if (!schema.HasAppliedRegionTypeColumn)
        {
            return query.Where(l =>
                SqlFunc.Subqueryable<FlLabelRegionDbEntity>()
                    .Where(lr => lr.LabelId == l.Id && lr.GroupId == gid)
                    .Any());
        }

        return query.Where(
            """
            (AppliedRegionType = @all
             OR EXISTS (SELECT 1 FROM fl_label_region lr WHERE lr.LabelId = fl_label.Id AND lr.GroupId = @gid))
            """,
            new { all = AppliedRegionAll, gid });
    }

    /// <summary>
    /// 无可见门店时：仅保留 <c>AppliedRegionType=ALL</c> 的标签（用于 PartnerId 筛选但该公司暂无门店）。
    /// </summary>
    public static ISugarQueryable<FlLabelDbEntity> ApplyLabelAllRegionOnlyFilter(
        ISqlSugarClient db,
        ISugarQueryable<FlLabelDbEntity> query,
        LabelRegionSchemaHelper.LabelRegionSchemaStatus schema)
    {
        if (!schema.HasAppliedRegionTypeColumn)
        {
            // 未迁移列时无法识别 ALL，保守返回空
            return query.Where(_ => false);
        }

        return query.Where("AppliedRegionType = @all", new { all = AppliedRegionAll });
    }

    /// <summary>按 scoped 门店过滤；AppliedRegionType=ALL 对任意门店筛选均命中。</summary>
    public static ISugarQueryable<FlLabelDbEntity> ApplyLabelLocationListFilter(
        ISqlSugarClient db,
        ISugarQueryable<FlLabelDbEntity> query,
        List<string> scopedLocationIds,
        LabelRegionSchemaHelper.LabelRegionSchemaStatus schema)
    {
        if (scopedLocationIds.Count == 0)
        {
            return ApplyLabelAllRegionOnlyFilter(db, query, schema);
        }

        return ApplyLabelLocationMatchFilter(query, scopedLocationIds, schema);
    }

    /// <summary>
    /// ?????? Region Id?? App ??/?????
    /// </summary>
    public static async Task<List<string>> ResolveGroupIdsForLocationAsync(ISqlSugarClient db, string locationId)
    {
        return await LocationScopeBindingHelper.ResolveGroupIdsFromLocationIdsAsync(
            db, new List<string> { locationId.Trim() });
    }

    /// <summary>
    /// ??????????????????????
    /// </summary>
    public static async Task EnsureLabelAppliesToLocationAsync(
        ISqlSugarClient db,
        string labelId,
        string locationId)
    {
        var label = await LabelQueryHelper.QueryProjected(db)
            .FirstAsync(x => !x.IsDeleted && x.Id == labelId);
        var groupIds = await ResolveGroupIdsForLocationAsync(db, locationId);
        if (!await LabelAppliesToLocationAsync(db, label, locationId, groupIds))
        {
            throw new UserFriendlyException("??????????????? Region");
        }
    }

    public static async Task<bool> LabelAppliesToLocationAsync(
        ISqlSugarClient db,
        FlLabelDbEntity label,
        string locationId,
        List<string> groupIdsForLocation)
    {
        var appliedType = await LabelRegionSchemaHelper.GetAppliedRegionTypeForLabelAsync(
            db, label.Id, label.AppliedRegionType);

        if (string.Equals(appliedType, AppliedRegionAll, StringComparison.OrdinalIgnoreCase))
        {
            var locationIds = await GetLocationIdsForLabelAsync(db, label.Id, label.LocationId);
            if (locationIds.Count == 0)
            {
                // 指定 Company 的 ALL：仅该公司下门店可用
                if (!string.IsNullOrWhiteSpace(label.PartnerId))
                {
                    var locPartners = await LocationScopeBindingHelper.ResolvePartnerIdsFromLocationIdsAsync(
                        db, new List<string> { locationId.Trim() });
                    return locPartners.Contains(label.PartnerId.Trim(), StringComparer.OrdinalIgnoreCase);
                }

                return true;
            }

            return locationIds.Contains(locationId.Trim(), StringComparer.OrdinalIgnoreCase);
        }

        var schema = await LabelRegionSchemaHelper.GetStatusAsync(db);
        if (schema.HasLabelLocationTable)
        {
            var hitLocation = await db.Queryable<FlLabelLocationDbEntity>()
                .AnyAsync(x => x.LabelId == label.Id && x.LocationId == locationId);
            if (hitLocation)
            {
                return true;
            }
        }

        if (!string.IsNullOrWhiteSpace(label.LocationId) &&
            string.Equals(label.LocationId.Trim(), locationId, StringComparison.OrdinalIgnoreCase))
        {
            return true;
        }

        if (groupIdsForLocation.Count == 0)
        {
            return false;
        }

        if (!schema.HasLabelRegionTable)
        {
            return false;
        }

        return await db.Queryable<FlLabelRegionDbEntity>()
            .AnyAsync(lr => lr.LabelId == label.Id && groupIdsForLocation.Contains(lr.GroupId));
    }

    /// <summary>
    /// App labeling-tree 单门店可见性过滤 SQL（多表 join 时 fl_label 别名为 <c>l</c>）。
    /// </summary>
    public static (string Sql, object Parameters) BuildAppLabelingTreeStoreFilterSql(
        LabelRegionSchemaHelper.LabelRegionSchemaStatus schema,
        string locationId,
        IReadOnlyList<string> storeGroupIds)
    {
        var loc = locationId.Trim();
        var groupIds = LocationScopeBindingHelper.NormalizeIds(storeGroupIds);
        var hasLocTable = schema.HasLabelLocationTable;
        var hasRegTable = schema.HasLabelRegionTable;
        var hasAppliedCol = schema.HasAppliedRegionTypeColumn;

        if (!hasAppliedCol)
        {
            return (BuildAppLabelingTreeSpecifiedOnlySql(hasLocTable, hasRegTable, groupIds), new { loc, groupIds });
        }

        var allGlobalSql = hasLocTable
            ? """
              (NOT EXISTS (SELECT 1 FROM fl_label_location ll WHERE ll.LabelId = l.Id)
               AND (l.LocationId IS NULL OR l.LocationId = ''))
              """
            : "(l.LocationId IS NULL OR l.LocationId = '')";

        var locMatchParts = new List<string> { allGlobalSql, "l.LocationId = @loc" };
        if (hasLocTable)
        {
            locMatchParts.Insert(1,
                """
                EXISTS (SELECT 1 FROM fl_label_location ll
                        WHERE ll.LabelId = l.Id AND ll.LocationId = @loc)
                """);
        }

        var allBranchSql = string.Join(" OR ", locMatchParts);

        var specifiedParts = new List<string> { "l.LocationId = @loc" };
        if (hasLocTable)
        {
            specifiedParts.Insert(0,
                """
                EXISTS (SELECT 1 FROM fl_label_location ll
                        WHERE ll.LabelId = l.Id AND ll.LocationId = @loc)
                """);
        }

        if (hasRegTable && groupIds.Count > 0)
        {
            specifiedParts.Add(
                """
                EXISTS (SELECT 1 FROM fl_label_region lr
                        WHERE lr.LabelId = l.Id AND lr.GroupId IN (@groupIds))
                """);
        }

        var specifiedBranchSql = string.Join(" OR ", specifiedParts);

        var sql = $"""
                   ((l.AppliedRegionType = @all AND ({allBranchSql}))
                    OR (l.AppliedRegionType <> @all AND ({specifiedBranchSql})))
                   """;

        return (sql, new { all = AppliedRegionAll, loc, groupIds });
    }

    private static string BuildAppLabelingTreeSpecifiedOnlySql(
        bool hasLocTable,
        bool hasRegTable,
        IReadOnlyList<string> groupIds)
    {
        var parts = new List<string> { "l.LocationId = @loc" };
        if (hasLocTable)
        {
            parts.Insert(0,
                """
                EXISTS (SELECT 1 FROM fl_label_location ll
                        WHERE ll.LabelId = l.Id AND ll.LocationId = @loc)
                """);
        }

        if (hasRegTable && groupIds.Count > 0)
        {
            parts.Add(
                """
                EXISTS (SELECT 1 FROM fl_label_region lr
                        WHERE lr.LabelId = l.Id AND lr.GroupId IN (@groupIds))
                """);
        }

        return $"({string.Join(" OR ", parts)})";
    }

    private static ISugarQueryable<FlLabelDbEntity> ApplyLabelLocationMatchFilter(
        ISugarQueryable<FlLabelDbEntity> query,
        List<string> scopedLocationIds,
        LabelRegionSchemaHelper.LabelRegionSchemaStatus schema)
    {
        if (schema.HasAppliedRegionTypeColumn && schema.HasLabelLocationTable)
        {
            return query.Where(
                """
                (AppliedRegionType = @all
                 OR LocationId IN (@locs)
                 OR EXISTS (SELECT 1 FROM fl_label_location ll WHERE ll.LabelId = fl_label.Id AND ll.LocationId IN (@locs)))
                """,
                new { all = AppliedRegionAll, locs = scopedLocationIds });
        }

        if (schema.HasAppliedRegionTypeColumn)
        {
            return query.Where(
                """
                (AppliedRegionType = @all OR LocationId IN (@locs))
                """,
                new { all = AppliedRegionAll, locs = scopedLocationIds });
        }

        if (schema.HasLabelLocationTable)
        {
            return query.Where(
                """
                (LocationId IN (@locs)
                 OR EXISTS (SELECT 1 FROM fl_label_location ll WHERE ll.LabelId = fl_label.Id AND ll.LocationId IN (@locs)))
                """,
                new { locs = scopedLocationIds });
        }

        return query.Where(l => scopedLocationIds.Contains(l.LocationId));
    }

    private static List<string> MergeExplicitLocationIds(string? locationId, IReadOnlyList<string>? locationIds)
    {
        var merged = LocationScopeBindingHelper.NormalizeIds(locationIds);
        var single = locationId?.Trim();
        if (!string.IsNullOrWhiteSpace(single) && !merged.Contains(single, StringComparer.Ordinal))
        {
            merged.Insert(0, single);
        }

        return merged;
    }

    private static List<string> NormalizeRegionIds(
        IReadOnlyList<string>? regionIds,
        IReadOnlyList<string>? groupIds)
    {
        var merged = new HashSet<string>(StringComparer.Ordinal);
        foreach (var id in LocationScopeBindingHelper.NormalizeIds(regionIds))
        {
            merged.Add(id);
        }

        foreach (var id in LocationScopeBindingHelper.NormalizeIds(groupIds))
        {
            merged.Add(id);
        }

        return merged.OrderBy(x => x, StringComparer.Ordinal).ToList();
    }

    private static async Task ValidateRegionIdsExistAsync(ISqlSugarClient db, List<string> regionIds)
    {
        var ids = LocationScopeBindingHelper.FilterConcreteScopeIds(regionIds);
        if (ids.Count == 0)
        {
            return;
        }

        var count = await db.Queryable<FlGroupDbEntity>()
            .Where(g => !g.IsDeleted && ids.Contains(g.Id))
            .CountAsync();
        if (count != ids.Count)
        {
            throw new UserFriendlyException("存在无效的 Region Id，请刷新后重试");
        }
    }
}