namespace Admin.NET.Plugin.AiDOP.SmartOps;

public sealed class S1KpiDailySnapshotStore : IS1KpiDailySnapshotStore, ITransient
{
    private readonly ISqlSugarClient _db;

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

    public async Task ExecuteInTransactionAsync(Func<Task> action, CancellationToken ct = default)
    {
        await _db.Ado.BeginTranAsync();
        try
        {
            await action();
            await _db.Ado.CommitTranAsync();
        }
        catch
        {
            await _db.Ado.RollbackTranAsync();
            throw;
        }
    }

    public async Task<DateTime?> GetMaxBizDateAsync(int level, long tenantId, long factoryId, string moduleCode, CancellationToken ct = default)
    {
        var table = Table(level);
        return await _db.Ado.SqlQuerySingleAsync<DateTime?>(
            $"""
             SELECT MAX(biz_date) FROM {table}
             WHERE tenant_id=@TenantId AND factory_id=@FactoryId
               AND module_code=@ModuleCode AND is_deleted=0
             """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@FactoryId", factoryId),
            new SugarParameter("@ModuleCode", moduleCode));
    }

    public async Task<List<S1KpiDailySnapshotRow>> ListLatestAsync(int level, long tenantId, long factoryId, DateTime bizDate, string moduleCode, CancellationToken ct = default)
    {
        var table = Table(level);
        return await _db.Ado.SqlQueryAsync<S1KpiDailySnapshotRow>(
            $"""
             SELECT metric_code AS MetricCode, biz_date AS BizDate, metric_value AS MetricValue
             FROM {table}
             WHERE tenant_id=@TenantId AND factory_id=@FactoryId
               AND module_code=@ModuleCode AND biz_date=@BizDate AND is_deleted=0
             """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@FactoryId", factoryId),
            new SugarParameter("@ModuleCode", moduleCode),
            new SugarParameter("@BizDate", bizDate.Date));
    }

    public async Task<int> UpdateTargetsAsync(
        int level,
        long tenantId,
        long factoryId,
        DateTime bizDate,
        string metricCode,
        KpiTargetSnapshot snap,
        string moduleCode,
        CancellationToken ct = default)
    {
        var table = Table(level);
        var pars = new List<SugarParameter>
        {
            new("@TenantId", tenantId),
            new("@FactoryId", factoryId),
            new("@ModuleCode", moduleCode),
            new("@BizDate", bizDate.Date),
            new("@MetricCode", metricCode)
        };
        pars.AddRange(KpiTargetSnapshotSql.Parameters(snap));
        return await _db.Ado.ExecuteCommandAsync(
            $"""
             UPDATE {table}
             SET target_value=@TargetValue,
                 target_config_id=@TargetConfigId,
                 target_source=@TargetSource,
                 target_resolved_at=@TargetResolvedAt,
                 update_time=CURRENT_TIMESTAMP(3)
             WHERE tenant_id=@TenantId AND factory_id=@FactoryId
               AND module_code=@ModuleCode AND biz_date=@BizDate
               AND metric_code=@MetricCode AND is_deleted=0
             """,
            pars.ToArray());
    }

    private static string Table(int level) => level switch
    {
        1 => "ado_s9_kpi_value_l1_day",
        2 => "ado_s9_kpi_value_l2_day",
        3 => "ado_s9_kpi_value_l3_day",
        4 => "ado_s9_kpi_value_l4_day",
        _ => throw new ArgumentOutOfRangeException(nameof(level))
    };
}