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; }

        /// <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'
                """);

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

        return _cached;
    }

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

    /// <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>
    /// 已迁移 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() });
        }
    }
}