package db

import (
	_ "embed"
	"fmt"
	"github.com/ssgo/db"
	"github.com/ssgo/log"
	"github.com/ssgo/u"
	"os"
	"regexp"
	"strings"
	"text/template"
)

type TableFieldDesc struct {
	Field   string
	Type    string
	Null    string
	Key     string
	Default string
	Extra   string
	After   string
}

type TableKeyDesc struct {
	Key_name    string
	Column_name string
}

type TableField struct {
	Name       string
	Type       string
	Index      string
	IndexGroup string
	Default    string
	Comment    string
	IsNull     bool
	Extra      string
	desc       string
	Null       string
}

type ERGroup struct {
	Group   string
	Comment string
	Tables  []TableStruct
}

type TableStruct struct {
	Name    string
	Comment string
	Fields  []TableField
}

//go:embed er.html
var erTpl string

func (field *TableField) Parse(tableType string) {
	//if field.Index == "autoId" {
	//	field.Type += " unsigned"
	//}

	if field.IsNull {
		field.Null = "NULL"
	} else {
		field.Null = "NOT NULL"
	}

	if tableType == "sqlite3" {
		// sqlite3 不能修改字段，统一使用NULL
		field.Null = "NULL"
		if field.Extra == "AUTO_INCREMENT" {
			field.Extra = "PRIMARY KEY AUTOINCREMENT"
			field.Type = "integer"
			field.Null = "NOT NULL"
		}
	}

	a := make([]string, 0)
	a = append(a, fmt.Sprintf("`%s` %s", field.Name, field.Type))

	if tableType == "mysql" {
		lowerType := strings.ToLower(field.Type)
		if strings.Contains(lowerType, "varchar") || strings.Contains(lowerType, "text") {
			a = append(a, " COLLATE utf8mb4_general_ci")
		}
	}
	//if field.Index == "autoId" {
	//	a = append(a, " AUTO_INCREMENT")
	//	field.Index = "pk"
	//	//a = append(a, " NOT NULL")
	//}

	//if field.Index == "uniqueId" {
	//	field.Index = "pk"
	//	//a = append(a, " NOT NULL")
	//}

	if field.Extra != "" {
		a = append(a, " "+field.Extra)
	}
	a = append(a, " "+field.Null)

	if field.Default != "" {
		if strings.Contains(field.Default, "CURRENT_TIMESTAMP") {
			a = append(a, " DEFAULT "+field.Default)
		} else {
			a = append(a, " DEFAULT '"+field.Default+"'")
		}
	}
	if tableType == "sqlite3" {
		field.Comment = ""
		field.Type = "numeric"
	} else if tableType == "mysql" {
		if field.Comment != "" {
			a = append(a, " COMMENT '"+field.Comment+"'")
		}
	}
	field.desc = strings.Join(a, "")
}

var ddlTableMatcher = regexp.MustCompile("(?is)^\\s*CREATE\\s+TABLE\\s+`?([\\w]+)`?\\s*\\(\\s*(.*?)\\s*\\);?\\s*$")
var ddlFieldMatcher = regexp.MustCompile("(?s)\\s*[`\\[]?([\\w]+)[`\\]]?\\s+\\[?([\\w() ]+)\\]?\\s*(.*?)(,|$)")
var ddlKeyMatcher = regexp.MustCompile("[`\\[]?([\\w]+)[`\\]]?\\s*(,|\\))")
var ddlNotNullMatcher = regexp.MustCompile("(?i)\\s+NOT NULL")
var ddlNullMatcher = regexp.MustCompile("(?i)\\s+NULL")

//var ddlDefaultMatcher = regexp.MustCompile("(?i)\\s+DEFAULT\\s+(.*?)$")
var ddlIndexMatcher = regexp.MustCompile("(?is)^\\s*CREATE\\s+([A-Za-z ]+)\\s+`?([\\w]+)`?\\s+ON\\s+`?([\\w]+)`?\\s*\\(\\s*(.*?)\\s*\\);?\\s*$")
var ddlIndexFieldMatcher = regexp.MustCompile("[`\\[]?([\\w]+)[`\\]]?\\s*(,|$)")

// MakeER 创建ER图文件
func MakeER(groups []ERGroup, outputFile *string, tplFile *string, logger *log.Logger) error {
	tplStr := ""
	if tplFile == nil || *tplFile == "" {
		tplStr = erTpl
	} else {
		tplStr = *tplFile
	}
	erOutputFile := "er.html"
	if outputFile != nil && *outputFile != "" {
		erOutputFile = *outputFile
	}
	tpl := template.New(erOutputFile).Funcs(template.FuncMap{
		"short": func(in string) string {
			switch in {
			case "NULL":
				return "n"
			case "NOT NULL":
				return "nn"
			case "AUTO_INCREMENT":
				return "ai"
			case "CURRENT_TIMESTAMP":
				return "ct"
			case "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP":
				return "ctu"
			}
			return in
		},
	})
	var err error
	tpl, err = tpl.Parse(tplStr)
	if err == nil {
		var fp *os.File
		fp, err = os.OpenFile(erOutputFile, os.O_CREATE|os.O_WRONLY|os.O_TRUNC, 0666)
		if err == nil {
			err = tpl.Execute(fp, map[string]interface{}{
				"title":  "ER",
				"groups": groups,
			})
			_ = fp.Close()
		}
	}
	if err != nil {
		logger.Error(err.Error())
	}
	return err
}

func MakeTable(conn *db.DB, table *TableStruct, logger *log.Logger) ([]string, error) {
	//fmt.Println(u.JsonP(ddlKeyMatcher.FindAllStringSubmatch("(`key`,id, `name` )", 100)), "====================")
	fieldSets := make([]string, 0)
	//fieldSetBy := make(map[string]string)
	pks := make([]string, 0)
	keySets := make([]string, 0)
	keySetBy := make(map[string]string)
	keySetFields := make(map[string]string)
	for i, field := range table.Fields {
		field.Parse(conn.Config.Type)
		table.Fields[i] = field

		if conn.Config.Type == "sqlite3" {
			if field.Index == "pk" && field.Extra != "PRIMARY KEY AUTOINCREMENT" {
				// sqlite3 用 unique 代替 pk
				field.Index = "unique"
				field.IndexGroup = "0"
				field.Null = "NULL"
			}
		}

		switch field.Index {
		case "pk":
			if conn.Config.Type == "sqlite3" {
				if field.Extra != "PRIMARY KEY AUTOINCREMENT" {
					pks = append(pks, field.Name)
				}
			} else {
				pks = append(pks, field.Name)
			}
		case "unique":
			keyName := fmt.Sprint("uk_", table.Name, "_", field.Name)
			if field.IndexGroup != "" {
				keyName = fmt.Sprint("uk_", table.Name, "_", field.IndexGroup)
			}
			if keySetBy[keyName] != "" {
				keySetFields[keyName] += " " + field.Name
				// 复合索引
				if conn.Config.Type == "sqlite3" {
					keySetBy[keyName] = strings.Replace(keySetBy[keyName], ")", ", `"+field.Name+"`)", 1)
				} else if conn.Config.Type == "mysql" {
					keySetBy[keyName] = strings.Replace(keySetBy[keyName], ") COMMENT", ", `"+field.Name+"`) COMMENT", 1)
				}
			} else {
				keySetFields[keyName] = field.Name
				keySet := ""
				if conn.Config.Type == "sqlite3" {
					keySet = fmt.Sprintf("CREATE UNIQUE INDEX `%s` ON `%s` (`%s`)", keyName, table.Name, field.Name)
				} else if conn.Config.Type == "mysql" {
					keySet = fmt.Sprintf("UNIQUE KEY `%s` (`%s`) COMMENT '%s'", keyName, field.Name, field.Comment)
				}
				keySets = append(keySets, keySet)
				keySetBy[keyName] = keySet
			}
		case "fulltext":
			if conn.Config.Type == "mysql" {
				keyName := fmt.Sprint("tk_", table.Name, "_", field.Name)
				keySet := fmt.Sprintf("FULLTEXT KEY `%s` (`%s`) COMMENT '%s'", keyName, field.Name, field.Comment)
				keySets = append(keySets, keySet)
				keySetBy[keyName] = keySet
			}
		case "index":
			keyName := fmt.Sprint("ik_", table.Name, "_", field.Name)
			if field.IndexGroup != "" {
				keyName = fmt.Sprint("ik_", table.Name, "_", field.IndexGroup)
			}
			if keySetBy[keyName] != "" {
				keySetFields[keyName] += " " + field.Name
				// 复合索引
				if conn.Config.Type == "sqlite3" {
					keySetBy[keyName] = strings.Replace(keySetBy[keyName], ")", ", `"+field.Name+"`)", 1)
				} else if conn.Config.Type == "mysql" {
					keySetBy[keyName] = strings.Replace(keySetBy[keyName], ") COMMENT", ", `"+field.Name+"`) COMMENT", 1)
				}
			} else {
				keySetFields[keyName] = field.Name
				keySet := ""
				if conn.Config.Type == "sqlite3" {
					keySet = fmt.Sprintf("CREATE INDEX `%s` ON `%s` (`%s`)", keyName, table.Name, field.Name)
				} else if conn.Config.Type == "mysql" {
					keySet = fmt.Sprintf("KEY `%s` (`%s`) COMMENT '%s'", keyName, field.Name, field.Comment)
				}
				keySets = append(keySets, keySet)
				keySetBy[keyName] = keySet
			}
		}

		fieldSets = append(fieldSets, field.desc)
		//fieldSetBy[field.Name] = field.desc
	}
	//fmt.Println(u.JsonP(table.Fields))
	//fmt.Println(u.JsonP(keySetBy), 3)
	//fmt.Println(u.JsonP(keySets), 4)

	outSql := make([]string, 0)

	var result *db.ExecResult
	var tableInfo map[string]interface{}
	if conn.Config.Type == "sqlite3" {
		tableInfo = conn.Query("SELECT `name`, `sql` FROM `sqlite_master` WHERE `type`='table' AND `name`='" + table.Name + "'").MapOnR1()
		tableInfo["comment"] = ""
	} else if conn.Config.Type == "mysql" {
		tableInfo = conn.Query("SELECT TABLE_NAME name, TABLE_COMMENT comment FROM information_schema.TABLES WHERE TABLE_SCHEMA='" + conn.Config.DB + "' AND TABLE_NAME='" + table.Name + "'").MapOnR1()
	}
	oldTableComment := u.String(tableInfo["comment"])

	if tableInfo["name"] != nil && tableInfo["name"] != "" {
		// 合并字段
		oldFieldList := make([]*TableFieldDesc, 0)
		oldFields := make(map[string]*TableFieldDesc)
		oldIndexes := make(map[string]string)
		oldIndexInfos := make([]*TableKeyDesc, 0)

		oldComments := map[string]string{}
		if conn.Config.Type == "sqlite3" {
			tableM := ddlTableMatcher.FindStringSubmatch(u.String(tableInfo["sql"]))
			if tableM != nil {
				fieldsM := ddlFieldMatcher.FindAllStringSubmatch(tableM[2], 2000)
				if fieldsM != nil {
					for _, m := range fieldsM {
						if m[1] == "PRIMARY" && m[2] == "KEY" {
							keysM := ddlKeyMatcher.FindAllStringSubmatch(m[3], 20)
							if keysM != nil {
								for _, km := range keysM {
									oldIndexInfos = append(oldIndexInfos, &TableKeyDesc{
										Key_name:    "PRIMARY",
										Column_name: km[1],
									})
								}
							}
						} else {
							Null := "NULL"
							//fmt.Println("    =====", m[0], m[1], m[2])
							if ddlNotNullMatcher.MatchString(m[2]) {
								m[2] = ddlNotNullMatcher.ReplaceAllString(m[2], "")
								Null = "NOT NULL"
							} else if ddlNullMatcher.MatchString(m[2]) {
								m[2] = ddlNullMatcher.ReplaceAllString(m[2], "")
								Null = "NULL"
							}
							//fmt.Println("        =====", m[2], "|", Null)

							oldFieldList = append(oldFieldList, &TableFieldDesc{
								Field: m[1],
								Type:  m[2],
								//Null:    u.StringIf(strings.Contains(m[3], "NOT NULL"), "NO", "YES"),
								Null:    u.StringIf(Null == "NOT NULL", "NO", "YES"),
								Key:     "",
								Default: "",
								Extra:   "",
								After:   "",
							})
						}
					}
				}
				//fmt.Println(u.JsonP(fieldsM), 222)
			}

			// 读取索引信息
			for _, indexInfo := range conn.Query("SELECT `name`, `sql` FROM `sqlite_master` WHERE `type`='index' AND `tbl_name`='" + table.Name + "'").StringMapResults() {
				//fmt.Println(u.JsonP(indexInfo), 777)
				indexM := ddlIndexMatcher.FindStringSubmatch(indexInfo["sql"])
				if indexM != nil {
					//fmt.Println(u.JsonP(indexM), 666)
					indexFieldM := ddlIndexFieldMatcher.FindAllStringSubmatch(indexM[4], 20)
					//fmt.Println(u.JsonP(indexFieldM), 555)
					if indexFieldM != nil {
						for _, km := range indexFieldM {
							oldIndexInfos = append(oldIndexInfos, &TableKeyDesc{
								Key_name:    indexInfo["name"],
								Column_name: km[1],
							})
						}
					}
				}
			}

			//fmt.Println(u.JsonP(oldFieldList), 1)
			//fmt.Println(u.JsonP(oldIndexInfos), 2)
		} else if conn.Config.Type == "mysql" {
			conn.Query("SELECT column_name, column_comment FROM information_schema.columns WHERE TABLE_SCHEMA='" + conn.Config.DB + "' AND TABLE_NAME='" + table.Name + "'").ToKV(&oldComments)
			_ = conn.Query("DESC `" + table.Name + "`").To(&oldFieldList)
			_ = conn.Query("SHOW INDEX FROM `" + table.Name + "`").To(&oldIndexInfos)
		}
		//fmt.Println(u.JsonP(oldComments), 111)

		for _, indexInfo := range oldIndexInfos {
			if oldIndexes[indexInfo.Key_name] == "" {
				oldIndexes[indexInfo.Key_name] = indexInfo.Column_name
			} else {
				oldIndexes[indexInfo.Key_name] += " " + indexInfo.Column_name
			}
		}
		//fmt.Println(u.JsonP(oldIndexes), 111)
		//fmt.Println(u.JsonP(keySetFields), 222)
		//fmt.Println(u.JsonP(keySetBy), 333)

		// 先后顺序
		prevFieldId := ""
		for _, field := range oldFieldList {
			if conn.Config.Type == "sqlite3" {
				field.Type = "numeric"
			} else if conn.Config.Type == "mysql" {
				field.After = prevFieldId
			}
			prevFieldId = field.Field
			oldFields[field.Field] = field
		}
		//fmt.Println(111, u.JsonP(oldFields), 111)

		actions := make([]string, 0)
		for keyId := range oldIndexes {
			if keyId != "PRIMARY" && strings.ToLower(keySetFields[keyId]) != strings.ToLower(oldIndexes[keyId]) {
				if conn.Config.Type == "sqlite3" {
					actions = append(actions, "DROP INDEX `"+keyId+"`")
				} else if conn.Config.Type == "mysql" {
					actions = append(actions, "DROP KEY `"+keyId+"`")
				}
			}
		}
		//fmt.Println("  =>>>>>>>>", oldIndexes, pks)
		if oldIndexes["PRIMARY"] != "" && strings.ToLower(oldIndexes["PRIMARY"]) != strings.ToLower(strings.Join(pks, " ")) {
			if conn.Config.Type == "sqlite3" {
			} else if conn.Config.Type == "mysql" {
				actions = append(actions, "DROP PRIMARY KEY")
			}
		}
		//for fieldId, fieldSet := range fieldSetBy {
		newFieldExists := map[string]bool{}
		prevFieldId = ""
		for _, field := range table.Fields {
			newFieldExists[field.Name] = true
			oldField := oldFields[field.Name]

			// 修复部分数据库的特殊性
			if oldField == nil {
				if conn.Config.Type == "sqlite3" {
					actions = append(actions, "ALTER TABLE `"+table.Name+"` ADD COLUMN "+field.desc)
				} else if conn.Config.Type == "mysql" {
					actions = append(actions, "ADD COLUMN "+field.desc)
				}
			} else {
				oldField.Type = strings.TrimSpace(strings.ReplaceAll(oldField.Type, " (", "("))
				fixedOldDefault := u.String(oldField.Default)
				if fixedOldDefault == "CURRENT_TIMESTAMP" && strings.Contains(oldField.Extra, "on update CURRENT_TIMESTAMP") {
					fixedOldDefault = "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
				}
				fixedOldNull := "NOT NULL"
				if oldField.Null == "YES" {
					fixedOldNull = "NULL"
				}
				//fmt.Println("  ==", field.Type, "!=", oldField.Type, "||", field.Default, "!=", fixedOldDefault, "||", field.Null, "!=", fixedOldNull, "||", oldField.After, "!=", prevFieldId, "||", oldComments[field.Name], "!=", field.Comment)
				//fmt.Println("  ==", strings.ToLower(field.Type) != strings.ToLower(oldField.Type), strings.ToLower(field.Default) != strings.ToLower(fixedOldDefault), strings.ToLower(field.Null) != strings.ToLower(fixedOldNull), strings.ToLower(oldField.After) != strings.ToLower(prevFieldId), strings.ToLower(oldComments[field.Name]) != strings.ToLower(field.Comment))
				if strings.ToLower(field.Type) != strings.ToLower(oldField.Type) || strings.ToLower(field.Default) != strings.ToLower(fixedOldDefault) || strings.ToLower(field.Null) != strings.ToLower(fixedOldNull) || strings.ToLower(oldField.After) != strings.ToLower(prevFieldId) || strings.ToLower(oldComments[field.Name]) != strings.ToLower(field.Comment) {
					//fmt.Println("    > > > > ", u.JsonP(oldField), 1111)
					// `t4f34` varchar(100) COLLATE utf8mb4_general_ci COMMENT ''
					// f34, varchar(100), YES, , ,
					//fmt.Println(111111, u.JsonP(field), 1111)
					// 为什么Desc是空？？？？
					after := ""
					if conn.Config.Type == "mysql" {
						if oldField.After != prevFieldId {
							if prevFieldId == "" {
								after = " FIRST"
							} else {
								after = " AFTER `" + prevFieldId + "`"
							}
						}
					}
					//DROP INDEX `uk_config_key`;
					//ALTER TABLE `config` RENAME COLUMN `key` TO `keyOld`;
					//ALTER TABLE `config` ADD COLUMN `key` varchar(30) NULL;
					//UPDATE `config` SET `key`=`keyOld`;
					//ALTER TABLE `config` DROP COLUMN `keyOld`;
					//CREATE INDEX `uk_config_key` ON `config` (`key`);
					if conn.Config.Type == "sqlite3" {
						// 不支持修改字段，所以要先创建然后复制数据再删除

						// 方案一（已放弃）重新创建表实现修改
						//actions = append(actions, fmt.Sprintf("CREATE TABLE `%s_temp` (\n%s\n)", table.Name, strings.Join(fieldSets, ",\n")))
						//actions = append(actions, fmt.Sprintf("INSERT INTO `%s_temp` SELECT * FROM `%s`", table.Name, table.Name))
						//actions = append(actions, fmt.Sprintf("DROP TABLE `%s`", table.Name))
						//actions = append(actions, fmt.Sprintf("ALTER TABLE `%s_temp` RENAME TO `%s`", table.Name, table.Name))
						//INSERT INTO t1_new SELECT foo, bar, baz FROM t1;
						//DROP TABLE t1;
						//ALTER TABLE t1_new RENAME TO t1;

						// 方案二（已放弃）创建新字段复制数据后删除（部分Sqlite不支持DROP COLUMN）
						//redoIndexes := make([]string, 0)
						//for oldIndexName, oldIndex := range oldIndexes {
						//	if u.StringIn(strings.Split(oldIndex, " "), field.Name) {
						//		indexSql := conn.Query("SELECT `sql` FROM `sqlite_master` WHERE `type`='index' AND `name`='" + oldIndexName + "'").StringOnR1C1()
						//		redoIndexes = append(redoIndexes, indexSql)
						//		actions = append(actions, "DROP INDEX `"+oldIndexName+"`")
						//	}
						//}
						//oldPostfix := u.UniqueId()
						//actions = append(actions, "ALTER TABLE `"+table.Name+"` RENAME COLUMN `"+field.Name+"` TO `d_"+field.Name+"_"+oldPostfix+"`")
						//actions = append(actions, "ALTER TABLE `"+table.Name+"` ADD COLUMN "+field.Desc+after)
						//actions = append(actions, "UPDATE `"+table.Name+"` SET `"+field.Name+"`=`d_"+field.Name+"_"+oldPostfix+"`")
						////actions = append(actions, "ALTER TABLE `"+table.Name+"` DROP COLUMN `"+field.Name+"Old`")
						//for _, redoIndex := range redoIndexes {
						//	actions = append(actions, redoIndex)
						//}

						// 方案三 不修改字段类型，Sqlite可以兼容

						//actions = append(actions, "ALTER TABLE `"+table.Name+"` ADD COLUMN "+field.desc)
					} else if conn.Config.Type == "mysql" {
						actions = append(actions, "CHANGE `"+field.Name+"` "+field.desc+after)
					}
				}
			}

			if conn.Config.Type == "mysql" {
				prevFieldId = field.Name
			}
		}

		for oldFieldName := range oldFields {
			if newFieldExists[oldFieldName] != true {
				if conn.Config.Type == "sqlite3" {
					//actions = append(actions, "ALTER TABLE `"+table.Name+"` DROP COLUMN `"+oldFieldName+"`")
				} else if conn.Config.Type == "mysql" {
					actions = append(actions, "DROP COLUMN `"+oldFieldName+"`")
				}
			}
		}

		// sqlite3 不支持添加主键
		if conn.Config.Type == "mysql" {
			if len(pks) > 0 && strings.ToLower(oldIndexes["PRIMARY"]) != strings.ToLower(strings.Join(pks, " ")) {
				actions = append(actions, "ADD PRIMARY KEY(`"+strings.Join(pks, "`,`")+"`)")
			}
		}

		//fmt.Println(111, u.JsonP(oldIndexes), 222 )
		//fmt.Println(222, u.JsonP(keySetBy), 222 )
		for keyId, keySet := range keySetBy {
			if oldIndexes[keyId] == "" || strings.ToLower(oldIndexes[keyId]) != strings.ToLower(keySetFields[keyId]) {
				if conn.Config.Type == "sqlite3" {
					actions = append(actions, keySet)
				} else if conn.Config.Type == "mysql" {
					actions = append(actions, "ADD "+keySet)
				}
			}
		}

		//fmt.Println("	=>", table.Comment, "|", oldTableComment )
		if conn.Config.Type == "mysql" {
			if table.Comment != oldTableComment {
				actions = append(actions, "COMMENT '"+table.Comment+"'")
			}
		}

		if len(actions) == 0 {
			// 不需要更新
			return outSql, nil
		}

		tx := conn.Begin()
		defer tx.CheckFinished()
		if conn.Config.Type == "sqlite3" {
			for _, action := range actions {
				//fmt.Println(u.Dim("\t" + strings.ReplaceAll(action, "\n", "\n\t")))
				outSql = append(outSql, action)
				result = tx.Exec(action)
				if result.Error != nil {
					break
				}
			}
		} else if conn.Config.Type == "mysql" {
			sql := "ALTER TABLE `" + table.Name + "` " + strings.Join(actions, "\n,") + ";"
			//fmt.Println(u.Dim("\t" + strings.ReplaceAll(sql, "\n", "\n\t")))
			outSql = append(outSql, sql)
			result = tx.Exec(sql)
		}
		if result.Error != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	} else {
		// 创建新表
		if len(pks) > 0 {
			fieldSets = append(fieldSets, "PRIMARY KEY (`"+strings.Join(pks, "`,`")+"`)")
		}

		indexSets := make([]string, 0) // sqlite3 额外创建索引的sql
		if conn.Config.Type == "sqlite3" {
			for _, indexSql := range keySetBy {
				indexSets = append(indexSets, indexSql)
			}
		} else if conn.Config.Type == "mysql" {
			for _, key := range keySets {
				fieldSets = append(fieldSets, key)
			}
		}

		sql := ""

		if conn.Config.Type == "sqlite3" {
			sql = fmt.Sprintf("CREATE TABLE `%s` (\n  %s\n);", table.Name, strings.Join(fieldSets, ",\n  "))
		} else if conn.Config.Type == "mysql" {
			sql = fmt.Sprintf("CREATE TABLE `%s` (\n  %s\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='%s';", table.Name, strings.Join(fieldSets, ",\n  "), table.Comment)
		}
		tx := conn.Begin()
		defer tx.CheckFinished()
		//fmt.Println(u.Dim("\t" + strings.ReplaceAll(sql, "\n", "\n\t")))
		outSql = append(outSql, sql)
		result = tx.Exec(sql)

		if result.Error == nil {
			if conn.Config.Type == "sqlite3" {
				for _, indexSet := range indexSets {
					//fmt.Println(indexSet)
					//fmt.Println(u.Dim("\t" + strings.ReplaceAll(indexSet, "\n", "\n\t")))
					outSql = append(outSql, indexSet)
					r := tx.Exec(indexSet)
					if r.Error != nil {
						result = r
					}
				}
			}
		}
		if result.Error != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}

	}

	if result == nil {
		return outSql, nil
	}

	if result.Error != nil {
		logger.Error(result.Error.Error())
	}

	return outSql, result.Error
}