package reconciliation import ( "bytes" "context" "encoding/csv" "encoding/json" "errors" "fmt" "reflect" "slices" "sort" "strconv" "time" "github.com/pingcap/dbaas/billing2/billing_errors" "github.com/pingcap/dbaas/billing2/config" "github.com/pingcap/dbaas/billing2/constant" "github.com/pingcap/dbaas/billing2/db" "github.com/pingcap/dbaas/billing2/db/entity" "github.com/pingcap/dbaas/billing2/forms" "github.com/pingcap/dbaas/billing2/handler/awshandler" "github.com/pingcap/dbaas/billing2/handler/centralhandler" "github.com/pingcap/dbaas/billing2/handler/paymenthandler" "github.com/pingcap/dbaas/billing2/model/billing" "github.com/pingcap/dbaas/billing2/model/billingconfig" "github.com/pingcap/dbaas/billing2/model/costexplorer" "github.com/pingcap/dbaas/billing2/model/marketing" "github.com/pingcap/dbaas/billing2/model/payment" "github.com/pingcap/dbaas/billing2/model/payment/paymentsub" "github.com/pingcap/dbaas/billing2/utils/timeutils" "github.com/pingcap/log" "github.com/shopspring/decimal" "go.uber.org/zap" "gorm.io/gorm" "gorm.io/gorm/clause" ) type ARTableRow struct { TenantID string PartnerID string PaymentCode string PaymentType string BillMonth string StartDate string EndDate string StripeAmount []string StripeTaxRate []string StripeAmountIncludingTax []string StripeInvoiceNumber []string WireTransferAmount string MarketplaceAmount string PrepaymentCredits string TotalAmount string InvoiceAmount string StripeInvoice []string StripeInvoicePaid []bool StripeInvoiceFinalizedDate []string StripeInvoicePaidDate []string RawServerlessAmount string RawDedicatedAmount string RawStarterAmount string RawEssentialAmount string RawSupportAmount string RawPremiumAmount string RawLakeAmount string RawBYOCAmount string RawServerlessProgramCreditsAmount string RawDedicatedProgramCreditsAmount string RawStarterProgramCreditsAmount string RawEssentialProgramCreditsAmount string RawSupportProgramCreditsAmount string RawPremiumProgramCreditsAmount string RawLakeProgramCreditsAmount string RawBYOCProgramCreditsAmount string RawServerlessRedeemCreditsAmount string RawDedicatedRedeemCreditsAmount string RawStarterRedeemCreditsAmount string RawEssentialRedeemCreditsAmount string RawSupportRedeemCreditsAmount string RawPremiumRedeemCreditsAmount string RawLakeRedeemCreditsAmount string RawBYOCRedeemCreditsAmount string RawServerlessCustomerSuccessAmount string RawDedicatedCustomerSuccessAmount string RawStarterCustomerSuccessAmount string RawEssentialCustomerSuccessAmount string RawSupportCustomerSuccessAmount string RawPremiumCustomerSuccessAmount string RawLakeCustomerSuccessAmount string RawBYOCCustomerSuccessAmount string ChannelLossAmount string ServerlessAllocatedLoss string DedicatedAllocatedLoss string StarterAllocatedLoss string EssentialAllocatedLoss string SupportAllocatedLoss string PremiumAllocatedLoss string LakeAllocatedLoss string BYOCAllocatedLoss string CPPODiscountableProductAmount string CPPONonDiscountableProductAmount string IsEstimated bool DedicatedAccrualAmount string SupportAccrualAmount string BindedBillingConfigID uint } type ARTableModel struct{} func initARRow(payment *forms.PaymentMethod, date time.Time) (*ARTableRow, error) { ret := &ARTableRow{ PaymentCode: payment.PaymentCode, PaymentType: payment.PaymentType, TenantID: fmt.Sprint(payment.TenantID), BillMonth: date.Format("2006-01"), StartDate: timeutils.GetMonthDate(date).Format("2006-01-02"), EndDate: timeutils.GetMonthDate(date).AddDate(0, 1, -1).Format("2006-01-02"), BindedBillingConfigID: *payment.BindedBillingConfigID, } if payment.PaymentMeta.EnlargeMarketplaceMetering { log.Info("partner managed billing config found, fill customer info") bcfg, err := (&billingconfig.BillingConfigModel{}).GetBillingConfig(*payment.BindedBillingConfigID) if err != nil { return nil, err } ret.PartnerID = fmt.Sprint(payment.TenantID) ret.TenantID = fmt.Sprint(bcfg.ManagedTenantID) } return ret, nil } func (m *ARTableModel) GetTenantPrepayCreditsBindedPackageIDMap(date time.Time) (map[uint][]uint, error) { startDate := timeutils.GetMonthDate(date) expiredDate := startDate.AddDate(0, 1, 0) creditsList, err := (&marketing.CreditsPackageModel{}).ListAllPrepaymentCreditsPackages(&startDate, &expiredDate) if err != nil { return nil, err } ret := make(map[uint][]uint) for _, credits := range creditsList { if credits.BindedPackageID != nil { ret[credits.TenantID] = append(ret[credits.TenantID], *credits.BindedPackageID) } } return ret, nil } func (m *ARTableModel) GetTenantCreditsBindedPackageIDMapByUsage(usage string, date time.Time) (map[uint][]uint, error) { startDate := timeutils.GetMonthDate(date) expiredDate := startDate.AddDate(0, 1, 0) creditsList, err := (&marketing.CreditsPackageModel{}).ListAllCreditsPackagesByUsage(usage, &startDate, &expiredDate) if err != nil { return nil, err } ret := make(map[uint][]uint) for _, credits := range creditsList { if credits.BindedPackageID != nil { ret[credits.TenantID] = append(ret[credits.TenantID], *credits.BindedPackageID) } } return ret, nil } func (m *ARTableModel) ListInvoicingPayments(date time.Time) ([]*forms.PaymentMethod, error) { var err error start := timeutils.GetMonthDate(date) end := start.AddDate(0, 1, 0) var queryRes []struct { TenantID uint BindedBillingConfigID uint } err = db.GetDB().Model(&entity.Invoice{}).Where("invoice_date >= ? and invoice_date < ?", start, end). Distinct("tenant_id", "binded_billing_config_id").Select("tenant_id", "binded_billing_config_id"). Find(&queryRes).Error if err != nil { return nil, err } creditsList, err := (&marketing.CreditsPackageModel{}).ListAllPrepaymentCreditsPackages(&start, &end) if err != nil { return nil, err } log.Info("retrieve prepayment credits", zap.Int("size", len(creditsList))) tenantBillingConfigIDMap := make(map[uint]map[uint]*struct{}) for _, r := range queryRes { if tenantBillingConfigIDMap[r.TenantID] == nil { tenantBillingConfigIDMap[r.TenantID] = make(map[uint]*struct{}) } tenantBillingConfigIDMap[r.TenantID][r.BindedBillingConfigID] = nil } for _, c := range creditsList { if tenantBillingConfigIDMap[c.TenantID] == nil { tenantBillingConfigIDMap[c.TenantID] = make(map[uint]*struct{}) } tenantBillingConfigIDMap[c.TenantID][*c.BindedBillingConfigID] = nil } var ret []*forms.PaymentMethod for tenantID, billingConfigIDs := range tenantBillingConfigIDMap { for billingConfigID, _ := range billingConfigIDs { p, err := (&paymentsub.PaymentModel{}).GetActivePaymentMethod(tenantID, billingConfigID) if err != nil { log.Error("failed to get payment", zap.Error(err), zap.Uint("tenantID", tenantID), zap.Uint("billingConfigID", billingConfigID)) continue } if p.PaymentType == entity.PaymentType_MSP || p.PaymentType == entity.PaymentType_AWSCPPOTransfer { log.Info("ignore msp / cppo customer on listing invoicing payment") continue } ret = append(ret, p) } } sort.Slice(ret, func(i, j int) bool { return ret[i].TenantID < ret[j].TenantID }) return ret, nil } func (m *ARTableModel) GetCategoryCost(cost forms.Costs, categoryName string) decimal.Decimal { if cost.KindCategoryUnitsCost == nil { return decimal.Zero } if catCost, ok := cost.KindCategoryUnitsCost[categoryName]; ok { return catCost.CostPrice } return decimal.Zero } func (m *ARTableModel) ListPayableBills(date time.Time) ([]*ARTableRow, error) { payments, err := m.ListInvoicingPayments(date) if err != nil { return nil, err } ids := make([]uint, len(payments)) for i, p := range payments { ids[i] = p.TenantID } tenants, err := centralhandler.GetCentralHandler().GetTenants(ids...) if err != nil { return nil, err } tenantInternalMap := make(map[uint]bool) for _, t := range tenants { tenantInternalMap[t.TenantID] = t.Kind == forms.TenantKindInternal } costExplorerModel := &costexplorer.CostExplorerModel{} tenantPrepayBindePackageIDMap, err := m.GetTenantPrepayCreditsBindedPackageIDMap(date) if err != nil { return nil, err } targetUsages := []string{entity.CreditsPackageUsage_CustomerSuccess, entity.CreditsPackageUsage_Redeem, entity.CreditsPackageUsage_Program} tenantCreditsAmountUsageMap := map[string]map[uint][]uint{} for _, usage := range targetUsages { tenantCreditsAmountUsageMap[usage], err = m.GetTenantCreditsBindedPackageIDMapByUsage(usage, date) if err != nil { log.Error("failed to get binded packages on ar table generating", zap.Error(err), zap.String("usage", usage)) return nil, err } } var ret []*ARTableRow for _, p := range payments { if internal, ok := tenantInternalMap[p.TenantID]; !ok || internal { log.Info("ignore internal or non-existed tenant", zap.Uint("tenantID", p.TenantID)) continue } row, err := initARRow(p, date) if err != nil { return nil, err } skipList := config.Get().GetStringSlice(config.ARTableSkipTenantIDs) skipKey := fmt.Sprintf("%d-%d", p.TenantID, *p.BindedBillingConfigID) if slices.Contains(skipList, fmt.Sprintf("%d", p.TenantID)) || slices.Contains(skipList, skipKey) { log.Info("skip tenant on AR table generation", zap.Uint("tenantID", p.TenantID), zap.Uint("billingConfigID", *p.BindedBillingConfigID), zap.String("skipKey", skipKey)) continue } cost, err := costExplorerModel.GetMonthlyCostWithCache(p.TenantID, *p.BindedBillingConfigID, date.Year(), int(date.Month())) if err != nil { return nil, err } row.TotalAmount = cost.InvoiceCostsCosts.CostPrice.String() switch p.PaymentType { case entity.PaymentType_WireTransfer: row.WireTransferAmount = row.TotalAmount invoice, err := (&payment.InvoiceModel{}).GetMonthlyInvoice(p.TenantID, *p.BindedBillingConfigID, timeutils.GetMonthDate(date)) if err != nil { if errors.Is(err, billing_errors.ObjectNotExistsError) { break } return nil, err } if invoice.TaxRate != nil { row.StripeTaxRate = []string{invoice.TaxRate.Value.String()} } case entity.PaymentType_AWS, entity.PaymentType_GCP, entity.PaymentType_AliCloud: row.MarketplaceAmount = row.TotalAmount case entity.PaymentType_AWSCPPOReseller: row.MarketplaceAmount = row.TotalAmount } invoiceAmount := cost.InvoiceCostsCosts.CostPrice totalServerlessCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBServerlessCatName) totalDedicatedCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBDedicatedCatName) totalStarterCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBStarterCatName) totalEssintialCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBEssentialCatName) totalSupportCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.SupportPlanCatName) totalPremiumCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBPremiumCatName) totalLakeCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBLakeCatName) totalBYOCCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBBYOCCatName) if prepayBindedPackageIDs, ok := tenantPrepayBindePackageIDMap[p.TenantID]; ok { startDate := timeutils.GetMonthDate(date) endDate := startDate.AddDate(0, 1, 0) log.Info("tenant has prepay credits package(s)", zap.Uints("prepayBindedPackageIDs", prepayBindedPackageIDs)) prepayCost, err := costExplorerModel.CalculateCostV2WithCache(p.TenantID, []uint{*p.BindedBillingConfigID}, &forms.CostExplorerQueryV2{ StartDate: startDate, EndDate: endDate, BindedPackageIDs: prepayBindedPackageIDs, }) if err != nil { return nil, err } row.PrepaymentCredits = prepayCost.ExternalCosts.CreditsCosts.CostPrice.String() invoiceAmount = invoiceAmount.Add(prepayCost.ExternalCosts.CreditsCosts.CostPrice) totalServerlessCost = totalServerlessCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBServerlessCatName)) totalDedicatedCost = totalDedicatedCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBDedicatedCatName)) totalStarterCost = totalStarterCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBStarterCatName)) totalEssintialCost = totalEssintialCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBEssentialCatName)) totalSupportCost = totalSupportCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.SupportPlanCatName)) totalPremiumCost = totalPremiumCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBPremiumCatName)) totalLakeCost = totalLakeCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBLakeCatName)) totalBYOCCost = totalBYOCCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBBYOCCatName)) } row.InvoiceAmount = invoiceAmount.String() row.RawServerlessAmount = totalServerlessCost.String() row.RawDedicatedAmount = totalDedicatedCost.String() row.RawStarterAmount = totalStarterCost.String() row.RawEssentialAmount = totalEssintialCost.String() row.RawSupportAmount = totalSupportCost.String() row.RawPremiumAmount = totalPremiumCost.String() row.RawLakeAmount = totalLakeCost.String() row.RawBYOCAmount = totalBYOCCost.String() if decimal.RequireFromString(row.InvoiceAmount).LessThanOrEqual(decimal.Zero) { log.Info("ignore zero cost tenant", zap.Uint("tenantID", p.TenantID)) continue } targetUsagesMap := map[string]map[string]*string{ entity.CreditsPackageUsage_Program: {constant.TiDBServerlessCatName: &row.RawServerlessProgramCreditsAmount, constant.TiDBDedicatedCatName: &row.RawDedicatedProgramCreditsAmount, constant.TiDBStarterCatName: &row.RawStarterProgramCreditsAmount, constant.TiDBEssentialCatName: &row.RawEssentialProgramCreditsAmount, constant.SupportPlanCatName: &row.RawSupportProgramCreditsAmount, constant.TiDBPremiumCatName: &row.RawPremiumProgramCreditsAmount, constant.TiDBLakeCatName: &row.RawLakeProgramCreditsAmount, constant.TiDBBYOCCatName: &row.RawBYOCProgramCreditsAmount}, entity.CreditsPackageUsage_Redeem: {constant.TiDBServerlessCatName: &row.RawServerlessRedeemCreditsAmount, constant.TiDBDedicatedCatName: &row.RawDedicatedRedeemCreditsAmount, constant.TiDBStarterCatName: &row.RawStarterRedeemCreditsAmount, constant.TiDBEssentialCatName: &row.RawEssentialRedeemCreditsAmount, constant.SupportPlanCatName: &row.RawSupportRedeemCreditsAmount, constant.TiDBPremiumCatName: &row.RawPremiumRedeemCreditsAmount, constant.TiDBLakeCatName: &row.RawLakeRedeemCreditsAmount, constant.TiDBBYOCCatName: &row.RawBYOCRedeemCreditsAmount}, entity.CreditsPackageUsage_CustomerSuccess: {constant.TiDBServerlessCatName: &row.RawServerlessCustomerSuccessAmount, constant.TiDBDedicatedCatName: &row.RawDedicatedCustomerSuccessAmount, constant.TiDBStarterCatName: &row.RawStarterCustomerSuccessAmount, constant.TiDBEssentialCatName: &row.RawEssentialCustomerSuccessAmount, constant.SupportPlanCatName: &row.RawSupportCustomerSuccessAmount, constant.TiDBPremiumCatName: &row.RawPremiumCustomerSuccessAmount, constant.TiDBLakeCatName: &row.RawLakeCustomerSuccessAmount, constant.TiDBBYOCCatName: &row.RawBYOCCustomerSuccessAmount}, } for usage, usageMap := range targetUsagesMap { if bindedPackageIDs, ok := tenantCreditsAmountUsageMap[usage][p.TenantID]; ok { startDate := timeutils.GetMonthDate(date) endDate := startDate.AddDate(0, 1, 0) creditsCost, err := costExplorerModel.CalculateCostV2WithCache(p.TenantID, []uint{*p.BindedBillingConfigID}, &forms.CostExplorerQueryV2{ StartDate: startDate, EndDate: endDate, BindedPackageIDs: bindedPackageIDs, }) if err != nil { return nil, err } for cat, ptr := range usageMap { *ptr = m.GetCategoryCost(creditsCost.ExternalCosts.CreditsCosts, cat).String() } } } if row.MarketplaceAmount != "" { invoices, err := (&payment.InvoiceModel{}).ListAllMonthlyInvoices(p.TenantID, *p.BindedBillingConfigID, timeutils.GetMonthDate(date)) if err != nil { log.Error("failed to list all monthly invoices", zap.Error(err)) return nil, err } totalChannelLoss := decimal.Zero totalCPPODiscountableProductAmount := decimal.Zero totalCPPONonDiscountableProductAmount := decimal.Zero for _, inv := range invoices { if p.ManagedBySales && inv.Type != entity.InvoiceType_COMMITMENT { log.Info("payment is managed by sales, ignore no commitment invoice to calculate channel loss", zap.Uint("paymentID", p.PaymentMethodID), zap.Uint("invoiceID", inv.InvoiceID), zap.Uint("tenantID", p.TenantID)) continue } if p.PaymentType == entity.PaymentType_AWSCPPOReseller { if inv.Type == entity.InvoiceType_SUBORDINATED { totalChannelLoss = totalChannelLoss.Add(inv.ChannelLoss) } } else { totalChannelLoss = totalChannelLoss.Add(inv.ChannelLoss) } if p.PaymentType == entity.PaymentType_AWSCPPOReseller && inv.Type == entity.InvoiceType_SUBORDINATED && inv.PaymentCode != nil { if *inv.PaymentCode == p.PaymentCode { totalCPPODiscountableProductAmount = totalCPPODiscountableProductAmount.Add(inv.CostPrice).Sub(inv.ChannelLoss) } else if p.PaymentSubCode != nil && *p.PaymentSubCode == *inv.PaymentCode { totalCPPONonDiscountableProductAmount = totalCPPONonDiscountableProductAmount.Add(inv.CostPrice).Sub(inv.ChannelLoss) } } } row.ChannelLossAmount = totalChannelLoss.String() if !totalCPPODiscountableProductAmount.Equal(decimal.Zero) { row.CPPODiscountableProductAmount = totalCPPODiscountableProductAmount.String() } if !totalCPPONonDiscountableProductAmount.Equal(decimal.Zero) { row.CPPONonDiscountableProductAmount = totalCPPONonDiscountableProductAmount.String() } total := decimal.RequireFromString(row.InvoiceAmount) if !total.IsZero() && !totalChannelLoss.IsZero() { allocating := []*struct { Name string Amount decimal.Decimal AllocatedLoss *string }{ {Name: "dedicated", Amount: decimal.RequireFromString(row.RawDedicatedAmount), AllocatedLoss: &row.DedicatedAllocatedLoss}, {Name: "serverless", Amount: decimal.RequireFromString(row.RawServerlessAmount), AllocatedLoss: &row.ServerlessAllocatedLoss}, {Name: "starter", Amount: decimal.RequireFromString(row.RawStarterAmount), AllocatedLoss: &row.StarterAllocatedLoss}, {Name: "essential", Amount: decimal.RequireFromString(row.RawEssentialAmount), AllocatedLoss: &row.EssentialAllocatedLoss}, {Name: "premium", Amount: decimal.RequireFromString(row.RawPremiumAmount), AllocatedLoss: &row.PremiumAllocatedLoss}, {Name: "support", Amount: decimal.RequireFromString(row.RawSupportAmount), AllocatedLoss: &row.SupportAllocatedLoss}, {Name: "lake", Amount: decimal.RequireFromString(row.RawLakeAmount), AllocatedLoss: &row.LakeAllocatedLoss}, {Name: "byoc", Amount: decimal.RequireFromString(row.RawBYOCAmount), AllocatedLoss: &row.BYOCAllocatedLoss}, } sort.Slice(allocating, func(i, j int) bool { return allocating[i].Amount.GreaterThan(allocating[j].Amount) }) restLoss := totalChannelLoss for i := len(allocating) - 1; i != 0; i-- { allocatedLoss := totalChannelLoss.Mul(allocating[i].Amount.Div(total)).Truncate(config.Get().GetInt32(config.BillingCostsPrecisionDigits)) *allocating[i].AllocatedLoss = allocatedLoss.String() restLoss = restLoss.Sub(allocatedLoss) log.Info("allocated loss", zap.String("name", allocating[i].Name), zap.String("allocatedLoss", allocatedLoss.String()), zap.Uint("tenantID", p.TenantID), zap.Uint("paymentID", p.PaymentMethodID)) } *allocating[0].AllocatedLoss = restLoss.String() log.Info("allocated rest loss", zap.String("name", allocating[0].Name), zap.String("allocatedLoss", restLoss.String()), zap.Uint("tenantID", p.TenantID), zap.Uint("paymentID", p.PaymentMethodID)) } } ret = append(ret, row) } return ret, nil } func (m *ARTableModel) GenerateARInitTable(date time.Time) ([]*ARTableRow, error) { rows, err := m.ListPayableBills(date) if err != nil { return nil, err } stripeTenantMap := make(map[string]*ARTableRow) for _, r := range rows { if r.PaymentType == entity.PaymentType_STRIPE { stripeTenantMap[r.PaymentCode] = r } } handler, err := paymenthandler.GetPaymentHandler().GetProvider(entity.PaymentType_STRIPE) if err != nil { return nil, err } stripeHandler, ok := handler.(paymenthandler.StripePaymentHandler) if !ok { return nil, errors.New("failed to get stripe handler") } invoices, err := stripeHandler.GetMonthlyCreatedInvoices(date) if err != nil { return nil, err } for _, i := range invoices { if r, ok := stripeTenantMap[i.CustomerID]; ok { r.StripeAmount = append(r.StripeAmount, i.SubTotal.String()) r.StripeInvoiceNumber = append(r.StripeInvoiceNumber, i.Number) r.StripeTaxRate = append(r.StripeTaxRate, i.TaxRate.String()) r.StripeAmountIncludingTax = append(r.StripeAmountIncludingTax, i.Total.String()) r.StripeInvoice = append(r.StripeInvoice, i.InvoicePDF) r.StripeInvoicePaid = append(r.StripeInvoicePaid, i.Paid) if i.StatusTransitions != nil { r.StripeInvoiceFinalizedDate = append(r.StripeInvoiceFinalizedDate, time.Unix(i.StatusTransitions.FinalizedAt, 0).Format("2006-01-02")) if i.Paid && i.StatusTransitions.PaidAt != 0 { r.StripeInvoicePaidDate = append(r.StripeInvoicePaidDate, time.Unix(i.StatusTransitions.PaidAt, 0).Format("2006-01-02")) } else { r.StripeInvoicePaidDate = append(r.StripeInvoicePaidDate, "null") } } else { log.Error("failed to get stripe invoice status on generating ar table", zap.String("InvoiceID", i.ID)) } } } var ret []*ARTableRow for _, r := range rows { if r.PaymentType == entity.PaymentType_STRIPE && len(r.StripeInvoice) == 0 && r.PrepaymentCredits == "" { continue } r.IsEstimated = false ret = append(ret, r) } return ret, nil } func serialize(input interface{}) string { if input == nil { return "" } v := reflect.ValueOf(input) if v.Kind() == reflect.Ptr || v.Kind() == reflect.Slice || v.Kind() == reflect.Map || v.Kind() == reflect.Chan || v.Kind() == reflect.Func || v.Kind() == reflect.Interface { if v.IsNil() { return "" } } ret, err := json.Marshal(input) if err != nil { log.Error("failed to serialize", zap.Any("input", input)) return "" } return string(ret) } func (m *ARTableModel) RenderARTable(rows []*ARTableRow) ([]byte, error) { header := []string{ "bill_month", "start_date", "end_date", "tenant_id", "stripe_amount", "stripe_tax_rate", "stripe_amount_including_tax", "stripe_invoice_number", "wire_transfer_amount", "marketplace_amount", "prepayment_credits", "total_amount", "invoice_amount", "is_estimated", "dedicated_accrual_amount", "support_accrual_amount", "stripe_invoice", "stripe_invoice_paid", "stripe_invoice_finalized_date", "stripe_invoice_paid_date", "raw_starter_amount", "raw_dedicated_amount", "raw_essential_amount", "raw_support_amount", "raw_premium_amount", "raw_lake_amount", "raw_byoc_amount", "serverless_amount", "dedicated_amount", "starter_amount", "essential_amount", "support_amount", "premium_amount", "lake_amount", "byoc_amount", "raw_program_credit_amount_starter", "raw_program_credit_amount_dedicated", "raw_program_credit_amount_essential", "raw_program_credit_amount_support", "raw_program_credit_amount_premium", "raw_program_credit_amount_lake", "raw_program_credit_amount_byoc", "raw_redeem_credit_amount_starter", "raw_redeem_credit_amount_dedicated", "raw_redeem_credit_amount_essential", "raw_redeem_credit_amount_support", "raw_redeem_credit_amount_premium", "raw_redeem_credit_amount_lake", "raw_redeem_credit_amount_byoc", "raw_customer_success_credit_amount_starter", "raw_customer_success_credit_amount_dedicated", "raw_customer_success_credit_amount_essential", "raw_customer_success_credit_amount_support", "raw_customer_success_credit_amount_premium", "raw_customer_success_credit_amount_lake", "raw_customer_success_credit_amount_byoc", "channel_loss_amount", "starter_allocated_loss", "dedicated_allocated_loss", "essential_allocated_loss", "support_allocated_loss", "premium_allocated_loss", "lake_allocated_loss", "byoc_allocated_loss", "cppo_discountable_product_amount", "cppo_non_discountable_product_amount", } buf := new(bytes.Buffer) writer := csv.NewWriter(buf) err := writer.Write(header) if err != nil { return nil, err } for _, row := range rows { data := []string{ row.BillMonth, row.StartDate, row.EndDate, row.TenantID, serialize(row.StripeAmount), serialize(row.StripeTaxRate), serialize(row.StripeAmountIncludingTax), serialize(row.StripeInvoiceNumber), row.WireTransferAmount, row.MarketplaceAmount, row.PrepaymentCredits, subDecimal(row.InvoiceAmount, row.ChannelLossAmount), row.InvoiceAmount, strconv.FormatBool(row.IsEstimated), row.DedicatedAccrualAmount, row.SupportAccrualAmount, serialize(row.StripeInvoice), serialize(row.StripeInvoicePaid), serialize(row.StripeInvoiceFinalizedDate), serialize(row.StripeInvoicePaidDate), row.RawStarterAmount, row.RawDedicatedAmount, row.RawEssentialAmount, row.RawSupportAmount, row.RawPremiumAmount, row.RawLakeAmount, row.RawBYOCAmount, subDecimal(row.RawServerlessAmount, row.ServerlessAllocatedLoss), subDecimal(row.RawDedicatedAmount, row.DedicatedAllocatedLoss), subDecimal(row.RawStarterAmount, row.StarterAllocatedLoss), subDecimal(row.RawEssentialAmount, row.EssentialAllocatedLoss), subDecimal(row.RawSupportAmount, row.SupportAllocatedLoss), subDecimal(row.RawPremiumAmount, row.PremiumAllocatedLoss), subDecimal(row.RawLakeAmount, row.LakeAllocatedLoss), subDecimal(row.RawBYOCAmount, row.BYOCAllocatedLoss), row.RawStarterProgramCreditsAmount, row.RawDedicatedProgramCreditsAmount, row.RawEssentialProgramCreditsAmount, row.RawSupportProgramCreditsAmount, row.RawPremiumProgramCreditsAmount, row.RawLakeProgramCreditsAmount, row.RawBYOCProgramCreditsAmount, row.RawStarterRedeemCreditsAmount, row.RawDedicatedRedeemCreditsAmount, row.RawEssentialRedeemCreditsAmount, row.RawSupportRedeemCreditsAmount, row.RawPremiumRedeemCreditsAmount, row.RawLakeRedeemCreditsAmount, row.RawBYOCRedeemCreditsAmount, row.RawStarterCustomerSuccessAmount, row.RawDedicatedCustomerSuccessAmount, row.RawEssentialCustomerSuccessAmount, row.RawSupportCustomerSuccessAmount, row.RawPremiumCustomerSuccessAmount, row.RawLakeCustomerSuccessAmount, row.RawBYOCCustomerSuccessAmount, row.ChannelLossAmount, row.StarterAllocatedLoss, row.DedicatedAllocatedLoss, row.EssentialAllocatedLoss, row.SupportAllocatedLoss, row.PremiumAllocatedLoss, row.LakeAllocatedLoss, row.BYOCAllocatedLoss, row.CPPODiscountableProductAmount, row.CPPONonDiscountableProductAmount, } err = writer.Write(data) if err != nil { return nil, err } } writer.Flush() return buf.Bytes(), nil } func (m *ARTableModel) PutARTableResultToS3(key string, result []byte) error { return awshandler.PutARTableToS3(key, result) } func (m *ARTableModel) ListEstimatedPayments(date time.Time) ([]*forms.PaymentMethod, error) { start := timeutils.GetMonthDate(date) end := start.AddDate(0, 1, 0) disabledTenants := config.Get().GetStringSlice(config.DisableClusterDisplayTenants) disabledTenantIDMap := make(map[uint]bool) for _, t := range disabledTenants { id, err := strconv.ParseUint(t, 10, 64) if err != nil { log.Error("invalid disabled tenant id in config", zap.String("tenantID", t), zap.Error(err)) continue } disabledTenantIDMap[uint(id)] = true } var disabledTenantIDs []uint for id := range disabledTenantIDMap { disabledTenantIDs = append(disabledTenantIDs, id) } seen, err := m.QueryTenantBillingPairsForMonth(start, end, disabledTenantIDs) if err != nil { return nil, err } creditsList, err := (&marketing.CreditsPackageModel{}).ListAllPrepaymentCreditsPackages(&start, &end) if err != nil { return nil, err } log.Info("retrieve prepayment credits for estimated AR", zap.Int("size", len(creditsList))) var commitmentRes []struct { TenantID uint BindedBillingConfigID uint } err = db.GetDB().Model(&entity.Invoice{}). Where("invoice_date >= ? AND invoice_date < ?", start, end). Where("type = ?", entity.InvoiceType_COMMITMENT). Distinct("tenant_id", "binded_billing_config_id"). Select("tenant_id", "binded_billing_config_id"). Find(&commitmentRes).Error if err != nil { log.Error("failed to query commitment invoices for estimated AR", zap.Error(err), zap.Time("date", date)) return nil, err } log.Info("retrieve commitment invoices for estimated AR", zap.Int("size", len(commitmentRes))) tenantBillingConfigIDMap := make(map[uint]map[uint]*struct{}) for r := range seen { if tenantBillingConfigIDMap[r.TenantID] == nil { tenantBillingConfigIDMap[r.TenantID] = make(map[uint]*struct{}) } tenantBillingConfigIDMap[r.TenantID][r.BindedBillingConfigID] = nil } for _, c := range creditsList { if tenantBillingConfigIDMap[c.TenantID] == nil { tenantBillingConfigIDMap[c.TenantID] = make(map[uint]*struct{}) } tenantBillingConfigIDMap[c.TenantID][*c.BindedBillingConfigID] = nil } for _, r := range commitmentRes { if tenantBillingConfigIDMap[r.TenantID] == nil { tenantBillingConfigIDMap[r.TenantID] = make(map[uint]*struct{}) } tenantBillingConfigIDMap[r.TenantID][r.BindedBillingConfigID] = nil } for tenantID := range disabledTenantIDMap { bcfgID, err := (&billingconfig.BillingConfigModel{}).GetActiveBillingConfigID(tenantID) if err != nil { log.Error("failed to get active billing config for disabled tenant", zap.Error(err), zap.Uint("tenantID", tenantID)) continue } if tenantBillingConfigIDMap[tenantID] == nil { tenantBillingConfigIDMap[tenantID] = make(map[uint]*struct{}) } tenantBillingConfigIDMap[tenantID][bcfgID] = nil } var ret []*forms.PaymentMethod for tenantID, billingConfigIDs := range tenantBillingConfigIDMap { for billingConfigID := range billingConfigIDs { p, err := (&paymentsub.PaymentModel{}).GetActivePaymentMethod(tenantID, billingConfigID) if err != nil { log.Error("failed to get payment for estimated AR", zap.Error(err), zap.Uint("tenantID", tenantID), zap.Uint("billingConfigID", billingConfigID)) continue } if p.PaymentType == entity.PaymentType_MSP || p.PaymentType == entity.PaymentType_AWSCPPOTransfer { log.Info("ignore msp / cppo customer on listing estimated payment") continue } ret = append(ret, p) } } sort.Slice(ret, func(i, j int) bool { return ret[i].TenantID < ret[j].TenantID }) return ret, nil } func (m *ARTableModel) ListEstimatedPayableBills(date time.Time) ([]*ARTableRow, error) { payments, err := m.ListEstimatedPayments(date) if err != nil { return nil, err } ids := make([]uint, len(payments)) for i, p := range payments { ids[i] = p.TenantID } tenants, err := centralhandler.GetCentralHandler().GetTenants(ids...) if err != nil { return nil, err } tenantInternalMap := make(map[uint]bool) for _, t := range tenants { tenantInternalMap[t.TenantID] = t.Kind == forms.TenantKindInternal } costExplorerModel := &costexplorer.CostExplorerModel{} tenantPrepayBindePackageIDMap, err := m.GetTenantPrepayCreditsBindedPackageIDMap(date) if err != nil { return nil, err } targetUsages := []string{entity.CreditsPackageUsage_CustomerSuccess, entity.CreditsPackageUsage_Redeem, entity.CreditsPackageUsage_Program} tenantCreditsAmountUsageMap := map[string]map[uint][]uint{} for _, usage := range targetUsages { tenantCreditsAmountUsageMap[usage], err = m.GetTenantCreditsBindedPackageIDMapByUsage(usage, date) if err != nil { log.Error("failed to get binded packages on estimated AR generating", zap.Error(err), zap.String("usage", usage)) return nil, err } } var ret []*ARTableRow for _, p := range payments { if internal, ok := tenantInternalMap[p.TenantID]; !ok || internal { log.Info("ignore internal or non-existed tenant", zap.Uint("tenantID", p.TenantID)) continue } row, err := initARRow(p, date) if err != nil { return nil, err } skipList := config.Get().GetStringSlice(config.ARTableSkipTenantIDs) skipKey := fmt.Sprintf("%d-%d", p.TenantID, *p.BindedBillingConfigID) if slices.Contains(skipList, fmt.Sprintf("%d", p.TenantID)) || slices.Contains(skipList, skipKey) { log.Info("skip tenant on estimated AR table generation", zap.Uint("tenantID", p.TenantID), zap.Uint("billingConfigID", *p.BindedBillingConfigID), zap.String("skipKey", skipKey)) continue } cost, err := costExplorerModel.GetMonthlyCostWithCache(p.TenantID, *p.BindedBillingConfigID, date.Year(), int(date.Month())) if err != nil { return nil, err } row.TotalAmount = cost.InvoiceCostsCosts.CostPrice.String() row.InvoiceAmount = cost.InvoiceCostsCosts.CostPrice.String() switch p.PaymentType { case entity.PaymentType_WireTransfer: row.WireTransferAmount = row.TotalAmount case entity.PaymentType_AWS, entity.PaymentType_GCP, entity.PaymentType_AliCloud: row.MarketplaceAmount = row.TotalAmount case entity.PaymentType_AWSCPPOReseller: row.MarketplaceAmount = row.TotalAmount } totalServerlessCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBServerlessCatName) totalDedicatedCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBDedicatedCatName) totalStarterCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBStarterCatName) totalEssintialCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBEssentialCatName) totalSupportCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.SupportPlanCatName) totalPremiumCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBPremiumCatName) totalLakeCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBLakeCatName) totalBYOCCost := m.GetCategoryCost(cost.InvoiceCostsCosts, constant.TiDBBYOCCatName) if prepayBindedPackageIDs, ok := tenantPrepayBindePackageIDMap[p.TenantID]; ok { startDate := timeutils.GetMonthDate(date) endDate := startDate.AddDate(0, 1, 0) log.Info("tenant has prepay credits package(s)", zap.Uints("prepayBindedPackageIDs", prepayBindedPackageIDs)) prepayCost, err := costExplorerModel.CalculateCostV2WithCache(p.TenantID, []uint{*p.BindedBillingConfigID}, &forms.CostExplorerQueryV2{ StartDate: startDate, EndDate: endDate, BindedPackageIDs: prepayBindedPackageIDs, }) if err != nil { return nil, err } row.PrepaymentCredits = prepayCost.ExternalCosts.CreditsCosts.CostPrice.String() row.InvoiceAmount = decimal.RequireFromString(row.InvoiceAmount).Add(prepayCost.ExternalCosts.CreditsCosts.CostPrice).String() totalServerlessCost = totalServerlessCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBServerlessCatName)) totalDedicatedCost = totalDedicatedCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBDedicatedCatName)) totalStarterCost = totalStarterCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBStarterCatName)) totalEssintialCost = totalEssintialCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBEssentialCatName)) totalSupportCost = totalSupportCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.SupportPlanCatName)) totalPremiumCost = totalPremiumCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBPremiumCatName)) totalLakeCost = totalLakeCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBLakeCatName)) totalBYOCCost = totalBYOCCost.Add(m.GetCategoryCost(prepayCost.ExternalCosts.CreditsCosts, constant.TiDBBYOCCatName)) } // Note: contract-based support estimate is NOT computed here. // The new design does the estimate in GenerateEstimatedARTable // (estimateSupportForRow), then overwrites raw_support and // recomputes invoice/total as sum(8 raw). Setting raw_support // from the cost explorer here is only so estimateSupportForRow // can read the pre-merge metering value via row.RawSupportAmount. row.RawServerlessAmount = totalServerlessCost.String() row.RawDedicatedAmount = totalDedicatedCost.String() row.RawStarterAmount = totalStarterCost.String() row.RawEssentialAmount = totalEssintialCost.String() row.RawSupportAmount = totalSupportCost.String() row.RawPremiumAmount = totalPremiumCost.String() row.RawLakeAmount = totalLakeCost.String() row.RawBYOCAmount = totalBYOCCost.String() if decimal.RequireFromString(row.InvoiceAmount).LessThanOrEqual(decimal.Zero) { log.Info("ignore zero cost tenant", zap.Uint("tenantID", p.TenantID)) continue } targetUsagesMap := map[string]map[string]*string{ entity.CreditsPackageUsage_Program: {constant.TiDBServerlessCatName: &row.RawServerlessProgramCreditsAmount, constant.TiDBDedicatedCatName: &row.RawDedicatedProgramCreditsAmount, constant.TiDBStarterCatName: &row.RawStarterProgramCreditsAmount, constant.TiDBEssentialCatName: &row.RawEssentialProgramCreditsAmount, constant.SupportPlanCatName: &row.RawSupportProgramCreditsAmount, constant.TiDBPremiumCatName: &row.RawPremiumProgramCreditsAmount, constant.TiDBLakeCatName: &row.RawLakeProgramCreditsAmount, constant.TiDBBYOCCatName: &row.RawBYOCProgramCreditsAmount}, entity.CreditsPackageUsage_Redeem: {constant.TiDBServerlessCatName: &row.RawServerlessRedeemCreditsAmount, constant.TiDBDedicatedCatName: &row.RawDedicatedRedeemCreditsAmount, constant.TiDBStarterCatName: &row.RawStarterRedeemCreditsAmount, constant.TiDBEssentialCatName: &row.RawEssentialRedeemCreditsAmount, constant.SupportPlanCatName: &row.RawSupportRedeemCreditsAmount, constant.TiDBPremiumCatName: &row.RawPremiumRedeemCreditsAmount, constant.TiDBLakeCatName: &row.RawLakeRedeemCreditsAmount, constant.TiDBBYOCCatName: &row.RawBYOCRedeemCreditsAmount}, entity.CreditsPackageUsage_CustomerSuccess: {constant.TiDBServerlessCatName: &row.RawServerlessCustomerSuccessAmount, constant.TiDBDedicatedCatName: &row.RawDedicatedCustomerSuccessAmount, constant.TiDBStarterCatName: &row.RawStarterCustomerSuccessAmount, constant.TiDBEssentialCatName: &row.RawEssentialCustomerSuccessAmount, constant.SupportPlanCatName: &row.RawSupportCustomerSuccessAmount, constant.TiDBPremiumCatName: &row.RawPremiumCustomerSuccessAmount, constant.TiDBLakeCatName: &row.RawLakeCustomerSuccessAmount, constant.TiDBBYOCCatName: &row.RawBYOCCustomerSuccessAmount}, } for usage, usageMap := range targetUsagesMap { if bindedPackageIDs, ok := tenantCreditsAmountUsageMap[usage][p.TenantID]; ok { startDate := timeutils.GetMonthDate(date) endDate := startDate.AddDate(0, 1, 0) creditsCost, err := costExplorerModel.CalculateCostV2WithCache(p.TenantID, []uint{*p.BindedBillingConfigID}, &forms.CostExplorerQueryV2{ StartDate: startDate, EndDate: endDate, BindedPackageIDs: bindedPackageIDs, }) if err != nil { return nil, err } for cat, ptr := range usageMap { *ptr = m.GetCategoryCost(creditsCost.ExternalCosts.CreditsCosts, cat).String() } } } ret = append(ret, row) } return ret, nil } func (m *ARTableModel) calculateAccrual(tenantID uint, billingConfigID uint, date time.Time, category string) (decimal.Decimal, error) { monthStart := timeutils.GetMonthDate(date) monthEnd := monthStart.AddDate(0, 1, 0) lastDay := monthEnd.AddDate(0, 0, -1) tailDays := 2 tailStart := lastDay.AddDate(0, 0, -(tailDays - 1)) refStart := tailStart.AddDate(0, 0, -7) refEnd := tailStart costExplorerModel := &costexplorer.CostExplorerModel{} units, err := (&billing.UnitModel{}).GetAllUnits() if err != nil { return decimal.Zero, err } unitsCategory := make(map[string]string) for _, u := range units { unitsCategory[u.UnitKey] = u.Category } categoryItemInfo := costexplorer.GetAllCategoryItemInfoWithCache() var starterCategories map[string]bool for _, item := range categoryItemInfo { if item.ShowKindName == constant.TiDBStarterCatName { if starterCategories == nil { starterCategories = make(map[string]bool) } starterCategories[item.Category] = true } } var excludeUnits []string for _, u := range units { if starterCategories != nil && starterCategories[u.Category] { excludeUnits = append(excludeUnits, u.UnitKey) } } refCosts, err := costExplorerModel.GetDailyCostsByDateRangeExcludingUnits(tenantID, billingConfigID, refStart, refEnd, excludeUnits) if err != nil { return decimal.Zero, err } dailyTotals := make(map[string]decimal.Decimal) for _, c := range refCosts { cat := unitsCategory[c.Unit] if cat == category { dailyTotals[c.BilledDate] = dailyTotals[c.BilledDate].Add(c.CostPrice) } } if len(dailyTotals) == 0 { return decimal.Zero, nil } var totalRef decimal.Decimal for _, v := range dailyTotals { totalRef = totalRef.Add(v) } y := totalRef.Div(decimal.NewFromInt(int64(len(dailyTotals)))) tailCosts, err := costExplorerModel.GetDailyCostsByDateRangeExcludingUnits(tenantID, billingConfigID, tailStart, monthEnd, excludeUnits) if err != nil { return decimal.Zero, err } var x decimal.Decimal for _, c := range tailCosts { cat := unitsCategory[c.Unit] if cat == category { x = x.Add(c.CostPrice) } } accrual := y.Mul(decimal.NewFromInt(int64(tailDays))).Sub(x) accrual = accrual.Round(2) if accrual.IsPositive() { return accrual, nil } return decimal.Zero, nil } func (m *ARTableModel) GenerateEstimatedARTable(date time.Time) ([]*ARTableRow, error) { rows, err := m.ListEstimatedPayableBills(date) if err != nil { return nil, err } stripeTenantMap := make(map[string]*ARTableRow) for _, r := range rows { if r.PaymentType == entity.PaymentType_STRIPE { stripeTenantMap[r.PaymentCode] = r } } handler, err := paymenthandler.GetPaymentHandler().GetProvider(entity.PaymentType_STRIPE) if err != nil { return nil, err } stripeHandler, ok := handler.(paymenthandler.StripePaymentHandler) if !ok { return nil, errors.New("failed to get stripe handler") } invoices, err := stripeHandler.GetMonthlyCreatedInvoices(date) if err != nil { return nil, err } for _, i := range invoices { if r, ok := stripeTenantMap[i.CustomerID]; ok { r.StripeAmount = append(r.StripeAmount, i.SubTotal.String()) r.StripeInvoiceNumber = append(r.StripeInvoiceNumber, i.Number) r.StripeTaxRate = append(r.StripeTaxRate, i.TaxRate.String()) r.StripeAmountIncludingTax = append(r.StripeAmountIncludingTax, i.Total.String()) r.StripeInvoice = append(r.StripeInvoice, i.InvoicePDF) r.StripeInvoicePaid = append(r.StripeInvoicePaid, i.Paid) if i.StatusTransitions != nil { r.StripeInvoiceFinalizedDate = append(r.StripeInvoiceFinalizedDate, time.Unix(i.StatusTransitions.FinalizedAt, 0).Format("2006-01-02")) if i.Paid && i.StatusTransitions.PaidAt != 0 { r.StripeInvoicePaidDate = append(r.StripeInvoicePaidDate, time.Unix(i.StatusTransitions.PaidAt, 0).Format("2006-01-02")) } else { r.StripeInvoicePaidDate = append(r.StripeInvoicePaidDate, "null") } } else { log.Error("failed to get stripe invoice status on generating estimated ar table", zap.String("InvoiceID", i.ID)) } } } var ret []*ARTableRow for _, r := range rows { tenantID, _ := strconv.ParseUint(r.TenantID, 10, 64) // T1: dedicated accrual (v1220 tail-end forecast; v1220 logic kept). dedicatedAccrual, err := m.calculateAccrual(uint(tenantID), r.BindedBillingConfigID, date, constant.TiDBDedicatedCatName) if err != nil { log.Error("failed to calculate dedicated accrual", zap.Error(err), zap.String("tenantID", r.TenantID)) dedicatedAccrual = decimal.Zero } r.DedicatedAccrualAmount = dedicatedAccrual.String() // T2: contract-based support estimate. Reads r.Raw*Amount as // pre-merge metering values (set by ListEstimatedPayableBills), // uses the AR columns + dedicatedAccrual for full support, falls // back to user_unit_costs for partial support. Returns (E, A) // where E is the new raw_support and A is the contract gap // (max(0, E - meteredSupport)). E, A, err := m.EstimateSupportForRow(uint(tenantID), r.BindedBillingConfigID, date, r) if err != nil { log.Error("failed to estimate support fee for tenant", zap.Error(err), zap.String("tenantID", r.TenantID)) return nil, err } // T3: merge. // - raw_dedicated += dedicatedAccrual (v1220 tail forecast). // - raw_support = E (replaces metering with the contract estimate). // - support_accrual = A (replaces the v1220 tail forecast with the // contract gap, which is the customer-facing "additional // amount beyond the metering to reach the contract floor"). // - invoice / total = sum(8 raw). The estimate-step total is the // "post-estimate" view: cost explorer categories + dedicated // accrual (fully forecasted non-support) + contract support // (estimate). Wire / marketplace match the payment type. dedicatedTotal := decimal.RequireFromString(r.RawDedicatedAmount).Add(dedicatedAccrual) r.RawDedicatedAmount = dedicatedTotal.String() r.RawSupportAmount = E.String() r.SupportAccrualAmount = A.String() sum8 := decimal.RequireFromString(r.RawServerlessAmount). Add(decimal.RequireFromString(r.RawDedicatedAmount)). Add(decimal.RequireFromString(r.RawStarterAmount)). Add(decimal.RequireFromString(r.RawEssentialAmount)). Add(decimal.RequireFromString(r.RawSupportAmount)). Add(decimal.RequireFromString(r.RawPremiumAmount)). Add(decimal.RequireFromString(r.RawLakeAmount)). Add(decimal.RequireFromString(r.RawBYOCAmount)) r.InvoiceAmount = sum8.String() r.TotalAmount = sum8.String() // Map total to wire / marketplace by payment type. switch r.PaymentType { case entity.PaymentType_WireTransfer: r.WireTransferAmount = r.TotalAmount case entity.PaymentType_AWS, entity.PaymentType_GCP, entity.PaymentType_AliCloud: r.MarketplaceAmount = r.TotalAmount case entity.PaymentType_AWSCPPOReseller: r.MarketplaceAmount = r.TotalAmount } r.IsEstimated = true ret = append(ret, r) } return ret, nil } func arTableRowToRecord(row *ARTableRow) *entity.ARTableRecord { stripeInvoicePaid := make(entity.StringSlice, len(row.StripeInvoicePaid)) for i, v := range row.StripeInvoicePaid { stripeInvoicePaid[i] = strconv.FormatBool(v) } tenantID, _ := strconv.ParseUint(row.TenantID, 10, 64) return &entity.ARTableRecord{ TenantID: uint(tenantID), PartnerID: row.PartnerID, PaymentCode: row.PaymentCode, PaymentType: row.PaymentType, BillMonth: row.BillMonth, StartDate: row.StartDate, EndDate: row.EndDate, BindedBillingConfigID: row.BindedBillingConfigID, IsEstimated: row.IsEstimated, StripeAmount: entity.StringSlice(row.StripeAmount), StripeTaxRate: entity.StringSlice(row.StripeTaxRate), StripeAmountIncludingTax: entity.StringSlice(row.StripeAmountIncludingTax), StripeInvoiceNumber: entity.StringSlice(row.StripeInvoiceNumber), WireTransferAmount: row.WireTransferAmount, MarketplaceAmount: row.MarketplaceAmount, PrepaymentCredits: row.PrepaymentCredits, TotalAmount: row.TotalAmount, InvoiceAmount: row.InvoiceAmount, StripeInvoice: entity.StringSlice(row.StripeInvoice), StripeInvoicePaid: stripeInvoicePaid, StripeInvoiceFinalizedDate: entity.StringSlice(row.StripeInvoiceFinalizedDate), StripeInvoicePaidDate: entity.StringSlice(row.StripeInvoicePaidDate), RawServerlessAmount: row.RawServerlessAmount, RawDedicatedAmount: row.RawDedicatedAmount, RawStarterAmount: row.RawStarterAmount, RawEssentialAmount: row.RawEssentialAmount, RawSupportAmount: row.RawSupportAmount, RawPremiumAmount: row.RawPremiumAmount, RawLakeAmount: row.RawLakeAmount, RawBYOCAmount: row.RawBYOCAmount, RawServerlessProgramCreditsAmount: row.RawServerlessProgramCreditsAmount, RawDedicatedProgramCreditsAmount: row.RawDedicatedProgramCreditsAmount, RawStarterProgramCreditsAmount: row.RawStarterProgramCreditsAmount, RawEssentialProgramCreditsAmount: row.RawEssentialProgramCreditsAmount, RawSupportProgramCreditsAmount: row.RawSupportProgramCreditsAmount, RawPremiumProgramCreditsAmount: row.RawPremiumProgramCreditsAmount, RawLakeProgramCreditsAmount: row.RawLakeProgramCreditsAmount, RawBYOCProgramCreditsAmount: row.RawBYOCProgramCreditsAmount, RawServerlessRedeemCreditsAmount: row.RawServerlessRedeemCreditsAmount, RawDedicatedRedeemCreditsAmount: row.RawDedicatedRedeemCreditsAmount, RawStarterRedeemCreditsAmount: row.RawStarterRedeemCreditsAmount, RawEssentialRedeemCreditsAmount: row.RawEssentialRedeemCreditsAmount, RawSupportRedeemCreditsAmount: row.RawSupportRedeemCreditsAmount, RawPremiumRedeemCreditsAmount: row.RawPremiumRedeemCreditsAmount, RawLakeRedeemCreditsAmount: row.RawLakeRedeemCreditsAmount, RawBYOCRedeemCreditsAmount: row.RawBYOCRedeemCreditsAmount, RawServerlessCustomerSuccessAmount: row.RawServerlessCustomerSuccessAmount, RawDedicatedCustomerSuccessAmount: row.RawDedicatedCustomerSuccessAmount, RawStarterCustomerSuccessAmount: row.RawStarterCustomerSuccessAmount, RawEssentialCustomerSuccessAmount: row.RawEssentialCustomerSuccessAmount, RawSupportCustomerSuccessAmount: row.RawSupportCustomerSuccessAmount, RawPremiumCustomerSuccessAmount: row.RawPremiumCustomerSuccessAmount, RawLakeCustomerSuccessAmount: row.RawLakeCustomerSuccessAmount, RawBYOCCustomerSuccessAmount: row.RawBYOCCustomerSuccessAmount, ChannelLossAmount: row.ChannelLossAmount, ServerlessAllocatedLoss: row.ServerlessAllocatedLoss, DedicatedAllocatedLoss: row.DedicatedAllocatedLoss, StarterAllocatedLoss: row.StarterAllocatedLoss, EssentialAllocatedLoss: row.EssentialAllocatedLoss, SupportAllocatedLoss: row.SupportAllocatedLoss, PremiumAllocatedLoss: row.PremiumAllocatedLoss, LakeAllocatedLoss: row.LakeAllocatedLoss, BYOCAllocatedLoss: row.BYOCAllocatedLoss, CPPODiscountableProductAmount: row.CPPODiscountableProductAmount, CPPONonDiscountableProductAmount: row.CPPONonDiscountableProductAmount, DedicatedAccrualAmount: row.DedicatedAccrualAmount, SupportAccrualAmount: row.SupportAccrualAmount, } } func (m *ARTableModel) SaveARTableToDB(rows []*ARTableRow, date time.Time) error { if len(rows) == 0 { return nil } const batchSize = 100 for i := 0; i < len(rows); i += batchSize { end := i + batchSize if end > len(rows) { end = len(rows) } batch := rows[i:end] records := make([]*entity.ARTableRecord, len(batch)) for j, row := range batch { records[j] = arTableRowToRecord(row) } result := db.GetDB().Clauses(clause.OnConflict{ Columns: []clause.Column{ {Name: "tenant_id"}, {Name: "bill_month"}, {Name: "binded_billing_config_id"}, {Name: "is_estimated"}, }, DoUpdates: clause.AssignmentColumns([]string{ "partner_id", "payment_code", "payment_type", "start_date", "end_date", "stripe_amount", "stripe_tax_rate", "stripe_amount_including_tax", "stripe_invoice_number", "wire_transfer_amount", "marketplace_amount", "prepayment_credits", "total_amount", "invoice_amount", "stripe_invoice", "stripe_invoice_paid", "stripe_invoice_finalized_date", "stripe_invoice_paid_date", "raw_serverless_amount", "raw_dedicated_amount", "raw_starter_amount", "raw_essential_amount", "raw_support_amount", "raw_premium_amount", "raw_lake_amount", "raw_byoc_amount", "raw_serverless_program_credits_amount", "raw_dedicated_program_credits_amount", "raw_starter_program_credits_amount", "raw_essential_program_credits_amount", "raw_support_program_credits_amount", "raw_premium_program_credits_amount", "raw_lake_program_credits_amount", "raw_byoc_program_credits_amount", "raw_serverless_redeem_credits_amount", "raw_dedicated_redeem_credits_amount", "raw_starter_redeem_credits_amount", "raw_essential_redeem_credits_amount", "raw_support_redeem_credits_amount", "raw_premium_redeem_credits_amount", "raw_lake_redeem_credits_amount", "raw_byoc_redeem_credits_amount", "raw_serverless_customer_success_amount", "raw_dedicated_customer_success_amount", "raw_starter_customer_success_amount", "raw_essential_customer_success_amount", "raw_support_customer_success_amount", "raw_premium_customer_success_amount", "raw_lake_customer_success_amount", "raw_byoc_customer_success_amount", "channel_loss_amount", "serverless_allocated_loss", "dedicated_allocated_loss", "starter_allocated_loss", "essential_allocated_loss", "support_allocated_loss", "premium_allocated_loss", "lake_allocated_loss", "byoc_allocated_loss", "cppo_discountable_product_amount", "cppo_non_discountable_product_amount", "dedicated_accrual_amount", "support_accrual_amount", "updated_at", }), }).Create(&records) if result.Error != nil { log.Error("failed to upsert AR table batch records", zap.Error(result.Error), zap.Int("batchStart", i), zap.Int("batchSize", len(batch))) return result.Error } } log.Info("saved AR table records to DB", zap.Int("count", len(rows))) return nil } func (m *ARTableModel) ListARTableFromDB(year int, month int, isEstimated *bool) ([]entity.ARTableRecord, error) { billMonth := fmt.Sprintf("%04d-%02d", year, month) q := db.GetDB().Where("bill_month = ?", billMonth) if isEstimated != nil { q = q.Where("is_estimated = ?", *isEstimated) } var records []entity.ARTableRecord err := q.Order("tenant_id asc").Find(&records).Error if err != nil { return nil, err } return records, nil } func (m *ARTableModel) DownloadARTables(query *forms.ARTableListQuery) ([]byte, error) { q := db.GetDB().Model(&entity.ARTableRecord{}) if query.Year != 0 && query.Month != 0 { billMonth := fmt.Sprintf("%04d-%02d", query.Year, query.Month) q = q.Where("bill_month = ?", billMonth) } if query.IsEstimated != nil { q = q.Where("is_estimated = ?", *query.IsEstimated) } if query.TenantID != nil { q = q.Where("tenant_id = ?", *query.TenantID) } if query.PaymentType != "" { q = q.Where("payment_type = ?", query.PaymentType) } if query.BindedBillingConfigID != nil { q = q.Where("binded_billing_config_id = ?", *query.BindedBillingConfigID) } var records []entity.ARTableRecord err := q.Order("tenant_id asc").Find(&records).Error if err != nil { return nil, err } return m.renderARTableRecordsCSV(records) } func (m *ARTableModel) renderARTableRecordsCSV(records []entity.ARTableRecord) ([]byte, error) { header := []string{ "id", "bill_month", "start_date", "end_date", "tenant_id", "partner_id", "payment_code", "payment_type", "stripe_amount", "stripe_tax_rate", "stripe_amount_including_tax", "stripe_invoice_number", "wire_transfer_amount", "marketplace_amount", "prepayment_credits", "total_amount", "invoice_amount", "is_estimated", "binded_billing_config_id", "stripe_invoice", "stripe_invoice_paid", "stripe_invoice_finalized_date", "stripe_invoice_paid_date", "raw_serverless_amount", "raw_dedicated_amount", "raw_starter_amount", "raw_essential_amount", "raw_support_amount", "raw_premium_amount", "raw_lake_amount", "raw_byoc_amount", "serverless_amount", "dedicated_amount", "starter_amount", "essential_amount", "support_amount", "premium_amount", "lake_amount", "byoc_amount", "raw_serverless_program_credits_amount", "raw_dedicated_program_credits_amount", "raw_starter_program_credits_amount", "raw_essential_program_credits_amount", "raw_support_program_credits_amount", "raw_premium_program_credits_amount", "raw_lake_program_credits_amount", "raw_byoc_program_credits_amount", "raw_serverless_redeem_credits_amount", "raw_dedicated_redeem_credits_amount", "raw_starter_redeem_credits_amount", "raw_essential_redeem_credits_amount", "raw_support_redeem_credits_amount", "raw_premium_redeem_credits_amount", "raw_lake_redeem_credits_amount", "raw_byoc_redeem_credits_amount", "raw_serverless_customer_success_amount", "raw_dedicated_customer_success_amount", "raw_starter_customer_success_amount", "raw_essential_customer_success_amount", "raw_support_customer_success_amount", "raw_premium_customer_success_amount", "raw_lake_customer_success_amount", "raw_byoc_customer_success_amount", "channel_loss_amount", "serverless_allocated_loss", "dedicated_allocated_loss", "starter_allocated_loss", "essential_allocated_loss", "support_allocated_loss", "premium_allocated_loss", "lake_allocated_loss", "byoc_allocated_loss", "cppo_discountable_product_amount", "cppo_non_discountable_product_amount", "dedicated_accrual_amount", "support_accrual_amount", "created_at", "updated_at", } buf := new(bytes.Buffer) writer := csv.NewWriter(buf) if err := writer.Write(header); err != nil { return nil, err } for _, r := range records { data := []string{ strconv.FormatUint(uint64(r.ID), 10), r.BillMonth, r.StartDate, r.EndDate, strconv.FormatUint(uint64(r.TenantID), 10), r.PartnerID, r.PaymentCode, r.PaymentType, serialize([]string(r.StripeAmount)), serialize([]string(r.StripeTaxRate)), serialize([]string(r.StripeAmountIncludingTax)), serialize([]string(r.StripeInvoiceNumber)), r.WireTransferAmount, r.MarketplaceAmount, r.PrepaymentCredits, subDecimal(r.InvoiceAmount, r.ChannelLossAmount), r.InvoiceAmount, strconv.FormatBool(r.IsEstimated), strconv.FormatUint(uint64(r.BindedBillingConfigID), 10), serialize([]string(r.StripeInvoice)), serialize([]string(r.StripeInvoicePaid)), serialize([]string(r.StripeInvoiceFinalizedDate)), serialize([]string(r.StripeInvoicePaidDate)), r.RawServerlessAmount, r.RawDedicatedAmount, r.RawStarterAmount, r.RawEssentialAmount, r.RawSupportAmount, r.RawPremiumAmount, r.RawLakeAmount, r.RawBYOCAmount, subDecimal(r.RawServerlessAmount, r.ServerlessAllocatedLoss), subDecimal(r.RawDedicatedAmount, r.DedicatedAllocatedLoss), subDecimal(r.RawStarterAmount, r.StarterAllocatedLoss), subDecimal(r.RawEssentialAmount, r.EssentialAllocatedLoss), subDecimal(r.RawSupportAmount, r.SupportAllocatedLoss), subDecimal(r.RawPremiumAmount, r.PremiumAllocatedLoss), subDecimal(r.RawLakeAmount, r.LakeAllocatedLoss), subDecimal(r.RawBYOCAmount, r.BYOCAllocatedLoss), r.RawServerlessProgramCreditsAmount, r.RawDedicatedProgramCreditsAmount, r.RawStarterProgramCreditsAmount, r.RawEssentialProgramCreditsAmount, r.RawSupportProgramCreditsAmount, r.RawPremiumProgramCreditsAmount, r.RawLakeProgramCreditsAmount, r.RawBYOCProgramCreditsAmount, r.RawServerlessRedeemCreditsAmount, r.RawDedicatedRedeemCreditsAmount, r.RawStarterRedeemCreditsAmount, r.RawEssentialRedeemCreditsAmount, r.RawSupportRedeemCreditsAmount, r.RawPremiumRedeemCreditsAmount, r.RawLakeRedeemCreditsAmount, r.RawBYOCRedeemCreditsAmount, r.RawServerlessCustomerSuccessAmount, r.RawDedicatedCustomerSuccessAmount, r.RawStarterCustomerSuccessAmount, r.RawEssentialCustomerSuccessAmount, r.RawSupportCustomerSuccessAmount, r.RawPremiumCustomerSuccessAmount, r.RawLakeCustomerSuccessAmount, r.RawBYOCCustomerSuccessAmount, r.ChannelLossAmount, r.ServerlessAllocatedLoss, r.DedicatedAllocatedLoss, r.StarterAllocatedLoss, r.EssentialAllocatedLoss, r.SupportAllocatedLoss, r.PremiumAllocatedLoss, r.LakeAllocatedLoss, r.BYOCAllocatedLoss, r.CPPODiscountableProductAmount, r.CPPONonDiscountableProductAmount, r.DedicatedAccrualAmount, r.SupportAccrualAmount, r.CreatedAt.Format("2006-01-02T15:04:05Z"), r.UpdatedAt.Format("2006-01-02T15:04:05Z"), } if err := writer.Write(data); err != nil { return nil, err } } writer.Flush() return buf.Bytes(), nil } // ARTableListResponse is the paginated response for listing AR table records. type ARTableListResponse = forms.ARTableListResponse // applySupportDelta flows a support-fee change (e.g., contract-based // override minus the cost explorer's metering-based value) into the // cumulative AR accumulators. The cost explorer seeds invoice / total / // wire_transfer / marketplace with the metering-based support; without // this helper those accumulators keep the stale value even after // raw_support_amount has been overwritten with the post-hoc figure. // // `hasCommitment` gates total / wire / marketplace — those accumulators // are only meaningful when commitment > 0 (otherwise the cost explorer // path leaves them empty / zero). // // `paymentType` selects wire_transfer_amount (WireTransfer) vs // marketplace_amount (AWS / GCP / AliCloud / AWSCPPOReseller); other // payment types don't touch either column. Wire / marketplace are // clamped to 0 if the delta would drive them negative (rare: when the // post-hoc support figure is smaller than the metering baseline, e.g., // a commitment floor bigger than the contract value). func applySupportDelta( invoice, total, wireTransfer, marketplace string, delta decimal.Decimal, paymentType string, hasCommitment bool, ) (newInvoice, newTotal, newWireTransfer, newMarketplace string) { newInvoice = decimal.RequireFromString(invoice).Add(delta).String() if !hasCommitment { return newInvoice, total, wireTransfer, marketplace } newTotal = decimal.RequireFromString(total).Add(delta).String() // Default: pass wire / marketplace through. Only override the // matching column for the matching payment type. newWireTransfer = wireTransfer newMarketplace = marketplace addToAcc := func(current string) string { if current == "" { return current } v := decimal.RequireFromString(current).Add(delta) if v.IsNegative() { v = decimal.Zero } return v.String() } switch paymentType { case entity.PaymentType_WireTransfer: newWireTransfer = addToAcc(wireTransfer) case entity.PaymentType_AWS, entity.PaymentType_GCP, entity.PaymentType_AliCloud, entity.PaymentType_AWSCPPOReseller: newMarketplace = addToAcc(marketplace) } return newInvoice, newTotal, newWireTransfer, newMarketplace } func subDecimal(a, b string) string { da := decimal.Zero if a != "" { da = decimal.RequireFromString(a) } db := decimal.Zero if b != "" { db = decimal.RequireFromString(b) } return da.Sub(db).String() } func arTableRecordToResponse(r *entity.ARTableRecord) forms.ARTableRecordResponse { return forms.ARTableRecordResponse{ ID: r.ID, CreatedAt: r.CreatedAt.Format("2006-01-02T15:04:05Z"), UpdatedAt: r.UpdatedAt.Format("2006-01-02T15:04:05Z"), TenantID: r.TenantID, PartnerID: r.PartnerID, PaymentCode: r.PaymentCode, PaymentType: r.PaymentType, BillMonth: r.BillMonth, StartDate: r.StartDate, EndDate: r.EndDate, BindedBillingConfigID: r.BindedBillingConfigID, IsEstimated: r.IsEstimated, StripeAmount: []string(r.StripeAmount), StripeTaxRate: []string(r.StripeTaxRate), StripeAmountIncludingTax: []string(r.StripeAmountIncludingTax), StripeInvoiceNumber: []string(r.StripeInvoiceNumber), WireTransferAmount: r.WireTransferAmount, MarketplaceAmount: r.MarketplaceAmount, PrepaymentCredits: r.PrepaymentCredits, TotalAmount: subDecimal(r.InvoiceAmount, r.ChannelLossAmount), InvoiceAmount: r.InvoiceAmount, StripeInvoice: []string(r.StripeInvoice), StripeInvoicePaid: []string(r.StripeInvoicePaid), StripeInvoiceFinalizedDate: []string(r.StripeInvoiceFinalizedDate), StripeInvoicePaidDate: []string(r.StripeInvoicePaidDate), RawServerlessAmount: r.RawServerlessAmount, RawDedicatedAmount: r.RawDedicatedAmount, RawStarterAmount: r.RawStarterAmount, RawEssentialAmount: r.RawEssentialAmount, RawSupportAmount: r.RawSupportAmount, RawPremiumAmount: r.RawPremiumAmount, RawLakeAmount: r.RawLakeAmount, RawBYOCAmount: r.RawBYOCAmount, ServerlessAmount: subDecimal(r.RawServerlessAmount, r.ServerlessAllocatedLoss), DedicatedAmount: subDecimal(r.RawDedicatedAmount, r.DedicatedAllocatedLoss), StarterAmount: subDecimal(r.RawStarterAmount, r.StarterAllocatedLoss), EssentialAmount: subDecimal(r.RawEssentialAmount, r.EssentialAllocatedLoss), SupportAmount: subDecimal(r.RawSupportAmount, r.SupportAllocatedLoss), PremiumAmount: subDecimal(r.RawPremiumAmount, r.PremiumAllocatedLoss), LakeAmount: subDecimal(r.RawLakeAmount, r.LakeAllocatedLoss), BYOCAmount: subDecimal(r.RawBYOCAmount, r.BYOCAllocatedLoss), RawServerlessProgramCreditsAmount: r.RawServerlessProgramCreditsAmount, RawDedicatedProgramCreditsAmount: r.RawDedicatedProgramCreditsAmount, RawStarterProgramCreditsAmount: r.RawStarterProgramCreditsAmount, RawEssentialProgramCreditsAmount: r.RawEssentialProgramCreditsAmount, RawSupportProgramCreditsAmount: r.RawSupportProgramCreditsAmount, RawPremiumProgramCreditsAmount: r.RawPremiumProgramCreditsAmount, RawLakeProgramCreditsAmount: r.RawLakeProgramCreditsAmount, RawBYOCProgramCreditsAmount: r.RawBYOCProgramCreditsAmount, RawServerlessRedeemCreditsAmount: r.RawServerlessRedeemCreditsAmount, RawDedicatedRedeemCreditsAmount: r.RawDedicatedRedeemCreditsAmount, RawStarterRedeemCreditsAmount: r.RawStarterRedeemCreditsAmount, RawEssentialRedeemCreditsAmount: r.RawEssentialRedeemCreditsAmount, RawSupportRedeemCreditsAmount: r.RawSupportRedeemCreditsAmount, RawPremiumRedeemCreditsAmount: r.RawPremiumRedeemCreditsAmount, RawLakeRedeemCreditsAmount: r.RawLakeRedeemCreditsAmount, RawBYOCRedeemCreditsAmount: r.RawBYOCRedeemCreditsAmount, RawServerlessCustomerSuccessAmount: r.RawServerlessCustomerSuccessAmount, RawDedicatedCustomerSuccessAmount: r.RawDedicatedCustomerSuccessAmount, RawStarterCustomerSuccessAmount: r.RawStarterCustomerSuccessAmount, RawEssentialCustomerSuccessAmount: r.RawEssentialCustomerSuccessAmount, RawSupportCustomerSuccessAmount: r.RawSupportCustomerSuccessAmount, RawPremiumCustomerSuccessAmount: r.RawPremiumCustomerSuccessAmount, RawLakeCustomerSuccessAmount: r.RawLakeCustomerSuccessAmount, RawBYOCCustomerSuccessAmount: r.RawBYOCCustomerSuccessAmount, ChannelLossAmount: r.ChannelLossAmount, ServerlessAllocatedLoss: r.ServerlessAllocatedLoss, DedicatedAllocatedLoss: r.DedicatedAllocatedLoss, StarterAllocatedLoss: r.StarterAllocatedLoss, EssentialAllocatedLoss: r.EssentialAllocatedLoss, SupportAllocatedLoss: r.SupportAllocatedLoss, PremiumAllocatedLoss: r.PremiumAllocatedLoss, LakeAllocatedLoss: r.LakeAllocatedLoss, BYOCAllocatedLoss: r.BYOCAllocatedLoss, CPPODiscountableProductAmount: r.CPPODiscountableProductAmount, CPPONonDiscountableProductAmount: r.CPPONonDiscountableProductAmount, DedicatedAccrualAmount: r.DedicatedAccrualAmount, SupportAccrualAmount: r.SupportAccrualAmount, } } func (m *ARTableModel) ListARTableFromDBWithPagination(query *forms.ARTableListQuery) (*ARTableListResponse, error) { q := db.GetDB().Model(&entity.ARTableRecord{}) if query.Year != 0 && query.Month != 0 { billMonth := fmt.Sprintf("%04d-%02d", query.Year, query.Month) q = q.Where("bill_month = ?", billMonth) } if query.IsEstimated != nil { q = q.Where("is_estimated = ?", *query.IsEstimated) } if query.TenantID != nil { q = q.Where("tenant_id = ?", *query.TenantID) } if query.PaymentType != "" { q = q.Where("payment_type = ?", query.PaymentType) } if query.BindedBillingConfigID != nil { q = q.Where("binded_billing_config_id = ?", *query.BindedBillingConfigID) } var total int64 if err := q.Count(&total).Error; err != nil { return nil, err } var records []entity.ARTableRecord offset := (query.Page - 1) * query.PageSize err := q.Order("tenant_id asc").Offset(offset).Limit(query.PageSize).Find(&records).Error if err != nil { return nil, err } respRecords := make([]forms.ARTableRecordResponse, len(records)) for i := range records { respRecords[i] = arTableRecordToResponse(&records[i]) } return &ARTableListResponse{ Records: respRecords, Page: query.Page, PageSize: query.PageSize, TotalResults: total, }, nil } func (m *ARTableModel) GetARTableRecordByID(id uint) (*forms.ARTableRecordResponse, error) { var record entity.ARTableRecord err := db.GetDB().Where("id = ?", id).First(&record).Error if err != nil { return nil, billing_errors.ObjectNotExistsError } resp := arTableRecordToResponse(&record) return &resp, nil } func (m *ARTableModel) CreateARTableRecord(input *forms.ARTableCreateInput) (*forms.ARTableRecordResponse, error) { record := &entity.ARTableRecord{ TenantID: input.TenantID, PartnerID: input.PartnerID, PaymentCode: input.PaymentCode, PaymentType: input.PaymentType, BillMonth: input.BillMonth, StartDate: input.StartDate, EndDate: input.EndDate, BindedBillingConfigID: input.BindedBillingConfigID, IsEstimated: input.IsEstimated, StripeAmount: entity.StringSlice(input.StripeAmount), StripeTaxRate: entity.StringSlice(input.StripeTaxRate), StripeAmountIncludingTax: entity.StringSlice(input.StripeAmountIncludingTax), StripeInvoiceNumber: entity.StringSlice(input.StripeInvoiceNumber), WireTransferAmount: input.WireTransferAmount, MarketplaceAmount: input.MarketplaceAmount, PrepaymentCredits: input.PrepaymentCredits, TotalAmount: input.TotalAmount, InvoiceAmount: input.InvoiceAmount, StripeInvoice: entity.StringSlice(input.StripeInvoice), StripeInvoicePaid: entity.StringSlice(input.StripeInvoicePaid), StripeInvoiceFinalizedDate: entity.StringSlice(input.StripeInvoiceFinalizedDate), StripeInvoicePaidDate: entity.StringSlice(input.StripeInvoicePaidDate), RawServerlessAmount: input.RawServerlessAmount, RawDedicatedAmount: input.RawDedicatedAmount, RawStarterAmount: input.RawStarterAmount, RawEssentialAmount: input.RawEssentialAmount, RawSupportAmount: input.RawSupportAmount, RawPremiumAmount: input.RawPremiumAmount, RawLakeAmount: input.RawLakeAmount, RawBYOCAmount: input.RawBYOCAmount, RawServerlessProgramCreditsAmount: input.RawServerlessProgramCreditsAmount, RawDedicatedProgramCreditsAmount: input.RawDedicatedProgramCreditsAmount, RawStarterProgramCreditsAmount: input.RawStarterProgramCreditsAmount, RawEssentialProgramCreditsAmount: input.RawEssentialProgramCreditsAmount, RawSupportProgramCreditsAmount: input.RawSupportProgramCreditsAmount, RawPremiumProgramCreditsAmount: input.RawPremiumProgramCreditsAmount, RawLakeProgramCreditsAmount: input.RawLakeProgramCreditsAmount, RawBYOCProgramCreditsAmount: input.RawBYOCProgramCreditsAmount, RawServerlessRedeemCreditsAmount: input.RawServerlessRedeemCreditsAmount, RawDedicatedRedeemCreditsAmount: input.RawDedicatedRedeemCreditsAmount, RawStarterRedeemCreditsAmount: input.RawStarterRedeemCreditsAmount, RawEssentialRedeemCreditsAmount: input.RawEssentialRedeemCreditsAmount, RawSupportRedeemCreditsAmount: input.RawSupportRedeemCreditsAmount, RawPremiumRedeemCreditsAmount: input.RawPremiumRedeemCreditsAmount, RawLakeRedeemCreditsAmount: input.RawLakeRedeemCreditsAmount, RawBYOCRedeemCreditsAmount: input.RawBYOCRedeemCreditsAmount, RawServerlessCustomerSuccessAmount: input.RawServerlessCustomerSuccessAmount, RawDedicatedCustomerSuccessAmount: input.RawDedicatedCustomerSuccessAmount, RawStarterCustomerSuccessAmount: input.RawStarterCustomerSuccessAmount, RawEssentialCustomerSuccessAmount: input.RawEssentialCustomerSuccessAmount, RawSupportCustomerSuccessAmount: input.RawSupportCustomerSuccessAmount, RawPremiumCustomerSuccessAmount: input.RawPremiumCustomerSuccessAmount, RawLakeCustomerSuccessAmount: input.RawLakeCustomerSuccessAmount, RawBYOCCustomerSuccessAmount: input.RawBYOCCustomerSuccessAmount, ChannelLossAmount: input.ChannelLossAmount, ServerlessAllocatedLoss: input.ServerlessAllocatedLoss, DedicatedAllocatedLoss: input.DedicatedAllocatedLoss, StarterAllocatedLoss: input.StarterAllocatedLoss, EssentialAllocatedLoss: input.EssentialAllocatedLoss, SupportAllocatedLoss: input.SupportAllocatedLoss, PremiumAllocatedLoss: input.PremiumAllocatedLoss, LakeAllocatedLoss: input.LakeAllocatedLoss, BYOCAllocatedLoss: input.BYOCAllocatedLoss, CPPODiscountableProductAmount: input.CPPODiscountableProductAmount, CPPONonDiscountableProductAmount: input.CPPONonDiscountableProductAmount, DedicatedAccrualAmount: input.DedicatedAccrualAmount, SupportAccrualAmount: input.SupportAccrualAmount, } if err := db.GetDB().Create(record).Error; err != nil { return nil, err } resp := arTableRecordToResponse(record) return &resp, nil } func (m *ARTableModel) UpdateARTableRecord(id uint, input *forms.ARTableUpdateInput) (*forms.ARTableRecordResponse, error) { var existing entity.ARTableRecord if err := db.GetDB().Where("id = ?", id).First(&existing).Error; err != nil { return nil, billing_errors.ObjectNotExistsError } updates := map[string]interface{}{} if input.PartnerID != "" { updates["partner_id"] = input.PartnerID } if input.PaymentCode != "" { updates["payment_code"] = input.PaymentCode } if input.PaymentType != "" { updates["payment_type"] = input.PaymentType } if input.BillMonth != "" { updates["bill_month"] = input.BillMonth } if input.StartDate != "" { updates["start_date"] = input.StartDate } if input.EndDate != "" { updates["end_date"] = input.EndDate } if input.BindedBillingConfigID != 0 { updates["binded_billing_config_id"] = input.BindedBillingConfigID } updates["is_estimated"] = input.IsEstimated if input.StripeAmount != nil { updates["stripe_amount"] = entity.StringSlice(input.StripeAmount) } if input.StripeTaxRate != nil { updates["stripe_tax_rate"] = entity.StringSlice(input.StripeTaxRate) } if input.StripeAmountIncludingTax != nil { updates["stripe_amount_including_tax"] = entity.StringSlice(input.StripeAmountIncludingTax) } if input.StripeInvoiceNumber != nil { updates["stripe_invoice_number"] = entity.StringSlice(input.StripeInvoiceNumber) } if input.WireTransferAmount != "" { updates["wire_transfer_amount"] = input.WireTransferAmount } if input.MarketplaceAmount != "" { updates["marketplace_amount"] = input.MarketplaceAmount } if input.PrepaymentCredits != "" { updates["prepayment_credits"] = input.PrepaymentCredits } if input.TotalAmount != "" { updates["total_amount"] = input.TotalAmount } if input.InvoiceAmount != "" { updates["invoice_amount"] = input.InvoiceAmount } if input.StripeInvoice != nil { updates["stripe_invoice"] = entity.StringSlice(input.StripeInvoice) } if input.StripeInvoicePaid != nil { updates["stripe_invoice_paid"] = entity.StringSlice(input.StripeInvoicePaid) } if input.StripeInvoiceFinalizedDate != nil { updates["stripe_invoice_finalized_date"] = entity.StringSlice(input.StripeInvoiceFinalizedDate) } if input.StripeInvoicePaidDate != nil { updates["stripe_invoice_paid_date"] = entity.StringSlice(input.StripeInvoicePaidDate) } if input.RawServerlessAmount != "" { updates["raw_serverless_amount"] = input.RawServerlessAmount } if input.RawDedicatedAmount != "" { updates["raw_dedicated_amount"] = input.RawDedicatedAmount } if input.RawStarterAmount != "" { updates["raw_starter_amount"] = input.RawStarterAmount } if input.RawEssentialAmount != "" { updates["raw_essential_amount"] = input.RawEssentialAmount } if input.RawSupportAmount != "" { updates["raw_support_amount"] = input.RawSupportAmount } if input.RawPremiumAmount != "" { updates["raw_premium_amount"] = input.RawPremiumAmount } if input.RawLakeAmount != "" { updates["raw_lake_amount"] = input.RawLakeAmount } if input.RawBYOCAmount != "" { updates["raw_byoc_amount"] = input.RawBYOCAmount } if input.RawServerlessProgramCreditsAmount != "" { updates["raw_serverless_program_credits_amount"] = input.RawServerlessProgramCreditsAmount } if input.RawDedicatedProgramCreditsAmount != "" { updates["raw_dedicated_program_credits_amount"] = input.RawDedicatedProgramCreditsAmount } if input.RawStarterProgramCreditsAmount != "" { updates["raw_starter_program_credits_amount"] = input.RawStarterProgramCreditsAmount } if input.RawEssentialProgramCreditsAmount != "" { updates["raw_essential_program_credits_amount"] = input.RawEssentialProgramCreditsAmount } if input.RawSupportProgramCreditsAmount != "" { updates["raw_support_program_credits_amount"] = input.RawSupportProgramCreditsAmount } if input.RawPremiumProgramCreditsAmount != "" { updates["raw_premium_program_credits_amount"] = input.RawPremiumProgramCreditsAmount } if input.RawLakeProgramCreditsAmount != "" { updates["raw_lake_program_credits_amount"] = input.RawLakeProgramCreditsAmount } if input.RawBYOCProgramCreditsAmount != "" { updates["raw_byoc_program_credits_amount"] = input.RawBYOCProgramCreditsAmount } if input.RawServerlessRedeemCreditsAmount != "" { updates["raw_serverless_redeem_credits_amount"] = input.RawServerlessRedeemCreditsAmount } if input.RawDedicatedRedeemCreditsAmount != "" { updates["raw_dedicated_redeem_credits_amount"] = input.RawDedicatedRedeemCreditsAmount } if input.RawStarterRedeemCreditsAmount != "" { updates["raw_starter_redeem_credits_amount"] = input.RawStarterRedeemCreditsAmount } if input.RawEssentialRedeemCreditsAmount != "" { updates["raw_essential_redeem_credits_amount"] = input.RawEssentialRedeemCreditsAmount } if input.RawSupportRedeemCreditsAmount != "" { updates["raw_support_redeem_credits_amount"] = input.RawSupportRedeemCreditsAmount } if input.RawPremiumRedeemCreditsAmount != "" { updates["raw_premium_redeem_credits_amount"] = input.RawPremiumRedeemCreditsAmount } if input.RawLakeRedeemCreditsAmount != "" { updates["raw_lake_redeem_credits_amount"] = input.RawLakeRedeemCreditsAmount } if input.RawBYOCRedeemCreditsAmount != "" { updates["raw_byoc_redeem_credits_amount"] = input.RawBYOCRedeemCreditsAmount } if input.RawServerlessCustomerSuccessAmount != "" { updates["raw_serverless_customer_success_amount"] = input.RawServerlessCustomerSuccessAmount } if input.RawDedicatedCustomerSuccessAmount != "" { updates["raw_dedicated_customer_success_amount"] = input.RawDedicatedCustomerSuccessAmount } if input.RawStarterCustomerSuccessAmount != "" { updates["raw_starter_customer_success_amount"] = input.RawStarterCustomerSuccessAmount } if input.RawEssentialCustomerSuccessAmount != "" { updates["raw_essential_customer_success_amount"] = input.RawEssentialCustomerSuccessAmount } if input.RawSupportCustomerSuccessAmount != "" { updates["raw_support_customer_success_amount"] = input.RawSupportCustomerSuccessAmount } if input.RawPremiumCustomerSuccessAmount != "" { updates["raw_premium_customer_success_amount"] = input.RawPremiumCustomerSuccessAmount } if input.RawLakeCustomerSuccessAmount != "" { updates["raw_lake_customer_success_amount"] = input.RawLakeCustomerSuccessAmount } if input.RawBYOCCustomerSuccessAmount != "" { updates["raw_byoc_customer_success_amount"] = input.RawBYOCCustomerSuccessAmount } if input.ChannelLossAmount != "" { updates["channel_loss_amount"] = input.ChannelLossAmount } if input.ServerlessAllocatedLoss != "" { updates["serverless_allocated_loss"] = input.ServerlessAllocatedLoss } if input.DedicatedAllocatedLoss != "" { updates["dedicated_allocated_loss"] = input.DedicatedAllocatedLoss } if input.StarterAllocatedLoss != "" { updates["starter_allocated_loss"] = input.StarterAllocatedLoss } if input.EssentialAllocatedLoss != "" { updates["essential_allocated_loss"] = input.EssentialAllocatedLoss } if input.SupportAllocatedLoss != "" { updates["support_allocated_loss"] = input.SupportAllocatedLoss } if input.PremiumAllocatedLoss != "" { updates["premium_allocated_loss"] = input.PremiumAllocatedLoss } if input.LakeAllocatedLoss != "" { updates["lake_allocated_loss"] = input.LakeAllocatedLoss } if input.BYOCAllocatedLoss != "" { updates["byoc_allocated_loss"] = input.BYOCAllocatedLoss } if input.CPPODiscountableProductAmount != "" { updates["cppo_discountable_product_amount"] = input.CPPODiscountableProductAmount } if input.CPPONonDiscountableProductAmount != "" { updates["cppo_non_discountable_product_amount"] = input.CPPONonDiscountableProductAmount } if input.DedicatedAccrualAmount != "" { updates["dedicated_accrual_amount"] = input.DedicatedAccrualAmount } if input.SupportAccrualAmount != "" { updates["support_accrual_amount"] = input.SupportAccrualAmount } if len(updates) == 0 { resp := arTableRecordToResponse(&existing) return &resp, nil } if err := db.GetDB().Model(&existing).Updates(updates).Error; err != nil { return nil, err } return m.GetARTableRecordByID(id) } // prorateCommitment returns the commitment cost prorated by days elapsed in the month. // If the month has fully elapsed, returns the full commitment cost unchanged. func prorateCommitment(commitmentCost decimal.Decimal, startDate, endDate time.Time) decimal.Decimal { if !commitmentCost.IsPositive() { return commitmentCost } now := timeutils.GetNow() daysInMonth := int(endDate.Sub(startDate).Hours() / 24) var daysElapsed int if now.Before(startDate) { daysElapsed = 0 } else if !now.Before(endDate) { daysElapsed = daysInMonth } else { daysElapsed = int(now.Sub(startDate).Hours()/24) + 1 } if daysElapsed < daysInMonth { return commitmentCost. Mul(decimal.NewFromInt(int64(daysElapsed))). Div(decimal.NewFromInt(int64(daysInMonth))) } return commitmentCost } // ARTableTenantBillingPair is the (tenant_id, billing_config_id) tuple // produced by queryTenantBillingPairsForMonth and consumed by // ListEstimatedPayments when building the candidate tenant list for the // AR table estimate. Exported so the tests/ package can compare against // the helper's output without re-exporting internal types. type ARTableTenantBillingPair struct { TenantID uint BindedBillingConfigID uint } // queryTenantBillingPairsForMonth walks user_unit_costs one day at a time // (to keep each sub-query under the DB timeout) and returns the set of // (tenant_id, billing_config_id) pairs that have produced a DONE user // unit cost in [start, end). The helper is the single source of truth for // "which tenants/billing-configs had any usage this month" used by the // AR table estimate. // // IMPORTANT: the WHERE clause accepts support-plan SKUs (e.g. // "support-plan:fixed_price") regardless of the `internal` flag. Support // plan rows are written by SecondaryMeasure and end up with internal=true // because no matching BindedPackage is found for the support SKU // (see model/billing/measure.go::procRawUnitCost). Without this OR // branch, a tenant whose only AR-relevant cost is the support fee would // be silently skipped by the estimate. func (m *ARTableModel) QueryTenantBillingPairsForMonth(start, end time.Time, disabledTenantIDs []uint) (map[ARTableTenantBillingPair]struct{}, error) { queryTimeout := 120 * time.Second // batch job, more generous than HTTP per-query timeout seen := make(map[ARTableTenantBillingPair]struct{}) cur := time.Date(start.Year(), start.Month(), start.Day(), 0, 0, 0, 0, start.Location()) for cur.Before(end) { next := cur.AddDate(0, 0, 1) if next.After(end) { next = end } ctx, cancel := context.WithTimeout(context.Background(), queryTimeout) var dayRes []ARTableTenantBillingPair q := db.GetReadOnlyDB().WithContext(ctx).Table("user_unit_costs force index(tenant_timestamp_status_cluster)"). Select("tenant_id, binded_billing_config_id"). Where("cost_time_stamp >= ? AND cost_time_stamp < ?", cur.Unix()-1, next.Unix()). Where("status = ?", entity.UserUnitCostStatus_DONE). // Accept normal usage (internal=false) OR support-plan SKUs // (which are written with internal=true by SecondaryMeasure // because the support SKUs have no matching BindedPackage). Where("internal = false OR unit LIKE 'support-plan:%'") if len(disabledTenantIDs) > 0 { q = q.Where("tenant_id NOT IN ?", disabledTenantIDs) } err := q.Group("tenant_id, binded_billing_config_id"). Find(&dayRes).Error cancel() if err != nil { return nil, err } for _, r := range dayRes { seen[r] = struct{}{} } cur = next } // Add active support-plan tenants even when no metering row has been // written for the current month yet. This catches the case where // SendSupportPlanMetering is late (e.g. runs at the start of the next // month, or hasn't run for a tenant whose only cost is support) — the // AR estimate on the 1st would otherwise drop them. The filter matches // any binded_support_plans row whose [active_date, closed/expired_date] // window intersects [start, end). if err := m.addActiveSupportPlanTenants(&seen, start, end); err != nil { return nil, err } return seen, nil } // addActiveSupportPlanTenants augments the candidate set with any tenant // that has an active BindedSupportPlan in [start, end) but has not yet // produced a user_unit_costs row for the current month. Without this // step, a support-only tenant whose SendSupportPlanMetering hasn't yet // fired (typically because the AR estimate runs at the start of the // next month, before the support-plan metering worker has caught up) // would be silently dropped from the AR estimate. // // For each active BindedSupportPlan, the helper resolves the tenant's // billing_config_id from the billing_configs table — the plan is attached // to the tenant, but billing_config is a separate concern. We pick the // billing_config whose [StartActiveTime, EndActiveTime] window covers the // estimate month (i.e. the one that would receive the support-fee invoice). func (m *ARTableModel) addActiveSupportPlanTenants(seen *map[ARTableTenantBillingPair]struct{}, start, end time.Time) error { type spPair struct { TenantID uint // Use the entity column name `id` for billing_configs; gorm picks // the table name from Model(&entity.BillingConfig{}) — the column // is the primary key and we alias it to billing_config_id below. BillingConfigID uint `gorm:"column:billing_config_id"` } var rows []spPair err := db.GetDB().Model(&entity.BindedSupportPlan{}). Select("DISTINCT binded_support_plans.tenant_id AS tenant_id, billing_configs.id AS billing_config_id"). Joins("LEFT JOIN billing_configs ON billing_configs.tenant_id = binded_support_plans.tenant_id"). Where("binded_support_plans.status = ?", entity.BindedSupportPlanStatus_ACTIVE). Where("binded_support_plans.active_date < ?", end). Where("binded_support_plans.closed_date IS NULL OR binded_support_plans.closed_date >= ?", start). Where("binded_support_plans.expired_date IS NULL OR binded_support_plans.expired_date >= ?", start). // pick the billing_config active in the estimate month Where("billing_configs.start_active_time <= ?", end). Where("billing_configs.end_active_time IS NULL OR billing_configs.end_active_time > ?", start). Where("billing_configs.deleted_at IS NULL"). Find(&rows).Error if err != nil { return err } for _, r := range rows { (*seen)[ARTableTenantBillingPair{TenantID: r.TenantID, BindedBillingConfigID: r.BillingConfigID}] = struct{}{} } return nil } // supportCommitmentOwner is the Owner value stored in commitment_records for // support-plan commitment invoices. ARTableModel.TenantCommitmentCostForSupport // only counts commitment invoices whose commitment_records.Owner matches this // value, so non-support commitments (compute, serverless, future SKUs that // share the InvoiceType_COMMITMENT wire type) are not attributed to support. // // IMPORTANT: this whitelist is intentionally narrow. If production has // historical commitment_records rows whose Owner is something other than // this value, those rows will be silently excluded from the support // estimate. Migrate them (or extend the whitelist) before deploying. See // docs/billing-commitment-owner-migration.md for the runbook. const supportCommitmentOwner = "support-plan:commitment" // TenantCommitmentCostForSupport returns the total cost_price of all // commitment-type invoices for the given tenant within [startDate, endDate), // scoped to support-purpose commitments only. // // It JOINs commitment_records on (tenant_id, binded_billing_config_id, // invoice_date) and filters by commitment_records.Owner = supportCommitmentOwner. // A commitment invoice without a matching commitment_records row is treated // as support (back-compat) — see the runbook for the rationale. // // Callers should treat this function as the single source of truth for // "support commitment cost" so that the owner attribution is consistent // across every backfill path. func (m *ARTableModel) TenantCommitmentCostForSupport(tenantID uint, startDate, endDate time.Time) (decimal.Decimal, error) { var rows []struct { CostPrice decimal.Decimal } // A commitment invoice is counted only when at least one // commitment_records row has Owner = supportCommitmentOwner // and no row has a different non-empty Owner. Invoices // without any matching commitment_records row are excluded // — there is no back-compat fallback that assumes "support" // when Owner is absent. err := db.GetDB().Model(&entity.Invoice{}). Joins("LEFT JOIN commitment_records ON commitment_records.binded_billing_config_id = billing2_invoices.binded_billing_config_id AND commitment_records.date = billing2_invoices.invoice_date"). Where("billing2_invoices.tenant_id = ? AND billing2_invoices.invoice_date >= ? AND billing2_invoices.invoice_date < ? AND billing2_invoices.type = ?", tenantID, startDate, endDate, entity.InvoiceType_COMMITMENT). Group("billing2_invoices.id"). // Only accept invoices with at least one commitment_records row // and no commitment_records row with a non-support Owner. Having("SUM(CASE WHEN commitment_records.id IS NOT NULL AND commitment_records.owner <> ? THEN 1 ELSE 0 END) = 0 AND COUNT(commitment_records.id) > 0", supportCommitmentOwner). Select("billing2_invoices.cost_price"). Find(&rows).Error if err != nil { return decimal.Zero, err } var total decimal.Decimal for _, r := range rows { total = total.Add(r.CostPrice) } return total, nil } // EstimateSupportFeeForTenant is a pure user_unit_costs fallback. It // reads ONLY user_unit_costs (via queryNonSupportUsageInRange) for // each plan's [planStart, planEnd+24h) window. It does NOT touch the // AR table's own raw_* columns. // // The AR table's own estimate is computed by estimateSupportForRow, // which uses AR columns for full support and falls back to // user_unit_costs for partial support. This function is kept for // callers that want a stand-alone, user_unit_costs-based estimate // (e.g., tests, or other downstream consumers that don't have the // AR row in hand). // // The estimate already includes the commitment as a floor // (max(percentage_or_tiered, prorated_commitment)) — no separate // proratedCommit is returned. func (m *ARTableModel) EstimateSupportFeeForTenant(tenantID, billingConfigID uint, date time.Time) (decimal.Decimal, error) { monthStart := timeutils.GetMonthDate(date) monthEnd := monthStart.AddDate(0, 1, 0) daysInMonth := int(monthEnd.Sub(monthStart).Hours() / 24) var plans []*entity.BindedSupportPlan err := db.GetDB().Preload("SupportPlan", func(db *gorm.DB) *gorm.DB { return db.Unscoped() }). Where("tenant_id = ?", tenantID). Where("status = ?", entity.BindedSupportPlanStatus_ACTIVE). Where("active_date < ?", monthEnd). Where("closed_date IS NULL OR closed_date >= ?", monthStart). Where("expired_date IS NULL OR expired_date >= ?", monthStart). Find(&plans).Error if err != nil { return decimal.Zero, err } rawCommitment, err := m.TenantCommitmentCostForSupport(tenantID, monthStart, monthEnd) if err != nil { return decimal.Zero, err } total := decimal.Zero for _, plan := range plans { planStart, planEnd, activeDays := planActiveWindow(plan, monthStart, monthEnd) if activeDays == 0 { continue } // Pure user_unit_costs: query the plan's [planStart, planEnd+24h) range. upperExclusive := time.Date(planEnd.Year(), planEnd.Month(), planEnd.Day()+1, 0, 0, 0, 0, planEnd.Location()) base, err := m.queryNonSupportUsageInRange(tenantID, billingConfigID, planStart, upperExclusive) if err != nil { return decimal.Zero, err } floor := rawCommitment if activeDays < daysInMonth { floor = floor.Mul(decimal.NewFromInt(int64(activeDays))).Div(decimal.NewFromInt(int64(daysInMonth))) } one, err := estimateOnePlanContract(plan, base, floor, activeDays, daysInMonth) if err != nil { return decimal.Zero, err } total = total.Add(one) } return total, nil } // EstimateSupportForRow computes the contract-based support fee estimate // (E) and the contract gap (A = max(0, E - row.RawSupportAmount)) for // the given AR table row. Called from GenerateEstimatedARTable in the // step BEFORE the v1220 merge, so the row's raw_* values are still // the metering originals; r.DedicatedAccrualAmount has already been // populated by calculateAccrual. // // Per plan: // - If activeDays == daysInMonth (full support): use the AR table's // own non-support raw_* columns + dedicatedAccrual as the base. // - If activeDays < daysInMonth (partial support): fall back to // user_unit_costs over [planStart, planEnd+24h) via // queryNonSupportUsageInRange. The AR table's raw columns are // point-in-time cost-explorer snapshots and don't bound to the // plan's partial-month window. // // For each plan, E_plan = max(base * pct_or_tiered, prorated_commitment). // // The final E is max(contract_estimate, metered_support): // - For partner-reseller payment types (CPPO reseller, GCP MCPO // reseller, Resale): the support fee is computed by the support-plan // worker and pushed to the partner. The metered value from cost // explorer is the customer's actual support cost (the "customer // support" reading). The contract estimate provides the floor // (e.g., a monthly minimum of 699 when metering is only 100). // - For other payment types: the contract estimate is the primary // value. The metered value acts as a safeguard — when it exceeds // the contract estimate (e.g., the support-plan worker has already // computed and written a higher value), the metered value is used. // // A = max(0, E - metered_support) — the gap between the final estimate // and what has already been metered. func (m *ARTableModel) EstimateSupportForRow(tenantID, billingConfigID uint, date time.Time, row *ARTableRow) (decimal.Decimal, decimal.Decimal, error) { meteredSupport := decimal.RequireFromString(row.RawSupportAmount) monthStart := timeutils.GetMonthDate(date) monthEnd := monthStart.AddDate(0, 1, 0) daysInMonth := int(monthEnd.Sub(monthStart).Hours() / 24) var plans []*entity.BindedSupportPlan err := db.GetDB().Preload("SupportPlan", func(db *gorm.DB) *gorm.DB { return db.Unscoped() }). Where("tenant_id = ?", tenantID). Where("status = ?", entity.BindedSupportPlanStatus_ACTIVE). Where("active_date < ?", monthEnd). Where("closed_date IS NULL OR closed_date >= ?", monthStart). Where("expired_date IS NULL OR expired_date >= ?", monthStart). Find(&plans).Error if err != nil { return decimal.Zero, decimal.Zero, err } rawCommitment, err := m.TenantCommitmentCostForSupport(tenantID, monthStart, monthEnd) if err != nil { return decimal.Zero, decimal.Zero, err } dedicatedAccrual := decimal.RequireFromString(row.DedicatedAccrualAmount) contractTotal := decimal.Zero for _, plan := range plans { planStart, planEnd, activeDays := planActiveWindow(plan, monthStart, monthEnd) if activeDays == 0 { continue } var base decimal.Decimal if activeDays == daysInMonth { // Full support: use AR table's own non-support raw columns // + dedicatedAccrual (the v1220 tail-end forecast). Excludes // raw_support (which is the metering-based support value at // this point; we're computing the contract estimate that // will replace it). base = decimal.RequireFromString(row.RawServerlessAmount). Add(decimal.RequireFromString(row.RawDedicatedAmount)). Add(decimal.RequireFromString(row.RawStarterAmount)). Add(decimal.RequireFromString(row.RawEssentialAmount)). Add(decimal.RequireFromString(row.RawPremiumAmount)). Add(decimal.RequireFromString(row.RawLakeAmount)). Add(dedicatedAccrual) } else { // Partial support: fall back to user_unit_costs over the // plan's [planStart, planEnd+24h) window. The +24h makes // the end inclusive of any rows written on planEnd itself. upperExclusive := time.Date(planEnd.Year(), planEnd.Month(), planEnd.Day()+1, 0, 0, 0, 0, planEnd.Location()) base, err = m.queryNonSupportUsageInRange(tenantID, billingConfigID, planStart, upperExclusive) if err != nil { return decimal.Zero, decimal.Zero, err } } floor := rawCommitment if activeDays < daysInMonth { floor = floor.Mul(decimal.NewFromInt(int64(activeDays))).Div(decimal.NewFromInt(int64(daysInMonth))) } one, err := estimateOnePlanContract(plan, base, floor, activeDays, daysInMonth) if err != nil { // Skip this plan rather than failing the entire AR table // for all tenants. A single plan with an unrecognised // SupportPlan type should not block estimation for every // other tenant. log.Error("failed to estimate one plan contract, skipping plan", zap.Error(err), zap.Uint("tenantID", plan.TenantID), zap.Uint("supportPlanID", plan.SupportPlanID), zap.String("planType", plan.SupportPlan.Type)) continue } contractTotal = contractTotal.Add(one) } // E = max(contract_estimate, metered_support). // // For partner-reseller payment types (CPPO reseller, GCP MCPO reseller, // Resale): the support-plan worker has already computed and pushed the // support fee. The metered value from cost explorer is the customer's // actual support cost (the "customer support" reading). The contract // estimate provides the floor — e.g. when the monthly minimum is 699 // but only 100 has been metered so far. We take the higher of the two. // // For other payment types: the contract estimate is the primary value. // The metered value acts as a safeguard — when it exceeds the contract // estimate (e.g. the support-plan worker has already computed and // written a higher value), the metered value takes precedence. if meteredSupport.GreaterThan(contractTotal) { // metered > contract: use metered (customer support for // partner-reseller; safeguard for others). A=0 — no gap to // fill since metered already covers it. return meteredSupport, decimal.Zero, nil } // contract >= metered: use contract. A = contract - metered. gap := contractTotal.Sub(meteredSupport) return contractTotal, gap, nil } // planActiveWindow returns the plan's date window intersected with the // calendar month. Returns (planStart, planEnd, activeDays) where // activeDays is the inclusive day count (planEnd - planStart + 1). // Returns activeDays=0 if the plan's window lies entirely outside the // month. All times are snapped to day boundaries in the plan's // location. func planActiveWindow(plan *entity.BindedSupportPlan, monthStart, monthEnd time.Time) (time.Time, time.Time, int) { planStart := plan.ActiveDate if planStart.Before(monthStart) { planStart = monthStart } planEnd := monthEnd.AddDate(0, 0, -1) // last day of the estimate month if plan.ExpiredDate != nil && plan.ExpiredDate.Before(planEnd) { planEnd = *plan.ExpiredDate } if plan.ClosedDate != nil && plan.ClosedDate.Before(planEnd) { planEnd = *plan.ClosedDate } planStart = time.Date(planStart.Year(), planStart.Month(), planStart.Day(), 0, 0, 0, 0, planStart.Location()) planEnd = time.Date(planEnd.Year(), planEnd.Month(), planEnd.Day(), 0, 0, 0, 0, planEnd.Location()) if planEnd.Before(planStart) { return planStart, planEnd, 0 } activeDays := int(planEnd.Sub(planStart).Hours()/24) + 1 return planStart, planEnd, activeDays } // estimateOnePlanContract computes the per-plan E given a pre-computed // base (raw dollars) and a prorated commitment floor. Tiered uses // range-based pricing (TieredPricing(rules, 0, base)) — no // previousCumulative, no monthly cumulative rollover. The old // previousCumulative + usageToDate form in the original estimateOnePlan // is a confirmed bug (handled by a separate PR); we do not reuse it // here. func estimateOnePlanContract(plan *entity.BindedSupportPlan, base, floor decimal.Decimal, activeDays, daysInMonth int) (decimal.Decimal, error) { switch plan.SupportPlan.Type { case entity.SupportPlanType_FixedPrice: return plan.SupportPlan.FixedChargePrice. Mul(decimal.NewFromInt(int64(activeDays))). Div(decimal.NewFromInt(int64(daysInMonth))), nil case entity.SupportPlanType_PercentagePrice: percentageCost := base.Mul(plan.SupportPlan.SupportPricePercentage) if percentageCost.GreaterThan(floor) { return percentageCost, nil } return floor, nil case entity.SupportPlanType_TieredPrice: // Range-based tiered: previousCumulative=0, totalUsage=base // (scaled). TieredPricing is internally scaled by // SupportPlan_PercentagePriceBase; convert back to raw. baseScaled := base.Mul(decimal.NewFromInt(int64(marketing.SupportPlan_PercentagePriceBase))) tieredScaled := decimal.NewFromInt(int64(marketing.TieredPricing(plan.SupportPlan.TieredPriceRules, decimal.Zero, baseScaled))) tieredCost := tieredScaled.Div(decimal.NewFromInt(int64(marketing.SupportPlan_PercentagePriceBase))) if tieredCost.GreaterThan(floor) { return tieredCost, nil } return floor, nil default: // Unknown plan type — surface the bug rather than silently // overcharging via the commitment floor. A new SupportPlanType // should land with an explicit case here. log.Error("estimateOnePlanContract: unknown SupportPlan type, refusing to estimate", zap.String("planType", plan.SupportPlan.Type), zap.Uint("tenantID", plan.TenantID), zap.Uint("supportPlanID", plan.SupportPlanID)) return decimal.Zero, billing_errors.NewSystemError( fmt.Errorf("unknown support plan type %q for plan id %d", plan.SupportPlan.Type, plan.SupportPlanID)) } } // cumulativeNonSupportUsageToDate sums the tenant's user_unit_costs from // the start of the month up to (and including) the full `asOf` day, // excluding the support-plan SKUs themselves. This is the per-day // usage base that percentage/tiered support fees are computed against // — same construction as support_plan.go::getCustomerUsageBase. // // `asOf` is interpreted as a day (year/month/day) — the upper bound is // `asOf + 24h` so rows written any time during that day are included. // `asOf.Unix()` would be midnight (00:00) and miss any row written // later the same day, which is the metered semantics in // support_plan.go. func (m *ARTableModel) cumulativeNonSupportUsageToDate(tenantID, billingConfigID uint, asOf time.Time) (decimal.Decimal, error) { monthStart := time.Date(asOf.Year(), asOf.Month(), 1, 0, 0, 0, 0, asOf.Location()) upperExclusive := asOf.Add(24 * time.Hour) var total decimal.Decimal err := db.GetReadOnlyDB().Model(&entity.UserUnitCost{}). Where("tenant_id = ? AND binded_billing_config_id = ?", tenantID, billingConfigID). Where("cost_time_stamp >= ? AND cost_time_stamp < ?", monthStart.Unix(), upperExclusive.Unix()). Where("status = ?", entity.UserUnitCostStatus_DONE). Where("unit NOT IN ?", marketing.SupportPlanSkuUnits). Select("COALESCE(SUM(cost_price), 0)"). Scan(&total).Error if err != nil { return decimal.Zero, err } return total, nil } // queryNonSupportUsageInRange sums the tenant's user_unit_costs over the // half-open interval [start, end), excluding the support-plan SKUs // themselves. Used by estimateSupportForRow when the plan is only active // for part of the month (partial support), so the percentage/tiered // base is bounded by the plan's actual [planStart, planEnd] window // instead of the full month. End is exclusive (a row with cost_time_stamp // == end is NOT included), matching the upper-bound semantics used by // getCustomerUsageBase in support_plan.go. func (m *ARTableModel) queryNonSupportUsageInRange(tenantID, billingConfigID uint, start, end time.Time) (decimal.Decimal, error) { var total decimal.Decimal err := db.GetReadOnlyDB().Model(&entity.UserUnitCost{}). Where("tenant_id = ? AND binded_billing_config_id = ?", tenantID, billingConfigID). Where("cost_time_stamp >= ? AND cost_time_stamp < ?", start.Unix(), end.Unix()). Where("status = ?", entity.UserUnitCostStatus_DONE). Where("unit NOT IN ?", marketing.SupportPlanSkuUnits). Select("COALESCE(SUM(cost_price), 0)"). Scan(&total).Error if err != nil { return decimal.Zero, err } return total, nil } // previousCumulativeUsageForPlan was removed: the previousCumulative + // usageToDate form in tiered estimation is a confirmed bug (handled // by a separate PR). Tiered estimation is now range-based via // estimateOnePlanContract. func (m *ARTableModel) DeleteARTableRecord(id uint) error { result := db.GetDB().Where("id = ?", id).Delete(&entity.ARTableRecord{}) if result.Error != nil { return result.Error } if result.RowsAffected == 0 { return billing_errors.ObjectNotExistsError } return nil }