package gcp

import (
	"fmt"
	"os"
	"strings"
	"testing"
	"time"

	. "github.com/onsi/gomega"
)

const (
	// SQL template
	EQTemplate      = `%s = "%s"`
	GETemplate      = `%s >= "%s"`
	LTTemplate      = `%s < "%s"`
	LikeTemplate    = `%s LIKE "%s"`
	mainSQLTemplate = `
SELECT 
    %s 
FROM
    %s
WHERE
    %s
`
)

const (
	// TenantTag is the billing tag name
	TenantTag      = "tenant"
	ProjectTag     = "project"
	EnvironmentTag = "environment"
	ComponentTag   = "component"
	UsedByTag      = "usedby"
)

func makeSubSQLForLabels(table string, labelKeys []string, startTime time.Time, endTime time.Time) string {
	subquerySQLForTags := makeFieldSQLForSubQueryLabels(labelKeys)
	fileds := []string{subquerySQLForTags, "*"}
	filedsSQL := strings.Join(fileds, ",")

	// prefiltering
	conditionSQL := makeSQLForFieldCondition(startTime, endTime)
	sql := fmt.Sprintf(mainSQLTemplate, filedsSQL, table, conditionSQL)
	return sql
}

func makeFieldSQLForSubQueryLabels(keys []string) string {
	subQueryTemplate := "(select value from UNNEST(labels) where key='%s' limit 1) as %s"
	subQueryList := []string{}
	for _, v := range keys {
		sql := fmt.Sprintf(subQueryTemplate, v, v+"_id")
		subQueryList = append(subQueryList, sql)
	}
	subQuerySQL := strings.Join(subQueryList, ",")
	return subQuerySQL
}

// makeSQLForLabelsCondition  [start_time,end_time)
func makeSQLForLabelsCondition(tenantID string, projectID string) string {
	conditionList := []string{}

	conditionList = append(conditionList, fmt.Sprintf(EQTemplate, "tenant_id", tenantID))
	if projectID != "" {
		conditionList = append(conditionList, fmt.Sprintf(EQTemplate, "project_id", projectID))
	}

	conditionSQL := strings.Join(conditionList, " AND ")
	return conditionSQL
}

func makeSQLForFieldCondition(start_time time.Time, end_time time.Time) string {
	conditionList := []string{}

	conditionList = append(
		conditionList,
		fmt.Sprintf(GETemplate, "usage_start_time", start_time.Format(TIME_TEMPLATE)),
		fmt.Sprintf(LTTemplate, "usage_start_time", end_time.Format(TIME_TEMPLATE)),
	)

	orConditionList := []string{
		makeConditionForNetworkBilling(),
		makeConditionForStorageBilling(),
	}
	bizCondition := fmt.Sprintf("((%s))", strings.Join(orConditionList, ") OR ("))

	conditionList = append(conditionList, bizCondition)
	conditionSQL := strings.Join(conditionList, " AND ")
	return conditionSQL
}

// business special

func makeConditionForNetworkBilling() string {
	orConditionList := []string{}
	orConditionList = append(orConditionList,
		fmt.Sprintf(LikeTemplate, "sku.description", "Network Internet %"),
		fmt.Sprintf(LikeTemplate, "sku.description", "Network Inter Region %"),
	)
	condition := fmt.Sprintf("(%s) AND %s",
		strings.Join(orConditionList, " OR "),
		fmt.Sprintf(EQTemplate, "service.description", "Compute Engine"),
	)
	return condition
}

func makeConditionForStorageBilling() string {
	condition := fmt.Sprintf(EQTemplate, "service.description", "Cloud Storage")

	return condition
}

func makeBigQuerySQL(table string, tenantID string, projectID string, startTime time.Time, endTime time.Time) {
	tagKeys := []string{TenantTag, ProjectTag, EnvironmentTag, ComponentTag, UsedByTag}
	fileds := []string{
		"usage_start_time", "usage_end_time",
		"usage.amount_in_pricing_units AS amount",
		"usage.pricing_unit AS unit",
		"sku.description AS description",
		"service.description AS service_name",
		"cost",
	}
	for _, v := range tagKeys {
		fileds = append(fileds, v+"_id")
	}
	filedsSQL := strings.Join(fileds, ",")
	conditionSQL := makeSQLForLabelsCondition(tenantID, projectID)
	tableFromSubquery := makeSubSQLForLabels(
		table,
		tagKeys,
		startTime, endTime,
	)
	sql := fmt.Sprintf(mainSQLTemplate, filedsSQL, fmt.Sprintf("(%s)", tableFromSubquery), conditionSQL)
	fmt.Println(sql)
}

func TestMakeSubquery(t *testing.T) {
	g := NewGomegaWithT(t)
	ans := "(select value from UNNEST(labels) where key='tenant' limit 1) as tenant_id,(select value from UNNEST(labels) where key='project' limit 1) as project_id,(select value from UNNEST(labels) where key='environment' limit 1) as environment_id,(select value from UNNEST(labels) where key='component' limit 1) as component_id,(select value from UNNEST(labels) where key='usedby' limit 1) as usedby_id"

	sql := makeFieldSQLForSubQueryLabels([]string{TenantTag, ProjectTag, EnvironmentTag, ComponentTag, UsedByTag})
	g.Expect(ans).To(Equal(sql))
}
func TestMakeBigQuerySQL(t *testing.T) {
	g := NewGomegaWithT(t)
	// if not set credentials skip test
	if os.Getenv(GOOGLE_APPLICATION_CREDENTIALS) == "" {
		t.Skip()
	}

	startTime, err := time.Parse(TIME_TEMPLATE, "2022-01-11 19:00:00")
	g.Expect(err).To(BeNil())
	endTime, err := time.Parse(TIME_TEMPLATE, "2022-01-11 23:00:00")
	g.Expect(err).To(BeNil())
	makeBigQuerySQL(BIGQUERY_TABLE_NAME, "1", "", startTime, endTime)
}