package checker

import (
	"database/sql"
	"fmt"
	"testing"

	_ "github.com/go-sql-driver/mysql"
	"github.com/pingcap/tispector/config"
	"github.com/pingcap/tispector/service/db_info"
	"github.com/pingcap/tispector/service/task"
	"github.com/pingcap/tispector/util/logger"
	"github.com/stretchr/testify/assert"
)

const (
	configDir = "pingcap/tispector/config/"
	dbAddr    = "127.0.0.1:3306"
	dbuser    = "root"
	dbpasswd  = "root"
)

func TestHeuristicRuleAll(t *testing.T) {
	// test part 01
	TestRule_RuleCreateTableCharset(t)
	TestRule_RuleCreateTableComment(t)
	TestRule_RuleCreateTableIndex(t)
	TestRule_RuleColCommentCheck(t)
	TestRule_RuleCreateTableUniqIndex(t)
	TestRule_RuleCreateTableNormalIndex(t)
	TestRule_RuleCreateTableIndexColNum(t)
	TestRule_RuleCreateTableIndexNum(t)
	TestRule_RuleCreateTableDupIndex(t)
	TestRule_RuleCreateTableNotNullValue(t)
	TestRule_RuleCreateTableSetColCharset(t)
	TestRule_RuleCreateTableCoIndexOrder(t)
	TestRule_RuleCreateTableCoIndexEx(t)
	TestRule_RuleCreateTableIndexLen(t)
	TestRule_RuleCreateTableTextColNum(t)
	TestRule_RuleCreateTableNotUseKeyWorld(t)
	TestRule_RuleNotUseIntAsPrimaryKey(t)
	TestRule_RuleVarcharLengthLimit(t)
	TestRule_RuleAutoIncrementLimit(t)
	TestRule_RuleAlterTableDropColumn(t)
	TestRule_RuleAlterTableDrop(t)
	TestRule_RuleAffectRows(t)
	TestRule_RuleBanAddMulti(t)
	TestRule_RuleUnsupportedType(t)
	TestRule_RuleDMLTableNoWhere(t)
	// test part 02
	TestRule_RuleAddCharCol(t)
	TestRule_RuleAlterViewJoin(t)
	TestRule_RuleAlterViewSubquery(t)
	TestRule_RuleAlterViewUpdate(t)
	TestRule_RuleColUseText(t)
	TestRule_RuleColUseUsignInt(t)
	TestRule_RuleDDLUseCharset(t)
	TestRule_RuleDdlIdxNoname(t)
	TestRule_RuleDmlSQLColCompute(t)
	TestRule_RuleDmlSeleNoWhere(t)
	TestRule_RuleDmlUseOrderBy(t)
	TestRule_RuleSQLCountCol(t)
	TestRule_RuleSQLMultiAutoIncr(t)
	TestRule_RuleSQLUseBlob(t)
	TestRule_RuleVarcharSmall(t)
	// test part 03
	TestRule_RuleDmlLimitOversized(t)
	TestRule_RuleCreateTableWithOutDate(t)
	TestRule_RuleCreateTooManyColumn(t)
	TestRule_RuleDmlHavingClause(t)
	TestRule_RuleDmlProhibitCrossJoin(t)
	TestRule_RuleDmlOrderByFieldsExceedsLimit(t)
	TestRule_RuleDmlSubqueriesWithNotInOrNotLike(t)
	TestRule_RuleDmlWithUnion(t)
	TestRule_RuleDmlSubqueriesInWhereClause(t)
	TestRule_RuleDmlSubqueriesInFromClause(t)
	TestRule_RuleDmlDeletDataFromMultipleTables(t)
	TestRule_RuleDmlUpdateFromMultipleTables(t)
	TestRule_RuleCreateTableNotUseForeignKey(t)
	TestRule_RuleCreateTableUsePartitioning(t)
	TestRule_RuleCreateSequenceNoCache(t)
	TestRule_RuleAlterUseForeignKey(t)
	TestRule_RulePlnIdxRangeSan(t)
	TestRule_RulePlnIdxFullSan(t)
	TestRule_RulePushdownNotSupportedPredicates(t)
	TestRule_RulePushdownToJoinOperator(t)
	TestRule_RulePushdownWithUserVaribales(t)

}

// TestTOOLS

func getDbInfo() *task.DBInfo {

	cluster := db_info.TispectorCluster{
		ID:          0,
		Name:        "tispector",
		Description: "1",
		Addr:        dbAddr,
		User:        dbuser,
		Pwd:         dbpasswd,
		Ct:          0,
		Ut:          0,
		Operator:    "1",
	}
	var dbName string = "tispector"
	defaultDBName := dbName
	db, err := sql.Open("mysql",
		fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8", cluster.User, cluster.Pwd, cluster.Addr, dbName))
	if err != nil {
		fmt.Printf("open db conn err: %s", err.Error())
	}

	cluster2 := db_info.TispectorCluster{
		ID:          0,
		Name:        "tispector",
		Description: "1",
		Addr:        dbAddr,
		User:        dbuser,
		Pwd:         dbpasswd,
		Ct:          0,
		Ut:          0,
		Operator:    "1",
	}
	defaultDB, err := sql.Open("mysql",
		fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8", cluster2.User, cluster2.Pwd, cluster2.Addr, defaultDBName))
	if err != nil {
		fmt.Printf("open db conn err: %s", err.Error())
	}
	dbInfo := task.DBInfo{DB: db, DefaultDB: defaultDB, DBName: dbName}
	return &dbInfo
}

func getAudit(sql string) *Audit {
	audit, _ := NewAudit(sql, "", "")
	return audit

}

func getRule(ruleName string) *Rule {
	RuleMap, err := config.GetRule(configDir + "rules.yml")
	config.InitConfig(configDir + "config.yml")
	logger.InitLog(config.Conf.Server.LogDir, "tispector.log", config.Conf.Server.LogLevel)
	// RuleMap = &rulemap
	if err != nil {
		logger.Fatal("GetRule failed ; error: %v", err)
	}
	rule := RuleMap[ruleName]
	RuleFunc := makeFuncMap()
	var rulee Rule
	_, ok := RuleFunc[rule.CheckFunc]
	if ok {
		rulee.CheckFunc = RuleFunc[rule.CheckFunc]
	}
	if !ok {
		logger.Errorf("规则 %s 未定义执行方法", rule.Item)
		rule.Close = 0
	}

	rulee.Item = rule.Item
	rulee.Name = rule.Name
	rulee.Summary = rule.Summary
	rulee.Content = rule.Content
	if rule.Close == 1 {
		rulee.Close = false
	} else if rule.Close == 0 {
		rulee.Close = true
	} else {
		rulee.Close = true
		logger.Fatal(" The parameter  %s  setting is wrong", rule.Item)
	}
	rulee.Level = rule.Level
	rulee.Case = rule.Case
	rulee.Position = rule.Position

	rulee.Threshold = rule.Threshold
	return &rulee
}

// rule test part01
func TestRule_RuleCreateTableCharset(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate001")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableCharset(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableCharset failed")
}

func TestRule_RuleCreateTableComment(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate002")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableComment(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableComment failed")
}

func TestRule_RuleCreateTableIndex(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate003")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableIndex(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableIndex failed")
}

func TestRule_RuleColCommentCheck(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate004")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleColCommentCheck(audit, info)
	asst.Equal(false, pass, "TestRule_RuleColCommentCheck failed")
}

func TestRule_RuleCreateTableUniqIndex(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate005")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableUniqIndex(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableUniqIndex failed")
}

func TestRule_RuleCreateTableNormalIndex(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate006")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableNormalIndex(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableNormalIndex failed")
}

func TestRule_RuleCreateTableIndexColNum(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate007")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableIndexColNum(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableIndexColNum failed")
}

func TestRule_RuleCreateTableIndexNum(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate008")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableIndexNum(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableIndexNum failed")
}

func TestRule_RuleCreateTableDupIndex(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate009")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableDupIndex(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableDupIndex failed")
}

func TestRule_RuleCreateTableNotNullValue(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate010")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableNotNullValue(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableNotNullValue failed")
}

func TestRule_RuleCreateTableSetColCharset(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate011")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableSetColCharset(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableSetColCharset failed")
}

func TestRule_RuleCreateTableCoIndexOrder(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate012")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableCoIndexOrder(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableCoIndexOrder failed")
}

func TestRule_RuleCreateTableCoIndexEx(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate013")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableCoIndexEx(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableCoIndexEx failed")
}

func TestRule_RuleCreateTableIndexLen(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate014")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableIndexLen(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableIndexLen failed")
}

func TestRule_RuleCreateTableTextColNum(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate015")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableTextColNum(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableTextColNum failed")
}

func TestRule_RuleCreateTableNotUseKeyWorld(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate016")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableNotUseKeyWorld(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableNotUseKeyWorld failed")
}

func TestRule_RuleNotUseIntAsPrimaryKey(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate017")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleNotUseIntAsPrimaryKey(audit, info)
	asst.Equal(false, pass, "TestRule_RuleNotUseIntAsPrimaryKey failed")
}

func TestRule_RuleVarcharLengthLimit(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate018")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleVarcharLengthLimit(audit, info)
	asst.Equal(false, pass, "TestRule_RuleVarcharLengthLimit failed")
}

func TestRule_RuleAutoIncrementLimit(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate019")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleAutoIncrementLimit(audit, info)
	asst.Equal(false, pass, "TestRule_RuleAutoIncrementLimit failed")
}

func TestRule_RuleAlterTableDropColumn(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleAlter001")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleAlterTableDropColumn(audit, info)
	asst.Equal(false, pass, "TestRule_RuleAlterTableDropColumn failed")
}

func TestRule_RuleAlterTableDrop(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleAlter002")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleAlterTableDrop(audit, info)
	asst.Equal(false, pass, "TestRule_RuleAlterTableDrop failed")
}

func TestRule_RuleAffectRows(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleAlter003")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleAffectRows(audit, info)
	asst.Equal(false, pass, "TestRule_RuleAffectRows failed")
}

func TestRule_RuleBanAddMulti(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleAlter004")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleBanAddMulti(audit, info)
	asst.Equal(false, pass, "TestRule_RuleBanAddMulti failed")
}

func TestRule_RuleUnsupportedType(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleAlter005")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleUnsupportedType(audit, info)
	asst.Equal(false, pass, "TestRule_RuleUnsupportedType failed")
}

func TestRule_RuleDMLTableNoWhere(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml001")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDMLTableNoWhere(audit, info)
	asst.Equal(false, pass, "TestRule_RuleDMLTableNoWhere failed")
}

func RuleMeaninglessWhere(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml002")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDMLTableNoWhere(audit, info)
	asst.Equal(false, pass, "RuleMeaninglessWhere failed")
}

func RuleMultiDeleteUpdate(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml003")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDMLTableNoWhere(audit, info)
	asst.Equal(false, pass, "RuleMultiDeleteUpdate failed")
}

func RuleInsertColDef(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml004")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDMLTableNoWhere(audit, info)
	asst.Equal(false, pass, "RuleInsertColDef failed")
}

func RuleInsertColValueEqual(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml005")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDMLTableNoWhere(audit, info)
	asst.Equal(false, pass, "RuleInsertColValueEqual failed")
}

func RuleAlterTableExist(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml006")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDMLTableNoWhere(audit, info)
	asst.Equal(false, pass, "RuleAlterTableExist failed")
}

func RuleAlterTableColumnExist(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml007")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDMLTableNoWhere(audit, info)
	asst.Equal(false, pass, "RuleAlterTableColumnExist failed")
}

func RuleDmlIndexMatch(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml008")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDMLTableNoWhere(audit, info)
	asst.Equal(false, pass, "RuleDmlIndexMatch failed")
}

func RuleDmlNoMoreThan100(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml009")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDMLTableNoWhere(audit, info)
	asst.Equal(false, pass, "RuleDmlNoMoreThan100 failed")
}

// rule test part02
func TestRule_RuleColUseText(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate029")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleColUseText(audit, info)
	asst.Equal(false, pass, "TestRule_RuleColUseText failed")
}

func TestRule_RuleAddCharCol(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleAlter010")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleAddCharCol(audit, info)
	asst.Equal(false, pass, "TestRule_RuleAddCharCol failed")
}

func TestRule_RuleColUseUsignInt(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate030")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleColUseUsignInt(audit, info)
	asst.Equal(false, pass, "TestRule_RuleColUseUsignInt failed")
}

func TestRule_RuleSQLMultiAutoIncr(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate031")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleSQLMultiAutoIncr(audit, info)
	asst.Equal(false, pass, "TestRule_RuleSQLMultiAutoIncr failed")
}

func TestRule_RuleSQLUseBlob(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleAlter012")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleSQLUseBlob(audit, info)
	asst.Equal(false, pass, "TestRule_RuleSQLUseBlob failed")
}

func TestRule_RuleDdlIdxNoname(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleAlter013")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDdlIdxNoname(audit, info)
	asst.Equal(false, pass, "TestRule_RuleDdlIdxNoname failed")
}

func TestRule_RuleVarcharSmall(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleAlter014")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleVarcharSmall(audit, info)
	asst.Equal(false, pass, "TestRule_RuleVarcharSmall failed")
}

func TestRule_RuleDDLUseCharset(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleAlter016")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDDLUseCharset(audit, info)
	asst.Equal(false, pass, "TestRule_RuleDDLUseCharset failed")
}

func TestRule_RuleDmlSQLColCompute(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml029")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDmlSQLColCompute(audit, info)
	asst.Equal(false, pass, "TestRule_RuleDmlSQLColCompute failed")
}

func TestRule_RuleDmlSeleNoWhere(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml030")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDmlSeleNoWhere(audit, info)
	asst.Equal(false, pass, "TestRule_RuleDmlSeleNoWhere failed")
}

func TestRule_RuleDmlUseOrderBy(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml031")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleDmlUseOrderBy(audit, info)
	asst.Equal(false, pass, "TestRule_RuleDmlUseOrderBy failed")
}

func TestRule_RuleSQLCountCol(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml033")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleSQLCountCol(audit, info)
	asst.Equal(false, pass, "TestRule_RuleSQLCountCol failed")
}

func TestRule_RuleAlterViewUpdate(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleAlter021")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleAlterViewUpdate(audit, info)
	asst.Equal(false, pass, "TestRule_RuleAlterViewUpdate failed")
}

func TestRule_RuleAlterViewSubquery(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleAlter022")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleAlterViewSubquery(audit, info)
	asst.Equal(false, pass, "TestRule_RuleAlterViewSubquery failed")
}

func TestRule_RuleAlterViewJoin(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleAlter023")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleAlterViewJoin(audit, info)
	asst.Equal(false, pass, "TestRule_RuleAlterViewJoin failed")
}

///////////////////////////////////////////////////////////

func TestRule_RuleSQLFormatQuery(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleDml037")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleSQLFomatQuery(audit, info)
	asst.Equal(false, pass, "TestRule_RuleSQLFormatQuery failed")
}

func TestRule_RuleCreateTablePK(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate321")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleSQLFomatQuery(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTablePK failed")
}

func TestRule_RuleCreateTableFK(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate322")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleSQLFomatQuery(audit, info)
	asst.Equal(false, pass, "TestRule_RuleCreateTableFK failed")
}

func TestRule_RuleCreateTableUseEnum(t *testing.T) {
	asst := assert.New(t)

	q := getRule("RuleCreate034")
	info := getDbInfo()
	audit := getAudit(q.Case)
	pass, _, _ := q.RuleCreateTableUseEnum(audit, info)
	asst.Equal(false, pass, "Test_RuleCreateTableUseEnum failed")
}

// rule test by part 03

func TestRule_RuleDmlLimitOversized(t *testing.T) {

	sql := "select ur.username from user_role ur ,(select u.userid from user u where u.stats =1 limit 2000 ) ul where ur.userid = ul.userid limit  10 "
	//items := []string{"DML.010"}
	q := getRule("RuleDml010")
	asst := assert.New(t)
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleDmlLimitOversized(audit, info)
	asst.Equal(false, pass, "Test_RuleDmlLimitOversized failed")
}

func TestRule_RuleCreateTableWithOutDate(t *testing.T) {

	sql := "\nCREATE TABLE IF NOT EXISTS `store` (\n    `store_id` varchar(20) NOT NULL,\n  `type` tinyint(3) unsigned NOT NULL COMMENT '店铺类型',\n     `create_user_id` bigint(20) unsigned NOT NULL,\n      PRIMARY KEY (`store_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='店铺信息';"
	//items := []string{"Create.020"}
	asst := assert.New(t)
	q := getRule("Create020")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleCreateTableWithOutDate(audit, info)
	asst.Equal(false, pass, "Test_RuleCreateTableWithOutDate failed")
}

func TestRule_RuleCreateTooManyColumn(t *testing.T) {

	sql := "\nCREATE TABLE IF NOT EXISTS `store` (\n    `store_id` varchar(20) NOT NULL,\n  `type` tinyint(3) unsigned NOT NULL COMMENT '店铺类型',\n     `create_user_id` bigint(20) unsigned NOT NULL,\n      PRIMARY KEY (`store_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='店铺信息';"
	//items := []string{"Create.021"}
	asst := assert.New(t)
	q := getRule("Create021")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleCreateTooManyColumn(audit, info)
	asst.Equal(false, pass, "Test_RuleCreateTooManyColumn failed")

}

func TestRule_RuleDmlHavingClause(t *testing.T) {

	sql := "select\n    ur.user_id,\n    (select count(a.id) from a where a.status = 1),\n    ur.state,\n    (select count(b.id) from\n                          b,\n                          (select * from c left join e on c.id = e.id where e.name is null and c.status = 2 having c.id >2 ) ce\n    where\n          b.tx in (select d.tx from d where d.status =3 )\n      and b.id = ce.id\n      and b.status = 4)\nfrom user_role ur,\n    (select * from f where f.status = 8 and f.tx in (select d.tx from d where d.status =7 )) f\nwhere ur.role_id = '1'\n  and exists(select 1 from q left join w on q.a = w.a where w.id is null and q.tx in (select d.tx from d where d.status =9))"

	//items := []string{"DML.011"}
	asst := assert.New(t)
	q := getRule("RuleDml011")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleDmlHavingClause(audit, info)
	asst.Equal(false, pass, "Test_RuleDmlHavingClause failed")
}

func TestRule_RuleDmlProhibitCrossJoin(t *testing.T) {

	sql := "select * from user_role ur cross join user u "

	//items := []string{"DML.014"}
	asst := assert.New(t)
	q := getRule("RuleDml014")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleDmlProhibitCrossJoin(audit, info)
	asst.Equal(false, pass, "Test_RuleDmlProhibitCrossJoin failed")
}

func TestRule_RuleDmlOrderByFieldsExceedsLimit(t *testing.T) {

	sql := "select userid,roleid from user_role order by userid,roleid "

	//items := []string{"DML.015"}
	asst := assert.New(t)
	q := getRule("RuleDml015")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleDmlOrderByFieldsExceedsLimit(audit, info)
	asst.Equal(false, pass, "Test_RuleDmlOrderByFieldsExceedsLimit failed")
}

func TestRule_RuleDmlSubqueriesWithNotInOrNotLike(t *testing.T) {

	sql := "select\n    ur.user_id,\n    (select count(a.id) from a where a.status = 1),\n    ur.state,\n    (select count(b.id) from\n                          b,\n                          (select * from c left join e on c.id = e.id where e.name is null and c.status = 2 having c.id >2 ) ce\n    where\n          b.tx in (select d.tx from d where d.status =3 )\n      and b.id = ce.id\n      and b.status = 4)\nfrom user_role ur,\n    (select * from f where f.status = 8 and f.tx in (select ur.userid from user_role ur where ur.userid not in (1,2,3,4) and ur.username not like 'tidb%' )) f \nwhere ur.role_id = '1'\n  and exists(select 1 from q left join w on q.a = w.a where w.id is null and q.tx in (select d.tx from d where d.status =9)) "

	//items := []string{"DML.016"}
	asst := assert.New(t)
	q := getRule("RuleDml016")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleDmlSubqueriesWithNotInOrNotLike(audit, info)
	asst.Equal(false, pass, "Test_RuleDmlSubqueriesWithNotInOrNotLike failed")
}

func TestRule_RuleDmlWithUnion(t *testing.T) {

	sql := "select userid,roleid from user_role where userid not in (1,2,3,4) union select userid,roleid from user_role where userid not like '1%'"

	//items := []string{"DML.017"}
	asst := assert.New(t)
	q := getRule("RuleDml017")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleDmlWithUnion(audit, info)
	asst.Equal(false, pass, "Test_RuleDmlWithUnion failed")
}

func TestRule_RuleDmlSubqueriesInWhereClause(t *testing.T) {
	sql := "select\n    ur.user_id,\n    (select count(a.id) from a where a.status = 1),\n    ur.state,\n    (select count(b.id) from\n                          b,\n                          (select * from c left join e on c.id = e.id where e.name is null and c.status = 2 having c.id >2 ) ce\n    where\n          b.tx in (select d.tx from d where d.status =3 )\n      and b.id = ce.id\n      and b.status = 4)\nfrom user_role ur,\n    (select * from f where f.status = 8 and f.tx in (select ur.userid from user_role ur where ur.userid not in (1,2,3,4) and ur.username not like 'tidb%' )) f \nwhere ur.role_id = '1'\n  "

	//items := []string{"DML.018"}
	asst := assert.New(t)
	q := getRule("RuleDml018")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleDmlSubqueriesInWhereClause(audit, info)
	asst.Equal(false, pass, "Test_RuleDmlSubqueriesInWhereClause failed")
}

func TestRule_RuleDmlSubqueriesInFromClause(t *testing.T) {
	sql := "select\n    ur.user_id,\n    ur.state\nfrom\n     user_role ur\nwhere\n    ur.user_id in (select b.id from b, (select c.tx from c left join e on c.id = e.id ) ce where b.tx = ce.tx)"

	//items := []string{"DML.019"}
	asst := assert.New(t)
	q := getRule("RuleDml019")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleDmlSubqueriesInFromClause(audit, info)
	asst.Equal(false, pass, "Test_RuleDmlSubqueriesInFromClause failed")
}

func TestRule_RuleDmlDeletDataFromMultipleTables(t *testing.T) {
	sql := "DELETE a ,b ,c FROM table1 a LEFT JOIN table2 b ON a.id = b.aid LEFT JOIN table3 c ON a.id = c.aid WHERE a.id = 1"

	//items := []string{"DML.020"}
	asst := assert.New(t)
	q := getRule("RuleDml020")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleDmlDeletDataFromMultipleTables(audit, info)
	asst.Equal(false, pass, "Test_RuleDmlDeletDataFromMultipleTables failed")
}

func TestRule_RuleDmlUpdateFromMultipleTables(t *testing.T) {
	sql := "UPDATE student s , class c SET s.class_name='test00',c.stu_name='test00' WHERE s.class_id = c.id"

	//items := []string{"DML.021"}
	asst := assert.New(t)
	q := getRule("RuleDml021")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleDmlUpdateFromMultipleTables(audit, info)
	asst.Equal(false, pass, "Test_RuleDmlUpdateFromMultipleTables failed")
}

func TestRule_RuleCreateTableNotUseForeignKey(t *testing.T) {
	sql := "create table user_test\n(\n    id     int     not null     auto_increment,\n    rID     int     null,\n    primary key (ID),\n    -- 方式二：指定外键名称为(FK_Name)\n    constraint FK_Name foreign key (rID) references user(user_id) on delete cascade on update cascade\n)"

	//items := []string{"Create.022"}
	asst := assert.New(t)
	q := getRule("RuleCreate022")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleCreateTableNotUseForeignKey(audit, info)
	asst.Equal(false, pass, "Test_RuleCreateTableNotUseForeignKey failed")
}

func TestRule_RuleCreateTableUsePartitioning(t *testing.T) {
	sql := "CREATE TABLE employees (\n    id INT NOT NULL,\n    fname VARCHAR(30),\n    lname VARCHAR(30),\n    hired DATE NOT NULL DEFAULT '1970-01-01',\n    separated DATE DEFAULT '9999-12-31',\n    job_code INT,\n    store_id INT NOT NULL\n)\n\nPARTITION BY RANGE (job_code) (\n    PARTITION p0 VALUES LESS THAN (100),\n    PARTITION p1 VALUES LESS THAN (1000),\n    PARTITION p2 VALUES LESS THAN (10000)\n);"

	//items := []string{"Create.023"}
	asst := assert.New(t)
	q := getRule("RuleCreate023")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleCreateTableUsePartitioning(audit, info)
	asst.Equal(false, pass, "Test_RuleCreateTableUsePartitioning failed")
}

func TestRule_RuleCreateSequenceNoCache(t *testing.T) {
	sql := "CREATE SEQUENCE seq2 start 3 increment 2 minvalue 1 maxvalue 10 cache 3;"

	//items := []string{"Create.024"}
	asst := assert.New(t)
	q := getRule("RuleCreate024")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleCreateSequenceNoCache(audit, info)
	asst.Equal(false, pass, "Test_RuleCreateSequenceNoCache failed")
}

func TestRule_RuleAlterUseForeignKey(t *testing.T) {
	sql := "ALTER TABLE t1 ADD FOREIGN KEY (Id) REFERENCES t2(t_Id)"

	//items := []string{"Alter.006"}
	asst := assert.New(t)
	q := getRule("RuleAlter006")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RuleAlterUseForeignKey(audit, info)
	asst.Equal(false, pass, "Test_RuleAlterUseForeignKey failed")
}

func TestRule_RulePlnIdxRangeSan(t *testing.T) {

	sql := "select * from user u, user_role ur where  u.user_id = '1' and  ur.user_id = '2';"

	//items := []string{"PLN.001"}
	asst := assert.New(t)
	q := getRule("RulePln001")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RulePlnIdxRangeSan(audit, info)
	asst.Equal(false, pass, "Test_RulePlnIdxRangeSan failed")
}

func TestRule_RulePlnIdxFullSan(t *testing.T) {
	sql := "SELECT id, name, email, phone,taxrate FROM users WHERE id=1 ;"

	//items := []string{"PLN.002"}
	asst := assert.New(t)
	q := getRule("RulePln002")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RulePlnIdxFullSan(audit, info)
	asst.Equal(false, pass, "Test_RulePlnIdxFullSan failed")
}

func TestRule_RulePushdownNotSupportedPredicates(t *testing.T) {
	//sql := "select id from t where substring('123', a, 1) = '1';"
	sql := "select t.* from user_role t where t.user_id=JSON_TYPE(user_id)\n"

	//items := []string{"PUSHDOWN.001"}
	asst := assert.New(t)
	q := getRule("Pushdown001")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RulePushdownNotSupportedPredicates(audit, info)
	asst.Equal(false, pass, "Test_RulePushdownNotSupportedPredicates failed")
}

func TestRule_RulePushdownToJoinOperator(t *testing.T) {
	//sql := "select ur.role_id from user_role ur left join user_info ui on ur.user_id = ui.Id left join role r on ur.role_id = r.role_id  where r.role_name is null and ui.password is null and ur.state = 1"

	//sql := "select ur.role_id from user_role ur , (select s.id from t right join s on t.a = s.a where t.id is null) tmp where tmp.id = ur.user_id  and ur.state = 1"
	//sql := "select s.id from t right join s on t.a = s.a where t.id is null"

	//sql := "select ur.role_id from user_role ur where ur.userid in (select s.id from t right join s on t.a = s.a where t.id is null) and  exists (select 1 from q left join w on q.a = w.a where w.id is null ) and ur.status = (select c.id from c where c.id = 12)"

	//sql := "select ur.user_id,(select count(ur.role_id) from user_role ur right join user_info ui on ur.user_id = ui.Id  where ur.role_id is null),ur.state,(select count(ur.role_id) from user_role ur right join user_info ui on ur.user_id = ui.Id  where ur.role_id is null) from user_role ur where ur.role_id = '1';"

	//sql := "select\n    ur.user_id,\n    (select count(a.id) from a where a.status = 1),\n    ur.state,\n    (select count(b.id) from\n                          b,\n                          (select * from c left join e on c.id = e.id where e.name is null and c.status = 2) ce\n    where\n          b.tx in (select d.tx from d where d.status =3 )\n      and b.id = ce.id\n      and b.status = 4)\nfrom user_role ur,\n    (select * from f where f.status = 8 and f.tx in (select d.tx from d where d.status =7 )) f\nwhere ur.role_id = '1'\n  and exists(select 1 from q left join w on q.a = w.a where w.id is null and q.tx in (select d.tx from d where d.status =9))\n"

	//sql := "select id from view1 where id >ANY(select id from view1123)"

	//sql := "insert into projectfile (id, projectid) select projectfile.id ,projectfile2.projectid from projectfile ;"
	//sql := "Update Table1 set c = (select c from Table2 where a = Table1.a) where c is null and (workdate, cino) in (select workdate, cino from tab2)"
	//sql := "Update tab1 t set val1 = 1 where (workdate, cino) in (select workdate, cino from tab2) ;"
	//sql := "UPDATE test.tb_vobile a\njoin\n(select max(id) id from test.tb_vobile) b\non a.id = b.id\nset a.name = '123 ';"
	//sql := "UPDATE test.tb_vobile a ,(select max(id) id from test.tb_vobile) b\nset a.name = '321 '\nWHERE\na.id = b.id ;"
	sql := "delete from  t_book  where fcategoryid in\n(\nselect fcategoryid from t_book t group by t.fcategoryid having count(*)>3\n)"

	//items := []string{"PUSHDOWN.002"}
	asst := assert.New(t)
	q := getRule("Pushdown002")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RulePushdownToJoinOperator(audit, info)
	asst.Equal(false, pass, "Test_RulePushdownToJoinOperator failed")
}

func TestRule_RulePushdownWithUserVaribales(t *testing.T) {
	//sql := "select id  from t where a = @a;"
	sql := "select t.* from user_role t where t.user_id=JSON_TYPE(user_id)\n"

	//items := []string{"PUSHDOWN.003"}
	asst := assert.New(t)
	q := getRule("Pushdown003")
	info := getDbInfo()
	audit := getAudit(sql)
	pass, _, _ := q.RulePushdownWithUserVaribales(audit, info)
	asst.Equal(false, pass, "Test_RulePushdownWithUserVaribales failed")
}