package costexplorer

import (
	"bytes"
	"context"
	"encoding/csv"
	"errors"
	"fmt"
	"slices"
	"sort"
	"strconv"
	"strings"
	"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/centralhandler"
	"github.com/pingcap/dbaas/billing2/handler/infrahandler"
	"github.com/pingcap/dbaas/billing2/utils/log"
	"github.com/pingcap/dbaas/billing2/utils/timeutils"
	"github.com/shopspring/decimal"
	"go.uber.org/zap"
)

var tierNames = [3]string{"product", "category", "component"}

const (
	unitCostsTable           = "user_unit_costs"
	unitCostsGroupTaleName   = "unit_costs_group_tbl"
	unitCostsJoinedTableName = "unit_costs_joined_tbl"
)

const OthersRegion = "Others"
const defaultElemVal = "Others"

func filterClusterBreakdownForTenants(tenantIDs []uint, breakdownBy []forms.CostExplorerTrendsBreakdownKey) []forms.CostExplorerTrendsBreakdownKey {
	disabledTenants := config.Get().GetStringSlice(config.DisableClusterDisplayTenants)
	for _, tenantID := range tenantIDs {
		if slices.Contains(disabledTenants, fmt.Sprint(tenantID)) {
			var filtered []forms.CostExplorerTrendsBreakdownKey
			for _, key := range breakdownBy {
				if key != forms.CostExploreTrendsBreakdownKeyCluster {
					filtered = append(filtered, key)
				}
			}
			return filtered
		}
	}
	return breakdownBy
}

func queryBreakdownDetails(tenantIDs []uint, billingConfigIDs []uint, query *forms.CostExploreTrendsInput) ([]forms.CostExploreTrendsOutputBreakdownDetail, error) {
	if query.TimeGroup != nil && *query.TimeGroup != "" &&
		query.StartDate != nil && !query.StartDate.IsZero() &&
		query.EndDate != nil && !query.EndDate.IsZero() {

		var ranges []timeRange
		var splitLabel string

		switch *query.TimeGroup {
		case forms.CostExploreTrendsTimeGroupDay:
			// Split by day. The most common query is "last month, daily" — even a
			// single month can timeout when a tenant has tens of thousands of clusters.
			// Each per-day sub-query scans exactly one day of user_unit_costs so it
			// completes well under 30s regardless of cluster count.
			ranges = splitDateRangeByDay(*query.StartDate, *query.EndDate)
			splitLabel = "day"
		case forms.CostExploreTrendsTimeGroupMonth:
			// Month-grouped queries are already aggregated coarsely; split by calendar
			// month when the range spans more than one.
			ranges = splitDateRangeByMonth(*query.StartDate, *query.EndDate)
			splitLabel = "month"
		}

		if len(ranges) > 1 {
			log.Info("split cost explore trends query",
				zap.String("splitBy", splitLabel),
				zap.Int("subQueryCount", len(ranges)),
				zap.Uints("tenantIDs", tenantIDs),
				zap.Time("startDate", *query.StartDate),
				zap.Time("endDate", *query.EndDate))

			// Overall timeout for the entire split loop — if the HTTP request times
			// out or the client disconnects, stop sending sub-queries.
			ctx, cancel := context.WithTimeout(context.Background(),
				getSplitLoopTimeout(false, query.SplitLoopDeadline))
			defer cancel()

			var allResults []forms.CostExploreTrendsOutputBreakdownDetail
			for _, r := range ranges {
				if err := ctx.Err(); err != nil {
					log.Error("trends query cancelled by overall timeout",
						zap.Uints("tenantIDs", tenantIDs),
						zap.Error(err))
					return nil, billing_errors.NewSystemError(fmt.Errorf("trends query cancelled: %w", err))
				}
				subQuery := *query
				start, end := r.start, r.end
				subQuery.StartDate = &start
				subQuery.EndDate = &end
				sql, params := GenerateCostTrendsSqlAndParams(tenantIDs, billingConfigIDs, &subQuery)
				var dbOutput []forms.CostExploreTrendsOutputBreakdownDetail
				if err := db.GetReadOnlyDB().Raw(sql, params).Scan(&dbOutput).Error; err != nil {
					return nil, err
				}
				allResults = append(allResults, dbOutput...)
			}
			return FormatBreakdownDetails(tenantIDs, query, allResults)
		}
	}

	sql, params := GenerateCostTrendsSqlAndParams(tenantIDs, billingConfigIDs, query)
	log.Info("start exec cost explore trends query", zap.String("sql", sql), zap.Any("params", params))
	var dbOutput []forms.CostExploreTrendsOutputBreakdownDetail
	err := db.GetReadOnlyDB().Raw(sql, params).Scan(&dbOutput).Error
	if err != nil {
		return nil, err
	}
	return FormatBreakdownDetails(tenantIDs, query, dbOutput)
}

func CostTrends(tenantID, billingConfigID uint, query *forms.CostExploreTrendsInput) (*forms.CostExploreTrendsOutput, error) {
	var billingConfigIDs []uint
	if billingConfigID != 0 {
		billingConfigIDs = append(billingConfigIDs, billingConfigID)
	}
	return CostTrendsWithMultiBillingConfigs(tenantID, billingConfigIDs, query)
}

func CostTrendsWithMultiBillingConfigs(tenantID uint, billingConfigIDs []uint, query *forms.CostExploreTrendsInput) (*forms.CostExploreTrendsOutput, error) {
	return CostTrendsWithMultiTenantsMultiBillingConfigs([]uint{tenantID}, billingConfigIDs, query)
}

func CostTrendsWithMultiTenantsMultiBillingConfigs(tenantIDs []uint, billingConfigIDs []uint, query *forms.CostExploreTrendsInput) (*forms.CostExploreTrendsOutput, error) {
	breakdownDetails, err := queryBreakdownDetails(tenantIDs, billingConfigIDs, query)
	if err != nil {
		return nil, err
	}
	groupElements, err := GroupBreakdownDetailsByBillingDate(query, breakdownDetails)
	if err != nil {
		return nil, err
	}
	ret := FormatCostExploreTrendsOutput(groupElements)
	return ret, nil
}

type TrendsCSVRow struct {
	forms.CostExploreTrendsOutputBreakdownDetail
	DateBreakdown []forms.CostExploreTrendsOutputPrice
}

func CostTrendsCSV(tenantID uint, billingConfigID uint, query *forms.CostExploreTrendsCSVInput) ([]byte, error) {
	var billingConfigIDs []uint
	if billingConfigID != 0 {
		billingConfigIDs = append(billingConfigIDs, billingConfigID)
	}
	return CostTrendsCSVWithMultiBillingConfigs(tenantID, billingConfigIDs, query)
}

func CostTrendsCSVWithMultiBillingConfigs(tenantID uint, billingConfigIDs []uint, query *forms.CostExploreTrendsCSVInput) ([]byte, error) {
	return CostTrendsCSVWithMultiTenantsMultiBillingConfigs([]uint{tenantID}, billingConfigIDs, query)
}

func CostTrendsCSVWithMultiTenantsMultiBillingConfigs(tenantIDs []uint, billingConfigIDs []uint, query *forms.CostExploreTrendsCSVInput) ([]byte, error) {
	breakdownDetails, err := queryBreakdownDetails(tenantIDs, billingConfigIDs, &query.CostExploreTrendsInput)
	if err != nil {
		return nil, err
	}
	billingDates, rows, err := GenerateTrendsCSVHeadersAndRows(query, breakdownDetails)
	if err != nil {
		return nil, err
	}
	return FormatCostExploreTrendsCSV(query, billingDates, rows)
}

func GenerateCostTrendsSqlAndParams(tenantIDs []uint, billingConfigIDs []uint, query *forms.CostExploreTrendsInput) (string, map[string]interface{}) {
	filteredQuery := *query
	filteredQuery.BreakdownBy = filterClusterBreakdownForTenants(tenantIDs, query.BreakdownBy)
	query = &filteredQuery

	var index string
	unitCostsFilters := []string{fmt.Sprintf("%s.tenant_id in @tenant_id", unitCostsTable), fmt.Sprintf("%s.status = @status", unitCostsTable)}

	params := map[string]interface{}{"tenant_id": tenantIDs, "status": entity.UserUnitCostStatus_DONE}

	if len(billingConfigIDs) != 0 {
		unitCostsFilters = append(unitCostsFilters, fmt.Sprintf("%s.binded_billing_config_id in @binded_billing_config_ids", unitCostsTable))
		params["binded_billing_config_ids"] = billingConfigIDs
	}

	customerColumn := fmt.Sprintf("JSON_UNQUOTE(JSON_EXTRACT(%s.labels, '$.%s'))", unitCostsTable, constant.MSPCustomerIDLabel)

	if len(tenantIDs) > 1 {
		customerColumn = "tenant_id"
	}

	for _, q := range []struct {
		IDs      []string
		Column   string
		ParaName string
	}{
		{IDs: query.Invoices, Column: fmt.Sprintf("%s.invoice_id", unitCostsTable), ParaName: "invoices"},
		{IDs: query.Clusters, Column: fmt.Sprintf("%s.cluster_id", unitCostsTable), ParaName: "clusters"},
		{IDs: query.Projects, Column: fmt.Sprintf("JSON_UNQUOTE(JSON_EXTRACT(%s.labels, '$.projectID'))", unitCostsTable), ParaName: "projects"},
		{IDs: query.Customer, Column: customerColumn, ParaName: "customers"},
	} {
		if len(q.IDs) != 0 {
			unitCostsFilters = append(unitCostsFilters, fmt.Sprintf("%s in @%s", q.Column, q.ParaName))
			params[q.ParaName] = q.IDs
		}
	}

	if query.IgnoreBeta {
		unitCostsFilters = append(unitCostsFilters, fmt.Sprintf("(JSON_UNQUOTE(JSON_EXTRACT(%s.labels, '$.beta')) is null or JSON_UNQUOTE(JSON_EXTRACT(%s.labels, '$.beta')) != \"true\")", unitCostsTable, unitCostsTable))
	}

	if query.ConsumedSource != nil {
		unitCostsFilters = append(unitCostsFilters, fmt.Sprintf("%s.consumed_source = @consumed_source", unitCostsTable))
		params["consumed_source"] = *query.ConsumedSource
	}

	if query.StartDate != nil && !query.StartDate.IsZero() {
		unitCostsFilters = append(unitCostsFilters, fmt.Sprintf("%s.cost_time_stamp > @start_date", unitCostsTable))
		params["start_date"] = query.StartDate.Unix() - 1
		index = forceClusterIndex
	}

	if query.EndDate != nil && !query.EndDate.IsZero() {
		unitCostsFilters = append(unitCostsFilters, fmt.Sprintf("%s.cost_time_stamp < @end_date", unitCostsTable))
		params["end_date"] = query.EndDate.Unix()
		index = forceClusterIndex
	}

	if query.Internal != nil {
		unitCostsFilters = append(unitCostsFilters, fmt.Sprintf("IFNULL(%s.internal, false) = @internal", unitCostsTable))
		params["internal"] = *query.Internal
	}

	joinedTableFilters := []string{" 1 != 0"}
	var servicesFilters []string
	for sIndex, svc := range query.Services {
		var svcFilterElems []string
		for tIndex, tierFilter := range []*string{svc.Product, svc.Category, svc.Component} {
			if tierFilter == nil || *tierFilter == "" {
				break
			}
			tierName := tierNames[tIndex]
			tierFilterKey := fmt.Sprintf("svc_%s_%d", tierName, sIndex)
			svcFilterElems = append(svcFilterElems, fmt.Sprintf("%s.%s = @%s", unitCostsJoinedTableName, tierName, tierFilterKey))
			params[tierFilterKey] = *tierFilter
		}
		servicesFilters = append(servicesFilters, fmt.Sprintf("(%s)", strings.Join(svcFilterElems, " AND ")))
	}

	if len(servicesFilters) != 0 {
		joinedTableFilters = append(joinedTableFilters, fmt.Sprintf("(%s)", strings.Join(servicesFilters, " OR ")))
	}

	var providerFilters []string
	for pIndex, provider := range query.Providers {
		if provider.Name == "" {
			continue
		}
		providerFilterKey := fmt.Sprintf("provider_%d", pIndex)
		providerFilter := fmt.Sprintf("(lower(%s.provider) = lower(@%s))", unitCostsJoinedTableName, providerFilterKey)
		params[providerFilterKey] = provider.Name
		if len(provider.Regions) != 0 {
			regionsFilterKey := fmt.Sprintf("regions_%d", pIndex)
			providerFilter = fmt.Sprintf("(%s AND lower(%s.region) in @%s)", providerFilter, unitCostsJoinedTableName, regionsFilterKey)
			lowerRegions := make([]string, len(provider.Regions))
			for i := 0; i != len(provider.Regions); i++ {
				lowerRegions[i] = strings.ToLower(provider.Regions[i])
			}
			params[regionsFilterKey] = lowerRegions
		}
		providerFilters = append(providerFilters, providerFilter)
	}
	if len(providerFilters) != 0 {
		joinedTableFilters = append(joinedTableFilters, fmt.Sprintf("(%s)", strings.Join(providerFilters, " OR ")))
	}

	var breakdownCols, unitCostsSelectCols, unitCostsGroupCols string

	if len(query.BreakdownBy) != 0 {
		var bColumns, usColumns, ugColumns []string
		var bColumnSet = make(map[string]*struct{})

		for _, b := range query.BreakdownBy {
			switch b {
			case forms.CostExploreTrendsBreakdownKeyProduct, forms.CostExploreTrendsBreakdownKeyProvider:
				bColumnSet[string(b)] = nil
			case forms.CostExploreTrendsBreakdownKeyService:
				for _, key := range []string{"product", "category", "component"} {
					bColumnSet[key] = nil
				}
			case forms.CostExploreTrendsBreakdownKeyRegion:
				bColumnSet[string(forms.CostExploreTrendsBreakdownKeyProvider)] = nil
				bColumnSet[string(forms.CostExploreTrendsBreakdownKeyRegion)] = nil
			case forms.CostExploreTrendsBreakdownKeyCluster:
				bColumnSet["cluster_id"] = nil
				usColumns = append(usColumns, "cluster_id")
				ugColumns = append(ugColumns, "cluster_id")
			case forms.CostExploreTrendsBreakdownKeyProject:
				bColumnSet["project_id"] = nil
				usColumns = append(usColumns, fmt.Sprintf("JSON_UNQUOTE(JSON_EXTRACT(%s.labels, '$.projectID')) as project_id", unitCostsTable))
				ugColumns = append(ugColumns, fmt.Sprintf("JSON_UNQUOTE(JSON_EXTRACT(%s.labels, '$.projectID'))", unitCostsTable))
			case forms.CostExploreTrendsBreakdownKeyCustomer:
				bColumnSet["customer_id"] = nil
				if len(tenantIDs) > 1 {
					usColumns = append(usColumns, "tenant_id as customer_id")
					ugColumns = append(ugColumns, "tenant_id")
				} else {
					usColumns = append(usColumns, fmt.Sprintf("JSON_UNQUOTE(JSON_EXTRACT(%s.labels, '$.%s')) as customer_id", unitCostsTable, constant.MSPCustomerIDLabel))
					ugColumns = append(ugColumns, fmt.Sprintf("JSON_UNQUOTE(JSON_EXTRACT(%s.labels, '$.%s'))", unitCostsTable, constant.MSPCustomerIDLabel))
				}
			}
		}
		for col, _ := range bColumnSet {
			bColumns = append(bColumns, col)
		}
		breakdownCols = strings.Join(bColumns, ",")
		unitCostsSelectCols = strings.Join(usColumns, ",")
		unitCostsGroupCols = strings.Join(ugColumns, ",")
	}
	for _, s := range []*string{&breakdownCols, &unitCostsSelectCols, &unitCostsGroupCols} {
		if *s != "" {
			*s += "," // for sql selection tail
		}
	}

	firstGroupKeptDecimalPlaces := config.Get().GetInt32(config.BillingCostsMaxPrecisionDigits)
	//if unitCostsSelectCols == "" {
	//	// if any exclusive columns is not in the group by sql, we should make the result equals bills
	//	firstGroupKeptDecimalPlaces = config.Get().GetInt32(config.BillingCostsPrecisionDigits)
	//}
	timeGroup := "''"

	if query.TimeGroup != nil {
		switch *query.TimeGroup {
		case forms.CostExploreTrendsTimeGroupDay:
			timeGroup = "DATE_FORMAT(FROM_UNIXTIME(cost_time_stamp), '%Y-%m-%d')"
			firstGroupKeptDecimalPlaces = config.Get().GetInt32(config.BillingCostsMaxPrecisionDigits)
		case forms.CostExploreTrendsTimeGroupMonth:
			timeGroup = "DATE_FORMAT(FROM_UNIXTIME(cost_time_stamp), '%Y-%m-01')"
		}
	}
	params["first_group_kept_decimal_places"] = firstGroupKeptDecimalPlaces

	sql := fmt.Sprintf("select %s `billing_date`, \n"+
		"    SUM(raw_price) as raw_price, \n"+
		"    SUM(raw_price - cost_price) as discount,\n"+
		"    SUM(IF(consumed_source='credits', cost_price, 0)) as credits_usage, \n"+
		"    SUM(IF(consumed_source='invoice', cost_price, 0)) as cost_price \n"+
		"from\n"+
		"(select\n"+
		"    units.provider as provider, \n"+
		"    IF(region='' OR region='all', 'Others', region) as region, \n"+
		"    category_item_shows.`show_kind_name` as product,\n"+
		"    IF(LENGTH(category_item_shows.`show_category_name`) > 0, category_item_shows.`show_category_name`, NULL) as category, \n"+
		"    IF(LENGTH(category_item_shows.`show_category_name`) > 0, \n"+
		"      IF(category_item_shows.`use_cate_gory`, \n"+
		"        IF(LENGTH(category_item_shows.`label_key`) > 0 , \n"+
		"          JSON_UNQUOTE(JSON_EXTRACT(category_item_shows.`labels`, concat('$.\"', category_item_shows.`label_key`, '\"'))),\n"+
		"          NULL\n"+
		"        ),\n"+
		"        IFNULL(JSON_UNQUOTE(JSON_EXTRACT(\n"+
		"            category_item_shows.`labels`,\n"+
		"            concat('$.', JSON_EXTRACT(%s.labels, concat('$.\"', category_item_shows.`label_key`, '\"')))\n"+
		"         )),\n"+
		"         JSON_UNQUOTE(JSON_EXTRACT(category_item_shows.`labels`, \"$.default\"))\n"+
		"       )\n"+
		"     ),\n"+
		"      null\n"+
		"    ) as component,\n"+
		"    %s.* FROM (\n"+
		"      SELECT\n"+
		"        %s as `billing_date`, %s \n"+
		"        unit, labels, consumed_source, invoice_id,\n"+
		"        ifnull(internal, false) as internal,\n"+
		"        TRUNCATE(sum(cost_price), @first_group_kept_decimal_places) as cost_price,\n"+
		"        TRUNCATE(sum(raw_price), @first_group_kept_decimal_places) as raw_price,\n"+
		"        sum(cost) as cost\n"+
		"        from %s %s\n"+
		"        where %s\n"+
		"        group by %s, %s unit, labels, consumed_source, invoice_id, ifnull(internal, false)\n"+
		"     ) %s \n"+
		"        left join units on units.unit_key =  %s.unit \n"+
		"        left join category_item_shows on units.category = category_item_shows.category ) %s\n"+
		"     WHERE %s \n"+
		" group by %s `billing_date`",
		breakdownCols, unitCostsGroupTaleName, unitCostsGroupTaleName, timeGroup, unitCostsSelectCols,
		unitCostsTable, index, strings.Join(unitCostsFilters, " AND "), timeGroup, unitCostsGroupCols,
		unitCostsGroupTaleName, unitCostsGroupTaleName,
		unitCostsJoinedTableName, strings.Join(joinedTableFilters, " AND "),
		breakdownCols)
	return sql, params
}

func FormatBreakdownDetails(tenantIDs []uint, query *forms.CostExploreTrendsInput, dbOutput []forms.CostExploreTrendsOutputBreakdownDetail) ([]forms.CostExploreTrendsOutputBreakdownDetail, error) {
	filteredBreakdown := filterClusterBreakdownForTenants(tenantIDs, query.BreakdownBy)
	query.BreakdownBy = filteredBreakdown

	if len(query.BreakdownBy) == 0 {
		return dbOutput, nil
	}
	var breakdownSet = make(map[forms.CostExplorerTrendsBreakdownKey]bool)
	var clusterNameCache map[uint]string
	var projectNameCache map[uint]string
	var customerNameCache map[string]string
	queryCustomers := make([]uint, len(query.Customer))
	for i, customer := range query.Customer {
		c, err := strconv.ParseUint(customer, 10, 64)
		if err != nil {
			return nil, err
		}
		queryCustomers[i] = uint(c)
	}

	for _, b := range query.BreakdownBy {
		switch b {
		case forms.CostExploreTrendsBreakdownKeyProduct:
			if used, ok := breakdownSet[forms.CostExploreTrendsBreakdownKeyService]; !ok && !used {
				breakdownSet[b] = true
			}
		case forms.CostExploreTrendsBreakdownKeyService:
			breakdownSet[b] = true
			breakdownSet[forms.CostExploreTrendsBreakdownKeyProduct] = false
		case forms.CostExploreTrendsBreakdownKeyProvider:
			if used, ok := breakdownSet[forms.CostExploreTrendsBreakdownKeyRegion]; !ok && !used {
				breakdownSet[b] = true
			}
		case forms.CostExploreTrendsBreakdownKeyRegion:
			breakdownSet[b] = true
			breakdownSet[forms.CostExploreTrendsBreakdownKeyProvider] = false
		case forms.CostExploreTrendsBreakdownKeyCluster:
			breakdownSet[b] = true
			clusterNameCache = make(map[uint]string)
			for _, tenantID := range append(append([]uint{}, tenantIDs...), queryCustomers...) {
				clusters, err := infrahandler.Get().ListClusters(tenantID)
				if err != nil {
					log.Error("failed to list cluster", zap.Error(err), zap.Uint("tenantID", tenantID))
					return nil, err
				}
				for _, c := range clusters {
					clusterNameCache[c.ID] = c.Name
				}
			}
		case forms.CostExploreTrendsBreakdownKeyProject:
			breakdownSet[b] = true
			projectNameCache = make(map[uint]string)
			for _, tenantID := range append(append([]uint{}, tenantIDs...), queryCustomers...) {
				projects, err := centralhandler.GetCentralHandler().ListProjects(tenantID, true)
				if err != nil {
					log.Error("failed to list projects", zap.Error(err), zap.Uint("tenantID", tenantID))
					return nil, err
				}
				for _, p := range projects {
					projectNameCache[p.ID] = p.Name
				}
			}
		case forms.CostExploreTrendsBreakdownKeyCustomer:
			breakdownSet[b] = true
			customerNameCache = make(map[string]string)
			if len(tenantIDs) > 1 {
				tenants, err := centralhandler.GetCentralHandler().GetTenants(tenantIDs...)
				if err != nil {
					log.Error("failed to list customers", zap.Error(err), zap.Uints("tenantIDs", tenantIDs))
					return nil, err
				}
				for _, c := range tenants {
					customerNameCache[fmt.Sprint(c.TenantID)] = c.Name
				}
			} else {
				tenantID := tenantIDs[0]
				customers, err := centralhandler.GetCentralHandler().ListPartnerCustomers(tenantID)
				if err != nil {
					log.Error("failed to list customers", zap.Error(err), zap.Uint("tenantID", tenantID))
					return nil, err
				}
				for _, c := range customers {
					customerNameCache[c.ID] = c.Name
				}
			}
		}
	}
	ret := make([]forms.CostExploreTrendsOutputBreakdownDetail, len(dbOutput))
	for i, o := range dbOutput {
		var detailNameSlice []string
		var detailKeySlice []string

		for _, b := range query.BreakdownBy {
			used := breakdownSet[b]
			if !used {
				continue
			}
			keyElem := defaultElemVal
			nameElem := defaultElemVal
			switch b {
			case forms.CostExploreTrendsBreakdownKeyProduct:
				if o.Product != nil {
					nameElem = *o.Product
					keyElem = nameElem
				}
			case forms.CostExploreTrendsBreakdownKeyService:
				nameElem = FormatServiceName(&o.CostExplorerService)
				o.Service = &nameElem
				keyElem = nameElem
			case forms.CostExploreTrendsBreakdownKeyRegion:
				var region []string
				if o.Provider != nil {
					region = append(region, *o.Provider)
					if o.Region != nil && *o.Region != "" {
						region = append(region, *o.Region)
					} else {
						r := "others"
						region = append(region, r)
						o.Region = &r
					}
				}
				if len(region) != 0 {
					nameElem = strings.Join(region, " - ")
					keyElem = nameElem
				}
			case forms.CostExploreTrendsBreakdownKeyProvider:
				if o.Provider != nil {
					nameElem = *o.Provider
					keyElem = nameElem
				}
			case forms.CostExploreTrendsBreakdownKeyCluster:
				if o.ClusterID != nil {
					clusterName, ok := clusterNameCache[*o.ClusterID]
					if !ok {
						log.Error("failed to get cluster name", zap.Uint("clusterID", *o.ClusterID), zap.Uints("tenantIDs", tenantIDs))
						clusterName = fmt.Sprint(*o.ClusterID)
					}
					nameElem = clusterName
					keyElem = fmt.Sprint(*o.ClusterID)
					o.ClusterName = &clusterName
				} else {
					name := defaultElemVal
					o.ClusterName = &name
				}
			case forms.CostExploreTrendsBreakdownKeyProject:
				if o.ProjectID != nil {
					projectName, ok := projectNameCache[*o.ProjectID]
					if !ok {
						log.Error("failed to get project name", zap.Uint("projectID", *o.ProjectID), zap.Uints("tenantIDs", tenantIDs))
						projectName = fmt.Sprint(*o.ProjectID)
					}
					nameElem = projectName
					keyElem = fmt.Sprint(*o.ProjectID)
					o.ProjectName = &projectName
				} else {
					name := defaultElemVal
					o.ProjectName = &name
				}
			case forms.CostExploreTrendsBreakdownKeyCustomer:
				if o.CustomerID != nil {
					customerName, ok := customerNameCache[*o.CustomerID]
					if !ok {
						log.Error("failed to get customer name", zap.String("customerID", *o.CustomerID), zap.Uints("tenantIDs", tenantIDs))
						customerName = fmt.Sprint(*o.CustomerID)
					}
					nameElem = customerName
					keyElem = *o.CustomerID
					o.CustomerName = &nameElem
				} else {
					name := defaultElemVal
					o.CustomerName = &name
				}
			}
			detailNameSlice = append(detailNameSlice, nameElem)
			detailKeySlice = append(detailKeySlice, keyElem)
		}
		o.DetailKey = strings.Join(detailKeySlice, "-")
		o.DetailName = strings.Join(detailNameSlice, " / ")
		ret[i] = o
	}
	return ret, nil
}

func GenerateTimeSpan(
	query *forms.CostExploreTrendsInput,
	breakdownDetails []forms.CostExploreTrendsOutputBreakdownDetail) (time.Time, time.Time) {
	end := timeutils.GetDayDate(timeutils.GetNow()).AddDate(0, 0, 1)
	start := end.AddDate(0, 0, config.Get().GetInt(config.MaxCostExplorerTrendsQueryDaysSpan))
	if query.StartDate != nil {
		start = *query.StartDate
	} else if len(breakdownDetails) != 0 && breakdownDetails[0].BillingDate != "" {
		t, err := time.Parse("2006-01-02", breakdownDetails[0].BillingDate)
		if err == nil {
			log.Error("failed to parse time", zap.Error(err), zap.String("BillingDate", breakdownDetails[0].BillingDate))
		} else {
			start = t
		}
	}
	if query.EndDate != nil {
		end = *query.EndDate
	} else if len(breakdownDetails) != 0 && breakdownDetails[len(breakdownDetails)-1].BillingDate != "" {
		t, err := time.Parse("2006-01-02", breakdownDetails[len(breakdownDetails)-1].BillingDate)
		if err != nil {
			log.Error("failed to parse time", zap.Error(err), zap.String("BillingDate", breakdownDetails[len(breakdownDetails)-1].BillingDate))
		} else {
			end = timeutils.GetDayDate(t).AddDate(0, 0, 1)
		}
	}
	return start, end
}

func InitBillingDates(start, end time.Time, group *forms.CostExplorerTrendsTimeGroup) ([]string, map[string]int) {
	indexMap := make(map[string]int)
	var billingDates []string
	var dateFunc, nextFunc func(t time.Time) time.Time
	if group != nil {
		switch *group {
		case forms.CostExploreTrendsTimeGroupMonth:
			dateFunc = timeutils.GetMonthDate
			nextFunc = func(t time.Time) time.Time {
				return t.AddDate(0, 1, 0)
			}
		case forms.CostExploreTrendsTimeGroupDay:
			dateFunc = timeutils.GetDayDate
			nextFunc = func(t time.Time) time.Time {
				return t.AddDate(0, 0, 1)
			}
		default:
			billingDates = []string{""}
			indexMap[""] = 0
			return billingDates, indexMap
		}
	} else {
		billingDates = []string{""}
		indexMap[""] = 0
		return billingDates, indexMap
	}
	for cur := dateFunc(start); cur.Before(end); cur = nextFunc(cur) {
		billingDate := cur.Format("2006-01-02")
		indexMap[billingDate] = len(billingDates)
		billingDates = append(billingDates, billingDate)
	}
	return billingDates, indexMap
}

func GroupBreakdownDetailsByBillingDate(
	query *forms.CostExploreTrendsInput,
	breakdownDetails []forms.CostExploreTrendsOutputBreakdownDetail,
) ([]forms.CostExploreTrendsOutputElement, error) {
	start, end := GenerateTimeSpan(query, breakdownDetails)
	billingDates, elementsIndexMap := InitBillingDates(start, end, query.TimeGroup)
	ret := make([]forms.CostExploreTrendsOutputElement, len(billingDates))
	for i, billingDate := range billingDates {
		ret[i] = forms.CostExploreTrendsOutputElement{BillingDate: billingDate}
	}
	for _, elem := range breakdownDetails {
		if index, ok := elementsIndexMap[elem.BillingDate]; ok {
			ret[index].RawPrice = ret[index].RawPrice.Add(elem.RawPrice)
			ret[index].Discount = ret[index].Discount.Add(elem.Discount)
			ret[index].CreditsUsage = ret[index].CreditsUsage.Add(elem.CreditsUsage)
			ret[index].CostPrice = ret[index].CostPrice.Add(elem.CostPrice)
			ret[index].Breakdown = append(ret[index].Breakdown, elem)
		} else {
			err := errors.New("invalidated billing date")
			log.Error(err.Error(), zap.String("elem.BillingDate", elem.BillingDate), zap.Any("ElementsIndexMap", elementsIndexMap), zap.Any("timeGroup", query.TimeGroup), zap.Any("startDate", query.StartDate), zap.Any("endDate", query.EndDate))
			return nil, billing_errors.NewSystemError(err)
		}
	}
	return ret, nil
}

func FormatCostExploreTrendsOutput(elements []forms.CostExploreTrendsOutputElement) *forms.CostExploreTrendsOutput {
	total := forms.CostExploreTrendsOutputPrice{}
	for _, elem := range elements {
		total.RawPrice = total.RawPrice.Add(elem.RawPrice)
		total.Discount = total.Discount.Add(elem.Discount)
		total.CreditsUsage = total.CreditsUsage.Add(elem.CreditsUsage)
		total.CostPrice = total.CostPrice.Add(elem.CostPrice)
	}
	return &forms.CostExploreTrendsOutput{
		CostExploreTrendsOutputPrice: total,
		Elements:                     elements,
	}
}

func GenerateTrendsCSVHeadersAndRows(
	query *forms.CostExploreTrendsCSVInput,
	breakdownDetails []forms.CostExploreTrendsOutputBreakdownDetail,
) ([]string, []TrendsCSVRow, error) {
	start, end := GenerateTimeSpan(&query.CostExploreTrendsInput, breakdownDetails)
	billingDates, elementsIndexMap := InitBillingDates(start, end, query.TimeGroup)
	var detailKeys []string
	indexMap := make(map[string]int)
	for _, b := range breakdownDetails {
		if _, existed := indexMap[b.DetailKey]; !existed {
			indexMap[b.DetailKey] = 0
			detailKeys = append(detailKeys, b.DetailKey)
		}
	}
	sort.Strings(detailKeys)
	rows := make([]TrendsCSVRow, len(detailKeys))
	for i, d := range detailKeys {
		indexMap[d] = i
		rows[i].DetailKey = d
		rows[i].DateBreakdown = make([]forms.CostExploreTrendsOutputPrice, len(billingDates))
	}
	for _, b := range breakdownDetails {
		index := indexMap[b.DetailKey]
		row := &rows[index]
		row.RawPrice = row.RawPrice.Add(b.RawPrice)
		row.Discount = row.Discount.Add(b.Discount)
		row.CreditsUsage = row.CreditsUsage.Add(b.CreditsUsage)
		row.CostPrice = row.CostPrice.Add(b.CostPrice)
		row.CostExplorerService = b.CostExplorerService
		row.Provider = b.Provider
		row.Region = b.Region
		row.ProjectName = b.ProjectName
		row.ProjectID = b.ProjectID
		row.ClusterName = b.ClusterName
		row.ClusterID = b.ClusterID
		row.DetailName = b.DetailName
		row.CustomerID = b.CustomerID
		row.CustomerName = b.CustomerName
		dateIndex, ok := elementsIndexMap[b.BillingDate]
		if !ok {
			err := errors.New("invalidated billing date")
			log.Error(err.Error(), zap.String("BillingDate", b.BillingDate), zap.Any("ElementsIndexMap", elementsIndexMap), zap.Any("timeGroup", query.TimeGroup), zap.Any("startDate", query.StartDate), zap.Any("endDate", query.EndDate))
			return nil, nil, billing_errors.NewSystemError(err)
		}
		row.DateBreakdown[dateIndex] = b.CostExploreTrendsOutputPrice
	}
	if query.TimeGroup == nil {
		return []string{}, rows, nil
	}
	return billingDates, rows, nil
}

func FormatCostExploreTrendsCSV(query *forms.CostExploreTrendsCSVInput, billingDates []string, rows []TrendsCSVRow) ([]byte, error) {
	buf := new(bytes.Buffer)
	writer := csv.NewWriter(buf)
	if len(query.BreakdownBy) == 0 {
		// charge type
		headers := append([]string{"charge_type", "subtotal"}, billingDates...)
		err := writer.Write(headers)
		if err != nil {
			return nil, err
		}
		if len(rows) > 1 {
			log.Error("invalidate rows", zap.Any("query", query), zap.Int("rows", len(rows)))
			return nil, billing_errors.NewSystemError(errors.New("unexpected err"))
		}
		chargeTypes := []string{"Total Cost", "Running Total", "Discount", "Credits"}
		data := make([][]string, len(chargeTypes))
		for i := 0; i != len(data); i++ {
			data[i] = make([]string, len(headers))
			data[i][0] = chargeTypes[i]
		}
		if len(rows) == 1 {
			data[0][1] = rows[0].CostPrice.String()
			data[1][1] = rows[0].RawPrice.String()
			data[2][1] = rows[0].Discount.Mul(decimal.NewFromInt(-1)).String()
			data[3][1] = rows[0].CreditsUsage.Mul(decimal.NewFromInt(-1)).String()
			for i := 0; i != len(billingDates); i++ {
				dateElem := rows[0].DateBreakdown[i]
				data[0][i+2] = dateElem.CostPrice.String()
				data[1][i+2] = dateElem.RawPrice.String()
				data[2][i+2] = dateElem.Discount.Mul(decimal.NewFromInt(-1)).String()
				data[3][i+2] = dateElem.CreditsUsage.Mul(decimal.NewFromInt(-1)).String()

			}
		} else {
			for i := 0; i != len(chargeTypes); i++ {
				for j := 1; j != len(headers); j++ {
					data[i][j] = "0"
				}
			}
		}
		for i := 0; i != len(data); i++ {
			err = writer.Write(data[i])
			if err != nil {
				return nil, err
			}
		}
	} else {
		// others
		var headers []string
		for _, b := range query.BreakdownBy {
			headers = append(headers, string(b))
		}
		if query.DisplayCreditsAndDiscountOnCSV {
			headers = append(headers, []string{"running_total", "discounts", "credits", "total_cost"}...)
		} else {
			headers = append(headers, "total_cost")
		}
		headers = append(headers, billingDates...)
		err := writer.Write(headers)
		if err != nil {
			return nil, err
		}
		total := make([]decimal.Decimal, len(headers))
		for _, row := range rows {
			data := make([]string, len(headers))
			index := 0
			for _, b := range query.BreakdownBy {
				var value *string
				switch b {
				case forms.CostExploreTrendsBreakdownKeyProduct:
					value = row.Product
				case forms.CostExploreTrendsBreakdownKeyService:
					value = row.Service
				case forms.CostExploreTrendsBreakdownKeyProvider:
					value = row.Provider
				case forms.CostExploreTrendsBreakdownKeyRegion:
					value = row.Region
				case forms.CostExploreTrendsBreakdownKeyProject:
					value = row.ProjectName
				case forms.CostExploreTrendsBreakdownKeyCluster:
					value = row.ClusterName
				case forms.CostExploreTrendsBreakdownKeyCustomer:
					value = row.CustomerName
				}
				if value == nil {
					data[index] = ""
				} else {
					data[index] = *value
				}
				index++
			}
			if query.DisplayCreditsAndDiscountOnCSV {
				total[index] = total[index].Add(row.RawPrice)
				total[index+1] = total[index+1].Sub(row.Discount)
				total[index+2] = total[index+2].Sub(row.CreditsUsage)
				total[index+3] = total[index+3].Add(row.CostPrice)
				data[index] = row.RawPrice.String()
				data[index+1] = row.Discount.Mul(decimal.NewFromInt(-1)).String()
				data[index+2] = row.CreditsUsage.Mul(decimal.NewFromInt(-1)).String()
				data[index+3] = row.CostPrice.String()
				index += 4
			} else {
				total[index] = total[index].Add(row.CostPrice)
				data[index] = row.CostPrice.String()
				index++
			}
			for i := 0; i != len(billingDates); i++ {
				dateElem := row.DateBreakdown[i]
				data[index] = dateElem.CostPrice.String()
				total[index] = total[index].Add(dateElem.CostPrice)
				index++
			}
			err = writer.Write(data)
			if err != nil {
				return nil, err
			}
		}
		totalRow := make([]string, len(headers))
		totalRow[0] = "Total"
		for i := 1; i != len(totalRow); i++ {
			totalRow[i] = total[i].String()
		}
		err = writer.Write(totalRow)
		if err != nil {
			return nil, err
		}
	}
	writer.Flush()
	return buf.Bytes(), nil
}