package common

import (
	"crypto/tls"
	"crypto/x509"
	"database/sql"
	"errors"
	"fmt"
	"os"
	"time"

	"github.com/go-sql-driver/mysql"

	l4g "github.com/alecthomas/log4go"
)

var createTable = "CREATE TABLE `testUser` (\n\t  `id` int(10) primary key,\n\t  `name` varchar(20));"
var insert = "INSERT INTO testUser values(1, 'autotest')"
var insertforLog = "INSERT INTO testUser values(2, 'logtest')"
var deleteforLog = "DELETE from testUser where id=2;"
var parquetCreateTable = "CREATE TABLE `item` (\n\t  `i_id` int(11) NOT NULL,\n\t  `i_im_id` int(11) DEFAULT NULL,\n\t  `i_name` varchar(24) DEFAULT NULL,\n\t  `i_price` decimal(5,2) DEFAULT NULL,\n\t  `i_data` varchar(50) DEFAULT NULL,\n\t  PRIMARY KEY (`i_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"
var tableName = fmt.Sprintf("testCdc%d", currentTime)
var dropDataBasetest = "DROP DATABASE IF EXISTS test;"
var dropDataBaseab = "DROP DATABASE IF EXISTS `a!b!`;"
var dropDataBasedbaas = "DROP DATABASE IF EXISTS dbaas;"
var dropDataBaseimport = "DROP DATABASE IF EXISTS import;"

// var cdcCreateTable = fmt.Sprintf("CREATE TABLE `testCdc%d` (`id` int(10) primary key, `name` varchar(20));", currentTime)
var cdcCreateTable = fmt.Sprintf("CREATE TABLE `%s` (`id` int(10) primary key, `name` varchar(20));", tableName)
var dropCdcTable = fmt.Sprintf("DROP TABLE `testCdc%d`;", currentTime)
var testUserSelect = "select * from testUser"
var testCdcSelect = fmt.Sprintf("select * from testCdc%d", currentTime)
var database = "test"
var secureTransportSQL = "SET GLOBAL require_secure_transport = ON"

func Retry(attempts int, sleep time.Duration, f func() error) (err error) {
	for i := 0; i < attempts; i++ {
		if i > 0 {
			l4g.Info("retrying after error: %v", err)
			time.Sleep(sleep)
		}
		err = f()
		if err == nil {
			return nil
		}
	}
	return fmt.Errorf("after %d attempts, last error: %s", attempts, err)
}

func ConnectTidb(host string, port int64, password, database, username string) (*sql.DB, error) {
	connectData := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s", username, password, host, port, database)
	var db *sql.DB
	// TODO(aylei): make attempts and interval configurable
	err := Retry(30, 10*time.Second, func() error {
		opened, err := sql.Open("mysql", connectData)
		if err != nil {
			return err
		}
		db = opened
		return nil
	})

	//defer db.Close()
	if err != nil {
		l4g.Error("connect tidb failed, %s", err)
		return nil, errors.New("connect tidb failed")
	} else {
		return db, nil
	}
}

func ConnectServerlessUseTLS(host string, port int64, password, database, username string) (*sql.DB, error) {
	mysql.RegisterTLSConfig("tidb", &tls.Config{
		MinVersion: tls.VersionTLS12,
		ServerName: host,
	})

	connectData := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?tls=tidb", username, password, host, port, database)
	var db *sql.DB
	// TODO(aylei): make attempts and interval configurable
	err := Retry(30, 10*time.Second, func() error {
		opened, err := sql.Open("mysql", connectData)
		if err != nil {
			return err
		}
		db = opened
		return nil
	})
	//defer db.Close()
	if err != nil {
		l4g.Error("connect tidb failed, %s", err)
		return nil, errors.New("connect tidb failed")
	} else {
		return db, nil
	}
}

func ConnectTidbUseTLS(host string, port int64, password, database, username string) (*sql.DB, error) {
	rootCertPool := x509.NewCertPool()
	pem, err := os.ReadFile(fmt.Sprintf("../instancesshkey/%scatest.pem", Envn))
	if err != nil {
		l4g.Error(err)
	}
	if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
		l4g.Error("Failed to append PEM.")
	}
	mysql.RegisterTLSConfig("tidb", &tls.Config{
		RootCAs:    rootCertPool,
		MinVersion: tls.VersionTLS12,
		ServerName: host,
	})

	connectData := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?tls=tidb", username, password, host, port, database)
	var db *sql.DB
	// TODO(aylei): make attempts and interval configurable
	err = Retry(30, 10*time.Second, func() error {
		opened, err := sql.Open("mysql", connectData)
		if err != nil {
			return err
		}
		db = opened
		return nil
	})

	//defer db.Close()
	if err != nil {
		l4g.Error("connect tidb failed, %s", err)
		return nil, errors.New("connect tidb failed")
	} else {
		return db, nil
	}
}

func CreateTable(db *sql.DB, createData string) (bool, error) {
	return Query(db, createData, "create table")
}

func DropTestDatabases(db *sql.DB, dropDatabase string) (bool, error) {
	return Query(db, dropDatabase, "drop database")
}

func InsertDataToTest(db *sql.DB, instertData string) (bool, error) {
	return Query(db, instertData, "insert data")

}

func DeleteData(db *sql.DB, deleteData string) (bool, error) {
	return Query(db, deleteData, "delete data")
}

func SelectData(db *sql.DB, selectData string, checkDataFlag bool, idValue int, nameVaule string) (bool, error) {
	var id int
	var name string
	if db != nil {
		var rows *sql.Rows
		var err error
		err = Retry(3, 30*time.Second, func() error {
			rows, err = db.Query(selectData)
			if err != nil {
				l4g.Error("query tidb failed, %s", err)
				return errors.New("query tidb failed")
			}
			return nil
		})

		if err != nil {
			return false, err
		}
		if checkDataFlag == false {
			return true, nil
		}

		var result bool

		for rows.Next() {
			err = rows.Scan(&id, &name)
			l4g.Info("query result， id:%s, name:%s", id, name)
			if err != nil {
				l4g.Error("read data from tidb failed, %s", err)
				return false, errors.New("read data from tidb failed")
			} else if id == idValue && fmt.Sprint(name) == nameVaule {
				l4g.Info("data check success, %s", err)
				result = true
				break
			} else {
				err = errors.New("data check failed")
			}

		}
		return result, err
	}
	return false, errors.New("db is nil")
}
func Query(db *sql.DB, SQL_sentence, topic string) (bool, error) {
	if db != nil {
		err := Retry(3, 10*time.Second, func() error {
			_, err := db.Exec(SQL_sentence)
			l4g.Info("exec result:%s", err)
			if err != nil {
				l4g.Error("%s failed, %s", topic, err)
				return err
			} else {
				return nil
			}
		})
		if err != nil {
			return false, errors.New(fmt.Sprintf("%s failed", topic))
		} else {
			return true, nil
		}

	} else {
		return false, errors.New("db is nil")
	}
}

func QueryData(db *sql.DB, SQL_sentence string) ([]string, error) {
	var rows *sql.Rows
	var err error
	err = Retry(10, 30*time.Second, func() error {
		rows, err = db.Query(SQL_sentence)
		if err != nil {
			l4g.Error("query tidb failed, err : %s", err)
			return errors.New("query tidb failed")
		} else {
			return err
		}
	})
	if err != nil {
		return nil, err
	}
	// this func set scan -> *string
	var queryRes []string
	for rows.Next() {
		l4g.Info("rows info is:%s", rows)
		var rowRes string
		err = rows.Scan(&rowRes)
		if err != nil {
			l4g.Error("read data from tidb failed, %s", err)
			return nil, err
		}
		queryRes = append(queryRes, rowRes)
	}
	return queryRes, err
}
func InsertClusterCreateTimeToMysql(host string, port int64, password, database, env, clusterId string, totalTime,
	initialTime, prepareTime, lunchTime, istimeout int, createTime string, isdevtier int, provider, profileType,
	infraProvider string, hasTiflash, isarm int) {
	if env == "pod" {
		env = "prod"
	}
	db, err := ConnectTidb(host, port, password, database, "root")
	if err != nil {
		l4g.Info("connect mysql failed, host:%s, port:%d, database:%s", host, port, database)
		return
	}
	if isdevtier == 1 {
		provider = "aws-free-tier"
		profileType = "shared"
	}

	insertSql := fmt.Sprintf("insert into create_cluster_time values(\"%s\", \"%s\", %d, %d, %d, %d, %d, \"%s\",%d, \"%s\", \"%s\", %d, %d, \"%s\")", env, clusterId,
		totalTime, initialTime, prepareTime, lunchTime, istimeout, createTime, isdevtier, provider, profileType, hasTiflash, isarm, infraProvider)
	l4g.Info("insertSql:%s", insertSql)
	_, ierr := InsertDataToTest(db, insertSql)
	db.Close()
	if ierr != nil {
		l4g.Info("insert mysql failed, host:%s, port:%d, database:%s, insertSql:%s, ierr:%s", host, port, database, insertSql, ierr)
	}
}

func QueryClusterStatusFromMysql(host string, port int64, password, database, username, clusterId string) string {
	db, err := ConnectTidbUseTLS(host, port, password, database, username)
	if err != nil {
		l4g.Error("connect mysql failed, host:%s, port:%d, database:%s", host, port, database)
		return ""
	}
	var status string
	var rows *sql.Rows
	err = Retry(2, 1*time.Second, func() error {
		rows, err = db.Query(fmt.Sprintf("select status from clusters where id='%s'", clusterId))
		if err != nil {
			l4g.Error("query tidb failed, %s, host:%s, port:%d, database:%s, password:%s", err, host, port, database, password)
			return errors.New("query tidb failed")
		} else {
			return nil
		}
	})
	if err != nil {
		return ""
	}
	for rows.Next() {
		l4g.Info("rows info is：%s", rows)
		err = rows.Scan(&status)
		if err != nil {
			l4g.Error("read data from tidb failed, %s", err)
			return ""
		}
	}
	return status
}

func InsertClusterScaleTimeToMysql(host string, port int64, password, database, env, clusterId string, totalTime,
	istimeout int, scaleTime string) {
	if env == "pod" {
		env = "prod"
	}
	db, err := ConnectTidb(host, port, password, database, "root")
	if err != nil {
		l4g.Info("connect mysql failed, host:%s, port:%d, database:%s", host, port, database)
		return
	}

	insertSql := fmt.Sprintf("insert into scale_cluster_time values(\"%s\", \"%s\", %d, %d, \"%s\")", env, clusterId,
		totalTime, istimeout, scaleTime)
	l4g.Info("insertSql:%s", insertSql)
	_, ierr := InsertDataToTest(db, insertSql)
	db.Close()
	if ierr != nil {
		l4g.Info("insert mysql failed, host:%s, port:%d, database:%s, insertSql:%s, ierr:%s", host, port, database, insertSql, ierr)
	}
}

func InsertClusterResumeTimeToMysql(host string, port int64, password, database, env, clusterId string, totalTime,
	istimeout int, resumeTime string) {
	if env == "pod" {
		env = "prod"
	}
	db, err := ConnectTidb(host, port, password, database, "root")
	if err != nil {
		l4g.Info("connect mysql failed, host:%s, port:%d, database:%s", host, port, database)
		return
	}

	insertSql := fmt.Sprintf("insert into resume_cluster_time values(\"%s\", \"%s\", %d, %d, \"%s\")", env, clusterId,
		totalTime, istimeout, resumeTime)
	l4g.Info("insertSql:%s", insertSql)
	_, ierr := InsertDataToTest(db, insertSql)
	db.Close()
	if ierr != nil {
		l4g.Info("insert mysql failed, host:%s, port:%d, database:%s, insertSql:%s, ierr:%s", host, port, database, insertSql, ierr)
	}
}

func InsertClusterConnectTimeToMysql(host string, port int64, password, database, env, clusterId string, totalTime,
	istimeout int, connectTime string) {
	if env == "pod" {
		env = "prod"
	}
	db, err := ConnectTidb(host, port, password, database, "root")
	if err != nil {
		l4g.Info("connect mysql failed, host:%s, port:%d, database:%s", host, port, database)
		return
	}

	insertSql := fmt.Sprintf("insert into connect_cluster_time values(\"%s\", \"%s\", %d, %d, \"%s\")", env, clusterId,
		totalTime, istimeout, connectTime)
	l4g.Info("insertSql:%s", insertSql)
	_, ierr := InsertDataToTest(db, insertSql)
	db.Close()
	if ierr != nil {
		l4g.Info("insert mysql failed, host:%s, port:%d, database:%s, insertSql:%s, ierr:%s", host, port, database, insertSql, ierr)
	}
}

func InsertClusterVpcpeeringTimeToMysql(host string, port int64, password, database, env, clusterId string, totalTime,
	vpcActiveTotalTime, connectTidbTotalTime, istimeout int, vpcpeeringTime string) {
	if env == "pod" {
		env = "prod"
	}
	db, err := ConnectTidb(host, port, password, database, "root")
	if err != nil {
		l4g.Info("connect mysql failed, host:%s, port:%d, database:%s", host, port, database)
		return
	}

	insertSql := fmt.Sprintf("insert into vpcpeering_cluster_time values(\"%s\", \"%s\", %d, %d, %d, %d, \"%s\")", env, clusterId,
		totalTime, vpcActiveTotalTime, connectTidbTotalTime, istimeout, vpcpeeringTime)
	l4g.Info("insertSql:%s", insertSql)
	_, ierr := InsertDataToTest(db, insertSql)
	db.Close()
	if ierr != nil {
		l4g.Info("insert mysql failed, host:%s, port:%d, database:%s, insertSql:%s, ierr:%s", host, port, database, insertSql, ierr)
	}
}

func InsertClusterPrivateLinkTimeToMysql(host string, port int64, password, database, env, clusterId string, totalTime,
	endpointServiceActiveTotalTime, endpointActiveTotalTime, connectMysqlTotalTime, istimeout int, privateLinkTime string) {
	if env == "pod" {
		env = "prod"
	}
	db, err := ConnectTidb(host, port, password, database, "root")
	if err != nil {
		l4g.Info("connect mysql failed, host:%s, port:%d, database:%s", host, port, database)
		return
	}

	insertSql := fmt.Sprintf("insert into privatelink_time values(\"%s\", \"%s\", %d, %d, %d, %d, %d, \"%s\")", env, clusterId,
		totalTime, endpointServiceActiveTotalTime, endpointActiveTotalTime, connectMysqlTotalTime, istimeout, privateLinkTime)
	l4g.Info("insertSql:%s", insertSql)
	_, ierr := InsertDataToTest(db, insertSql)
	db.Close()
	if ierr != nil {
		l4g.Info("insert mysql failed, host:%s, port:%d, database:%s, insertSql:%s, ierr:%s", host, port, database, insertSql, ierr)
	}
}