package checker

import (
	"fmt"
	"regexp"
	"strings"

	"github.com/pingcap/parser/ast"
	"github.com/pingcap/parser/mysql"
	"github.com/romberli/go-util/middleware/sql/parser"
	"vitess.io/vitess/go/vt/sqlparser"

	"github.com/pingcap/tispector/service/sql_util"
	"github.com/pingcap/tispector/service/task"
	"github.com/pingcap/tispector/util/logger"
)

// RuleColUseText Create.029
func (q *Rule) RuleColUseText(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	maxTextNum := q.GetThresholdInt()
	var textnum int
	sqlInfo, err := GetSQLInfo(audit.Query)
	if err == nil {

		for _, columnType := range sqlInfo.ColumnTypes {
			if columnType == "text" {
				textnum = textnum + 1
			}
		}
	}

	if textnum > maxTextNum {
		return false, q.Summary, textnum
	}
	return true, q.Summary, 0
}

// RuleAddCharCol Alter.010
func (q *Rule) RuleAddCharCol(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	for _, tiStmt := range audit.TiStmt {
		switch node := tiStmt.(type) {
		case *ast.AlterTableStmt:
			if len(node.Specs) < 1 {
				return true, q.Summary, 0
			}
			for _, n := range node.Specs {
				if n.Tp == ast.AlterTableAddColumns && n.NewColumns[0].Tp.Tp == mysql.TypeString {
					return false, q.Summary, 0
				}
			}
		}
	}
	return true, q.Summary, 0
}

// RuleColUseUsignInt Create.030
func (q *Rule) RuleColUseUsignInt(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	sqlInfo, err := GetSQLInfo(audit.Query)
	if err == nil {
		for _, columnType := range sqlInfo.ColumnTypes {
			match, _ := regexp.MatchString("bigint.* unsigned", columnType)
			if match {
				return false, q.Summary, 0
			}
		}
	}
	return true, q.Summary, 0
}

// RuleSQLMultiAutoIncr Create.031
func (q *Rule) RuleSQLMultiAutoIncr(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	colnum := 0
	switch s := audit.Stmt.(type) {
	case *sqlparser.DDL:
		if strings.ToLower(s.Action) == "create" {
			if s.TableSpec == nil {
				return true, q.Summary, 0
			}
			for _, col := range s.TableSpec.Columns {
				switch strings.ToLower(col.Type.Type) {
				case "int", "bigint", "integer":
					if col.Type.Autoincrement {
						colnum++
					}
				}
			}
		}
	}
	if colnum > 1 {
		return false, q.Summary, 0
	}
	return true, q.Summary, 0
}

// RuleSQLUseBlob Alter.012
func (q *Rule) RuleSQLUseBlob(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	sqlInfo, err := GetSQLInfo(audit.Query)
	if err == nil {
		for _, columnType := range sqlInfo.ColumnTypes {
			if columnType == "text" {
				return false, q.Summary, 0
			}
			if columnType == "blob" {
				return false, q.Summary, 0
			}
			if columnType == "json" {
				return false, q.Summary, 0
			}
		}
	}
	return true, q.Summary, 0
}

// RuleDdlIdxNoname Alter.013
func (q *Rule) RuleDdlIdxNoname(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {

	switch audit.Stmt.(type) {
	case *sqlparser.DDL:
		for _, tiStmt := range audit.TiStmt {
			switch node := tiStmt.(type) {
			case *ast.CreateTableStmt:
				for _, v := range node.Constraints {
					switch v.Tp {
					case ast.ConstraintIndex, ast.ConstraintKey:
						if v.Name == "" {
							return false, q.Summary, 0
						}
					}
				}
			case *ast.AlterTableStmt:
				for _, v := range node.Specs {
					switch v.Tp {
					case ast.AlterTableAddConstraint:
						switch v.Constraint.Tp {
						case ast.ConstraintIndex, ast.ConstraintKey:
							if v.Name == "" {
								return false, q.Summary, 0
							}
						}
					}
				}
			}
		}
	}
	return true, q.Summary, 0
}

// RuleVarcharSmall Alter.014
func (q *Rule) RuleVarcharSmall(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	var minVarchar = q.GetThresholdInt()
	for _, tiStmt := range audit.TiStmt {
		switch node := tiStmt.(type) {
		case *ast.AlterTableStmt:
			if len(node.Specs) < 1 {
				return true, q.Summary, 0
			}
			for _, n := range node.Specs {
				if n.Tp == ast.AlterTableAddColumns && n.NewColumns[0].Tp.Tp == mysql.TypeVarchar && n.NewColumns[0].Tp.Flen < minVarchar {
					return false, q.Summary, 0
				}
			}

		}
	}
	return true, q.Summary, 0
}

// RuleDDLUseCharset Alter.016
func (q *Rule) RuleDDLUseCharset(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	for _, tiStmt := range audit.TiStmt {
		switch tiStmt.(type) {
		case *ast.AlterTableStmt:
			results := reGet(audit.Query, "defaultcharacterset")
			if results != nil {
				return false, q.Summary, 0
			}
			results = reGet(audit.Query, "converttocharacterset")
			if results != nil {
				return false, q.Summary, 0
			}
			results = reGet(audit.Query, "characterset")
			if results != nil {
				return false, q.Summary, 0
			}
		}
	}
	return true, q.Summary, 0
}

// RuleDmlSQLColCompute Dml.029 --OK--
func (q *Rule) RuleDmlSQLColCompute(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	pass = true
	sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) {
		switch n := node.(type) {
		case *sqlparser.ComparisonExpr:
			switch n.Left.(type) {
			case *sqlparser.SQLVal, *sqlparser.ColName:
			default:
				pass = false
				return false, nil
			}
		}
		return true, nil
	}, audit.Stmt)

	for _, tiStmt := range audit.TiStmt {
		switch tiStmt.(type) {
		case *ast.SelectStmt, *ast.UpdateStmt, *ast.DeleteStmt:
			json, err := sql_util.StmtNode2JSON(audit.TiStmt)
			if err != nil {
				logger.Error(err)
				return true, "", 0
			}
			whereJSON := sql_util.JSONFind(json, "Where")
			for _, where := range whereJSON {
				if len(sql_util.JSONFind(where, "FnName")) > 0 {
					pass = false
				}
				break
			}
		}
	}

	return pass, q.Summary, 0
}

// switchType 帮助sqlparser.where类型的node判断是否存在运算函数
func switchType(node sqlparser.SQLNode) (pass bool) {

	switch (node).(type) {
	case *sqlparser.ComparisonExpr:
		if node.(*sqlparser.ComparisonExpr).Operator == "!=" ||
			node.(*sqlparser.ComparisonExpr).Operator == ">" ||
			node.(*sqlparser.ComparisonExpr).Operator == "<" ||
			node.(*sqlparser.ComparisonExpr).Operator == "in" {

			return false
		}
	case *sqlparser.IsExpr:
		if node.(*sqlparser.IsExpr).Operator == "is null" {

			return false
		}
	case *sqlparser.OrExpr:

		return false
	}
	return true
}

// RuleDmlSeleNoWhere Dml.030
func (q *Rule) RuleDmlSeleNoWhere(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	pass = true
	sqlparser.Walk(func(node sqlparser.SQLNode) (continueWalk bool, err error) {
		switch n := node.(type) {
		case *sqlparser.Select:
			if n.Where == nil {
				for _, from := range n.From {
					switch from.(type) {
					case *sqlparser.JoinTableExpr:
						pass = true
						return true, nil
					default:
						pass = false
						return false, nil
					}

				}

			}
		}
		return true, nil
	}, audit.Stmt)
	return pass, q.Summary, 0
}

// RuleDmlUseOrderBy Dml.031
func (q *Rule) RuleDmlUseOrderBy(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	pass = true
	sqlparser.Walk(func(node sqlparser.SQLNode) (continueWalk bool, err error) {
		switch n := node.(type) {
		case *sqlparser.Delete:
			if n.OrderBy != nil {
				pass = false
				return false, nil
			}

		case *sqlparser.Update:
			if n.OrderBy != nil {
				pass = false
				return false, nil
			}
		}
		return true, nil
	}, audit.Stmt)
	return pass, q.Summary, 0
}

// RuleSQLCountCol Dml.033
func (q *Rule) RuleSQLCountCol(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	results := reGet(audit.Query, "count\\([^)]+\\)")
	for _, result := range results {
		for _, res := range result {
			if res != "count(1)" && res != "count(*)" {
				return false, q.Summary, 0
			}
		}
	}
	return true, q.Summary, 0
}

// RuleDdlTabMulJoin Alter.020
func (q *Rule) RuleDdlTabMulJoin(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	pass = true
	var tables []string
	switch audit.Stmt.(type) {
	case *sqlparser.Union:
		pass = true
	default:
		sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) {
			switch n := node.(type) {
			case *sqlparser.AliasedTableExpr:
				switch table := n.Expr.(type) {
				case sqlparser.TableName:
					for _, t := range tables {
						if t == table.Name.String() {
							pass = false
							return false, nil
						}
					}
					tables = append(tables, table.Name.String())
				}
			}
			return true, nil
		}, audit.Stmt)

	}
	return pass, q.Summary, 0
}

// RuleAlterViewUpdate Alter.021
func (q *Rule) RuleAlterViewUpdate(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	// pass = true

	for _, tiStmt := range audit.TiStmt {
		switch tiStmt.(type) {
		case *ast.InsertStmt, *ast.DeleteStmt, *ast.UpdateStmt, *ast.TruncateTableStmt:
			sqlInfo, err := GetSQLInfo(audit.Query)
			if err != nil {
				return true, "", 0
			}
			for _, table := range sqlInfo.TableNames {
				exist, err := viewExist(table, info)
				if err == nil && exist {
					return false, q.Summary, 0
				}
			}
		}
	}
	return true, q.Summary, 0

}

// RuleAlterViewSubquery Alter.022 //
func (q *Rule) RuleAlterViewSubquery(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {

	pass = true
	if audit.SelectStmts != nil {
		sqlInfo, err := GetSQLInfo(audit.Query)
		if err != nil {
			pass = true
		}
		tables := SelectFromTables(sqlInfo.TableNames, audit)
		for _, table := range tables {
			exist, err := viewExist(table, info)
			if err == nil && exist {
				pass = false

			}
		}
	}

	sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) {
		switch node.(type) {
		case *sqlparser.Subquery, *sqlparser.Select:
			sqlInfo, err := GetSQLInfo(audit.Query)
			if err != nil {
				pass = true
			}
			tables := SelectFromTables(sqlInfo.TableNames, audit)
			for _, table := range tables {
				exist, err := viewExist(table, info)
				if err == nil && exist {
					pass = false
					return false, nil
				}
			}
			// return false, nil
		}
		return true, nil
	}, audit.Stmt)

	return pass, q.Summary, 0

}

// SelectFromTables 由于sqlInfo.TableNames当语句类型为select时，会把主句里面的tables加进去，容易造成干扰
func SelectFromTables(existTables []string, audit *Audit) (tables []string) {
	var selectTab []string
	switch n := audit.Stmt.(type) {
	case *sqlparser.Select:
		if n.From != nil {
			for _, from := range n.From {
				switch from.(type) {
				case *sqlparser.AliasedTableExpr:

					if expr := from.(*sqlparser.AliasedTableExpr).Expr; expr != nil {
						if name, ok := expr.(sqlparser.TableName); ok {
							tablename := name.Name.String()
							tablename = strings.ToLower(" " + tablename + " ")
							sql := strings.ToLower(audit.Query)
							re := regexp.MustCompile(tablename)
							if re == nil {
								logger.Errorf("error regexp %s", tablename)
							}
							result := re.FindAllStringSubmatch(sql, -1)
							if len(result) == 1 {
								selectTab = append(selectTab, tablename)
							}
						}
					}
				}
			}

		}

	}
	tables = existTables
	for num, et := range existTables {
		for _, st := range selectTab {
			st = strings.Replace(st, " ", "", -1)
			if et == st {
				tables = append(tables[0:num], tables[(num+1):]...)
			}
		}
	}
	// fmt.Println(tables)
	return tables
}

// RuleAlterViewJoin Alter.023
func (q *Rule) RuleAlterViewJoin(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	var tbs []string
	switch audit.Stmt.(type) {
	case *sqlparser.Delete, *sqlparser.Update, *sqlparser.Insert, *sqlparser.Select:
		sqlparser.Walk(func(node sqlparser.SQLNode) (continueWalk bool, err error) {
			switch n := node.(type) {
			case *sqlparser.JoinTableExpr:
				switch n.LeftExpr.(type) {
				case *sqlparser.AliasedTableExpr:
					tbs = append(tbs, n.LeftExpr.(*sqlparser.AliasedTableExpr).Expr.(sqlparser.TableName).Name.String())
				}
				switch n.RightExpr.(type) {
				case *sqlparser.AliasedTableExpr:
					tbs = append(tbs, n.RightExpr.(*sqlparser.AliasedTableExpr).Expr.(sqlparser.TableName).Name.String())
				}

			}

			return true, nil
		}, audit.Stmt)
	}
	for _, tableName := range tbs {
		exist, err := viewExist(tableName, info)
		if err == nil && exist {
			return false, q.Summary, 0
		}
	}
	return true, q.Summary, 0
}

type view struct {
	ViewName string `bdb:"TABLE_NAME"`
	Database string `bdb:"TABLE_SCHEMA"`
}

// RulePlnTableAll pln.004
func (q *Rule) RulePlnTableAll(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	res, err := explainSQL(audit, info)
	if err != nil {
		res := reGet(fmt.Sprintln(err), ("table'([^']+)'doesn'texist"))
		if len(res) < 1 {
			logger.Errorf("%v", err)
		}
		return true, q.Summary, 0
	}
	for _, explain := range res {
		res := reGet(explain.ID, "tablefullscan")
		if res != nil {
			// fmt.Println(res)
			return false, q.Summary, 0
		}
	}
	// fmt.Println(res)
	return true, q.Summary, 0
}

// RulePlnWhereNoIdxCol pln.005
func (q *Rule) RulePlnWhereNoIdxCol(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	checkWhere := reGet(audit.Query, "where")
	if checkWhere != nil {
		res, err := explainSQL(audit, info)
		if err != nil {
			res := reGet(fmt.Sprintln(err), ("table'([^']+)'doesn'texist"))
			if len(res) < 1 {
				logger.Errorf("%v", err)
			}
			return true, q.Summary, 0
		}
		for _, explain := range res {
			res := reGet(explain.AccessObject, "index")
			if res != nil {
				// fmt.Println(res)
				return true, q.Summary, 0
			}
		}
		return false, q.Summary, 0
	}
	return true, q.Summary, 0
}

// RulePlnCartesian pln.006施工中
func (q *Rule) RulePlnCartesian(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	res, err := explainSQL(audit, info)
	if err != nil {
		res := reGet(fmt.Sprintln(err), ("table'([^']+)'doesn'texist"))
		if len(res) < 1 {
			logger.Errorf("%v", err)
		}
		return true, q.Summary, 0
	}
	for _, explain := range res {
		res := reGet(explain.OperatorInfo, "cast\\([^)]+\\)")
		if res != nil {
			// fmt.Println(res)
			return false, q.Summary, 0
		}
	}
	// fmt.Println(res)
	return true, q.Summary, 0
}

// readViewStruct get view list from information_schema.views
func readViewStruct(info *task.DBInfo) ([]view, error) {
	sql := fmt.Sprintf("select TABLE_NAME, TABLE_SCHEMA from %s where  TABLE_SCHEMA = '%s';",
		"information_schema.VIEWS", info.DBName)

	rows, err := info.DB.Query(sql)
	if nil != err {
		return nil, err
	}
	defer rows.Close()
	var ts []view
	for rows.Next() {
		var vi view
		if err = rows.Scan(&vi.ViewName, &vi.Database); nil != err {
			return nil, err
		}
		ts = append(ts, vi)
	}
	return ts, nil
}

// viewExist check whether view exist
func viewExist(tableName string, info *task.DBInfo) (bool, error) {
	vi, err := readViewStruct(info)
	if err != nil {
		logger.Errorf("获取数据库视图列表失败，DBName: %s", info.DBName)
		return false, err
	}
	if tableName == "" {
		return false, fmt.Errorf("tablename is nil")
	}
	for _, view := range vi {
		if strings.ToUpper(view.ViewName) == strings.ToUpper(tableName) {
			return true, nil
		}
	}
	return false, nil
}

// GetSQLInfo 得到sql中的详细信息
func GetSQLInfo(sql string) (sqlInfo *parser.Result, err error) {
	p := parser.NewParser(parser.NewVisitorWithDefault())
	sqlInfo, warns, err := p.Parse(sql)
	if err != nil {
		logger.Errorf("parse error: %s", err.Error())
		return nil, err
	}
	if warns != nil {
		for _, warn := range warns {
			logger.Errorf("parse warn: %s", warn.Error())
			return nil, err
		}

	}
	return sqlInfo, nil
}

// reGet 通过正则表达式获取相关匹配项
func reGet(sql string, compile string) (res [][]string) {
	sql = strings.ToLower(sql)
	sql = strings.Replace(sql, " ", "", -1)
	re := regexp.MustCompile(compile)
	if re == nil {
		logger.Errorf("error regexp %s", compile)
	}
	result := re.FindAllStringSubmatch(sql, -1)
	return result
}

type explain struct {
	ID           string `bdb:"id"`
	EstRows      string `bdb:"estRows"`
	Task         string `bdb:"task"`
	AccessObject string `bdb:"access object"`
	OperatorInfo string `bdb:"operator info"`
}

// explainSQL get sql plan from tidb
func explainSQL(audit *Audit, info *task.DBInfo) ([]explain, error) {
	sql := fmt.Sprintf("explain %s", audit.Query)

	rows, err := info.DB.Query(sql)
	if nil != err {
		return nil, err
	}
	defer rows.Close()
	var explains []explain
	for rows.Next() {
		var pln explain
		if err = rows.Scan(&pln.ID, &pln.EstRows, &pln.Task, &pln.AccessObject, &pln.OperatorInfo); nil != err {
			return nil, err
		}
		explains = append(explains, pln)
	}
	return explains, nil
}

//RuleSQLFomatQuery DML.037
func (q *Rule) RuleSQLFomatQuery(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRow int) {
	pass = true
	err := sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) {
		switch expr := node.(type) {
		case *sqlparser.ComparisonExpr:
			if strings.ToLower(expr.Operator) == "like" {
				switch sqlval := expr.Right.(type) {
				case *sqlparser.SQLVal:
					// prefix like with '%', '_'
					if sqlval.Type == 0 && (sqlval.Val[0] == 0x25 || sqlval.Val[0] == 0x5f) {
						pass = false
						return false, nil
					}
				}
			}
		}
		return true, nil
	}, audit.Stmt)

	if err != nil {
		logger.Error(err)
		return true, q.Summary, 0
	}
	return pass, q.Summary, 0
}

//RuleCreateTablePK Create.321  存在问题
func (q *Rule) RuleCreateTablePK(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	var prefix = q.GetThresholdString()
	switch s := audit.Stmt.(type) {
	case *sqlparser.DDL:
		if strings.ToLower(s.Action) == "create" {
			if s.TableSpec != nil {
				for _, col := range s.TableSpec.Columns {
					if col.Type.KeyOpt == 1 {
						if !strings.HasPrefix(col.Name.String(), prefix) {
							return false, q.Summary, 0
						}
					}
				}
				if s.TableSpec.Indexes != nil {
					for _, idx := range s.TableSpec.Indexes {
						if strings.ToLower(idx.Info.Type) == "primary key" {
							if !strings.HasPrefix(idx.Columns[0].Column.String(), prefix) {
								return false, q.Summary, 0
							}
						}
					}
				}
			}
		}
		if strings.ToLower(s.Action) == "alter" {

			resss := reGet(audit.Query, "primarykey\\((.*?)\\)") //count\\([^)]+\\)
			for _, ress := range resss {
				for _, res := range ress {
					res = strings.Replace(res, "`", "", -1)
					if !strings.HasPrefix(res, prefix) {
						return false, q.Summary, 0
					}
				}
			}
		}
	}
	return true, q.Summary, 0
}

//RuleCreateTableFK Create.322 存在问题
func (q *Rule) RuleCreateTableFK(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	var prefix = q.GetThresholdString()
	switch audit.Stmt.(type) {
	case *sqlparser.DDL:
		for _, tiStmt := range audit.TiStmt {
			switch node := tiStmt.(type) {
			case *ast.CreateTableStmt:
				for _, v := range node.Constraints {
					switch v.Tp {
					case ast.ConstraintForeignKey:
						if !strings.HasPrefix(v.Name, prefix) {
							return false, q.Summary, 0
						}
					}
				}
			case *ast.AlterTableStmt:
				for _, v := range node.Specs {
					switch v.Tp {
					case ast.AlterTableAddConstraint:
						switch v.Constraint.Tp {
						case ast.ConstraintForeignKey:
							if !strings.HasPrefix(v.Constraint.Name, prefix) {
								return false, q.Summary, 0
							}
						}
					}
				}
			}

		}
	}
	return true, q.Summary, 0
}

// RuleCreateTableUseEnum Create.034
func (q *Rule) RuleCreateTableUseEnum(audit *Audit, info *task.DBInfo) (pass bool, newSummary string, affectRows int) {
	switch audit.Stmt.(type) {
	case *sqlparser.DDL:
		for _, tiStmt := range audit.TiStmt {
			switch node := tiStmt.(type) {
			case *ast.CreateTableStmt:
				for _, col := range node.Cols {
					if col.Tp != nil && col.Tp.Tp == mysql.TypeEnum {
						return false, q.Summary, 0
					}
				}
			case *ast.AlterTableStmt:
				for _, spec := range node.Specs {
					switch spec.Tp {
					case ast.AlterTableAlterColumn, ast.AlterTableAddColumns:
						for _, col := range spec.NewColumns {
							if col.Tp != nil && col.Tp.Tp == mysql.TypeEnum {
								return false, q.Summary, 0
							}
						}
					}
				}
			}
		}
	}
	return true, q.Summary, 0
}