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 = "All Regions";

    public const string AllLocationsDisplay = "All Locations";

    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 ??? Id ???
    /// </summary>
    public static async Task<LabelRegionScopeSaveResult> ResolveScopeForSaveAsync(
        ISqlSugarClient db,
        string? appliedRegionType,
        IReadOnlyList<string>? regionIds,
        IReadOnlyList<string>? groupIds,
        string? locationId,
        IReadOnlyList<string>? locationIds)
    {
        var mergedRegionIds = NormalizeRegionIds(regionIds, groupIds);
        var type = (appliedRegionType ?? AppliedRegionAll).Trim().ToUpperInvariant();
        var hasScopeArrays = regionIds is not null || groupIds is not null || locationIds is not null;

        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?");
        }

        var explicitLocationIds = MergeExplicitLocationIds(locationId, locationIds);

        if (string.Equals(type, AppliedRegionAll, StringComparison.OrdinalIgnoreCase))
        {
            if (explicitLocationIds.Count > 0)
            {
                await LocationScopeBindingHelper.ValidateLocationIdsExistAsync(db, explicitLocationIds);
            }

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

        if (mergedRegionIds.Count == 0 && explicitLocationIds.Count == 0)
        {
            throw new UserFriendlyException("??? Region?regionIds??/????locationIds?");
        }

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

        var mergedLocations = await LocationScopeBindingHelper.MergeToLocationIdsAsync(
            db, (IReadOnlyList<string>?)null, mergedRegionIds, explicitLocationIds);

        if (mergedLocations.Count == 0)
        {
            throw new UserFriendlyException("? Region ???????????? Region ? locationIds");
        }

        if (explicitLocationIds.Count > 0)
        {
            var invalid = explicitLocationIds
                .Where(id => !mergedLocations.Contains(id, StringComparer.Ordinal))
                .ToList();
            if (invalid.Count > 0)
            {
                throw new UserFriendlyException("???????? Region ???");
            }
        }

        await LocationScopeBindingHelper.ValidateLocationIdsExistAsync(db, mergedLocations);

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

    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>(),
                GroupIds = new List<string>()
            };
        }

        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())
            : "?";

        return new LabelRegionScopeDisplay
        {
            Region = regionText,
            RegionIds = regionIds,
            GroupIds = 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>()
            };
        }

        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())
            : "?";

        return new LabelLocationScopeDisplay
        {
            Location = locationText,
            LocationIds = locationIds.ToList()
        };
    }

    /// <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>? scoped ?????<c>fl_label_location</c> ???? <c>LocationId</c>??</summary>
    public static ISugarQueryable<FlLabelDbEntity> ApplyLabelLocationListFilter(
        ISqlSugarClient db,
        ISugarQueryable<FlLabelDbEntity> query,
        List<string> scopedLocationIds,
        LabelRegionSchemaHelper.LabelRegionSchemaStatus schema)
    {
        if (scopedLocationIds.Count == 0)
        {
            return query.Where(_ => false);
        }

        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)
            {
                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.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)
    {
        if (regionIds.Count == 0)
        {
            return;
        }

        var count = await db.Queryable<FlGroupDbEntity>()
            .Where(g => !g.IsDeleted && regionIds.Contains(g.Id))
            .CountAsync();
        if (count != regionIds.Count)
        {
            throw new UserFriendlyException("????? Region Id???????");
        }
    }
}