package common

import (
	"fmt"
	"strconv"
	"time"

	l4g "github.com/alecthomas/log4go"
	"github.com/dailymotion/allure-go"
	"github.com/stretchr/testify/assert"
)

var dbPrefix = "autoTest"

func GetMostCountDBAndTable(t assert.TestingT, clusterId, sessionId string) (string, string) {
	table_Name := ""
	db_Name := ""
	maxCount := 0

	dbassRequestInfo = GetDbassRequestInfo(t)
	orgId := dbassRequestInfo.CommonEnv.OrgId
	projectId := dbassRequestInfo.CommonEnv.ProjectId

	//get brief
	param := map[string]string{"isbrief": "true"}
	pathParam := map[string]string{"org_id": orgId, "project_id": projectId, "cluster_id": clusterId}

	getBriefResp := dbassRequestInfo.DbassRequest(t, "getSchema", nil, pathParam, param, nil)
	l4g.Info("get db list, path param is %s, status code is %d", pathParam, getBriefResp.Raw().StatusCode)
	getBriefResp.Status(StatusOK)
	getBriefResp.JSON().Object().ValueEqual("code", 200)

	for _, db := range getBriefResp.JSON().Path("$.data").Array().Iter() {
		dbName := db.Path("$.name").String().Raw()
		for _, table := range db.Path("$.tables").Array().Iter() {
			tableName := table.Path("$.name").String().Raw()
			sql := fmt.Sprintf("select count(*) from %s.%s;", dbName, tableName)
			body := map[string]string{"sql": sql, "sessionid": sessionId}
			runSqlResp := dbassRequestInfo.DbassRequest(t, "executeSql", body, pathParam, nil, nil)
			runSqlResp.Status(StatusOK)
			runSqlResp.JSON().Object().ValueEqual("code", 200)
			count, _ := strconv.Atoi(runSqlResp.JSON().Path("$.data.rows").Array().Element(0).Array().Element(0).String().Raw())
			if count > maxCount {
				maxCount = count
				db_Name = dbName
				table_Name = tableName
			}
		}
	}
	return db_Name, table_Name
}

func PrepareDBData(t assert.TestingT, clusterId, sessionId string) {
	// init config
	dbassRequestInfo = GetDbassRequestInfo(t)
	orgId := dbassRequestInfo.CommonEnv.OrgId
	projectId := dbassRequestInfo.CommonEnv.ProjectId
	pathParam := map[string]string{"org_id": orgId, "project_id": projectId, "cluster_id": clusterId}

	// create database
	dbName := fmt.Sprintf("%s_%s", dbPrefix, clusterId)
	sql := fmt.Sprintf("create database %s;", dbName)
	body := map[string]string{"sql": sql, "sessionid": sessionId}
	runSqlResp := dbassRequestInfo.DbassRequest(t, "executeSql", body, pathParam, nil, nil)
	l4g.Info("run sql command, path param is %s, body is %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)
	l4g.Info("run sql command response is %s", runSqlResp.JSON())
	allure.Step(
		allure.Story("PrepareDBData"),
		allure.Description(fmt.Sprintf("run sql, path param is %s, body is"+
			" %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)),
		allure.Action(func() {
			runSqlResp.Status(StatusOK)
		}))

	// use db
	sql = fmt.Sprintf("use %s;", dbName)
	body = map[string]string{"sql": sql, "sessionid": sessionId}
	dbassRequestInfo.DbassRequest(t, "executeSql", body, pathParam, nil, nil)
	l4g.Info("run sql command, path param is %s, body is %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)
	l4g.Info("run sql command response is %s", runSqlResp.JSON())
	allure.Step(
		allure.Story("PrepareDBData"),
		allure.Description(fmt.Sprintf("run sql, path param is %s, body is"+
			" %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)),
		allure.Action(func() {
			runSqlResp.Status(StatusOK)
		}))

	// create table
	sql = `
CREATE TABLE IF NOT EXISTS warehouse (
	w_id INT NOT NULL,
	w_name VARCHAR(10),
	w_street_1 VARCHAR(20),
	w_street_2 VARCHAR(20),
	w_city VARCHAR(20),
	w_state CHAR(2),
	w_zip CHAR(9),
	w_tax DECIMAL(4, 4),
	w_ytd DECIMAL(12, 2),
	PRIMARY KEY (w_id) /*T![clustered_index] CLUSTERED */
)`
	body = map[string]string{"sql": sql, "sessionid": sessionId}
	runSqlResp = dbassRequestInfo.DbassRequest(t, "executeSql", body, pathParam, nil, nil)
	l4g.Info("run sql command, path param is %s, body is %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)
	l4g.Info("run sql command response is %s", runSqlResp.JSON())
	allure.Step(
		allure.Story("PrepareDBData"),
		allure.Description(fmt.Sprintf("run sql, path param is %s, body is"+
			" %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)),
		allure.Action(func() {
			runSqlResp.Status(StatusOK)
			runSqlResp.JSON().Path("$.data.execute_time").String().NotEqual("")
			runSqlResp.JSON().Path("$.code").Number().Equal(200)
			expect := fmt.Sprintf("Query OK, 0 rows affected")
			runSqlResp.JSON().Path("$.data.query").String().Contains(expect)
			// need more check
		}))

	sql = `
CREATE TABLE IF NOT EXISTS district (
	d_id INT NOT NULL,
	d_w_id INT NOT NULL,
	d_name VARCHAR(10),
	d_street_1 VARCHAR(20),
	d_street_2 VARCHAR(20),
	d_city VARCHAR(20),
	d_state CHAR(2),
	d_zip CHAR(9),
	d_tax DECIMAL(4, 4),
	d_ytd DECIMAL(12, 2),
	d_next_o_id INT,
	PRIMARY KEY (d_w_id, d_id) /*T![clustered_index] CLUSTERED */
)`
	body = map[string]string{"sql": sql, "sessionid": sessionId}
	runSqlResp = dbassRequestInfo.DbassRequest(t, "executeSql", body, pathParam, nil, nil)
	l4g.Info("run sql command, path param is %s, body is %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)
	l4g.Info("run sql command response is %s", runSqlResp.JSON())
	allure.Step(
		allure.Story("PrepareDBData"),
		allure.Description(fmt.Sprintf("run sql, path param is %s, body is"+
			" %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)),
		allure.Action(func() {
			runSqlResp.Status(StatusOK)
			runSqlResp.JSON().Path("$.data.execute_time").String().NotEqual("")
			runSqlResp.JSON().Path("$.code").Number().Equal(200)
			expect := fmt.Sprintf("Query OK, 0 rows affected")
			runSqlResp.JSON().Path("$.data.query").String().Contains(expect)
			// need more check
		}))

	sql = `
CREATE TABLE IF NOT EXISTS customer (
	c_id INT NOT NULL,
	c_d_id INT NOT NULL,
	c_w_id INT NOT NULL,
	c_first VARCHAR(16),
	c_middle CHAR(2),
	c_last VARCHAR(16),
	c_street_1 VARCHAR(20),
	c_street_2 VARCHAR(20),
	c_city VARCHAR(20),
	c_state CHAR(2),
	c_zip CHAR(9),
	c_phone CHAR(16),
	c_since DATETIME,
	c_credit CHAR(2),
	c_credit_lim DECIMAL(12, 2),
	c_discount DECIMAL(4,4),
	c_balance DECIMAL(12,2),
	c_ytd_payment DECIMAL(12,2),
	c_payment_cnt INT,
	c_delivery_cnt INT, 
	c_data VARCHAR(500),
	PRIMARY KEY(c_w_id, c_d_id, c_id) /*T![clustered_index] CLUSTERED */,
	INDEX idx_customer (c_w_id, c_d_id, c_last, c_first)
)`
	body = map[string]string{"sql": sql, "sessionid": sessionId}
	runSqlResp = dbassRequestInfo.DbassRequest(t, "executeSql", body, pathParam, nil, nil)
	l4g.Info("run sql command, path param is %s, body is %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)
	l4g.Info("run sql command response is %s", runSqlResp.JSON())
	allure.Step(
		allure.Story("PrepareDBData"),
		allure.Description(fmt.Sprintf("run sql, path param is %s, body is"+
			" %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)),
		allure.Action(func() {
			runSqlResp.Status(StatusOK)
			runSqlResp.JSON().Path("$.data.execute_time").String().NotEqual("")
			runSqlResp.JSON().Path("$.code").Number().Equal(200)
			expect := fmt.Sprintf("Query OK, 0 rows affected")
			runSqlResp.JSON().Path("$.data.query").String().Contains(expect)
			// need more check
		}))

	// insert data to warehouse
	hint := "INSERT INTO warehouse (w_id, w_name, w_street_1, w_street_2, w_city, w_state, w_zip, w_tax, w_ytd) VALUES "
	for i := 1; i <= warehouses; i++ {
		wId := i
		wName := RandStr(8)
		wStree1 := RandStr(16)
		wStree2 := RandStr(16)
		wCity := RandStr(12)
		wState := RandStr(2)
		wZip := RandStr(8)
		wTax := 0.5
		wYtd := 300000.00

		sql = fmt.Sprintf("%s (%d, '%s', '%s', '%s', '%s', '%s', '%s', %f, %f)", hint, wId, wName, wStree1, wStree2, wCity, wState, wZip, wTax, wYtd)
		body = map[string]string{"sql": sql, "sessionid": sessionId}
		runSqlResp = dbassRequestInfo.DbassRequest(t, "executeSql", body, pathParam, nil, nil)

		//l4g.Info("run sql command, path param is %s, body is %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)
		l4g.Info("run sql command response is %s", runSqlResp.JSON())
		allure.Step(
			allure.Story("PrepareDBData"),
			allure.Description(fmt.Sprintf("run sql, path param is %s, body is"+
				" %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)),
			allure.Action(func() {
				runSqlResp.Status(StatusOK)
				runSqlResp.JSON().Path("$.code").Number().Equal(200)
				runSqlResp.JSON().Path("$.data.execute_time").String().NotEqual("")
				expect := fmt.Sprintf("Query OK, 1 row affected")
				runSqlResp.JSON().Path("$.data.query").String().Contains(expect)
				// need more check
			}))
	}

	// insert data to district
	hint = `INSERT INTO district (d_id, d_w_id, d_name, d_street_1, d_street_2,
d_city, d_state, d_zip, d_tax, d_ytd, d_next_o_id) VALUES `
	for round := 0; round < warehouses; round++ {
		values := ""
		for i := 1; i <= districtPerWarehouse; i++ {
			dID := i + round*districtPerWarehouse
			dWID := round + 1
			dName := RandStr(8)
			dStreet1 := RandStr(16)
			dStreet2 := RandStr(16)
			dCity := RandStr(16)
			dState := RandStr(2)
			dZip := RandStr(8)
			dTax := 0.2
			dYtd := 30000.00
			dNextOID := 3001
			value := fmt.Sprintf("(%d, %d, '%s', '%s', '%s', '%s', '%s', '%s', %f, %f, %d)", dID, dWID,
				dName, dStreet1, dStreet2, dCity, dState, dZip, dTax, dYtd, dNextOID)
			if values == "" {
				values = value
			} else {
				values = fmt.Sprintf("%s, %s", values, value)
			}
		}

		sql = fmt.Sprintf("%s  %s", hint, values)
		body = map[string]string{"sql": sql, "sessionid": sessionId}
		runSqlResp = dbassRequestInfo.DbassRequest(t, "executeSql", body, pathParam, nil, nil)
		//l4g.Info("run sql command, path param is %s, body is %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)
		l4g.Info("run sql command response is %s", runSqlResp.JSON())
		allure.Step(
			allure.Story("PrepareDBData"),
			allure.Description(fmt.Sprintf("run sql, path param is %s, body is"+
				" %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)),
			allure.Action(func() {
				runSqlResp.Status(StatusOK)
				runSqlResp.JSON().Path("$.code").Number().Equal(200)
				runSqlResp.JSON().Path("$.data.execute_time").String().NotEqual("")
				expect := fmt.Sprintf("Query OK, %d rows affected", districtPerWarehouse)
				runSqlResp.JSON().Path("$.data.query").String().Contains(expect)
				// need more check
			}))
	}

	// insert data to customer
	hint = `INSERT INTO customer (c_id, c_d_id, c_w_id, c_first, c_middle, c_last,
c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim,
c_discount, c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, c_data) VALUES `

	for round := 0; round < districtPerWarehouse*warehouses; round++ {
		values := ""
		for i := 1; i <= customerPerDistrict; i++ {
			cID := i + round*customerPerDistrict
			cDID := round + 1
			cWID := round/districtPerWarehouse + 1
			cMiddle := "OE"
			cFirst := RandStr(16)
			cLast := RandStr(16)
			cStreet1 := RandStr(20)
			cStreet2 := RandStr(20)
			cCity := RandStr(20)
			cState := RandStr(2)
			cZip := RandStr(8)
			cPhone := RandStr(16)
			timeUnix := time.Now().Unix()
			cSince := time.Unix(timeUnix, 0).Format(timeFormat)
			cCredit := "GC"
			if i%2 == 0 {
				cCredit = "BC"
			}
			cCreditLim := 50000.00
			cDisCount := 0.4
			cBalance := -10.00
			cYtdPayment := 10.00
			cPaymentCnt := 1
			cDeliveryCnt := 0
			cData := RandStr(12)

			value := fmt.Sprintf("(%d, %d, %d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %f, %f, %f, %f, %d, %d, '%s')", cID, cDID, cWID, cFirst, cMiddle, cLast, cStreet1, cStreet2, cCity, cState,
				cZip, cPhone, cSince, cCredit, cCreditLim, cDisCount, cBalance,
				cYtdPayment, cPaymentCnt, cDeliveryCnt, cData)
			if values == "" {
				values = value
			} else {
				values = fmt.Sprintf("%s, %s", values, value)
			}
		}

		sql = fmt.Sprintf("%s  %s", hint, values)
		body = map[string]string{"sql": sql, "sessionid": sessionId}
		runSqlResp = dbassRequestInfo.DbassRequest(t, "executeSql", body, pathParam, nil, nil)

		//l4g.Info("run sql command, path param is %s, body is %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)
		l4g.Info("run sql command response is %s ", runSqlResp.JSON())
		allure.Step(
			allure.Story("PrepareDBData"),
			allure.Description(fmt.Sprintf("run sql, path param is %s, body is"+
				" %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)),
			allure.Action(func() {
				runSqlResp.Status(StatusOK)
				runSqlResp.JSON().Path("$.code").Number().Equal(200)
				runSqlResp.JSON().Path("$.data.execute_time").String().NotEqual("")
				expect := fmt.Sprintf("Query OK, %d rows affected", customerPerDistrict)
				runSqlResp.JSON().Path("$.data.query").String().Contains(expect)
				// need more check
			}))
	}
}
func UseDBCheck(t assert.TestingT, clusterId, sessionId, dbName string, statusCode int) {
	// init config
	dbassRequestInfo = GetDbassRequestInfo(t)
	orgId := dbassRequestInfo.CommonEnv.OrgId
	projectId := dbassRequestInfo.CommonEnv.ProjectId
	pathParam := map[string]string{"org_id": orgId, "project_id": projectId, "cluster_id": clusterId}
	// use db
	if dbName == "" {
		dbName = fmt.Sprintf("%s_%s", dbPrefix, clusterId)
	}

	sql := fmt.Sprintf("use %s;", dbName)
	body := map[string]string{"sql": sql, "sessionid": sessionId}
	runSqlResp := dbassRequestInfo.DbassRequest(t, "executeSql", body, pathParam, nil, nil)

	allure.Step(
		allure.Story("UseDBCheck"),
		allure.Description(fmt.Sprintf("run sql, sql is : %s , status code is %d ,internal code is : %.0f, we expect status code is %d.",
			sql,
			runSqlResp.Raw().StatusCode,
			runSqlResp.JSON().Path("$.code").Number().Raw(),
			statusCode)),
		allure.Action(func() {
			runSqlResp.Status(statusCode)
		}))
}
func QueryTableCustomer(t assert.TestingT, clusterId, sessionId string) {
	// init config
	dbassRequestInfo = GetDbassRequestInfo(t)
	orgId := dbassRequestInfo.CommonEnv.OrgId
	projectId := dbassRequestInfo.CommonEnv.ProjectId
	pathParam := map[string]string{"org_id": orgId, "project_id": projectId, "cluster_id": clusterId}

	// query table
	sql := "select count(*) from customer;"
	body := map[string]string{"sql": sql, "sessionid": sessionId}
	runSqlResp := dbassRequestInfo.DbassRequest(t, "executeSql", body, pathParam, nil, nil)
	l4g.Info("run sql command, path param is %s, body is %s, status code is %d, response is %s", pathParam, body, runSqlResp.Raw().StatusCode, runSqlResp.JSON())
	allure.Step(
		allure.Story("CheckTableOperation"),
		allure.Description(fmt.Sprintf("run sql, path param is %s, body is"+
			" %s, status code is %d", pathParam, body, runSqlResp.Raw().StatusCode)),
		allure.Action(func() {
			runSqlResp.Status(StatusOK)
			runSqlResp.JSON().Path("$.code").Equal(200)
		}))
}