package db

import (
	"database/sql"
	"encoding/base64"
	"encoding/json"
	"errors"
	"fmt"
	"log/slog"

	sq "github.com/Masterminds/squirrel"
	"github.com/go-sql-driver/mysql"
)

func (db *realDB) CreateTemplateRow(id *int64, clusterType, configType, templateName, tags, contentHash string) (int64, error) {
	columns := []string{"cluster_type", "config_type", "template_name", "tags", "content_hash"}
	values := []any{clusterType, configType, templateName, tags, contentHash}
	if id != nil {
		columns = append(columns, "id")
		values = append(values, *id)
	}

	sqlStr, args, err := sq.Insert("templates").Columns(columns...).Values(values...).ToSql()
	if err != nil {
		return 0, err
	}
	res, err := db.Exec(sqlStr, args...)
	if e, ok := err.(*mysql.MySQLError); ok && e.Number == 1062 {
		return 0, ErrAlreadyExists
	} else if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func (db *realDB) GetTemplateRow(id int64) (TemplateRow, error) {
	template := TemplateRow{}
	err := db.Get(&template, "SELECT * FROM templates WHERE id = ?", id)
	if err == sql.ErrNoRows {
		return template, ErrNotFound
	}
	return template, err
}

func (db *realDB) ListTemplatesByHash(clusterType, configType, contentHash string) ([]TemplateRow, error) {
	templates := []TemplateRow{}
	err := db.Select(&templates, "SELECT * FROM templates WHERE cluster_type = ? AND config_type = ? AND content_hash = ? AND delete_time IS NULL", clusterType, configType, contentHash)
	return templates, err
}

func templateRowConds(clusterType, configType string, tagFilter map[string]string, templateName string) sq.And {
	conds := sq.And{
		sq.Eq{"cluster_type": clusterType},
		sq.Expr("delete_time IS NULL"),
	}
	if configType != "" {
		conds = append(conds, sq.Eq{"config_type": configType})
	}
	if templateName != "" {
		conds = append(conds, sq.Eq{"template_name": templateName})
	}
	for k, v := range tagFilter {
		conds = append(conds, sq.Eq{fmt.Sprintf("tags->\"$.%s\"", k): v})
	}
	return conds
}

func (db *realDB) ListTemplateRows(clusterType, configType string, tagFilter map[string]string, templateName string, pageSize, page int32) ([]TemplateRow, error) {
	builder := sq.Select("*").From("templates").
		Where(templateRowConds(clusterType, configType, tagFilter, templateName))
	// pageSize <= 0 is the compatibility path for callers that need the full list.
	if pageSize > 0 {
		if page <= 0 {
			page = 1
		}
		builder = builder.OrderBy("id ASC").Limit(uint64(pageSize)).Offset(uint64(pageSize) * uint64(page-1))
	}

	sql, args, err := builder.ToSql()
	if err != nil {
		return nil, err
	}
	templates := []TemplateRow{}
	err = db.Select(&templates, sql, args...)

	return templates, err
}

func (db *realDB) CountTemplateRows(clusterType, configType string, tagFilter map[string]string, templateName string) (int64, error) {
	var total int64
	sql, args, err := sq.Select("COUNT(*)").From("templates").Where(templateRowConds(clusterType, configType, tagFilter, templateName)).ToSql()
	if err != nil {
		return 0, err
	}
	err = db.Get(&total, sql, args...)
	return total, err
}

func (db *realDB) DeleteTemplateRow(id int64) error {
	_, err := db.Exec("UPDATE templates SET delete_time = CURRENT_TIMESTAMP(6) WHERE id = ? AND delete_time IS NULL", id)
	return err
}

func (db *realDB) InsertTemplateValueRows(rows []TemplateValueRow) error {
	_, err := db.NamedExec("INSERT INTO template_values (template_id, `key`, value, comment) VALUES (:template_id, :key, :value, :comment)", rows)
	if e, ok := err.(*mysql.MySQLError); ok && e.Number == 1062 {
		return ErrAlreadyExists
	}
	return err
}

func (db *realDB) ListTemplateValueRows(templateID int64) ([]TemplateValueRow, error) {
	templateValues := []TemplateValueRow{}
	err := db.Select(&templateValues, "SELECT `key`, value FROM template_values WHERE template_id = ?", templateID)
	return templateValues, err
}

// it is forbidden to update an exist template
// func (db *realDB) UpdateTemplateValueRows()

func (db *realDB) DeleteTemplateValueRows(templateID int64) error {
	_, err := db.Exec("DELETE FROM template_values WHERE template_id = ?", templateID)
	return err
}

func (db *realDB) GetDefaultTemplate(clusterType, configType, version, cloud, instance, region, tenantID string) (int64, error) {
	defaultTemplate := DefaultTemplateRow{}

	sqlStr, args, err := sq.Select("template_id").From("default_templates").Where(sq.Eq{
		"cluster_type":   clusterType,
		"config_type":    configType,
		"version":        version,
		"cloud_provider": cloud,
		"instance":       instance,
		"region":         region,
		"tenant_id":      tenantID,
	}).ToSql()
	if err != nil {
		return 0, err
	}

	err = db.Get(&defaultTemplate, sqlStr, args...)
	if err == sql.ErrNoRows {
		return 0, ErrNotFound
	}
	return defaultTemplate.TemplateID, err
}

func (db *realDB) CountDefaultTemplateByTemplateID(templateID int64) (int64, error) {
	var count int64
	err := db.Get(&count, "SELECT COUNT(*) FROM default_templates WHERE template_id = ?", templateID)
	return count, err
}

func (db *realDB) ListDefaultTemplateByTemplate(clusterType, configType string, templateID int64) ([]DefaultTemplateRow, error) {
	defaultTemplates := []DefaultTemplateRow{}
	err := db.Select(&defaultTemplates, "SELECT * FROM default_templates WHERE cluster_type = ? AND config_type = ? AND template_id = ?", clusterType, configType, templateID)
	return defaultTemplates, err
}

func decodeDefaultTemplatePageToken(token string) (DefaultTemplatePager, error) {
	if token == "" {
		return DefaultTemplatePager{}, nil
	}
	decoded, err := base64.RawURLEncoding.DecodeString(token)
	if err != nil {
		return DefaultTemplatePager{}, err
	}
	var ct DefaultTemplatePager
	err = json.Unmarshal(decoded, &ct)
	return ct, err
}

func encodeDefaultTemplatePager(pager DefaultTemplatePager) (string, error) {
	bytes, err := json.Marshal(pager)
	if err != nil {
		return "", err
	}
	return base64.RawURLEncoding.EncodeToString(bytes), nil
}

func (db *realDB) ListDefaultTemplateBindings(clusterType string, configType, version, cloud, instance, tenantID string, pageSize, pageNum int32, exactMatch bool) ([]DefaultTemplateRow, error) {
	if pageSize == 0 {
		pageSize = 200
	}

	conds := sq.Eq{
		"cluster_type": clusterType,
	}
	if configType != "" {
		conds["config_type"] = configType
	}
	if version != "" {
		values := []string{version}
		if !exactMatch {
			values = append(values, "")
		}

		conds["version"] = values
	}
	if cloud != "" {
		values := []string{cloud}
		if !exactMatch {
			values = append(values, "")
		}

		conds["cloud_provider"] = values
	}
	if instance != "" {
		values := []string{instance}
		if !exactMatch {
			values = append(values, "")
		}

		conds["instance"] = values
	}
	if tenantID != "" {
		values := []string{tenantID}
		if !exactMatch {
			values = append(values, "")
		}

		conds["tenant_id"] = values
	}

	sql, args, err := sq.Select("*").From("default_templates").Where(conds).
		Offset(uint64(pageSize * (pageNum - 1))).
		OrderBy("cluster_type ASC, config_type ASC, version ASC, cloud_provider ASC, instance ASC").
		Limit(uint64(pageSize)).ToSql()
	if err != nil {
		return nil, err
	}
	defaultTemplates := []DefaultTemplateRow{}
	err = db.Select(&defaultTemplates, sql, args...)

	return defaultTemplates, err
}

func (db *realDB) CountDefaultTemplateBindings(clusterType string, configType, version, cloud, instance, tenantID string, exactMatch bool) (int64, error) {
	conds := sq.Eq{
		"cluster_type": clusterType,
	}
	if configType != "" {
		conds["config_type"] = configType
	}
	if version != "" {
		values := []string{version}
		if !exactMatch {
			values = append(values, "")
		}

		conds["version"] = values
	}
	if cloud != "" {
		values := []string{cloud}
		if !exactMatch {
			values = append(values, "")
		}

		conds["cloud_provider"] = values
	}
	if instance != "" {
		values := []string{instance}
		if !exactMatch {
			values = append(values, "")
		}

		conds["instance"] = values
	}
	if tenantID != "" {
		values := []string{tenantID}
		if !exactMatch {
			values = append(values, "")
		}

		conds["tenant_id"] = values
	}

	sql, args, err := sq.Select("count(*)").From("default_templates").Where(conds).ToSql()
	if err != nil {
		return 0, err
	}
	var count int64
	err = db.Get(&count, sql, args...)

	return count, err
}

func (db *realDB) InsertDefaultTemplate(clusterType, configType, version, cloud, instance, region, tenantID string, templateID int64) error {
	sqlStr, args, err := sq.Insert("default_templates").
		Columns("cluster_type", "config_type", "version", "cloud_provider", "instance", "region", "tenant_id", "template_id").
		Values(clusterType, configType, version, cloud, instance, region, tenantID, templateID).
		ToSql()
	if err != nil {
		return err
	}
	_, err = db.Exec(sqlStr, args...)
	if e, ok := err.(*mysql.MySQLError); ok && e.Number == 1062 {
		return ErrAlreadyExists
	}
	return err
}

func (db *realDB) UpdateDefaultTemplate(clusterType, configType, version, cloud, instance, region, tenantID string, templateID int64) error {
	sqlStr, args, err := sq.Update("default_templates").
		Set("template_id", templateID).
		Where(sq.Eq{
			"cluster_type":   clusterType,
			"config_type":    configType,
			"version":        version,
			"cloud_provider": cloud,
			"instance":       instance,
			"region":         region,
			"tenant_id":      tenantID,
		}).
		ToSql()
	if err != nil {
		return err
	}
	_, err = db.Exec(sqlStr, args...)
	return err
}

func (db *realDB) DeleteDefaultTemplate(clusterType, configType, version, cloud, instance, region, tenantID string) error {
	sqlStr, args, err := sq.Delete("default_templates").
		Where(sq.Eq{
			"cluster_type":   clusterType,
			"config_type":    configType,
			"version":        version,
			"cloud_provider": cloud,
			"instance":       instance,
			"region":         region,
			"tenant_id":      tenantID,
		}).
		ToSql()
	if err != nil {
		return err
	}
	_, err = db.Exec(sqlStr, args...)
	return err
}

func (db *realDB) ListAvailableInstancesFromDefaultTemplate(clusterType string, configType, version *string) ([]UniqueInstance, error) {
	instances := []UniqueInstance{}
	conds := sq.Eq{
		"cluster_type": clusterType,
	}
	if configType != nil && *configType != "" {
		conds["config_type"] = *configType
	}
	if version != nil && *version != "" {
		conds["version"] = *version
	}

	sqlStr, args, err := sq.Select("DISTINCT cloud_provider, instance").From("default_templates").Where(conds).ToSql()
	if err != nil {
		return nil, err
	}
	err = db.Select(&instances, sqlStr, args...)
	// err := db.Select(&instances, "SELECT DISTINCT cloud_provider, instance FROM default_templates WHERE cluster_type = ? AND config_type = ? AND version = ?", clusterType, configType, version)
	return instances, err
}

func (db *realDB) InsertTenantInitialCustomParams(clusterType, configType string, tenantID uint64, items []ConfigurationItem) error {
	rows := []TenantInitialCustomParamRow{}
	for _, item := range items {
		rows = append(rows, TenantInitialCustomParamRow{
			ClusterType:       clusterType,
			TenantID:          tenantID,
			ConfigType:        configType,
			ConfigurationItem: item,
		})
	}
	_, err := db.NamedExec("INSERT INTO tenant_initial_custom_params (cluster_type, config_type, tenant_id, `key`, value, comment) VALUES (:cluster_type, :config_type, :tenant_id, :key, :value, :comment)", rows)
	if e, ok := err.(*mysql.MySQLError); ok && e.Number == 1062 {
		return ErrAlreadyExists
	}
	return err
}

func (db *realDB) ListTenantInitialCustomParams(clusterType, configType string, tenantID uint64) ([]TenantInitialCustomParamRow, error) {
	tenantInitialCustomParams := []TenantInitialCustomParamRow{}
	err := db.Select(&tenantInitialCustomParams, "SELECT * FROM tenant_initial_custom_params WHERE cluster_type = ? AND config_type = ? AND tenant_id = ?", clusterType, configType, tenantID)
	return tenantInitialCustomParams, err
}

func (db *realDB) ListAllTenantInitialCustomParams(clusterType string) ([]TenantInitialCustomParamRow, error) {
	tenantInitialCustomParams := []TenantInitialCustomParamRow{}
	err := db.Select(&tenantInitialCustomParams, "SELECT * FROM tenant_initial_custom_params WHERE cluster_type = ?", clusterType)
	return tenantInitialCustomParams, err
}

func (db *realDB) ListTenantInitialConfigTypes(clusterType string, tenantID uint64) ([]string, error) {
	var configTypes []string
	err := db.Select(&configTypes, "SELECT DISTINCT config_type FROM tenant_initial_custom_params WHERE cluster_type = ? AND tenant_id = ?", clusterType, tenantID)
	return configTypes, err
}

func (db *realDB) DeleteTenantInitialCustomParams(clusterType, configType string, tenantID uint64) error {
	_, err := db.Exec("DELETE FROM tenant_initial_custom_params WHERE cluster_type = ? AND config_type = ? AND tenant_id = ?", clusterType, configType, tenantID)
	return err
}

func (db *realDB) InsertClusterConfigInfo(row ClusterConfigInfoRow) (int64, error) {
	res, err := db.Exec("INSERT INTO cluster_config_info (cluster_type, cluster_id, filename, config_type, belong_to, format, template_id) VALUES (?, ?, ?, ?, ?, ?, ?)", row.ClusterType, row.ClusterID, row.Filename, row.ConfigType, row.BelongTo, row.Format, row.TemplateID)
	if e, ok := err.(*mysql.MySQLError); ok && e.Number == 1062 {
		return 0, ErrAlreadyExists
	} else if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func (db *realDB) GetClusterConfigInfo(clusterType, clusterID, filename string) (ClusterConfigInfoRow, error) {
	clusterConfigInfo := ClusterConfigInfoRow{}
	err := db.Get(&clusterConfigInfo, "SELECT * FROM cluster_config_info WHERE cluster_type = ? AND cluster_id = ? AND filename = ?", clusterType, clusterID, filename)
	if err == sql.ErrNoRows {
		return clusterConfigInfo, ErrNotFound
	}
	return clusterConfigInfo, err
}

func (db *realDB) ListClusterConfigInfo(clusterType, clusterID string) ([]ClusterConfigInfoRow, error) {
	clusterConfigInfo := []ClusterConfigInfoRow{}
	err := db.Select(&clusterConfigInfo, "SELECT * FROM cluster_config_info WHERE cluster_type = ? AND cluster_id = ? AND delete_time IS NULL", clusterType, clusterID)
	return clusterConfigInfo, err
}

func (db *realDB) ListClusterConfigInfo2(clusterType, clusterID, configType, relatedComponent string) ([]ClusterConfigInfoRow, error) {
	clusterConfigInfo := []ClusterConfigInfoRow{}

	conds := sq.And{
		sq.Eq{"cluster_type": clusterType},
		sq.Eq{"cluster_id": clusterID},
		sq.Expr("delete_time IS NULL"),
	}
	if configType != "" {
		conds = append(conds, sq.Eq{"config_type": configType})
	}
	if relatedComponent != "" {
		conds = append(conds, sq.Eq{"belong_to": relatedComponent})
	}

	sql, args, err := sq.Select("*").From("cluster_config_info").Where(conds).ToSql()
	if err != nil {
		return nil, err
	}

	err = db.Select(&clusterConfigInfo, sql, args...)
	return clusterConfigInfo, err
}

func (db *realDB) CountClusterConfigInfoByTemplateID(templateID int64) (int64, error) {
	var count int64
	err := db.Get(&count, "SELECT COUNT(*) FROM cluster_config_info WHERE template_id = ? AND delete_time IS NULL", templateID)
	return count, err
}

func (db *realDB) ListClusterConfigInfoByTemplate(clusterType, filename string, templateID int64) ([]ClusterConfigInfoRow, error) {
	clusterConfigInfo := []ClusterConfigInfoRow{}
	err := db.Select(&clusterConfigInfo, "SELECT * FROM cluster_config_info WHERE cluster_type = ? AND filename = ? AND template_id = ? AND delete_time IS NULL", clusterType, filename, templateID)
	return clusterConfigInfo, err
}

func (db *realDB) UpdateClusterConfigInfo(row ClusterConfigInfoRow) error {
	_, err := db.NamedExec("UPDATE cluster_config_info SET format = :format, template_id = :template_id WHERE cluster_type = :cluster_type AND cluster_id = :cluster_id AND filename = :filename AND config_type = :config_type AND belong_to = :belong_to", row)
	return err
}

func (db *realDB) DeleteClusterConfigInfoByFile(clusterType, clusterID, filename string) error {
	_, err := db.Exec("UPDATE cluster_config_info SET delete_time = CURRENT_TIMESTAMP(6) WHERE cluster_type = ? AND cluster_id = ? AND filename = ? AND delete_time IS NULL", clusterType, clusterID, filename)
	return err
}

func (db *realDB) DeleteClusterConfigInfoByCluster(clusterType, clusterID string) (int64, error) {
	res, err := db.Exec("UPDATE cluster_config_info SET delete_time = CURRENT_TIMESTAMP(6) WHERE cluster_type = ? AND cluster_id = ? AND delete_time IS NULL", clusterType, clusterID)
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

func (db *realDB) InsertClusterConfigParams(clusterType, clusterID, filename string, items []ConfigurationItem) error {
	rows := []ClusterConfigParamRow{}
	for _, item := range items {
		rows = append(rows, ClusterConfigParamRow{
			ClusterType:       clusterType,
			ClusterID:         clusterID,
			Filename:          filename,
			ConfigurationItem: item,
		})
	}
	_, err := db.NamedExec("INSERT INTO cluster_config_params (cluster_type, cluster_id, filename, `key`, value, comment) VALUES (:cluster_type, :cluster_id, :filename, :key, :value, :comment)", rows)
	if e, ok := err.(*mysql.MySQLError); ok && e.Number == 1062 {
		return ErrAlreadyExists
	}
	return err
}

func (db *realDB) UpsertClusterConfigParams(clusterType, clusterID, filename string, items []ConfigurationItem) error {
	builder := sq.Insert("cluster_config_params").Columns("cluster_type", "cluster_id", "filename", "`key`", "value", "comment")
	for _, row := range items {
		builder = builder.Values(clusterType, clusterID, filename, row.Key, row.Value, row.Comment)
	}
	sqlStr, args, err := builder.Suffix("ON DUPLICATE KEY UPDATE value = VALUES(value), comment = VALUES(comment)").ToSql()
	if err != nil {
		return err
	}
	slog.Debug("UpsertClusterConfigParams", "sql", sqlStr, "args", args)
	_, err = db.Exec(sqlStr, args...)
	return err
}

func (db *realDB) ListClusterConfigParams(clusterType, clusterID, filename string) ([]ClusterConfigParamRow, error) {
	clusterConfigParams := []ClusterConfigParamRow{}
	err := db.Select(&clusterConfigParams, "SELECT * FROM cluster_config_params WHERE cluster_type = ? AND cluster_id = ? AND filename = ?", clusterType, clusterID, filename)
	return clusterConfigParams, err
}

func (db *realDB) DeleteClusterConfigParamsByFile(clusterType, clusterID, filename string) error {
	_, err := db.Exec("DELETE FROM cluster_config_params WHERE cluster_type = ? AND cluster_id = ? AND filename = ?", clusterType, clusterID, filename)
	return err
}

func (db *realDB) DeleteClusterConfigParamsByCluster(clusterType, clusterID string) error {
	_, err := db.Exec("DELETE FROM cluster_config_params WHERE cluster_type = ? AND cluster_id = ?", clusterType, clusterID)
	return err
}

func (db *realDB) CreateClusterConfigText(clusterType, clusterID, filename, text string) error {
	if len(text) > 60000 {
		return errors.New("length of Configuration File text cannot be longer than 60,000 bytes")
	}
	_, err := db.Exec("INSERT INTO cluster_config_texts (cluster_type, cluster_id, filename, data) VALUES (?, ?, ?, ?)", clusterType, clusterID, filename, text)
	if e, ok := err.(*mysql.MySQLError); ok && e.Number == 1062 {
		return ErrAlreadyExists
	}
	return err
}

func (db *realDB) GetClusterConfigText(clusterType, clusterID, filename string) (text string, err error) {
	err = db.Get(&text, "SELECT data FROM cluster_config_texts WHERE cluster_type = ? AND cluster_id = ? AND filename = ?", clusterType, clusterID, filename)
	if err == sql.ErrNoRows {
		return "", ErrNotFound
	}
	return text, err
}

func (db *realDB) UpdateClusterConfigText(clusterType, clusterID, filename, text string) error {
	if len(text) > 60000 {
		return errors.New("length of Configuration File text cannot be longer than 60,000 bytes")
	}
	_, err := db.Exec("UPDATE cluster_config_texts SET data = ? WHERE cluster_type = ? AND cluster_id = ? AND filename = ?", text, clusterType, clusterID, filename)
	return err
}

func (db *realDB) DeleteClusterConfigText(clusterType, clusterID, filename string) error {
	_, err := db.Exec("DELETE FROM cluster_config_texts WHERE cluster_type = ? AND cluster_id = ? AND filename = ?", clusterType, clusterID, filename)
	return err
}