using Admin.NET.Core;
using Admin.NET.Plugin.AiDOP.Dto.SmartOps;
using Admin.NET.Plugin.AiDOP.Entity;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.SmartOps;

/// <summary>
/// KPI 计算配置服务：CRUD + 校验 + 试算 + 发布/停用/激活（发布状态机，单生效版本事务保证）。
/// 租户显式控制（ClearFilter）：配置落在"分发器运行时查找的租户"= KPI 数据租户（S5=AidopSourceTenantMap 解析），
/// 而非 API 调用者 JWT 租户；否则运行时找不到配置。SqlScript 属高危配置，接口须登录鉴权（Controller 层）。
/// </summary>
public sealed class AdoSmartOpsKpiCalcConfigService : ITransient
{
    private const string EngineLegacyCode = "LEGACY_CODE";
    private const string EngineConfigSql = "CONFIG_SQL";
    private const string EngineLegacyTvf = "LEGACY_TVF";
    private const string StatusDraft = "DRAFT";
    private const string StatusPublished = "PUBLISHED";
    private const string StatusRetired = "RETIRED";
    private static readonly string[] TenantScopedModules = { "S5", "S6", "S7" };

    private readonly ISqlSugarClient _db;
    private readonly KpiSqlReadOnlyExecutor _executor;

    public AdoSmartOpsKpiCalcConfigService(ISqlSugarClient db, KpiSqlReadOnlyExecutor executor)
    {
        _db = db;
        _executor = executor;
    }

    /// <summary>解析配置应归属的租户（= 运行时 KPI 落库租户）。S5/S6/S7 走 T8 账套映射（pbxfxp→AIDOP）。</summary>
    public static long ResolveKpiTenantId(string moduleCode)
    {
        if (TenantScopedModules.Contains((moduleCode ?? "").Trim().ToUpperInvariant()))
            return AidopSourceTenantMap.ResolveTenantId("pbxfxp");
        return SqlSugarConst.DefaultTenantId;
    }

    private ISugarQueryable<AdoSmartOpsKpiCalcConfig> Query() =>
        _db.Queryable<AdoSmartOpsKpiCalcConfig>().ClearFilter<ITenantIdFilter>();

    /// <summary>某 KPI 的全部版本（按版本号倒序）。</summary>
    public async Task<List<KpiCalcConfigDto>> GetByMetricAsync(string metricCode, string moduleCode)
    {
        var tenantId = ResolveKpiTenantId(moduleCode);
        var list = await Query()
            .Where(x => x.MetricCode == metricCode && x.TenantId == tenantId)
            .OrderBy(x => x.VersionNo, OrderByType.Desc)
            .ToListAsync();
        return list.Select(ToDto).ToList();
    }

    /// <summary>当前生效配置（PUBLISHED + IsCurrent），无则 null（= 走 legacy）。</summary>
    public async Task<AdoSmartOpsKpiCalcConfig?> GetActiveAsync(long tenantId, string metricCode)
    {
        return await Query()
            .Where(x => x.MetricCode == metricCode && x.TenantId == tenantId
                        && x.IsCurrent && x.PublishStatus == StatusPublished)
            .FirstAsync();
    }

    /// <summary>新增草稿（VersionNo = 现有最大 + 1）。</summary>
    public async Task<KpiCalcConfigDto> CreateDraftAsync(KpiCalcConfigUpsertDto dto, string operatorName)
    {
        ValidateEngineType(dto.CalcEngineType);
        var tenantId = ResolveKpiTenantId(dto.ModuleCode);
        var maxVer = await Query()
            .Where(x => x.MetricCode == dto.MetricCode && x.TenantId == tenantId)
            .MaxAsync(x => (int?)x.VersionNo) ?? 0;

        var entity = new AdoSmartOpsKpiCalcConfig
        {
            TenantId = tenantId,
            MetricCode = dto.MetricCode,
            ModuleCode = dto.ModuleCode,
            VersionNo = maxVer + 1,
            CalcEngineType = dto.CalcEngineType,
            DataSourceCode = dto.DataSourceCode,
            SqlScript = dto.SqlScript,
            SqlParametersJson = dto.SqlParametersJson,
            TimeoutSeconds = NormalizeTimeout(dto.TimeoutSeconds),
            PublishStatus = StatusDraft,
            IsCurrent = false,
            Remark = dto.Remark,
            CreatedBy = operatorName,
            CreatedAt = DateTime.Now,
        };
        entity.Id = await _db.Insertable(entity).ExecuteReturnBigIdentityAsync();
        return ToDto(entity);
    }

    /// <summary>编辑草稿（仅 DRAFT 可改；已发布/停用版本不可改，须新建版本）。</summary>
    public async Task UpdateDraftAsync(long id, KpiCalcConfigUpsertDto dto, string operatorName)
    {
        var entity = await Query().Where(x => x.Id == id).FirstAsync()
            ?? throw Oops.Bah("配置版本不存在");
        if (entity.PublishStatus != StatusDraft)
            throw Oops.Bah("仅草稿(DRAFT)可编辑；已发布/停用版本请新建版本");
        ValidateEngineType(dto.CalcEngineType);
        entity.CalcEngineType = dto.CalcEngineType;
        entity.DataSourceCode = dto.DataSourceCode;
        entity.SqlScript = dto.SqlScript;
        entity.SqlParametersJson = dto.SqlParametersJson;
        entity.TimeoutSeconds = NormalizeTimeout(dto.TimeoutSeconds);
        entity.Remark = dto.Remark;
        entity.UpdatedBy = operatorName;
        entity.UpdatedAt = DateTime.Now;
        await _db.Updateable(entity).ExecuteCommandAsync();
    }

    /// <summary>删除草稿（仅 DRAFT）。</summary>
    public async Task DeleteAsync(long id)
    {
        var entity = await Query().Where(x => x.Id == id).FirstAsync()
            ?? throw Oops.Bah("配置版本不存在");
        if (entity.PublishStatus != StatusDraft)
            throw Oops.Bah("仅草稿(DRAFT)可删除；已发布版本请用停用");
        await _db.Deleteable<AdoSmartOpsKpiCalcConfig>().Where(x => x.Id == id).ExecuteCommandAsync();
    }

    /// <summary>SQL 安全校验（多层非正则）。</summary>
    public KpiSqlValidateResultDto Validate(string? sql)
    {
        var r = KpiSqlSecurityValidator.Validate(sql);
        return new KpiSqlValidateResultDto
        {
            Ok = r.Ok,
            ValidationStatus = r.Ok ? "VALID" : "INVALID",
            ErrorCode = r.ErrorCode,
            ErrorMessage = r.ErrorMessage,
            ReferencedTables = r.ReferencedTables,
        };
    }

    /// <summary>试算（服务端重校验 + 只读执行；不写正式 KPI 值）。</summary>
    public async Task<KpiSqlPreviewResultDto> PreviewAsync(KpiSqlPreviewDto dto, CancellationToken ct)
    {
        var bizDate = (dto.BizDate ?? DateTime.Today.AddDays(-1)).Date;
        var res = new KpiSqlPreviewResultDto
        {
            MetricCode = dto.MetricCode,
            EngineType = EngineConfigSql,
            DataSourceCode = dto.DataSourceCode,
            BizDate = bizDate.ToString("yyyy-MM-dd"),
        };

        var val = KpiSqlSecurityValidator.Validate(dto.SqlScript);
        res.ValidationStatus = val.Ok ? "VALID" : "INVALID";
        if (!val.Ok)
        {
            res.Ok = false;
            res.ResultStatus = "FAILED";
            res.ErrorCode = val.ErrorCode;
            res.ErrorMessage = val.ErrorMessage;
            return res;
        }

        var tenantId = ResolveKpiTenantId(dto.ModuleCode);
        var pars = new KpiSqlRunParams
        {
            TenantId = tenantId,
            FactoryId = 1,
            ModuleCode = dto.ModuleCode,
            MetricCode = dto.MetricCode,
            BizDate = bizDate,
            PeriodStart = bizDate,
            PeriodEnd = bizDate.AddDays(1).AddSeconds(-1),
            SourceZtid = "pbxfxp",
        };
        var exec = await _executor.ExecuteAsync(dto.DataSourceCode, dto.SqlScript!, dto.TimeoutSeconds, true, pars, ct);

        res.Ok = exec.Status != "FAILED";
        res.ResultStatus = exec.Status;
        res.DurationMs = exec.DurationMs;
        res.RowCount = exec.RowCount;
        res.MetricValue = exec.MetricValue;
        res.NumeratorValue = exec.NumeratorValue;
        res.DenominatorValue = exec.DenominatorValue;
        res.ResultMessage = exec.ResultMessage;
        res.ErrorCode = exec.ErrorCode;
        res.ErrorMessage = exec.ErrorMessage;
        res.ExecutedParameters =
            $"tenant_id={pars.TenantId}, factory_id={pars.FactoryId}, module_code={pars.ModuleCode}, " +
            $"metric_code={pars.MetricCode}, biz_date={pars.BizDate:yyyy-MM-dd}, ztid={pars.SourceZtid}";
        return res;
    }

    /// <summary>发布：事务内校验→旧 current 置 0→本版 PUBLISHED+IsCurrent=1（保证单生效版本）。</summary>
    public async Task PublishAsync(long id, string operatorName)
    {
        var entity = await Query().Where(x => x.Id == id).FirstAsync()
            ?? throw Oops.Bah("配置版本不存在");
        if (entity.PublishStatus == StatusRetired)
            throw Oops.Bah("已停用版本不可发布，请新建版本");

        // CONFIG_SQL 发布前必须再过一次安全校验
        if (entity.CalcEngineType == EngineConfigSql)
        {
            var val = KpiSqlSecurityValidator.Validate(entity.SqlScript);
            if (!val.Ok) throw Oops.Bah($"SQL 安全校验未通过：{val.ErrorCode} {val.ErrorMessage}");
            if (string.IsNullOrWhiteSpace(entity.DataSourceCode))
                throw Oops.Bah("CONFIG_SQL 必须指定数据源");
        }

        var tenantId = entity.TenantId;
        var tran = await _db.AsTenant().UseTranAsync(async () =>
        {
            await _db.Updateable<AdoSmartOpsKpiCalcConfig>()
                .SetColumns(x => new AdoSmartOpsKpiCalcConfig { IsCurrent = false })
                .Where(x => x.MetricCode == entity.MetricCode && x.TenantId == tenantId && x.Id != id && x.IsCurrent)
                .ExecuteCommandAsync();
            await _db.Updateable<AdoSmartOpsKpiCalcConfig>()
                .SetColumns(x => new AdoSmartOpsKpiCalcConfig
                {
                    PublishStatus = StatusPublished, IsCurrent = true,
                    PublishedBy = operatorName, PublishedAt = DateTime.Now,
                })
                .Where(x => x.Id == id)
                .ExecuteCommandAsync();
        });
        if (!tran.IsSuccess) throw tran.ErrorException;
    }

    /// <summary>激活/回滚：把某历史版本设为当前生效（其它同 KPI 版本 IsCurrent 置 0），事务内。仅对已发布版本。</summary>
    public async Task ActivateAsync(long id, string operatorName)
    {
        var entity = await Query().Where(x => x.Id == id).FirstAsync()
            ?? throw Oops.Bah("配置版本不存在");
        if (entity.PublishStatus != StatusPublished)
            throw Oops.Bah("只能激活已发布(PUBLISHED)版本；回滚是激活历史已发布版本，不是复制重发");

        var tenantId = entity.TenantId;
        var tran = await _db.AsTenant().UseTranAsync(async () =>
        {
            await _db.Updateable<AdoSmartOpsKpiCalcConfig>()
                .SetColumns(x => new AdoSmartOpsKpiCalcConfig { IsCurrent = false })
                .Where(x => x.MetricCode == entity.MetricCode && x.TenantId == tenantId && x.Id != id && x.IsCurrent)
                .ExecuteCommandAsync();
            await _db.Updateable<AdoSmartOpsKpiCalcConfig>()
                .SetColumns(x => new AdoSmartOpsKpiCalcConfig { IsCurrent = true, UpdatedBy = operatorName, UpdatedAt = DateTime.Now })
                .Where(x => x.Id == id)
                .ExecuteCommandAsync();
        });
        if (!tran.IsSuccess) throw tran.ErrorException;
    }

    /// <summary>停用（RETIRED，IsCurrent=0）。停用当前生效版本后无 current → 运行时回落 legacy。</summary>
    public async Task RetireAsync(long id, string operatorName)
    {
        var entity = await Query().Where(x => x.Id == id).FirstAsync()
            ?? throw Oops.Bah("配置版本不存在");
        await _db.Updateable<AdoSmartOpsKpiCalcConfig>()
            .SetColumns(x => new AdoSmartOpsKpiCalcConfig
            {
                PublishStatus = StatusRetired, IsCurrent = false,
                RetiredBy = operatorName, RetiredAt = DateTime.Now,
            })
            .Where(x => x.Id == id)
            .ExecuteCommandAsync();
    }

    /// <summary>已登记数据源列表（第一版仅本地中台库）。</summary>
    public List<object> ListDataSources() => new()
    {
        new { code = KpiSqlReadOnlyExecutor.LocalDataSourceCode, name = "本地中台库（aidopdev / mdp_std_* / dwd_*）", readOnly = true },
    };

    private static void ValidateEngineType(string engine)
    {
        if (engine != EngineLegacyCode && engine != EngineConfigSql && engine != EngineLegacyTvf)
            throw Oops.Bah($"非法引擎类型：{engine}（LEGACY_CODE/CONFIG_SQL/LEGACY_TVF）");
    }

    private static int NormalizeTimeout(int t) =>
        t <= 0 ? 30 : Math.Min(t, KpiSqlReadOnlyExecutor.SystemMaxTimeoutSeconds);

    private static KpiCalcConfigDto ToDto(AdoSmartOpsKpiCalcConfig e) => new()
    {
        Id = e.Id, TenantId = e.TenantId, MetricCode = e.MetricCode, ModuleCode = e.ModuleCode,
        VersionNo = e.VersionNo, CalcEngineType = e.CalcEngineType, DataSourceCode = e.DataSourceCode,
        SqlScript = e.SqlScript, SqlParametersJson = e.SqlParametersJson, TimeoutSeconds = e.TimeoutSeconds,
        PublishStatus = e.PublishStatus, IsCurrent = e.IsCurrent, Remark = e.Remark,
        CreatedBy = e.CreatedBy, CreatedAt = e.CreatedAt, PublishedBy = e.PublishedBy, PublishedAt = e.PublishedAt,
    };
}