using SqlSugar;

namespace FoodLabeling.Application.Helpers;

/// <summary>
/// 探测标签模板 Company/Region scope 列与关联表是否已迁移（<c>fl_label_template_scope.sql</c>）。
/// 未迁移时 ORM 实体不含 <c>AppliedPartnerType</c>/<c>AppliedRegionType</c>，读写走 raw SQL 或关联表推断。
/// </summary>
public static class LabelTemplateScopeSchemaHelper
{
    private static LabelTemplateScopeSchemaStatus? _cached;

    public sealed class LabelTemplateScopeSchemaStatus
    {
        public bool HasAppliedPartnerTypeColumn { get; init; }

        public bool HasAppliedRegionTypeColumn { get; init; }

        public bool HasPartnerScopeTable { get; init; }

        public bool HasRegionScopeTable { get; init; }

        public bool HasBorderTypeColumn { get; init; }

        public bool HasContentsColumn { get; init; }

        /// <summary>partner/region 关联表均已创建（用于多选明细）。</summary>
        public bool HasPartnerRegionScopeTables => HasPartnerScopeTable && HasRegionScopeTable;

        /// <summary>列 + 关联表均已迁移。</summary>
        public bool IsFullSchema =>
            HasAppliedPartnerTypeColumn
            && HasAppliedRegionTypeColumn
            && HasPartnerRegionScopeTables;
    }

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

        try
        {
            var partnerColCount = await db.Ado.GetIntAsync(
                """
                SELECT COUNT(*)
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = 'fl_label_template'
                  AND COLUMN_NAME = 'AppliedPartnerType'
                """);

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

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

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

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

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

            _cached = new LabelTemplateScopeSchemaStatus
            {
                HasAppliedPartnerTypeColumn = partnerColCount > 0,
                HasAppliedRegionTypeColumn = regionColCount > 0,
                HasPartnerScopeTable = partnerTableCount > 0,
                HasRegionScopeTable = regionTableCount > 0,
                HasBorderTypeColumn = borderColCount > 0,
                HasContentsColumn = contentsColCount > 0
            };
        }
        catch
        {
            _cached = new LabelTemplateScopeSchemaStatus();
        }

        return _cached;
    }

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

    /// <summary>清除缓存并重新探测 schema（应用启动早于迁移脚本执行后调用）。</summary>
    public static async Task<LabelTemplateScopeSchemaStatus> RefreshStatusAsync(ISqlSugarClient db)
    {
        _cached = null;
        return await GetStatusAsync(db);
    }

    /// <summary>
    /// 是否已创建 <c>fl_label_template_partner</c> 与 <c>fl_label_template_region</c>。
    /// </summary>
    public static async Task<bool> HasPartnerRegionScopeTablesAsync(ISqlSugarClient db)
    {
        var status = await GetStatusAsync(db);
        return status.HasPartnerRegionScopeTables;
    }

    /// <summary>
    /// 是否已创建 <c>fl_label_template_partner</c>；缓存未命中时刷新后再探测。
    /// </summary>
    public static async Task<bool> EnsurePartnerScopeTableAsync(ISqlSugarClient db, bool needsPartnerRows)
    {
        var status = await GetStatusAsync(db);
        if (status.HasPartnerScopeTable)
        {
            return true;
        }

        if (!needsPartnerRows)
        {
            return false;
        }

        status = await RefreshStatusAsync(db);
        return status.HasPartnerScopeTable;
    }

    /// <summary>
    /// 是否已创建 <c>fl_label_template_region</c>；缓存未命中时刷新后再探测。
    /// </summary>
    public static async Task<bool> EnsureRegionScopeTableAsync(ISqlSugarClient db, bool needsRegionRows)
    {
        var status = await GetStatusAsync(db);
        if (status.HasRegionScopeTable)
        {
            return true;
        }

        if (!needsRegionRows)
        {
            return false;
        }

        status = await RefreshStatusAsync(db);
        return status.HasRegionScopeTable;
    }

    /// <summary>
    /// 已迁移 scope 列时，写入 <c>AppliedPartnerType</c> / <c>AppliedRegionType</c>（未迁移则 no-op）。
    /// </summary>
    public static async Task SetAppliedScopeTypesAsync(
        ISqlSugarClient db,
        string templateId,
        string appliedPartnerType,
        string appliedRegionType)
    {
        if (string.IsNullOrWhiteSpace(templateId))
        {
            return;
        }

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

        if (status.HasAppliedPartnerTypeColumn && status.HasAppliedRegionTypeColumn)
        {
            await db.Ado.ExecuteCommandAsync(
                """
                UPDATE fl_label_template
                SET AppliedPartnerType = @partnerType,
                    AppliedRegionType = @regionType
                WHERE Id = @id
                """,
                new
                {
                    partnerType = appliedPartnerType.Trim(),
                    regionType = appliedRegionType.Trim(),
                    id = templateId.Trim()
                });
            return;
        }

        if (status.HasAppliedPartnerTypeColumn)
        {
            await db.Ado.ExecuteCommandAsync(
                "UPDATE fl_label_template SET AppliedPartnerType = @type WHERE Id = @id",
                new { type = appliedPartnerType.Trim(), id = templateId.Trim() });
        }

        if (status.HasAppliedRegionTypeColumn)
        {
            await db.Ado.ExecuteCommandAsync(
                "UPDATE fl_label_template SET AppliedRegionType = @type WHERE Id = @id",
                new { type = appliedRegionType.Trim(), id = templateId.Trim() });
        }
    }

    /// <summary>已迁移 <c>BorderType</c> 列时写入（未迁移则 no-op）。</summary>
    public static async Task SetBorderTypeAsync(ISqlSugarClient db, string templateId, string borderType)
    {
        if (string.IsNullOrWhiteSpace(templateId))
        {
            return;
        }

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

        await db.Ado.ExecuteCommandAsync(
            "UPDATE fl_label_template SET BorderType = @border WHERE Id = @id",
            new { border = borderType.Trim(), id = templateId.Trim() });
    }

    /// <summary>读取模板外框类型；列不存在时返回 <c>none</c>。</summary>
    public static async Task<string> GetBorderTypeForTemplateAsync(ISqlSugarClient db, string templateId)
    {
        if (string.IsNullOrWhiteSpace(templateId))
        {
            return "none";
        }

        var status = await GetStatusAsync(db);
        if (!status.HasBorderTypeColumn)
        {
            return "none";
        }

        var value = await db.Ado.GetScalarAsync(
            "SELECT BorderType FROM fl_label_template WHERE Id = @id LIMIT 1",
            new { id = templateId.Trim() });

        var text = value?.ToString()?.Trim();
        return string.IsNullOrWhiteSpace(text) ? "none" : text;
    }

    /// <summary>批量读取模板 Contents；列不存在时返回空字典。</summary>
    public static async Task<Dictionary<string, string>> GetContentsMapAsync(
        ISqlSugarClient db,
        IReadOnlyList<string> templateIds)
    {
        var result = new Dictionary<string, string>(StringComparer.Ordinal);
        if (templateIds.Count == 0)
        {
            return result;
        }

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

        var rows = await db.Ado.SqlQueryAsync<ContentsRow>(
            """
            SELECT Id, Contents
            FROM fl_label_template
            WHERE Id IN (@ids)
            """,
            new { ids = templateIds.ToArray() });

        foreach (var row in rows)
        {
            var text = row.Contents?.Trim();
            if (!string.IsNullOrWhiteSpace(text))
            {
                result[row.Id] = text;
            }
        }

        return result;
    }

    /// <summary>读取单条模板 Contents；列不存在或未写入时返回 null。</summary>
    public static async Task<string?> GetContentsForTemplateAsync(ISqlSugarClient db, string templateId)
    {
        if (string.IsNullOrWhiteSpace(templateId))
        {
            return null;
        }

        var map = await GetContentsMapAsync(db, new[] { templateId.Trim() });
        return map.TryGetValue(templateId.Trim(), out var text) ? text : null;
    }

    /// <summary>已迁移 <c>Contents</c> 列时写入（未迁移则 no-op）。</summary>
    public static async Task SetContentsAsync(ISqlSugarClient db, string templateId, string? contents)
    {
        if (string.IsNullOrWhiteSpace(templateId))
        {
            return;
        }

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

        await db.Ado.ExecuteCommandAsync(
            "UPDATE fl_label_template SET Contents = @contents WHERE Id = @id",
            new { contents = contents?.Trim() ?? string.Empty, id = templateId.Trim() });
    }

    private sealed class ContentsRow
    {
        public string Id { get; init; } = string.Empty;

        public string? Contents { get; init; }
    }
}