package platform

import (
	"fmt"
	"net/http"
	"time"

	"github.com/gavv/httpexpect/v2"
	"github.com/tidbcloud/qa-PlatformAPIAuto/common"
	"github.com/tidbcloud/qa-gourd/ProductService"
	"github.com/tidbcloud/qa-gourd/util"
)

func RunChat2QuerySQLCheckSceneTest(w *ProductService.WebRequestInfo, UrlInfoMap map[string]ProductService.UrlInfo, reporter *util.AllureReporter) {
	pt := InitPlatformTest(w, UrlInfoMap, reporter)
	pt.CheckChat2QuerySQLScene()
}

func (pt *PlatformTest) CheckChat2QuerySQLScene() {
	ct := &Chat2QueryTest{
		pt:       pt,
		Provider: pt.w.Cluster.Provider,
		Region:   pt.w.Cluster.Region,
	}
	ct.pt.Reporter.StepReport("CreateSession",
		func() {
			ct.SessionId = pt.CreateSession()
		})
	ct.pt.Reporter.StepReport("CheckDBOperation",
		func() {
			ct.CheckDBOperation()
		})
	ct.pt.Reporter.StepReport("CheckTableOperation",
		func() {
			ct.CheckTableOperation()
		})
	ct.pt.Reporter.StepReport("CheckIndexOperation",
		func() {
			ct.CheckIndexOperation()
		})
	ct.pt.Reporter.StepReport("CheckViewOperation",
		func() {
			ct.CheckViewOperation()
		})
	ct.pt.Reporter.StepReport("CheckDropOperation",
		func() {
			ct.CheckDropOperation()
		})
	ct.pt.Reporter.StepReport("CheckPrivilege",
		func() {
			ct.CheckPrivilege()
		})
	ct.pt.Reporter.StepReport("CloseSession", func() {
		pt.CloseSession(ct.SessionId)
		pt.CheckSessionIsClosed(ct.SessionId)
	})
}
func (ct *Chat2QueryTest) CheckDBOperation() {
	pathparam := map[string]string{
		"org_id":     ct.pt.w.User.OrgId,
		"project_id": ct.pt.w.User.ProjectId,
		"cluster_id": ct.pt.w.Cluster.ClusterId,
	}
	// create database
	dbName := fmt.Sprintf("autoTest%s", ct.pt.w.Cluster.ClusterId)
	ct.pt.Reporter.StepReport("CreateDB",
		func() {
			sql := fmt.Sprintf("create database %s;", dbName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}

			runSqlResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("Create DB, path param is %s, body is"+
				" %s, status code is %d", pathparam, body, runSqlResp.Raw().StatusCode),
				func() {
					runSqlResp.Status(http.StatusOK)
				})
		})

	// show create database
	ct.pt.Reporter.StepReport("ShowCreateDB",
		func() {
			sql := fmt.Sprintf("show create database %s;", dbName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			runSqlResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("Show create db , path param is %s, body is"+
				" %s, status code is %d, response data is : %v",
				pathparam,
				body,
				runSqlResp.Raw().StatusCode,
				runSqlResp.JSON().Path("$.data").Raw()),
				func() {
					runSqlResp.Status(http.StatusOK)
					runSqlResp.JSON().Path("$.data.columns").Array().Length().IsEqual(2)
					runSqlResp.JSON().Path("$.data.columns").Array().Value(0).Path("$.col").IsEqual("Database")
					runSqlResp.JSON().Path("$.data.columns").Array().Value(0).Path("$.data_type").IsEqual("VARCHAR")
					runSqlResp.JSON().Path("$.data.columns").Array().Value(0).Path("$.nullable").IsEqual(true)
					len := int(runSqlResp.JSON().Path("$.data.columns").Array().Length().Raw())
					runSqlResp.JSON().Path("$.data.columns").Array().Value(len - 1).Path("$.col").IsEqual("Create Database")
					runSqlResp.JSON().Path("$.data.columns").Array().Value(len - 1).Path("$.data_type").IsEqual("VARCHAR")
					runSqlResp.JSON().Path("$.data.columns").Array().Value(len - 1).Path("$.nullable").IsEqual(true)
					runSqlResp.JSON().Path("$.data.rows").Array().Length().IsEqual(1)
					runSqlResp.JSON().Path("$.data.rows").Array().Value(0).Array().Value(0).IsEqual(dbName)
					expectContent := fmt.Sprintf("CREATE DATABASE `%s` /*!40100 DEFAULT CHARACTER SET utf8mb4 */", dbName)
					runSqlResp.JSON().Path("$.data.rows").Array().Value(0).Array().Value(len - 1).String().IsEqual(expectContent)
				})
		})

	// show databases
	ct.pt.Reporter.StepReport("ShowDB",
		func() {
			sql := fmt.Sprintf("show databases;")
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			runSqlResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("show database, path param is %s, body is"+
				" %s, status code is %d, response data is %+v",
				pathparam,
				body,
				runSqlResp.Raw().StatusCode,
				runSqlResp.JSON().Path("$.data").Raw()),
				func() {
					runSqlResp.Status(http.StatusOK)
					runSqlResp.JSON().Path("$.data.columns").Array().Length().IsEqual(1)
					runSqlResp.JSON().Path("$.data.columns").Array().Value(0).Path("$.col").IsEqual("Database")
					runSqlResp.JSON().Path("$.data.columns").Array().Value(0).Path("$.data_type").IsEqual("VARCHAR")
					runSqlResp.JSON().Path("$.data.columns").Array().Value(0).Path("$.nullable").IsEqual(true)
					runSqlResp.JSON().Path("$.data.row_count").Number().Ge(3)
					runSqlResp.JSON().Path("$.data.rows").Array().Length().Ge(3)
				})
			param := map[string]string{"isbrief": "true"}
			getBriefResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["getSchema"], nil, pathparam, param)
			for _, briefVal := range getBriefResp.JSON().Path("$.data").Array().Iter() {
				find := false
				dbName := briefVal.Path("$.name").String().Raw()
				for _, val := range runSqlResp.JSON().Path("$.data.rows").Array().Iter() {
					if val.Array().Value(0).String().Raw() == dbName {
						find = true
						break
					}
				}
				value := httpexpect.NewBoolean(ct.pt.Reporter.T, find)
				ct.pt.Reporter.StepReport(fmt.Sprintf("Brief and sql query consistency is : %v", find),
					func() {
						value.IsEqual(true)
					})
			}
		})
}

func (ct *Chat2QueryTest) CheckTableOperation() {
	pathparam := map[string]string{
		"org_id":     ct.pt.w.User.OrgId,
		"project_id": ct.pt.w.User.ProjectId,
		"cluster_id": ct.pt.w.Cluster.ClusterId,
	}
	// use db
	dbName := fmt.Sprintf("autoTest%s", ct.pt.w.Cluster.ClusterId)
	sql := fmt.Sprintf("use %s;", dbName)
	body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
	ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)

	// create table
	ct.pt.Reporter.StepReport("CreateTable[warehouse]Operation",
		func() {
			body := map[string]string{"sql": CreateTableSql_warehouse, "sessionid": ct.SessionId}
			createTableResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("CreateTable[warehouse], path param is %s, body is"+
				" %s, status code is %d,response data is : %v", pathparam, body, createTableResp.Raw().StatusCode, createTableResp.JSON().Path("$.data.query").Raw()),
				func() {
					createTableResp.Status(http.StatusOK)
					createTableResp.JSON().Path("$.data.execute_time").String().NotEqual("")
					createTableResp.JSON().Path("$.code").Number().IsEqual(200)
					expect := common.GenQueryOKExpect(0)
					createTableResp.JSON().Path("$.data.query").String().Contains(expect)
					// need more check
				})
		})

	ct.pt.Reporter.StepReport("CreateTable[district]Operation",
		func() {
			body = map[string]string{"sql": CreateTableSql_district, "sessionid": ct.SessionId}
			createTableResp_district := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("CreateTable[district], path param is %s, body is"+
				" %s, status code is %d,response data is : %v", pathparam, body, createTableResp_district.Raw().StatusCode, createTableResp_district.JSON().Path("$.data.query").Raw()),
				func() {
					createTableResp_district.Status(http.StatusOK)
					createTableResp_district.JSON().Path("$.data.execute_time").String().NotEqual("")
					createTableResp_district.JSON().Path("$.code").Number().IsEqual(200)
					expect := common.GenQueryOKExpect(0)
					createTableResp_district.JSON().Path("$.data.query").String().Contains(expect)
					// need more check
				})
		})

	ct.pt.Reporter.StepReport("CreateTable[customer]Operation",
		func() {
			body = map[string]string{"sql": CreateTableSql_customer, "sessionid": ct.SessionId}
			CreateTableSql_customer := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("CreateTable[customer], path param is %s, body is"+
				" %s, status code is %d,response data is : %v", pathparam, body, CreateTableSql_customer.Raw().StatusCode, CreateTableSql_customer.JSON().Path("$.data.query").Raw()),
				func() {
					CreateTableSql_customer.Status(http.StatusOK)
					CreateTableSql_customer.JSON().Path("$.data.execute_time").String().NotEqual("")
					CreateTableSql_customer.JSON().Path("$.code").Number().IsEqual(200)
					expect := common.GenQueryOKExpect(0)
					CreateTableSql_customer.JSON().Path("$.data.query").String().Contains(expect)
					// need more check
				})
		})

	// show create table
	ct.pt.Reporter.StepReport("showCreateTableOperation",
		func() {
			sql = "show create table warehouse;"
			body = map[string]string{"sql": sql, "sessionid": ct.SessionId}
			showCreateTableResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("showCreateTable , path param is %s, body is"+
				" %s, status code is %d , response data is : %v", pathparam, body, showCreateTableResp.Raw().StatusCode, showCreateTableResp.JSON().Path("$.data.rows").Raw()),
				func() {
					showCreateTableResp.Status(http.StatusOK)
					showCreateTableResp.JSON().Path("$.code").Number().IsEqual(200)
					showCreateTableResp.JSON().Path("$.data.execute_time").String().NotEqual("")
					showCreateTableResp.JSON().Path("$.data.columns").Array().Length().IsEqual(2)
					showCreateTableResp.JSON().Path("$.data.columns").Array().Value(0).Object().HasValue("col", "Table")
					showCreateTableResp.JSON().Path("$.data.columns").Array().Value(0).Object().HasValue("data_type", "VARCHAR")
					showCreateTableResp.JSON().Path("$.data.columns").Array().Value(0).Object().HasValue("nullable", true)
					len := int(showCreateTableResp.JSON().Path("$.data.columns").Array().Length().Raw())
					showCreateTableResp.JSON().Path("$.data.columns").Array().Value(len-1).Object().HasValue("col", "Create Table")
					showCreateTableResp.JSON().Path("$.data.columns").Array().Value(len-1).Object().HasValue("data_type", "VARCHAR")
					showCreateTableResp.JSON().Path("$.data.columns").Array().Value(len-1).Object().HasValue("nullable", true)
					showCreateTableResp.JSON().Path("$.data.rows").Array().Length().IsEqual(1)
					showCreateTableResp.JSON().Path("$.data.rows").Array().Value(0).Array().Value(0).String().IsEqual("warehouse")
					showCreateTableResp.JSON().Path("$.data.rows").Array().Value(0).Array().Value(1).String().Contains("CREATE TABLE `warehouse`")
					// need more check
				})
		})

	// desc table
	ct.pt.Reporter.StepReport("descTableOperation",
		func() {
			sql = "desc district;"
			body = map[string]string{"sql": sql, "sessionid": ct.SessionId}
			descTableResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("descTable , path param is %s, body is"+
				" %s, status code is %d , response data is : %v", pathparam, body, descTableResp.Raw().StatusCode, descTableResp.JSON().Path("$.data.rows").Raw()),
				func() {
					descTableResp.Status(http.StatusOK)
					descTableResp.JSON().Path("$.code").Number().IsEqual(200)
					descTableResp.JSON().Path("$.data.execute_time").String().NotEqual("")
					descTableResp.JSON().Path("$.data.row_count").Number().IsEqual(11)
					descTableResp.JSON().Path("$.data.columns").Array().Length().IsEqual(6)
					descTableResp.JSON().Path("$.data.columns").Array().Value(0).Object().HasValue("col", "Field")
					descTableResp.JSON().Path("$.data.columns").Array().Value(0).Object().HasValue("data_type", "VARCHAR")
					descTableResp.JSON().Path("$.data.columns").Array().Value(0).Object().HasValue("nullable", true)
					descTableResp.JSON().Path("$.data.columns").Array().Value(1).Object().HasValue("col", "Type")
					descTableResp.JSON().Path("$.data.columns").Array().Value(1).Object().HasValue("data_type", "VARCHAR")
					descTableResp.JSON().Path("$.data.columns").Array().Value(1).Object().HasValue("nullable", true)
					descTableResp.JSON().Path("$.data.columns").Array().Value(2).Object().HasValue("col", "Null")
					descTableResp.JSON().Path("$.data.columns").Array().Value(2).Object().HasValue("data_type", "VARCHAR")
					descTableResp.JSON().Path("$.data.columns").Array().Value(2).Object().HasValue("nullable", true)
					descTableResp.JSON().Path("$.data.columns").Array().Value(3).Object().HasValue("col", "Key")
					descTableResp.JSON().Path("$.data.columns").Array().Value(3).Object().HasValue("data_type", "VARCHAR")
					descTableResp.JSON().Path("$.data.columns").Array().Value(3).Object().HasValue("nullable", true)
					descTableResp.JSON().Path("$.data.columns").Array().Value(4).Object().HasValue("col", "Default")
					descTableResp.JSON().Path("$.data.columns").Array().Value(4).Object().HasValue("data_type", "VARCHAR")
					descTableResp.JSON().Path("$.data.columns").Array().Value(4).Object().HasValue("nullable", true)
					descTableResp.JSON().Path("$.data.columns").Array().Value(5).Object().HasValue("col", "Extra")
					descTableResp.JSON().Path("$.data.columns").Array().Value(5).Object().HasValue("data_type", "VARCHAR")
					descTableResp.JSON().Path("$.data.columns").Array().Value(5).Object().HasValue("nullable", true)
					descTableResp.JSON().Path("$.data.rows").Array().Length().IsEqual(11)
					descTableResp.JSON().Path("$.data.rows").Array().Value(0).Array().Value(0).String().IsEqual("d_id")
					descTableResp.JSON().Path("$.data.rows").Array().Value(0).Array().Value(1).String().InList("int", "int(11)")
					descTableResp.JSON().Path("$.data.rows").Array().Value(0).Array().Value(2).String().IsEqual("NO")
					descTableResp.JSON().Path("$.data.rows").Array().Value(0).Array().Value(3).String().IsEqual("PRI")
					// need more check
				})
		})

	// show tables
	ct.pt.Reporter.StepReport("showTablesOperation",
		func() {
			sql = "show tables;"
			body = map[string]string{"sql": sql, "sessionid": ct.SessionId}
			showTablesResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("showTables , path param is %s, body is"+
				" %s, status code is %d , response data is : %v", pathparam, body, showTablesResp.Raw().StatusCode, showTablesResp.JSON().Path("$.data.rows").Raw()),
				func() {
					showTablesResp.Status(http.StatusOK)
					showTablesResp.JSON().Path("$.code").Number().IsEqual(200)
					showTablesResp.JSON().Path("$.data.execute_time").String().NotEqual("")
					showTablesResp.JSON().Path("$.data.columns").Array().Length().IsEqual(1)
					expect := fmt.Sprintf("Tables_in_%s", dbName)
					showTablesResp.JSON().Path("$.data.columns").Array().Value(0).Path("$.col").IsEqual(expect)
					showTablesResp.JSON().Path("$.data.columns").Array().Value(0).Path("$.data_type").IsEqual("VARCHAR")
					showTablesResp.JSON().Path("$.data.columns").Array().Value(0).Path("$.nullable").IsEqual(true)
					showTablesResp.JSON().Path("$.data.row_count").Number().IsEqual(3)
					showTablesResp.JSON().Path("$.data.rows").Array().Length().IsEqual(3)
					showTablesResp.JSON().Path("$.data.rows").Array().Value(0).Array().Value(0).String().IsEqual("customer")
					// need more check
				})
		})

	// insert data to warehouse
	ct.pt.Reporter.StepReport("insertData[warehouse]Operation",
		func() {
			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 := common.RandStr(8)
				wStree1 := common.RandStr(16)
				wStree2 := common.RandStr(16)
				wCity := common.RandStr(12)
				wState := common.RandStr(2)
				wZip := common.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": ct.SessionId}
				insertDataResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
				ct.pt.Reporter.StepReport(fmt.Sprintf("insertData[warehouse], path param is %s, body is"+
					" %s, status code is %d,response data is : %v", pathparam, body, insertDataResp.Raw().StatusCode, insertDataResp.JSON().Path("$.data.query").Raw()),
					func() {
						insertDataResp.Status(http.StatusOK)
						insertDataResp.JSON().Path("$.code").Number().IsEqual(200)
						insertDataResp.JSON().Path("$.data.execute_time").String().NotEqual("")
						expect := common.GenQueryOKExpect(1)
						insertDataResp.JSON().Path("$.data.query").String().Contains(expect)
						// need more check
					})
			}
		})

	// insert data to district
	ct.pt.Reporter.StepReport("insertData[district]Operation",
		func() {
			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 := common.RandStr(8)
					dStreet1 := common.RandStr(16)
					dStreet2 := common.RandStr(16)
					dCity := common.RandStr(16)
					dState := common.RandStr(2)
					dZip := common.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": ct.SessionId}
				insertDataResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
				ct.pt.Reporter.StepReport(fmt.Sprintf("insertData[district], path param is %s, body is"+
					" %s, status code is %d,response data is : %v", pathparam, body, insertDataResp.Raw().StatusCode, insertDataResp.JSON().Path("$.data.query").Raw()),
					func() {
						insertDataResp.Status(http.StatusOK)
						insertDataResp.JSON().Path("$.code").Number().IsEqual(200)
						insertDataResp.JSON().Path("$.data.execute_time").String().NotEqual("")
						expect := common.GenQueryOKExpect(districtPerWarehouse)
						insertDataResp.JSON().Path("$.data.query").String().Contains(expect)
						// need more check
					})
			}
		})

	// insert data to customer
	ct.pt.Reporter.StepReport("insertData[customer]Operation",
		func() {
			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 := common.RandStr(16)
					cLast := common.RandStr(16)
					cStreet1 := common.RandStr(20)
					cStreet2 := common.RandStr(20)
					cCity := common.RandStr(20)
					cState := common.RandStr(2)
					cZip := common.RandStr(8)
					cPhone := common.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 := common.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": ct.SessionId}
				insertDataResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
				ct.pt.Reporter.StepReport(fmt.Sprintf("insertData[customer], path param is %s, body is"+
					" %s, status code is %d,response data is : %v", pathparam, body, insertDataResp.Raw().StatusCode, insertDataResp.JSON().Path("$.data.query").Raw()),
					func() {
						insertDataResp.Status(http.StatusOK)
						insertDataResp.JSON().Path("$.code").Number().IsEqual(200)
						insertDataResp.JSON().Path("$.data.execute_time").String().NotEqual("")
						expect := common.GenQueryOKExpect(customerPerDistrict)
						insertDataResp.JSON().Path("$.data.query").String().Contains(expect)
						// need more check
					})
			}
		})

	// update data
	ct.pt.Reporter.StepReport("updateData[customer]Operation",
		func() {
			for i := 1; i <= modifyCount; i++ {
				dbName = "customer"
				sql = fmt.Sprintf("update %s set c_middle='NE' where c_d_id=%d", dbName, i)
				body = map[string]string{"sql": sql, "sessionid": ct.SessionId}
				updateDataResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
				ct.pt.Reporter.StepReport(fmt.Sprintf("updateData[customer], path param is %s, body is"+
					" %s, status code is %d,response data is : %v", pathparam, body, updateDataResp.Raw().StatusCode, updateDataResp.JSON().Path("$.data.query").Raw()),
					func() {
						updateDataResp.Status(http.StatusOK)
						updateDataResp.JSON().Path("$.code").Number().IsEqual(200)
						updateDataResp.JSON().Path("$.data.execute_time").String().NotEqual("")
						expect := common.GenQueryOKExpect(customerPerDistrict)
						updateDataResp.JSON().Path("$.data.query").String().Contains(expect)
						// need more check
					})
			}
		})

	// query data
	ct.pt.Reporter.StepReport("queryData[customer]Operation",
		func() {
			sql = fmt.Sprintf("select count(*),c_d_id from %s GROUP BY c_d_id ORDER BY c_d_id;", dbName)
			body = map[string]string{"sql": sql, "sessionid": ct.SessionId}
			queryDataResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("queryData[customer], path param is %s, body is"+
				" %s, status code is %d,response data is : %v", pathparam, body, queryDataResp.Raw().StatusCode, queryDataResp.JSON().Path("$.data.rows").Raw()),
				func() {
					queryDataResp.Status(http.StatusOK)
					queryDataResp.JSON().Path("$.code").Number().IsEqual(200)
					queryDataResp.JSON().Path("$.data.execute_time").String().NotEqual("")
					queryDataResp.JSON().Path("$.data.columns").Array().Value(0).Object().HasValue("col", "count(*)")
					queryDataResp.JSON().Path("$.data.columns").Array().Value(0).Object().HasValue("data_type", "BIGINT")
					queryDataResp.JSON().Path("$.data.columns").Array().Value(0).Object().HasValue("nullable", false)
					queryDataResp.JSON().Path("$.data.columns").Array().Value(1).Object().HasValue("col", "c_d_id")
					queryDataResp.JSON().Path("$.data.columns").Array().Value(1).Object().HasValue("data_type", "INT")
					queryDataResp.JSON().Path("$.data.columns").Array().Value(1).Object().HasValue("nullable", false)
					queryDataResp.JSON().Path("$.data.rows").Array().Length().IsEqual(districtPerWarehouse * warehouses)
				})
		})

	// query cross two tables
	ct.pt.Reporter.StepReport("queryDataCrossTwoTableOperation",
		func() {
			sql = fmt.Sprintf("select * from %s w JOIN  %s d on w.w_id= d.d_w_id;", "warehouse", "district")
			body = map[string]string{"sql": sql, "sessionid": ct.SessionId}
			queryDataCrossTwoTableResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("queryDataCrossTwoTable, path param is %s, body is"+
				" %s, status code is %d,response data is : %v", pathparam, body, queryDataCrossTwoTableResp.Raw().StatusCode, queryDataCrossTwoTableResp.JSON().Path("$.data.rows").Raw()),
				func() {
					queryDataCrossTwoTableResp.Status(http.StatusOK)
					queryDataCrossTwoTableResp.JSON().Path("$.code").Number().IsEqual(200)
					queryDataCrossTwoTableResp.JSON().Path("$.data.execute_time").String().NotEqual("")
					queryDataCrossTwoTableResp.JSON().Path("$.data.columns").Array().Length().IsEqual(20)
					queryDataCrossTwoTableResp.JSON().Path("$.data.rows").Array().Length().IsEqual(districtPerWarehouse * warehouses)
				})
		})

	// delete data
	ct.pt.Reporter.StepReport("deleteData[customer]Operation",
		func() {
			for i := 1; i <= modifyCount; i++ {
				sql = fmt.Sprintf("delete from %s where c_d_id=%d;", "customer", i)
				body = map[string]string{"sql": sql, "sessionid": ct.SessionId}
				deleteDataResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
				ct.pt.Reporter.StepReport(fmt.Sprintf("deleteData[customer], path param is %s, body is"+
					" %s, status code is %d,response data is : %v", pathparam, body, deleteDataResp.Raw().StatusCode, deleteDataResp.JSON().Path("$.data.query").Raw()),
					func() {
						deleteDataResp.Status(http.StatusOK)
						deleteDataResp.JSON().Path("$.code").Number().IsEqual(200)
						deleteDataResp.JSON().Path("$.data.execute_time").String().NotEqual("")
						expect := common.GenQueryOKExpect(customerPerDistrict)
						deleteDataResp.JSON().Path("$.data.query").String().Contains(expect)
					})
			}
		})

	// alter table, add column
	ct.pt.Reporter.StepReport("alterTableAddColumn[warehouse]Operation",
		func() {
			sql = fmt.Sprintf("alter table %s add column test int;", "warehouse")
			body = map[string]string{"sql": sql, "sessionid": ct.SessionId}
			alterTableResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("alterTableAddColumn[warehouse], path param is %s, body is"+
				" %s, status code is %d,response data is : %v", pathparam, body, alterTableResp.Raw().StatusCode, alterTableResp.JSON().Path("$.data.query").Raw()),
				func() {
					alterTableResp.Status(http.StatusOK)
					alterTableResp.JSON().Path("$.code").Number().IsEqual(200)
					alterTableResp.JSON().Path("$.data.execute_time").String().NotEqual("")
					expect := common.GenQueryOKExpect(0)
					alterTableResp.JSON().Path("$.data.query").String().Contains(expect)
				})
		})

	// alter table, drop column
	ct.pt.Reporter.StepReport("alterTableDropColumn[warehouse]Operation",
		func() {
			sql = fmt.Sprintf("alter table %s drop column test;", "warehouse")
			body = map[string]string{"sql": sql, "sessionid": ct.SessionId}
			alterTableResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("alterTableDropColumn[warehouse], path param is %s, body is"+
				" %s, status code is %d,response data is : %v", pathparam, body, alterTableResp.Raw().StatusCode, alterTableResp.JSON().Path("$.data.query").Raw()),
				func() {
					alterTableResp.Status(http.StatusOK)
					alterTableResp.JSON().Path("$.code").Number().IsEqual(200)
					alterTableResp.JSON().Path("$.data.execute_time").String().NotEqual("")
					expect := common.GenQueryOKExpect(0)
					alterTableResp.JSON().Path("$.data.query").String().Contains(expect)
				})
		})
}

func (ct *Chat2QueryTest) CheckViewOperation() {
	pathparam := map[string]string{
		"org_id":     ct.pt.w.User.OrgId,
		"project_id": ct.pt.w.User.ProjectId,
		"cluster_id": ct.pt.w.Cluster.ClusterId,
	}
	viewName := "indexTest"

	// add view
	ct.pt.Reporter.StepReport("addViewOperation",
		func() {
			TableName := "district"
			sql := fmt.Sprintf("create view %s as select * from %s;", viewName, TableName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			addViewResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("addView, path param is %s, body is"+
				" %s, status code is %d,response data is : %v", pathparam, body, addViewResp.Raw().StatusCode, addViewResp.JSON().Path("$.data.query").Raw()),
				func() {
					addViewResp.Status(http.StatusOK)
					addViewResp.JSON().Object().HasValue("code", 200)
					addViewResp.JSON().Path("$.data.query").String().Contains("Query OK")
				})
		})

	// query from view
	ct.pt.Reporter.StepReport("queryViewOperation",
		func() {
			sql := fmt.Sprintf("select * from %s;", viewName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			queryViewResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("queryView, path param is %s, body is"+
				" %s, status code is %d,response data is : %v", pathparam, body, queryViewResp.Raw().StatusCode, queryViewResp.JSON().Path("$.data.rows").Raw()),
				func() {
					queryViewResp.Status(http.StatusOK)
					queryViewResp.JSON().Object().HasValue("code", 200)
					queryViewResp.JSON().Path("$.data.columns").Array().Length().IsEqual(11)
					queryViewResp.JSON().Path("$.data.rows").Array().Length().IsEqual(warehouses * districtPerWarehouse)
				})
		})

	// drop view
	ct.pt.Reporter.StepReport("dropViewOperation",
		func() {
			sql := fmt.Sprintf("drop view %s;", viewName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			dropViewResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("dropView, path param is %s, body is"+
				" %s, status code is %d,response data is : %v", pathparam, body, dropViewResp.Raw().StatusCode, dropViewResp.JSON().Path("$.data.query").Raw()),
				func() {
					dropViewResp.Status(http.StatusOK)
					dropViewResp.JSON().Object().HasValue("code", 200)
					dropViewResp.JSON().Path("$.data.query").String().Contains("Query OK")
				})
		})
}

func (ct *Chat2QueryTest) CheckDropOperation() {
	pathparam := map[string]string{
		"org_id":     ct.pt.w.User.OrgId,
		"project_id": ct.pt.w.User.ProjectId,
		"cluster_id": ct.pt.w.Cluster.ClusterId,
	}
	// drop  table
	ct.pt.Reporter.StepReport("dropTableOperation",
		func() {
			tableName := "warehouse"
			sql := fmt.Sprintf("drop table %s", tableName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			dropTableResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("dropTable, path param is %s, body is"+
				" %s, status code is %d, response data is : %v", pathparam, body, dropTableResp.Raw().StatusCode, dropTableResp.JSON().Path("$.data.query").Raw()),
				func() {
					dropTableResp.Status(http.StatusOK)
					dropTableResp.JSON().Path("$.data.execute_time").String().NotEqual("")
					expect := common.GenQueryOKExpect(0)
					dropTableResp.JSON().Path("$.data.query").String().Contains(expect)
				})
		})

	// drop database
	ct.pt.Reporter.StepReport("dropDbOperation",
		func() {
			dbName := fmt.Sprintf("autoTest%s", ct.pt.w.Cluster.ClusterId)
			sql := fmt.Sprintf("drop database %s", dbName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			dropDbResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("dropDb, path param is %s, body is"+
				" %s, status code is %d , response data is : %v", pathparam, body, dropDbResp.Raw().StatusCode, dropDbResp.JSON().Path("$.data.query").Raw()),
				func() {
					dropDbResp.Status(http.StatusOK)
					dropDbResp.JSON().Path("$.data.execute_time").String().NotEqual("")
					expect := common.GenQueryOKExpect(0)
					dropDbResp.JSON().Path("$.data.query").String().Contains(expect)
				})
		})
}

func (ct *Chat2QueryTest) CheckPrivilege() {
	pathparam := map[string]string{
		"org_id":     ct.pt.w.User.OrgId,
		"project_id": ct.pt.w.User.ProjectId,
		"cluster_id": ct.pt.w.Cluster.ClusterId,
	}
	// allowed privilege has been check in basic check, so here just check prohibition
	// mysql db
	dbName := "mysql"
	ct.pt.Reporter.StepReport("dropMySqlOperation",
		func() {
			sql := fmt.Sprintf("drop database %s;", dbName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			dropMySqlResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("dropMySql, path param is %s, body is"+
				" %s, status code is %d,response data is : %v", pathparam, body, dropMySqlResp.Raw().StatusCode, dropMySqlResp.JSON().Path("$.message").Raw()),
				func() {
					dropMySqlResp.Status(http.StatusOK)
					dropMySqlResp.JSON().Object().HasValue("code", 60100005)
					dropMySqlResp.JSON().Object().Path("$.message").String().Contains("Access denied for user")
				})
		})

	tableName := "advisory_locks"
	ct.pt.Reporter.StepReport("dropMySqlTableOperation",
		func() {
			sql := fmt.Sprintf("drop table %s.%s;", dbName, tableName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			dropMySqlTableResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("dropMySqlTable, path param is %s, body is"+
				" %s, status code is %d, response data is : %v", pathparam, body, dropMySqlTableResp.Raw().StatusCode, dropMySqlTableResp.JSON().Path("$.message").Raw()),
				func() {
					dropMySqlTableResp.Status(http.StatusOK)
					dropMySqlTableResp.JSON().Object().HasValue("code", 60100005)
					dropMySqlTableResp.JSON().Object().Path("$.message").String().Contains("DROP command denied to user")
				})
		})

	// INFORMATION_SCHEMA db
	dbName = "INFORMATION_SCHEMA"
	ct.pt.Reporter.StepReport("dropInfoSchemaOperation",
		func() {
			sql := fmt.Sprintf("drop database %s;", dbName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			dropInfoSchemaResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("dropInfoSchema, path param is %s, body is"+
				" %s, status code is %d,response data is : %v", pathparam, body, dropInfoSchemaResp.Raw().StatusCode, dropInfoSchemaResp.JSON().Path("$.message").Raw()),
				func() {
					dropInfoSchemaResp.Status(http.StatusOK)
					dropInfoSchemaResp.JSON().Object().HasValue("code", 60100005)
					dropInfoSchemaResp.JSON().Object().Path("$.message").String().Contains("Access denied for user")
				})
		})
	ct.pt.Reporter.StepReport("dropATTRIBUTESTableOperation",
		func() {
			sql := fmt.Sprintf("drop table %s.%s;", dbName, tableName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			dropATTRIBUTESTableResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("dropATTRIBUTESTable, path param is %s, body is"+
				" %s, status code is %d , response data is : %v", pathparam, body, dropATTRIBUTESTableResp.Raw().StatusCode, dropATTRIBUTESTableResp.JSON().Path("$.message").Raw()),
				func() {
					dropATTRIBUTESTableResp.Status(http.StatusOK)
					dropATTRIBUTESTableResp.JSON().Object().HasValue("code", 60100005)
					dropATTRIBUTESTableResp.JSON().Object().Path("$.message").String().Contains("DROP command denied to user")
				})
		})

	// PERFORMANCE_SCHEMA db
	dbName = "PERFORMANCE_SCHEMA"
	ct.pt.Reporter.StepReport("dropPerfSchemaOperation",
		func() {
			sql := fmt.Sprintf("drop database %s;", dbName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			dropPerfSchemaResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("dropPerfSchema, path param is %s, body is"+
				" %s, status code is %d,response data is : %v", pathparam, body, dropPerfSchemaResp.Raw().StatusCode, dropPerfSchemaResp.JSON().Path("$.message").Raw()),
				func() {
					dropPerfSchemaResp.Status(http.StatusOK)
					dropPerfSchemaResp.JSON().Object().HasValue("code", 60100005)
					dropPerfSchemaResp.JSON().Object().Path("$.message").String().Contains("Access denied for user")
				})
		})
	ct.pt.Reporter.StepReport("dropATTRIBUTESTableOperation",
		func() {
			tableName = "events_stages_current"
			sql := fmt.Sprintf("drop table %s.%s;", dbName, tableName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			dropEventsStagesCurrentTableResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("dropEventsStagesCurrentTable, path param is %s, body is"+
				" %s, status code is %d , response data is : %v", pathparam, body, dropEventsStagesCurrentTableResp.Raw().StatusCode, dropEventsStagesCurrentTableResp.JSON().Path("$.message").Raw()),
				func() {
					dropEventsStagesCurrentTableResp.Status(http.StatusOK)
					dropEventsStagesCurrentTableResp.JSON().Object().HasValue("code", 60100005)
					dropEventsStagesCurrentTableResp.JSON().Object().Path("$.message").String().Contains("DROP command denied to user")
				})
		})
}

func (ct *Chat2QueryTest) CheckIndexOperation() {
	pathparam := map[string]string{
		"org_id":     ct.pt.w.User.OrgId,
		"project_id": ct.pt.w.User.ProjectId,
		"cluster_id": ct.pt.w.Cluster.ClusterId,
	}

	indexName := "indexTest"
	TableName := "district"
	colName := "d_id"
	dbName := fmt.Sprintf("autoTest%s", ct.pt.w.Cluster.ClusterId)

	// add index
	ct.pt.Reporter.StepReport("AddIndexOperation",
		func() {
			sql := fmt.Sprintf("create index %s on %s.%s(%s);", indexName, dbName, TableName, colName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			addIndexResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("addIndex, path param is %s, body is"+
				" %s, status code is %d, response data is : %v", pathparam, body, addIndexResp.Raw().StatusCode, addIndexResp.JSON().Path("$.data.query").Raw()),
				func() {
					addIndexResp.Status(http.StatusOK)
					addIndexResp.JSON().Object().HasValue("code", 200)
					addIndexResp.JSON().Path("$.data.query").String().Contains("Query OK")
				})
		})

	// drop index
	ct.pt.Reporter.StepReport("DropIndexOperation",
		func() {
			sql := fmt.Sprintf("drop index %s on %s.%s;", indexName, dbName, TableName)
			body := map[string]string{"sql": sql, "sessionid": ct.SessionId}
			dropIndexResp := ct.pt.w.DoWebAPIRequestByUrlInfo(ct.pt.URLInfoMap["executeSql"], body, pathparam, nil)
			ct.pt.Reporter.StepReport(fmt.Sprintf("dropIndex, path param is %s, body is"+
				" %s, status code is %d, response data is : %v", pathparam, body, dropIndexResp.Raw().StatusCode, dropIndexResp.JSON().Path("$.data.query").Raw()),
				func() {
					dropIndexResp.Status(http.StatusOK)
					dropIndexResp.JSON().Object().HasValue("code", 200)
					dropIndexResp.JSON().Path("$.data.query").String().Contains("Query OK")
				})
		})
}