using FoodLabeling.Application.Services.DbModels;
using FoodLabeling.Domain.Entities;
using SqlSugar;
using Volo.Abp;
using Volo.Abp.Guids;
using Volo.Abp.Users;
using Yi.Framework.Rbac.Domain.Entities;
using Yi.Framework.SqlSugarCore.Abstractions;

namespace FoodLabeling.Application.Helpers;

/// <summary>
/// Region（<c>fl_group.Id</c>）与 Location（<c>location.Id</c>）范围解析：Region 落库对应 <c>location.GroupName</c>。
/// </summary>
public static class LocationScopeBindingHelper
{
    /// <summary>
    /// 读取用户 <c>userlocation</c> 绑定的门店 Id（Guid 归一化，避免大小写导致漏绑）。
    /// </summary>
    public static async Task<List<string>> ResolveBoundLocationIdsForUserAsync(
        ISqlSugarClient db,
        Guid userId)
    {
        var userKey = TeamMemberListScopeHelper.UserKey(userId);
        var userIdStr = userId.ToString("D");

        var rows = await db.Ado.SqlQueryAsync<string>(
            """
            SELECT LocationId
            FROM userlocation
            WHERE IsDeleted = 0
              AND (
                    UserId = @UserIdD
                 OR UserId = @UserIdLower
                 OR LOWER(TRIM(UserId)) = @UserIdLower
              )
            """,
            new
            {
                UserIdD = userIdStr,
                UserIdLower = userKey
            });

        var locationIds = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        foreach (var locationId in rows)
        {
            var locKey = TeamMemberListScopeHelper.NormalizeScopeKey(locationId);
            if (!string.IsNullOrEmpty(locKey))
            {
                locationIds.Add(locKey);
            }
        }

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

    /// <summary>
    /// App 可选门店 Id：Company Admin 展开为绑定 Company 下全部门店；其它角色为 <c>userlocation</c> 绑定。
    /// </summary>
    public static Task<List<string>> ResolveAppAccessibleLocationIdsForUserAsync(
        ISqlSugarClient db,
        Guid userId) =>
        ResolveAppAccessibleLocationIdsForUserAsync(db, userId, null);

    /// <summary>
    /// App 可选门店 Id；传入 <paramref name="currentUser"/> 时优先 JWT 声明识别 Company Admin，避免 ORM 查 Role 触发数据权限过滤器。
    /// </summary>
    public static async Task<List<string>> ResolveAppAccessibleLocationIdsForUserAsync(
        ISqlSugarClient db,
        Guid userId,
        ICurrentUser? currentUser)
    {
        var locationIds = await ResolveBoundLocationIdsForUserAsync(db, userId);
        if (locationIds.Count == 0)
        {
            return locationIds;
        }

        var isCompanyAdmin = currentUser?.Id == userId
            ? await TeamMemberRoleHelper.IsCompanyAdminUserAsync(db, currentUser)
            : await TeamMemberRoleHelper.IsCompanyAdminUserAsync(db, userId);
        if (!isCompanyAdmin)
        {
            return locationIds;
        }

        var partnerIds = await ResolvePartnerIdsFromLocationIdsAsync(db, locationIds);
        if (partnerIds.Count == 0)
        {
            return locationIds;
        }

        var expanded = await ResolveLocationIdsFromPartnerIdsAsync(db, partnerIds);
        if (expanded.Count == 0)
        {
            return locationIds;
        }

        var merged = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        foreach (var id in expanded)
        {
            var key = TeamMemberListScopeHelper.NormalizeScopeKey(id);
            if (!string.IsNullOrEmpty(key))
            {
                merged.Add(key);
            }
        }

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

    public static async Task<List<string>> ResolveDisplayRegionIdsForCurrentUserAsync(
        ISqlSugarClient db,
        ICurrentUser currentUser)
    {
        if (currentUser.Id is null)
        {
            return new List<string>();
        }

        return await ResolveDisplayRegionIdsForUserAsync(db, currentUser, null);
    }

    /// <summary>
    /// 与 Team Member 详情/编辑回显一致：Company Admin 展示绑定 Company 下全部 Region。
    /// </summary>
    public static async Task<List<string>> ResolveDisplayRegionIdsForUserAsync(
        ISqlSugarClient db,
        ICurrentUser currentUser,
        Guid? roleId)
    {
        if (currentUser.Id is null)
        {
            return new List<string>();
        }

        var locationIds = await ResolveBoundLocationIdsForUserAsync(db, currentUser.Id.Value);
        if (locationIds.Count == 0)
        {
            return new List<string>();
        }

        var partnerIds = await ResolvePartnerIdsFromLocationIdsAsync(db, locationIds);
        var regionIds = await ResolveGroupIdsFromLocationIdsAsync(db, locationIds);

        if (await TeamMemberRoleHelper.IsCompanyAdminUserAsync(db, currentUser) && partnerIds.Count > 0)
        {
            regionIds = await ResolveGroupIdsFromPartnerIdsAsync(db, partnerIds);
        }

        return regionIds;
    }

    /// <summary>
    /// 按用户 Id 解析可见 Region（无 JWT 上下文时用，如 Team Member 详情）。
    /// </summary>
    public static async Task<List<string>> ResolveDisplayRegionIdsForUserAsync(
        ISqlSugarClient db,
        Guid userId,
        Guid? roleId)
    {
        var locationIds = await ResolveBoundLocationIdsForUserAsync(db, userId);
        if (locationIds.Count == 0)
        {
            return new List<string>();
        }

        var partnerIds = await ResolvePartnerIdsFromLocationIdsAsync(db, locationIds);
        var regionIds = await ResolveGroupIdsFromLocationIdsAsync(db, locationIds);

        if (await TeamMemberRoleHelper.IsCompanyAdminUserAsync(db, userId) && partnerIds.Count > 0)
        {
            regionIds = await ResolveGroupIdsFromPartnerIdsAsync(db, partnerIds);
        }

        return regionIds;
    }

    /// <summary>
    /// 根据成员绑定的门店 Id 解析 Company / Region 范围（Team Member 详情、编辑回显）。
    /// </summary>
    public static async Task<(List<string> PartnerIds, List<string> RegionIds, List<string> LocationIds)>
        ResolveMemberScopeFromLocationIdsAsync(ISqlSugarClient db, IReadOnlyList<string> locationIds)
    {
        var normalized = NormalizeIds(locationIds);
        if (normalized.Count == 0)
        {
            return (new List<string>(), new List<string>(), new List<string>());
        }

        var partnerIds = await ResolvePartnerIdsFromLocationIdsAsync(db, normalized);
        var regionIds = await ResolveGroupIdsFromLocationIdsAsync(db, normalized);
        return (partnerIds, regionIds, normalized);
    }

    /// <summary>
    /// 列表筛选：门店 Id 优先，否则 Region（groupId），否则 Company（partnerId）；均未传返回 null。
    /// </summary>
    public static async Task<List<string>?> ResolveFilteredLocationIdsForListAsync(
        ISqlSugarClient db,
        string? partnerId,
        string? groupId,
        string? locationId)
    {
        var locId = locationId?.Trim();
        if (!string.IsNullOrWhiteSpace(locId))
        {
            if (!Guid.TryParse(locId, out var locationGuid))
            {
                return new List<string>();
            }

            var exists = await db.Queryable<LocationAggregateRoot>()
                .AnyAsync(x => !x.IsDeleted && x.Id == locationGuid);
            return exists ? new List<string> { locId } : new List<string>();
        }

        var gid = groupId?.Trim();
        var pid = partnerId?.Trim();

        if (string.IsNullOrWhiteSpace(pid) && string.IsNullOrWhiteSpace(gid))
        {
            return null;
        }

        var q = db.Queryable<LocationAggregateRoot>().Where(x => !x.IsDeleted);

        if (!string.IsNullOrWhiteSpace(gid))
        {
            var g = await db.Queryable<FlGroupDbEntity>()
                .FirstAsync(x => !x.IsDeleted && x.Id == gid);
            if (g is null)
            {
                return new List<string>();
            }

            var gName = g.GroupName?.Trim() ?? string.Empty;
            var partner = await db.Queryable<FlPartnerDbEntity>()
                .FirstAsync(x => !x.IsDeleted && x.Id == g.PartnerId);
            if (partner is null)
            {
                return new List<string>();
            }

            var pName = partner.PartnerName?.Trim() ?? string.Empty;
            q = q.Where(x => x.GroupName == gName && x.Partner == pName);
        }
        else if (!string.IsNullOrWhiteSpace(pid))
        {
            var partner = await db.Queryable<FlPartnerDbEntity>()
                .FirstAsync(x => !x.IsDeleted && x.Id == pid);
            if (partner is null)
            {
                return new List<string>();
            }

            var pName = partner.PartnerName?.Trim() ?? string.Empty;
            q = q.Where(x => x.Partner == pName);
        }

        return await q.Select(x => SqlFunc.ToString(x.Id)).ToListAsync();
    }

    /// <summary>
    /// 列表筛选：按门店 Id 优先，否则按 Region（groupId）解析门店主键；均未传返回 null。
    /// </summary>
    public static async Task<List<string>?> ResolveScopedLocationIdsAsync(
        ISqlSugarClient db,
        string? groupId,
        string? locationId)
    {
        var locId = locationId?.Trim();
        if (!string.IsNullOrWhiteSpace(locId))
        {
            if (!Guid.TryParse(locId, out var locationGuid))
            {
                return new List<string>();
            }

            var exists = await db.Queryable<LocationAggregateRoot>()
                .AnyAsync(x => !x.IsDeleted && x.Id == locationGuid);
            return exists ? new List<string> { locId } : new List<string>();
        }

        var gid = groupId?.Trim();
        if (string.IsNullOrWhiteSpace(gid))
        {
            return null;
        }

        return await ResolveLocationIdsFromGroupIdsAsync(db, new List<string> { gid });
    }

    /// <summary>
    /// 将 Company（partnerId）、Region（groupIds）与显式 locationIds 合并为去重后的门店 Id 列表。
    /// </summary>
    public static Task<List<string>> MergeToLocationIdsAsync(
        ISqlSugarClient db,
        string? partnerId,
        IReadOnlyList<string>? groupIds,
        IReadOnlyList<string>? locationIds) =>
        MergeToLocationIdsAsync(db,
            string.IsNullOrWhiteSpace(partnerId) ? null : new[] { partnerId.Trim() },
            groupIds,
            locationIds);

    /// <summary>
    /// 将 Company（partnerIds）、Region（groupIds）与显式 locationIds 合并为去重后的门店 Id 列表。
    /// </summary>
    public static async Task<List<string>> MergeToLocationIdsAsync(
        ISqlSugarClient db,
        IReadOnlyList<string>? partnerIds,
        IReadOnlyList<string>? groupIds,
        IReadOnlyList<string>? locationIds)
    {
        var merged = new HashSet<string>(StringComparer.Ordinal);

        var fromPartner = await ResolveLocationIdsFromPartnerIdsAsync(db, partnerIds);
        foreach (var id in fromPartner)
        {
            merged.Add(id);
        }

        var fromGroups = await ResolveLocationIdsFromGroupIdsAsync(db, groupIds);
        foreach (var id in fromGroups)
        {
            merged.Add(id);
        }

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

        return merged.ToList();
    }

    /// <summary>
    /// 标签类型/分类/多选项门店范围落库：仅按 Region/Location 合并，不因 Company（partner）展开并集。
    /// </summary>
    public static async Task<List<string>> ResolveEntityLocationIdsForSaveAsync(
        ISqlSugarClient db,
        bool locationSpecified,
        IReadOnlyList<string>? regionIds,
        IReadOnlyList<string>? locationIds)
    {
        if (!locationSpecified)
        {
            return new List<string>();
        }

        var merged = await MergeToLocationIdsAsync(
            db,
            (IReadOnlyList<string>?)null,
            regionIds,
            locationIds);
        if (merged.Count == 0)
        {
            throw new UserFriendlyException("指定适用区域或门店时，至少需要匹配到一个有效门店");
        }

        await ValidateLocationIdsExistAsync(db, merged);
        return merged;
    }

    /// <summary>
    /// 根据已绑定门店反推适用的 Company Id（<c>fl_partner.Id</c>）。
    /// </summary>
    public static async Task<List<string>> ResolvePartnerIdsFromLocationIdsAsync(
        ISqlSugarClient db,
        IReadOnlyList<string> locationIds)
    {
        var ids = NormalizeIds(locationIds);
        if (ids.Count == 0)
        {
            return new List<string>();
        }

        var guidList = ids.Where(x => Guid.TryParse(x, out _)).Select(Guid.Parse).ToList();
        if (guidList.Count == 0)
        {
            return new List<string>();
        }

        var locs = await db.Queryable<LocationAggregateRoot>()
            .Where(x => !x.IsDeleted && guidList.Contains(x.Id))
            .ToListAsync();
        if (locs.Count == 0)
        {
            return new List<string>();
        }

        var partnerNames = locs
            .Select(x => x.Partner?.Trim())
            .Where(x => !string.IsNullOrEmpty(x))
            .Distinct(StringComparer.OrdinalIgnoreCase)
            .ToList();
        if (partnerNames.Count == 0)
        {
            return new List<string>();
        }

        var partners = await db.Queryable<FlPartnerDbEntity>()
            .Where(x => !x.IsDeleted)
            .ToListAsync();

        var partnerIdSet = new HashSet<string>(StringComparer.Ordinal);
        foreach (var name in partnerNames)
        {
            var match = partners.FirstOrDefault(p =>
                string.Equals(p.PartnerName?.Trim(), name, StringComparison.OrdinalIgnoreCase) ||
                string.Equals(p.Id, name, StringComparison.OrdinalIgnoreCase));
            if (match is not null && !string.IsNullOrWhiteSpace(match.Id))
            {
                partnerIdSet.Add(match.Id.Trim());
            }
        }

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

    /// <summary>
    /// 解析 Company（<c>fl_partner.Id</c>）下全部未删除门店 Id。
    /// </summary>
    public static async Task<List<string>> ResolveLocationIdsFromPartnerIdsAsync(
        ISqlSugarClient db,
        IReadOnlyList<string>? partnerIds)
    {
        var ids = NormalizeIds(partnerIds);
        if (ids.Count == 0)
        {
            return new List<string>();
        }

        var result = new HashSet<string>(StringComparer.Ordinal);
        foreach (var pid in ids)
        {
            var partner = await db.Queryable<FlPartnerDbEntity>()
                .FirstAsync(x => !x.IsDeleted && x.Id == pid);
            if (partner is null)
            {
                continue;
            }

            var pName = partner.PartnerName?.Trim() ?? string.Empty;
            if (string.IsNullOrEmpty(pName))
            {
                continue;
            }

            var locIds = await db.Queryable<LocationAggregateRoot>()
                .Where(x => !x.IsDeleted && x.Partner == pName)
                .Select(x => SqlFunc.ToString(x.Id))
                .ToListAsync();
            foreach (var lid in locIds.Where(x => !string.IsNullOrWhiteSpace(x)))
            {
                result.Add(lid.Trim());
            }
        }

        return result.ToList();
    }

    /// <summary>
    /// 根据已绑定门店反推适用的 Region Id（<c>fl_group.Id</c>）。
    /// </summary>
    public static async Task<List<string>> ResolveGroupIdsFromLocationIdsAsync(
        ISqlSugarClient db,
        IReadOnlyList<string> locationIds)
    {
        var ids = NormalizeIds(locationIds);
        if (ids.Count == 0)
        {
            return new List<string>();
        }

        var guidList = ids.Where(x => Guid.TryParse(x, out _)).Select(Guid.Parse).ToList();
        if (guidList.Count == 0)
        {
            return new List<string>();
        }

        var locs = await db.Queryable<LocationAggregateRoot>()
            .Where(x => !x.IsDeleted && guidList.Contains(x.Id))
            .ToListAsync();
        if (locs.Count == 0)
        {
            return new List<string>();
        }

        var partners = await db.Queryable<FlPartnerDbEntity>()
            .Where(x => !x.IsDeleted)
            .ToListAsync();
        var partnerNameToId = partners
            .Where(x => !string.IsNullOrWhiteSpace(x.PartnerName))
            .GroupBy(x => x.PartnerName!.Trim(), StringComparer.OrdinalIgnoreCase)
            .ToDictionary(g => g.Key, g => g.First().Id, StringComparer.OrdinalIgnoreCase);

        var groups = await db.Queryable<FlGroupDbEntity>()
            .Where(x => !x.IsDeleted)
            .ToListAsync();

        var groupIdSet = new HashSet<string>(StringComparer.Ordinal);
        foreach (var loc in locs)
        {
            var gName = loc.GroupName?.Trim();
            var pName = loc.Partner?.Trim();
            if (string.IsNullOrEmpty(gName) || string.IsNullOrEmpty(pName))
            {
                continue;
            }

            if (!partnerNameToId.TryGetValue(pName, out var partnerId))
            {
                continue;
            }

            var match = groups.FirstOrDefault(g =>
                g.PartnerId == partnerId &&
                string.Equals(g.GroupName?.Trim(), gName, StringComparison.OrdinalIgnoreCase));
            if (match is not null && !string.IsNullOrWhiteSpace(match.Id))
            {
                groupIdSet.Add(match.Id.Trim());
            }
        }

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

    /// <summary>
    /// 解析 Region（<c>fl_group.Id</c>）下全部未删除门店 Id。
    /// </summary>
    public static async Task<List<string>> ResolveLocationIdsFromGroupIdsAsync(
        ISqlSugarClient db,
        IReadOnlyList<string>? groupIds)
    {
        var ids = NormalizeIds(groupIds);
        if (ids.Count == 0)
        {
            return new List<string>();
        }

        var result = new HashSet<string>(StringComparer.Ordinal);
        foreach (var gid in ids)
        {
            var g = await db.Queryable<FlGroupDbEntity>()
                .FirstAsync(x => !x.IsDeleted && x.Id == gid);
            if (g is null)
            {
                continue;
            }

            var partner = await db.Queryable<FlPartnerDbEntity>()
                .FirstAsync(x => !x.IsDeleted && x.Id == g.PartnerId);
            var pName = partner?.PartnerName?.Trim() ?? string.Empty;
            var gName = g.GroupName?.Trim() ?? string.Empty;
            if (string.IsNullOrEmpty(pName) || string.IsNullOrEmpty(gName))
            {
                continue;
            }

            var locIds = await db.Queryable<LocationAggregateRoot>()
                .Where(x => !x.IsDeleted && x.Partner == pName && x.GroupName == gName)
                .Select(x => SqlFunc.ToString(x.Id))
                .ToListAsync();
            foreach (var lid in locIds.Where(x => !string.IsNullOrWhiteSpace(x)))
            {
                result.Add(lid.Trim());
            }
        }

        return result.ToList();
    }

    /// <summary>
    /// 解析 Company（<c>fl_partner.Id</c>）下全部 Region Id（<c>fl_group.Id</c>）。
    /// </summary>
    public static async Task<List<string>> ResolveGroupIdsFromPartnerIdsAsync(
        ISqlSugarClient db,
        IReadOnlyList<string>? partnerIds)
    {
        var ids = NormalizeIds(partnerIds);
        if (ids.Count == 0)
        {
            return new List<string>();
        }

        var result = new HashSet<string>(StringComparer.Ordinal);
        foreach (var pid in ids)
        {
            var groupIds = await db.Queryable<FlGroupDbEntity>()
                .Where(x => !x.IsDeleted && x.PartnerId == pid)
                .Select(x => x.Id)
                .ToListAsync();
            foreach (var gid in groupIds.Where(x => !string.IsNullOrWhiteSpace(x)))
            {
                result.Add(gid.Trim());
            }
        }

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

    /// <summary>
    /// 校验合并后的门店 Id 均存在。
    /// </summary>
    public static async Task ValidateLocationIdsExistAsync(ISqlSugarClient db, IReadOnlyList<string> locationIds)
    {
        var ids = NormalizeIds(locationIds);
        if (ids.Count == 0)
        {
            return;
        }

        foreach (var id in ids)
        {
            if (!Guid.TryParse(id, out _))
            {
                throw new UserFriendlyException("门店Id格式不正确");
            }
        }

        var guidList = ids.Select(Guid.Parse).ToList();
        var existCount = await db.Queryable<LocationAggregateRoot>()
            .Where(x => !x.IsDeleted && guidList.Contains(x.Id))
            .CountAsync();
        if (existCount != ids.Count)
        {
            throw new UserFriendlyException("门店不存在");
        }
    }

    public static List<string> NormalizeIds(IReadOnlyList<string>? raw)
    {
        return raw?
            .Where(x => !string.IsNullOrWhiteSpace(x))
            .Select(x => x.Trim())
            .Distinct(StringComparer.Ordinal)
            .ToList() ?? new List<string>();
    }

    /// <summary>
    /// Company Admin 在其绑定 Company 下新建 Region 后，将该 Region 下已有门店追加写入 <c>userlocation</c>（不删除已有绑定）。
    /// </summary>
    public static async Task TryAutoBindCompanyAdminToNewRegionAsync(
        ISqlSugarClient db,
        ICurrentUser currentUser,
        IGuidGenerator guidGenerator,
        string partnerId,
        string groupName,
        string? creatorId)
    {
        if (currentUser.Id is null || ReportsRoleHelper.IsAdminRole(currentUser))
        {
            return;
        }

        if (!await TeamMemberRoleHelper.IsCompanyAdminUserAsync(db, currentUser))
        {
            return;
        }

        var pid = partnerId.Trim();
        if (!await IsPartnerAllowedForUserAsync(db, currentUser.Id.Value, pid))
        {
            return;
        }

        var locationIds = await ResolveLocationIdsForPartnerAndGroupNameAsync(db, pid, groupName);
        await AppendUserLocationBindingsAsync(db, guidGenerator, currentUser.Id.Value, locationIds, creatorId);
    }

    /// <summary>
    /// Company Admin 在其绑定 Company 下新建门店后，将该门店追加写入 <c>userlocation</c>（空 Region 先建后补门店场景）。
    /// </summary>
    public static async Task TryAutoBindCompanyAdminToNewLocationAsync(
        ISqlSugarClient db,
        ICurrentUser currentUser,
        IGuidGenerator guidGenerator,
        string locationId,
        string? partnerName,
        string? creatorId)
    {
        if (currentUser.Id is null || ReportsRoleHelper.IsAdminRole(currentUser))
        {
            return;
        }

        if (!await TeamMemberRoleHelper.IsCompanyAdminUserAsync(db, currentUser))
        {
            return;
        }

        var partnerIds = await ResolvePartnerIdsFromLocationIdsAsync(
            db, new List<string> { locationId });
        if (partnerIds.Count == 0)
        {
            partnerIds = await ResolvePartnerIdsFromPartnerNamesAsync(db, partnerName);
        }

        if (partnerIds.Count == 0 ||
            !await IsAnyPartnerAllowedForUserAsync(db, currentUser.Id.Value, partnerIds))
        {
            return;
        }

        await AppendUserLocationBindingsAsync(
            db, guidGenerator, currentUser.Id.Value, new List<string> { locationId }, creatorId);
    }

    /// <summary>
    /// 追加 <c>userlocation</c> 绑定（幂等；仅插入当前用户尚未绑定的门店）。
    /// </summary>
    public static async Task<int> AppendUserLocationBindingsAsync(
        ISqlSugarClient db,
        IGuidGenerator guidGenerator,
        Guid userId,
        IReadOnlyList<string> locationIds,
        string? creatorId)
    {
        var wanted = NormalizeIds(locationIds);
        if (wanted.Count == 0)
        {
            return 0;
        }

        foreach (var id in wanted)
        {
            if (!Guid.TryParse(id, out _))
            {
                return 0;
            }
        }

        var guidList = wanted.Select(Guid.Parse).ToList();
        var existCount = await db.Queryable<LocationAggregateRoot>()
            .Where(x => !x.IsDeleted && guidList.Contains(x.Id))
            .CountAsync();
        if (existCount != wanted.Count)
        {
            return 0;
        }

        var existing = await ResolveBoundLocationIdsForUserAsync(db, userId);
        var existingSet = new HashSet<string>(existing, StringComparer.OrdinalIgnoreCase);
        var toInsert = wanted.Where(x => !existingSet.Contains(x)).ToList();
        if (toInsert.Count == 0)
        {
            return 0;
        }

        var now = DateTime.Now;
        var userIdString = TeamMemberListScopeHelper.UserKey(userId);
        var rows = toInsert.Select(locationId => new UserLocationDbEntity
        {
            Id = guidGenerator.Create().ToString(),
            IsDeleted = false,
            CreationTime = now,
            CreatorId = creatorId,
            UserId = userIdString,
            LocationId = locationId,
            ConcurrencyStamp = string.Empty
        }).ToList();

        await db.Insertable(rows).ExecuteCommandAsync();
        return toInsert.Count;
    }

    private static async Task<bool> IsPartnerAllowedForUserAsync(
        ISqlSugarClient db,
        Guid userId,
        string partnerId)
    {
        var boundLocationIds = await ResolveBoundLocationIdsForUserAsync(db, userId);
        if (boundLocationIds.Count == 0)
        {
            return false;
        }

        var allowedPartnerIds = await ResolvePartnerIdsFromLocationIdsAsync(db, boundLocationIds);
        return allowedPartnerIds.Contains(partnerId.Trim(), StringComparer.OrdinalIgnoreCase);
    }

    private static async Task<bool> IsAnyPartnerAllowedForUserAsync(
        ISqlSugarClient db,
        Guid userId,
        IReadOnlyList<string> partnerIds)
    {
        var boundLocationIds = await ResolveBoundLocationIdsForUserAsync(db, userId);
        if (boundLocationIds.Count == 0)
        {
            return false;
        }

        var allowedPartnerIds = await ResolvePartnerIdsFromLocationIdsAsync(db, boundLocationIds);
        var allowedSet = new HashSet<string>(allowedPartnerIds, StringComparer.OrdinalIgnoreCase);
        return partnerIds.Any(id => allowedSet.Contains(id));
    }

    private static async Task<List<string>> ResolvePartnerIdsFromPartnerNamesAsync(
        ISqlSugarClient db,
        string? partnerName)
    {
        var name = partnerName?.Trim();
        if (string.IsNullOrWhiteSpace(name))
        {
            return new List<string>();
        }

        var partners = await db.Queryable<FlPartnerDbEntity>()
            .Where(x => !x.IsDeleted)
            .ToListAsync();
        var match = partners.FirstOrDefault(p =>
            string.Equals(p.PartnerName?.Trim(), name, StringComparison.OrdinalIgnoreCase) ||
            string.Equals(p.Id, name, StringComparison.OrdinalIgnoreCase));
        return match is null || string.IsNullOrWhiteSpace(match.Id)
            ? new List<string>()
            : new List<string> { match.Id.Trim() };
    }

    private static async Task<List<string>> ResolveLocationIdsForPartnerAndGroupNameAsync(
        ISqlSugarClient db,
        string partnerId,
        string groupName)
    {
        var partner = await db.Queryable<FlPartnerDbEntity>()
            .FirstAsync(x => !x.IsDeleted && x.Id == partnerId.Trim());
        if (partner is null)
        {
            return new List<string>();
        }

        var pName = partner.PartnerName?.Trim() ?? string.Empty;
        var gName = groupName.Trim();
        if (string.IsNullOrEmpty(pName) || string.IsNullOrEmpty(gName))
        {
            return new List<string>();
        }

        return await db.Queryable<LocationAggregateRoot>()
            .Where(x => !x.IsDeleted && x.Partner == pName && x.GroupName == gName)
            .Select(x => SqlFunc.ToString(x.Id))
            .ToListAsync();
    }
}