using Admin.NET.Plugin.AiDOP.Entity;

namespace Admin.NET.Plugin.AiDOP.SmartOps;

public sealed class KpiDailyTargetReader : IKpiDailyTargetReader, ITransient
{
    private static readonly string[] Tables =
    [
        "ado_s9_kpi_value_l1_day",
        "ado_s9_kpi_value_l2_day",
        "ado_s9_kpi_value_l3_day",
        "ado_s9_kpi_value_l4_day"
    ];

    private readonly ISqlSugarClient _db;

    public KpiDailyTargetReader(ISqlSugarClient db) => _db = db;

    public Task<decimal?> GetCurrentTargetAsync(long tenantId, long factoryId, string moduleCode, string metricCode, DateTime bizDate, CancellationToken ct = default)
        => QueryAsync(tenantId, factoryId, moduleCode, metricCode, bizDate, current: true, ct);

    public Task<decimal?> GetPriorTargetAsync(long tenantId, long factoryId, string moduleCode, string metricCode, DateTime bizDate, CancellationToken ct = default)
        => QueryAsync(tenantId, factoryId, moduleCode, metricCode, bizDate, current: false, ct);

    private async Task<decimal?> QueryAsync(
        long tenantId, long factoryId, string moduleCode, string metricCode, DateTime bizDate, bool current, CancellationToken ct)
    {
        foreach (var table in Tables)
        {
            var sql = current
                ? $"""
                   SELECT target_value FROM {table}
                   WHERE tenant_id=@TenantId AND factory_id=@FactoryId AND module_code=@ModuleCode
                     AND metric_code=@MetricCode AND biz_date=@BizDate AND is_deleted=0
                     AND target_value IS NOT NULL AND target_value > 0
                   ORDER BY id LIMIT 1
                   """
                : $"""
                   SELECT target_value FROM {table}
                   WHERE tenant_id=@TenantId AND factory_id=@FactoryId AND module_code=@ModuleCode
                     AND metric_code=@MetricCode AND biz_date<@BizDate AND is_deleted=0
                     AND target_value IS NOT NULL AND target_value > 0
                   ORDER BY biz_date DESC, id DESC LIMIT 1
                   """;
            var val = await _db.Ado.SqlQuerySingleAsync<decimal?>(sql,
                new SugarParameter("@TenantId", tenantId),
                new SugarParameter("@FactoryId", factoryId),
                new SugarParameter("@ModuleCode", moduleCode),
                new SugarParameter("@MetricCode", metricCode),
                new SugarParameter("@BizDate", bizDate.Date));
            if (val is > 0)
                return val;
        }
        return null;
    }
}