package tiup_upgrade

import (
	"database/sql"
	"strings"
	"time"

	. "github.com/onsi/ginkgo"
	. "github.com/pingcap/endless/pkg/matcher"
	. "github.com/pingcap/endless/pkg/workloadnode/tiup"
	"github.com/pingcap/log"
	"go.uber.org/zap"
)

type bind struct {
	Original_sql string
	Bind_sql     string
	Default_db   string
	Status       string
	Create_time  string
	Update_time  string
	Charset      string
	Collation    string
	Source       string
}

func SQL_BINDING_001(
	cluster *TiUPCluster,
) {
	db := Try(cluster.GetDB("test")).(*sql.DB)
	defer db.Close()
	Try(db.Query("use test;"))
	Try(db.Query("drop table if exists t;"))
	Try(db.Query("create table t(a int, b int, c int, index idx(a));"))
	Try(db.Query("create global binding for select * from t where a < 1 using select * from t use index(idx) where a < 1;"))
	Try(db.Query("create global binding for select * from t where a < 1 using select * from t ignore index(idx) where a < 1;"))
}

func SQL_BINDING_002(
	cluster *TiUPCluster,
) {
	db := Try(cluster.GetDB("test")).(*sql.DB)
	time.Sleep(60 * time.Second)
	defer db.Close()
	rows := Try(db.Query("show global bindings;")).(*sql.Rows)
	defer rows.Close()
	i := 0
	for rows.Next() {
		var b bind
		err := rows.Scan(&b.Original_sql, &b.Bind_sql, &b.Default_db, &b.Status, &b.Create_time, &b.Update_time,
			&b.Charset, &b.Collation, &b.Source)
		must(err)
		log.Info("sql bindings:", zap.String("original_sql", b.Original_sql), zap.String("bind_sql", b.Bind_sql))
		// expectResult := "SELECT * FROM `test`.`t` IGNORE INDEX (`idx`) WHERE `a` < 1"
		if strings.Contains(b.Bind_sql, "IGNORE") || strings.Contains(b.Bind_sql, "ignore") {
			if strings.Contains(b.Bind_sql, "< 1") {
				i = 1
				break
			}
		}

	}
	if i == 0 {
		Fail("can't find binding sql")
	}

}

func SQL_BINDING_003(
	cluster *TiUPCluster,
) {
	db := Try(cluster.GetDB("test")).(*sql.DB)
	defer db.Close()
	Try(db.Query("drop database if exists spm;"))
	Try(db.Query("create database spm;"))
	Try(db.Query("use spm;"))
	Try(db.Query("create global binding for select * from test.t where a <= 1 using select * from test.t use index(idx) where a <= 1;"))
	Try(db.Query("create global binding for select * from test.t where a <= 1 using select * from test.t ignore index(idx) where a <= 1;"))
}

func SQL_BINDING_004(
	cluster *TiUPCluster,
) {
	db := Try(cluster.GetDB("test")).(*sql.DB)
	time.Sleep(60 * time.Second)
	defer db.Close()
	rows := Try(db.Query("show global bindings;")).(*sql.Rows)
	defer rows.Close()
	i := 0
	for rows.Next() {
		var b bind
		err := rows.Scan(&b.Original_sql, &b.Bind_sql, &b.Default_db, &b.Status, &b.Create_time, &b.Update_time,
			&b.Charset, &b.Collation, &b.Source)
		must(err)
		log.Info("sql bindings:", zap.String("original_sql", b.Original_sql), zap.String("bind_sql", b.Bind_sql), zap.String("default_db", b.Default_db))
		// expectResult := "SELECT * FROM `test`.`t` IGNORE INDEX (`idx`) WHERE `a` <= 1"
		if strings.Contains(b.Bind_sql, "IGNORE") || strings.Contains(b.Bind_sql, "ignore") {
			if strings.Contains(b.Bind_sql, "<= 1") {
				i = 1
				// log.Info("default_db is null")
				break
			}
		}

	}
	if i == 0 {
		Fail("can't find binding sql")
	}

}