package openaisvc

import (
	"context"
	"encoding/json"
	"fmt"
	"net/http"
	"strings"
	"time"

	"github.com/tidbcloud/aipower/commons/config"
	"github.com/tidbcloud/aipower/commons/grpc"
	"github.com/tidbcloud/aipower/commons/httpclient"
	"github.com/tidbcloud/aipower/internal/bean/dto"

	"github.com/tidbcloud/dataservices/idl/pbgen/proto/dataapi"
	"github.com/tidbcloud/tiinf/errs"
	"github.com/tidbcloud/tiinf/monitor/tilog"
	"go.uber.org/zap"

	"github.com/patrickmn/go-cache"
)

const (
	sqlEditorGetMetaPathFormatter = "/api/v1/dataapps/sqleditor/orgs/%s/projects/%s/clusters/%s/dbs/%s/%s/meta"
	sqlEditorColumnsCacheKey      = "columns|%s|%s|%s|%s|%s"
	sqlEditorDatabaseCacheKey     = "database|%s|%s|%s" //nolint
	getDatabaseMetaQuery          = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE table_schema = '%s';"
	sqlDryRunPrefix               = "EXPLAIN "
)

var columnsCache = cache.New(15*time.Minute, 60*time.Second)

func GetSqlEditorTableColumns(ctx context.Context, param *dto.SqlEditorGetColumnParam) ([]string, error) {
	cacheKey := fmt.Sprintf(sqlEditorColumnsCacheKey, param.OrgID, param.ProjectID, param.ClusterID, param.DbName, param.TableName)
	cachedColumns, exist := columnsCache.Get(cacheKey)
	if exist {
		columns, ok := cachedColumns.([]string)
		if ok {
			return columns, nil
		}
	}

	columns, err := DoGetSqlEditorTableColumns(ctx, param)
	if err != nil {
		return columns, err
	}

	columnsCache.SetDefault(cacheKey, columns)

	return columns, nil

}

func DoGetSqlEditorTableColumns(ctx context.Context, param *dto.SqlEditorGetColumnParam) ([]string, error) {
	tilog.Info(ctx, "get sql editor table columns request", zap.Any("param", param))

	path := fmt.Sprintf(sqlEditorGetMetaPathFormatter, param.OrgID, param.ProjectID, param.ClusterID, param.DbName, param.TableName)
	url := config.Global().MessageSvcConf.SqlEditorEndpoint + path

	body, code, err := httpclient.Get(url)

	tilog.Info(ctx, "get sql editor table columns response", zap.Int("'code", code), zap.String("body", body), zap.Any("err", err))

	if err != nil || code != http.StatusOK {
		tilog.Error(ctx, "get sql editor table columns response error", zap.Any("err", err))
		return nil, errs.ErrInternalHTTP
	}
	var result dto.SqlEditorGetColumnResult
	err = json.Unmarshal([]byte(body), &result)
	if err != nil {
		tilog.Error(ctx, "get sql editor table columns unmarshal body error", zap.String("body", body), zap.Any("err", err))
		return nil, err
	}
	if result.Code != http.StatusOK {
		tilog.Error(ctx, "get sql editor table columns return biz failed", zap.String("body", body))
		return nil, errs.ErrInternalHTTP
	}

	dataColumns := result.Data.Columns
	var retColumns []string
	for _, column := range dataColumns {
		retColumns = append(retColumns, column.Col)
	}

	return retColumns, nil
}

func getSqlEditorClusterColumns(ctx context.Context, projectID uint64, clusterID uint64, caller string) ([]*dataapi.TableMeta, error) {
	req := &dataapi.GetClusterMetaReq{
		ProjectId: projectID,
		ClusterId: clusterID,
		Credential: &dataapi.Credential{
			Type: dataapi.CrtType_MIXED,
			Key:  caller,
		},
	}
	resp, err := grpc.GetClusterMeta(ctx, req)
	if err != nil {
		return nil, err
	}
	return resp.GetMetas(), nil
}

func getSqlEditorDatabaseColumns(ctx context.Context, req *dto.InteractRequest) ([]*dataapi.TableMeta, error) {
	metaReq := &dataapi.GetDatabaseMetaReq{
		ProjectId: req.ProjectID,
		ClusterId: req.ClusterID,
		Database:  req.Database,
		Credential: &dataapi.Credential{
			Type: dataapi.CrtType_MIXED,
			Key:  req.Caller,
		},
	}
	resp, err := grpc.GetDatabaseMeta(ctx, metaReq)
	if err != nil {
		return nil, err
	}
	return resp.Metas, nil
}

func getDatabaseDetailedMeta(ctx context.Context, req *dto.InteractRequest) ([]*dataapi.TableMeta, error) {
	metaReq := &dataapi.BasicEndpointReq{
		ProjectId: req.ProjectID,
		ClusterId: req.ClusterID,
		Database:  req.Database,
		Sql:       fmt.Sprintf(getDatabaseMetaQuery, req.Database),
		Credential: &dataapi.Credential{
			Type: dataapi.CrtType_MIXED,
			Key:  req.Caller,
		},
	}
	resp, err := grpc.GetDatabaseDetailedMeta(ctx, metaReq)
	if err != nil {
		return nil, err
	}
	// result format
	returnResp := make([]*dataapi.TableMeta, 0)
	for _, row := range resp.Rows {
		if row == nil || len(row.Items) != 4 {
			tilog.Warn(ctx, "get database detailed meta error for column", zap.Any("row", row))
			continue
		}
		isExist := false
		for _, tableMeta := range returnResp {
			if row.Items[0] == tableMeta.Table {
				isExist = true
				// add value
				row.Items[1] = "`" + row.Items[1] + "`"
				tableMeta.Columns = append(tableMeta.Columns, strings.Join(row.Items[1:], " "))
				break
			}
		}
		if !isExist {
			// add value
			row.Items[1] = "`" + row.Items[1] + "`"
			newTableMeta := &dataapi.TableMeta{
				Database: req.Database,
				Table:    row.Items[0],
				Columns: []string{
					strings.Join(row.Items[1:], " "),
				},
			}
			returnResp = append(returnResp, newTableMeta)
		}
	}
	return returnResp, nil
}

func validateSQLWithDryRun(ctx context.Context, req *dto.InteractRequest, sql string) bool {
	metaReq := &dataapi.BasicEndpointReq{
		ProjectId: req.ProjectID,
		ClusterId: req.ClusterID,
		Database:  req.Database,
		Sql:       sqlDryRunPrefix + sql,
		Credential: &dataapi.Credential{
			Type: dataapi.CrtType_MIXED,
			Key:  req.Caller,
		},
	}
	resp, err := grpc.ValidateSQLWithDryRun(ctx, metaReq)
	tilog.Warn(ctx, "validateSQLWithDryRun", zap.Any("resp", resp), zap.Error(err))
	return err == nil
}