package service

import (
	"context"
	"dataapps/commons/grpc"
	"dataapps/commons/logger"
	entity "dataapps/internal/entity/sqleditor"
	"dataapps/internal/service/util"
	"fmt"
	"sort"
	"strconv"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
	// _ "github.com/pingcap/tidb/parser/test_driver"
	"github.com/tidbcloud/dataservices/idl/pbgen/proto/dataapi"
	"github.com/tidbcloud/pkgv2/bizerr"
	"github.com/tidbcloud/tiinf/errs"
	_ "github.com/tidbcloud/tiinf/errs"
	"github.com/tidbcloud/tiinf/monitor/tilog"
)

// @Time    : 2022/11/16 6:54 PM
// @Author  : lvhongmeng

const (
	SELECT      = "select"
	INSERT      = "insert"
	UPDATE      = "update"
	DELETE      = "delete"
	DROP        = "drop"
	USE         = "use"
	SHOW        = "show"
	EXPLAIN     = "explain"
	OTHER       = "other"
	LIMIT       = 2000
	METALIMIT   = 10000
	SCHEMALIMIT = 1000

	EXPIRESEC = 2 * 14400
)

var (
	EXCLUDEDB       = []string{"__TiDB_BR_Temporary_mysql", "tidbcloud_dm_meta", "tidb_cdc", "lightning_task_info"}
	EXCLUDEDBANDSYS = []string{"__TiDB_BR_Temporary_mysql", "tidbcloud_dm_meta", "tidb_cdc", "lightning_task_info", "INFORMATION_SCHEMA", "mysql", "PERFORMANCE_SCHEMA", "METRICS_SCHEMA"}
)

func CreateSession(ctx context.Context, in entity.CreateSessionInput) (string, error) {
	logger.Info(ctx, fmt.Sprintf("CreateSession start! clusterid:%d, database:%s, user:%s", in.Clusterid, in.Database, in.CreatedBy))
	createSessionReq := &dataapi.CreateSessionReq{
		ProjectId: in.Projectid,
		ClusterId: in.Clusterid,
		Database:  in.Database,
		ExpireSec: EXPIRESEC,
	}
	if in.Auth0Token != "" {
		createSessionReq.Credential = &dataapi.Credential{
			Type:      dataapi.CrtType_AUTH0,
			Key:       in.Auth0Token,
			AccountId: in.CreatedBy,
		}
	}
	createSessionResp, bizErr := grpc.CreateSession(ctx, createSessionReq)
	if bizErr != nil {
		logger.LogWithError(ctx, bizErr, fmt.Sprintf("ERROR:rpc/dataapi CreateSession failed! err:%v, user:%s", bizErr, in.CreatedBy))
		return "", bizErr
	}
	logger.Info(ctx, fmt.Sprintf("CreateSession success! clusterid:%d, database:%s, sessionid:%s, user:%s", in.Clusterid, in.Database, createSessionResp.SessionId, in.CreatedBy))
	return createSessionResp.SessionId, nil

}

func CloseSession(ctx context.Context, createdBy string, clusterId uint64, sessionId, auth0Token string) error {
	logger.Info(ctx, fmt.Sprintf("CloseSession start! sessionid:%s, user:%s", sessionId, createdBy))
	_, bizErr := grpc.CloseSession(ctx, &dataapi.CloseSessionReq{
		ClusterId: clusterId,
		SessionId: sessionId,
		Credential: &dataapi.Credential{
			Type:      dataapi.CrtType_AUTH0,
			Key:       auth0Token,
			AccountId: createdBy,
		},
	})
	if bizErr != nil {
		logger.LogWithError(ctx, bizErr, fmt.Sprintf("ERROR:rpc/dataapi CloseSession failed! sessionId:%s,err:%v, user:%s", sessionId, bizErr, createdBy))
		return bizErr
	}
	logger.Info(ctx, fmt.Sprintf("CloseSession success! sessionid:%s, user:%s", sessionId, createdBy))
	return nil
}

func Statements(ctx context.Context, createdBy string, orgId, projectid, clusterid uint64, sql, sessionId, database, auth0Token string) (*entity.DataRes, error) {
	logger.Info(ctx, fmt.Sprintf("Statements start! sessionid:%s, sql:%s, user:%s", sessionId, logger.RedactSQL(sql), createdBy))
	res := &entity.DataRes{}
	//limit
	limit, err := ShowSettingsLimit(ctx, orgId, projectid, clusterid, createdBy)
	if err != nil {
		logger.LogWithError(ctx, err, fmt.Sprintf("ERROR:get user limit failed! err:%v, user:%s", err, createdBy))
		return res, bizerr.WrapGormError(err, "get user limit failed!")
	}
	res.Limit = limit
	credential := &dataapi.Credential{
		Type:      dataapi.CrtType_AUTH0,
		Key:       auth0Token,
		AccountId: createdBy,
	}
	startTime := time.Now()
	res.StartTime = startTime.Format("2006-01-02 15:04:05")
	res.StartTimeMS = startTime.UnixMilli()
	convertSQLResp, bizErr := grpc.ExecSqlWithSession(ctx, &dataapi.BasicEndpointReq{
		ProjectId:  projectid,
		ClusterId:  clusterid,
		Database:   database,
		Sql:        sql,
		SessionId:  sessionId,
		LimitRows:  uint64(limit),
		Credential: credential,
		Source:     "chat2query-console",
	})
	if bizErr != nil {
		endTime := time.Now()
		res.EndTime = endTime.Format("2006-01-02 15:04:05")
		res.EndTimeMS = endTime.UnixMilli()
		res.ExecuteTime = strconv.FormatInt(res.EndTimeMS-res.StartTimeMS, 10)
		if convertSQLResp != nil {
			res.Databases = convertSQLResp.Databases
		}
		return res, bizErr
	}
	res = convertDataAPIResult(ctx, sql, convertSQLResp, res)
	logger.Info(ctx, fmt.Sprintf("Statements success! sessionid:%s, sql:%s, user:%s", sessionId, logger.RedactSQL(sql), createdBy))
	return res, nil
}

func ShowClusterMeta(ctx context.Context, in entity.ShowClusterMetaInput) ([]*entity.DBMeta, error) {
	logger.Info(ctx, fmt.Sprintf("ShowClusterMeta start! user:%s", in.CreatedBy))
	var (
		clusterMeta []*entity.DBMeta
		dbRes       *dataapi.ExecSqlResp
		metaRes     *dataapi.ExecSqlResp
		execludeDB  []string
	)
	//settings
	settings, err := ShowSettings(ctx, entity.SQLEditorSettings{
		CreatedBy: in.CreatedBy,
		ClusterID: in.Clusterid,
		ProjectID: in.Projectid,
	})
	if err != nil {
		logger.LogWithError(ctx, err, fmt.Sprintf("ERROR:get user limit failed! err:%v, user:%s", err, in.CreatedBy))
		return nil, errs.NewInternalGeneralBusiness("get user settings failed", err)
	}
	showSystemDBFlag := settings.ShowSystemDbSchema
	if showSystemDBFlag {
		execludeDB = EXCLUDEDB
	} else {
		execludeDB = EXCLUDEDBANDSYS
	}

	credential := &dataapi.Credential{
		Type:      dataapi.CrtType_AUTH0,
		Key:       in.Auth0Token,
		AccountId: in.CreatedBy,
	}

	//	show dbs;
	dbSql := "select \n  schema_name,\n  case schema_name\n  when 'INFORMATION_SCHEMA' then 1\n  when 'mysql' then 1\n  when 'PERFORMANCE_SCHEMA' then 1\n  else 2\n  end as t1\n  from information_schema.schemata group by schema_name order by t1,lower(schema_name);"
	dbRes, bizErr := grpc.ExecSqlDirectly(ctx, &dataapi.BasicEndpointReq{
		ProjectId:  in.Projectid,
		ClusterId:  in.Clusterid,
		Sql:        dbSql,
		LimitRows:  uint64(LIMIT),
		Credential: credential,
	})
	if bizErr != nil {
		logger.LogWithError(ctx, bizErr, fmt.Sprintf("ERROR: ShowClusterMeta-Statements-databases failed! sql:%s,bizerr:%v, user:%s", dbSql, bizErr, in.CreatedBy))
		return nil, bizErr
	}
	dbArray := make([]string, 0)
	for _, v := range dbRes.Rows {
		if len(v.Items) == 0 || len(v.Items[0]) == 0 {
			continue
		}
		if !util.ArrayIn(v.Items[0], execludeDB) {
			dbArray = append(dbArray, v.Items[0])
		}
	}
	if len(dbArray) == 0 {
		return nil, nil
	}

	//	show tables;
	clusterMeta = make([]*entity.DBMeta, 0)
	dbArrayStr := ""
	for _, db := range dbArray {
		dbArrayStr = dbArrayStr + "'" + db + "',"
	}
	metaSql := ""
	if in.IsBrief == "true" {
		metaSql = "select '' as t1,TABLE_SCHEMA,TABLE_NAME from information_schema.columns where table_schema in (" + dbArrayStr[:len(dbArrayStr)-1] + ") group by TABLE_SCHEMA,TABLE_NAME;"
	} else {
		metaSql = "select * from information_schema.columns where table_schema in (" + dbArrayStr[:len(dbArrayStr)-1] + ");"
	}

	metaRes, bizErr = grpc.ExecSqlDirectly(ctx, &dataapi.BasicEndpointReq{
		ProjectId:  in.Projectid,
		ClusterId:  in.Clusterid,
		Sql:        metaSql,
		LimitRows:  uint64(METALIMIT),
		Credential: credential,
	})
	if bizErr != nil {
		logger.LogWithError(ctx, bizErr, fmt.Sprintf("ERROR: ShowClusterMeta-Statements-meta failed! sql:%s,bizerr:%v, user:%s", metaSql, bizErr, in.CreatedBy))
		return nil, err
	}

	for _, v1 := range dbArray {
		tableArrayTemp := make(map[string]bool, 0)
		dbMeta := &entity.DBMeta{Name: v1}
		for _, v := range metaRes.Rows {
			if v1 == v.Items[1] {
				tableArrayTemp[v.Items[2]] = true
			}
		}
		tableArray := make([]string, 0)
		for k, _ := range tableArrayTemp {
			tableArray = append(tableArray, k)
		}
		sort.Sort(util.Alphabetic(tableArray))

		tablesMeta := make([]*entity.TablesMeta, 0)
		for _, table := range tableArray {
			tMeta := &entity.TablesMeta{Name: table}
			tablesMeta = append(tablesMeta, tMeta)
		}
		dbMeta.Tables = tablesMeta
		clusterMeta = append(clusterMeta, dbMeta)
	}
	if in.IsBrief == "false" {
		for i := 0; i < len(clusterMeta); i++ {
			for k := 0; k < len(clusterMeta[i].Tables); k++ {
				cols := make([]*entity.Columns, 0)
				for j := 0; j < len(metaRes.Rows); j++ {
					if metaRes.Rows[j].Items[1] == clusterMeta[i].Name && clusterMeta[i].Tables[k].Name == metaRes.Rows[j].Items[2] {
						col := &entity.Columns{
							Col:      metaRes.Rows[j].Items[3],
							DataType: metaRes.Rows[j].Items[7],
							Nullable: strings.ToLower(metaRes.Rows[j].Items[6]) == "yes",
						}
						cols = append(cols, col)
					}
					clusterMeta[i].Tables[k].Columns = cols
				}

			}
		}
	}
	logger.Info(ctx, fmt.Sprintf("ShowClusterMeta success! clusterid:%d, user:%s", in.Clusterid, in.CreatedBy))
	return clusterMeta, nil
}

func ShowTableMeta(ctx context.Context, in entity.SqlExecInput) (*entity.MetaRes, error) {
	tilog.Info(ctx, fmt.Sprintf("ShowTableMeta start! user:%s", in.CreatedBy))
	sql := "select * from information_schema.columns where table_schema ='" + in.DbName + "'  and table_name = '" + in.TableName + "';"
	metaRes, bizErr := grpc.ExecSqlDirectly(ctx, &dataapi.BasicEndpointReq{
		ProjectId: in.Projectid,
		ClusterId: in.Clusterid,
		Database:  in.DbName,
		Sql:       sql,
		LimitRows: uint64(LIMIT),
		Credential: &dataapi.Credential{
			Type:      dataapi.CrtType_AUTH0,
			Key:       in.Auth0Token,
			AccountId: in.CreatedBy,
		},
	})
	if bizErr != nil {
		tilog.Error(ctx, fmt.Sprintf("ERROR: ShowTableMeta failed! sql:%s,bizerr:%v, user:%s", sql, bizErr, in.CreatedBy))
		return nil, bizErr
	}
	cols := make([]*entity.Columns, 0)
	for _, v := range metaRes.Rows {
		col := &entity.Columns{
			Col:      v.Items[3],
			DataType: v.Items[7],
			Nullable: strings.ToLower(v.Items[6]) == "yes",
		}
		cols = append(cols, col)
	}

	res := &entity.MetaRes{
		ClusterId: in.Clusterid,
		Database:  in.DbName,
		Table:     in.TableName,
		Columns:   cols,
	}
	tilog.Info(ctx, fmt.Sprintf("ShowTableMeta success! clusterid:%d, user:%s", in.Clusterid, in.CreatedBy))
	return res, nil
}

func IsDBTableExist(ctx context.Context, in entity.IsDBTableExistInput) (string, bool, error) {
	tilog.Info(ctx, fmt.Sprintf("IsDBTableExist start! user:%s", in.CreatedBy))
	var isExist bool
	sessionId, bizErr := CreateSession(ctx, entity.CreateSessionInput{
		CreatedBy:  in.CreatedBy,
		Projectid:  in.ProjectId,
		Clusterid:  in.ClusterId,
		Database:   in.DbName,
		Auth0Token: in.Auth0Token,
	})
	if bizErr != nil {
		tilog.Error(ctx, fmt.Sprintf("ERROR: IsDBTableExist-create session failed! projectId:%d,clusterId:%d,bizerr:%v, user:%s", in.ProjectId, in.ClusterId, bizErr, in.CreatedBy))
		return "", isExist, bizErr
	}
	sql := "select count(1) from information_schema.columns where table_schema ='" + in.DbName + "'  and table_name = '" + in.TableName + "';"
	isExistRes, bizErr := grpc.ExecSqlWithSession(ctx, &dataapi.BasicEndpointReq{
		ProjectId: in.ProjectId,
		ClusterId: in.ClusterId,
		SessionId: sessionId,
		Sql:       sql,
		LimitRows: uint64(LIMIT),
		Credential: &dataapi.Credential{
			Type:      dataapi.CrtType_AUTH0,
			Key:       in.Auth0Token,
			AccountId: in.CreatedBy,
		},
	})
	if bizErr != nil {
		tilog.Error(ctx, fmt.Sprintf("ERROR: IsDBTableExist failed! sessionId:%s,sql:%s,bizerr:%v, user:%s", sessionId, sql, bizErr, in.CreatedBy))
		return sessionId, isExist, bizErr
	}

	count, _ := strconv.Atoi(isExistRes.Rows[0].Items[0])
	if count >= 1 {
		isExist = true
	} else {
		isExist = false
	}

	tilog.Info(ctx, fmt.Sprintf("IsDBTableExist success! sessionid:%s, dbName:%s,tableName:%s, user:%s", sessionId, in.DbName, in.TableName, in.CreatedBy))
	return sessionId, isExist, nil
}

func GetStmtType(ctx context.Context, sql string) (dataapi.StmtType, error) {
	var (
		stmtType dataapi.StmtType
		err      error
	)
	resp, err := grpc.ParseOneStmt(ctx, &dataapi.ParseOneStmtReq{Sql: sql})
	if err != nil {
		return stmtType, err
	}
	stmtType = resp.StmtType
	return stmtType, nil
}

func convertDataAPIResult(ctx context.Context, sql string, dRes *dataapi.ExecSqlResp, res *entity.DataRes) *entity.DataRes {
	columns := make([]*entity.Columns, 0)
	rows := make([][]string, 0)

	dColumns := dRes.Columns
	for _, v := range dColumns {
		col := &entity.Columns{
			Col:      v.Name,
			DataType: v.DataType,
			Nullable: v.Nullable,
		}
		columns = append(columns, col)
	}
	dRows := dRes.Rows
	for _, v := range dRows {
		rows = append(rows, v.Items)
	}
	res.Columns = columns
	res.Rows = rows
	res.StartTime = time.Unix(dRes.StartMs/1000, 0).Format("2006-01-02 15:04:05")
	res.EndTime = time.Unix(dRes.EndMs/1000, 0).Format("2006-01-02 15:04:05")
	res.StartTimeMS = dRes.StartMs
	res.EndTimeMS = dRes.EndMs
	res.ExecuteTime = strconv.FormatInt(dRes.EndMs-dRes.StartMs, 10)
	res.RowCount = dRes.RowNum
	res.Databases = dRes.Databases
	res.WarnCount = int(dRes.WarnCount)
	res.WarnMessages = dRes.WarnMessages
	res.Query = dRes.Message
	return res
}