using SqlSugar;

namespace FoodLabeling.Application.Helpers;

/// <summary>
/// 探测 <c>fl_label.AppliedRegionType</c> / <c>fl_label_region</c> 是否已落库，兼容未执行 DDL 的环境。
/// </summary>
public static class LabelRegionSchemaHelper
{
    private static LabelRegionSchemaStatus? _cached;

    public sealed class LabelRegionSchemaStatus
    {
        public bool HasAppliedRegionTypeColumn { get; init; }

        public bool HasLabelRegionTable { get; init; }

        public bool IsFullSchema => HasAppliedRegionTypeColumn && HasLabelRegionTable;
    }

    public static async Task<LabelRegionSchemaStatus> GetStatusAsync(ISqlSugarClient db)
    {
        if (_cached is not null)
        {
            return _cached;
        }

        var colCount = await db.Ado.GetIntAsync(
            """
            SELECT COUNT(*)
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'fl_label'
              AND COLUMN_NAME = 'AppliedRegionType'
            """);

        var tableCount = await db.Ado.GetIntAsync(
            """
            SELECT COUNT(*)
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'fl_label_region'
            """);

        _cached = new LabelRegionSchemaStatus
        {
            HasAppliedRegionTypeColumn = colCount > 0,
            HasLabelRegionTable = tableCount > 0
        };

        return _cached;
    }

    /// <summary>单元测试或切换库后调用。</summary>
    public static void ResetCacheForTests() => _cached = null;

    public static async Task<string> GetAppliedRegionTypeForLabelAsync(
        ISqlSugarClient db,
        string labelId,
        string? fallback = null)
    {
        var map = await GetAppliedRegionTypesForLabelsAsync(db, new List<string> { labelId });
        if (map.TryGetValue(labelId, out var type) && !string.IsNullOrWhiteSpace(type))
        {
            return type.Trim();
        }

        return string.IsNullOrWhiteSpace(fallback)
            ? LabelRegionScopeHelper.AppliedRegionSpecified
            : fallback.Trim();
    }

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

        foreach (var id in labelIds)
        {
            result[id] = LabelRegionScopeHelper.AppliedRegionSpecified;
        }

        var status = await GetStatusAsync(db);
        if (!status.HasAppliedRegionTypeColumn)
        {
            return result;
        }

        var ids = labelIds.Where(x => !string.IsNullOrWhiteSpace(x)).Select(x => x.Trim()).Distinct().ToList();
        if (ids.Count == 0)
        {
            return result;
        }

        var rows = await db.Ado.SqlQueryAsync<LabelAppliedRegionRow>(
            "SELECT Id, AppliedRegionType FROM fl_label WHERE Id IN (@ids)",
            new { ids });

        foreach (var row in rows)
        {
            if (string.IsNullOrWhiteSpace(row.Id))
            {
                continue;
            }

            result[row.Id] = string.IsNullOrWhiteSpace(row.AppliedRegionType)
                ? LabelRegionScopeHelper.AppliedRegionSpecified
                : row.AppliedRegionType.Trim();
        }

        return result;
    }

    public static async Task SetAppliedRegionTypeAsync(
        ISqlSugarClient db,
        string labelId,
        string appliedRegionType)
    {
        var status = await GetStatusAsync(db);
        if (!status.HasAppliedRegionTypeColumn || string.IsNullOrWhiteSpace(labelId))
        {
            return;
        }

        await db.Ado.ExecuteCommandAsync(
            "UPDATE fl_label SET AppliedRegionType = @type WHERE Id = @id",
            new { type = appliedRegionType.Trim(), id = labelId.Trim() });
    }

    private sealed class LabelAppliedRegionRow
    {
        public string Id { get; set; } = string.Empty;

        public string? AppliedRegionType { get; set; }
    }
}