// Copyright 2022 The Liquigo Authors. All rights reserved.
// Use of this source code is governed by a BSD-style
// license that can be found in the LICENSE file.

package translate

import (
	"strings"

	utils "gitee.com/west0207/liquigo/core/utils"

	etree "gitee.com/west0207/etree"
)

var strKeys = [5]string{"char", "text", "clob", "blob", "binary"}

// 获取创建表的Entity标签的sql语句
// dbmsName *string 数据库类型名称，例如：mysql
// createTableEle *etree.Element 创建表的Entity标签
func GetCreateTableSql(dbmsName *string, createTableEle *etree.Element) (string, error) {
	switch *dbmsName {
	case MySQL, MariaDB, TiDB:
		return getDefaultCreateTableSql(dbmsName, createTableEle)
	case SQLite:
		return getSqliteCreateTableSql(dbmsName, createTableEle)
	case "h2":
		return getDefaultCreateTableSql(dbmsName, createTableEle)
	case MsSQLServer:
		return getMsSQLServerCreateTableSql(dbmsName, createTableEle)
	case PostgreSQL, Kingbase, Dameng, Oracle:
		return getPostgresCreateTableSql(dbmsName, createTableEle)
	default:
		return getDefaultCreateTableSql(dbmsName, createTableEle)
	}
	return "not match", nil
}

// 目前适用于MsSQLServer
func getMsSQLServerCreateTableSql(dbmsName *string, createTableEle *etree.Element) (string, error) {
	tableName := createTableEle.SelectAttrValue("tableName", utils.EMPTY)
	SetPropertyValue(dbmsName, &tableName)
	remarks := createTableEle.SelectAttrValue("remarks", utils.EMPTY)
	remarks = strings.ReplaceAll(remarks, utils.SINGLE_QUOTE, utils.EMPTY)
	var builder strings.Builder
	columnElements := createTableEle.SelectElements("column")
	columnLen := len(columnElements)
	builder.Grow(columnLen * utils.ONE_HUNDRED)

	utils.StringAppender(&builder, "create table ", tableName, " (", utils.LF)

	var columnElem *etree.Element
	columnRemarks := make([]string, 0, 10)
	primaryKeys := make([]string, 0, 10)
	uniqueKeys := make([]string, 0, 10)
	defaultValues := make([]string, 0, 10)
	var primaryKeyName string

	for i := 0; i < columnLen; i++ {
		columnElem = columnElements[i]
		cname := columnElem.SelectAttrValue("name", utils.EMPTY)
		SetPropertyValue(dbmsName, &cname)
		ctype := columnElem.SelectAttrValue("type", utils.EMPTY)
		SetPropertyValue(dbmsName, &ctype)
		SetColumnType(dbmsName, &ctype)

		defaultValue := columnElem.SelectAttrValue("defaultValue", utils.EMPTY)
		cremarks := columnElem.SelectAttrValue("remarks", utils.EMPTY)
		cremarks = strings.ReplaceAll(cremarks, utils.SINGLE_QUOTE, utils.EMPTY)
		utils.StringAppender(&builder, "  ", cname, utils.SPACE, ctype)

		constraintsElem := columnElem.SelectElement("constraints")
		if constraintsElem != nil {
			// <constraints primaryKey="true" nullable="false" primaryKeyName="pk_sum_role" />
			// <constraints unique="true" nullable="false" uniqueConstraintName="uk_sum_role_name" />
			nullable := constraintsElem.SelectAttrValue("nullable", utils.TRUE)
			primaryKey := constraintsElem.SelectAttrValue("primaryKey", utils.FALSE)
			if primaryKeyName == utils.EMPTY {
				primaryKeyName = constraintsElem.SelectAttrValue("primaryKeyName", utils.EMPTY)
				SetPropertyValue(dbmsName, &primaryKeyName)
			}
			unique := constraintsElem.SelectAttrValue("unique", utils.FALSE)
			uniqueConstraintName := constraintsElem.SelectAttrValue("uniqueConstraintName", utils.EMPTY)
			SetPropertyValue(dbmsName, &uniqueConstraintName)

			if nullable == utils.FALSE {
				utils.StringAppender(&builder, " not null")
			}

			if unique == utils.TRUE {
				// unique index uk_test_role_uuid(uuid) using btree
				if uniqueConstraintName == utils.EMPTY {
					uni := tableName + cname
					sum := utils.Sha256(&uni)
					ukname := sum[1:20]
					uniqueKeys = append(uniqueKeys, "create unique index uk_"+ukname+" on "+tableName+" ("+cname+");"+utils.LF)
				} else {
					uniqueKeys = append(uniqueKeys, "create unique index "+uniqueConstraintName+" on "+tableName+" ("+cname+");"+utils.LF)
				}
			}

			if primaryKey == utils.TRUE {
				primaryKeys = append(primaryKeys, cname)
			}
		}

		if utils.EMPTY != defaultValue {
			// MsSQLServer需要单独添加默认值约束
			// alter table test_role add constraint dfc_test_role_update_user default '20000' for update_user;
			SetPropertyValue(dbmsName, &defaultValue)
			defaultValues = append(defaultValues, "alter table "+tableName+" add constraint dfc_"+tableName+"_"+cname+GetDefaultValueClause(&defaultValue, &ctype)+" for "+cname+";"+utils.LF)
		}

		if utils.EMPTY != cremarks {
			// execute sp_addextendedproperty 'MS_Description','executed time','user','dbo','table','db_change_file','column','executed_time';
			columnRemarks = append(columnRemarks, "execute sp_addextendedproperty 'MS_Description','"+cremarks+"','user','dbo','table','"+tableName+"','column','"+cname+"';"+utils.LF)
		}
		if i < (columnLen - 1) {
			utils.StringAppender(&builder, utils.COMMA)
		} else {
			// 最后一列
			if len(primaryKeys) > utils.INT_ZERO {
				// 后续包含主键
				utils.StringAppender(&builder, utils.COMMA)
			}
		}
		utils.StringAppender(&builder, utils.LF)
	}
	primaryKeysLen := len(primaryKeys)
	if primaryKeysLen > utils.INT_ZERO {
		// constraint pk_name primary key (id, uuid)
		if primaryKeyName == utils.EMPTY {
			uni := tableName
			sum := utils.Sha256(&uni)
			pkname := sum[1:20]
			utils.StringAppender(&builder, "  constraint pk_", pkname, " primary key (")
		} else {
			utils.StringAppender(&builder, "  constraint ", primaryKeyName, " primary key (")
		}

		for i := 0; i < primaryKeysLen; i++ {
			if i == 0 {
				utils.StringAppender(&builder, primaryKeys[i])
			} else {
				utils.StringAppender(&builder, utils.COMMA, primaryKeys[i])
			}
		}
		utils.StringAppender(&builder, ")", utils.LF)
	}

	if remarks == utils.EMPTY {
		utils.StringAppender(&builder, ");", utils.LF)
	} else {
		utils.StringAppender(&builder, ");", utils.LF)
		// execute sp_addextendedproperty 'MS_Description','changeLog file','user','dbo','table','db_change_file',null,null;
		utils.StringAppender(&builder, "execute sp_addextendedproperty 'MS_Description','", remarks, "','user','dbo','table','", tableName, "',null,null;", utils.LF)
	}
	uniqueKeysLen := len(uniqueKeys)
	if uniqueKeysLen > utils.INT_ZERO {
		// 有唯一索引
		for _, uniqueKey := range uniqueKeys {
			utils.StringAppender(&builder, uniqueKey)
		}
	}

	for _, colRemarks := range columnRemarks {
		// 字段注释
		utils.StringAppender(&builder, colRemarks)
	}

	for _, defConstraint := range defaultValues {
		// 默认值约束
		utils.StringAppender(&builder, defConstraint)
	}

	return builder.String(), nil
}

// 目前适用于mysql/h2
func getDefaultCreateTableSql(dbmsName *string, createTableEle *etree.Element) (string, error) {
	tableName := createTableEle.SelectAttrValue("tableName", utils.EMPTY)
	SetPropertyValue(dbmsName, &tableName)
	remarks := createTableEle.SelectAttrValue("remarks", utils.EMPTY)
	remarks = strings.ReplaceAll(remarks, utils.SINGLE_QUOTE, utils.EMPTY)
	var builder strings.Builder
	columnElements := createTableEle.SelectElements("column")
	columnLen := len(columnElements)
	builder.Grow(columnLen * utils.ONE_HUNDRED)

	utils.StringAppender(&builder, "create table ", tableName, " (", utils.LF)

	var columnElem *etree.Element
	primaryKeys := make([]string, 0, 10)
	uniqueKeys := make([]string, 0, 10)
	var primaryKeyName string
	var usingBtree string

	if *dbmsName == MySQL || *dbmsName == TiDB {
		usingBtree = " using btree"
	}

	for i := 0; i < columnLen; i++ {
		columnElem = columnElements[i]
		cname := columnElem.SelectAttrValue("name", utils.EMPTY)
		SetPropertyValue(dbmsName, &cname)
		ctype := columnElem.SelectAttrValue("type", utils.EMPTY)
		SetPropertyValue(dbmsName, &ctype)
		SetColumnType(dbmsName, &ctype)

		defaultValue := columnElem.SelectAttrValue("defaultValue", utils.EMPTY)
		cremarks := columnElem.SelectAttrValue("remarks", utils.EMPTY)
		cremarks = strings.ReplaceAll(cremarks, utils.SINGLE_QUOTE, utils.EMPTY)
		utils.StringAppender(&builder, "  ", cname, utils.SPACE, ctype)

		constraintsElem := columnElem.SelectElement("constraints")
		if constraintsElem != nil {
			// <constraints primaryKey="true" nullable="false" primaryKeyName="pk_sum_role" />
			// <constraints unique="true" nullable="false" uniqueConstraintName="uk_sum_role_name" />
			nullable := constraintsElem.SelectAttrValue("nullable", utils.TRUE)
			primaryKey := constraintsElem.SelectAttrValue("primaryKey", utils.FALSE)
			if primaryKeyName == utils.EMPTY {
				primaryKeyName = constraintsElem.SelectAttrValue("primaryKeyName", utils.EMPTY)
				SetPropertyValue(dbmsName, &primaryKeyName)
			}
			unique := constraintsElem.SelectAttrValue("unique", utils.FALSE)
			uniqueConstraintName := constraintsElem.SelectAttrValue("uniqueConstraintName", utils.EMPTY)
			SetPropertyValue(dbmsName, &uniqueConstraintName)

			if nullable == utils.FALSE {
				utils.StringAppender(&builder, " not null")
			}

			if unique == utils.TRUE {
				// unique index uk_test_role_uuid(uuid) using btree
				if uniqueConstraintName == utils.EMPTY {
					uni := tableName + cname
					sum := utils.Sha256(&uni)
					ukname := sum[1:20]
					uniqueKeys = append(uniqueKeys, "  unique index uk_"+ukname+" ("+cname+")"+usingBtree)
				} else {
					uniqueKeys = append(uniqueKeys, "  unique index "+uniqueConstraintName+" ("+cname+")"+usingBtree)
				}
			}

			if primaryKey == utils.TRUE {
				primaryKeys = append(primaryKeys, cname)
			}
		}

		if defaultValue != utils.EMPTY {
			SetPropertyValue(dbmsName, &defaultValue)
			utils.StringAppender(&builder, GetDefaultValueClause(&defaultValue, &ctype))
		}

		if utils.EMPTY != cremarks {
			utils.StringAppender(&builder, " comment '", cremarks, "'")
		}
		if i < (columnLen - 1) {
			utils.StringAppender(&builder, utils.COMMA)
		} else {
			// 最后一列
			if len(primaryKeys) > utils.INT_ZERO || len(uniqueKeys) > utils.INT_ZERO {
				// 后续包含主键或唯一索引
				utils.StringAppender(&builder, utils.COMMA)
			}
		}
		utils.StringAppender(&builder, utils.LF)
	}
	primaryKeysLen := len(primaryKeys)
	uniqueKeysLen := len(uniqueKeys)
	if primaryKeysLen > utils.INT_ZERO {
		// primary key (id, uuid) using btree,
		if primaryKeyName == utils.EMPTY {
			uni := tableName
			sum := utils.Sha256(&uni)
			pkname := sum[1:20]
			utils.StringAppender(&builder, "  constraint pk_", pkname, " primary key (")
		} else {
			utils.StringAppender(&builder, "  constraint ", primaryKeyName, " primary key (")
		}
		// switch *dbmsName {
		// case MySQL:
		// 	utils.StringAppender(&builder, "  primary key (")
		// case "h2":

		// default:
		// 	if primaryKeyName == utils.EMPTY {
		// 		uni := tableName
		// 		sum := utils.Sha256(&uni)
		// 		pkname := sum[1:20]
		// 		utils.StringAppender(&builder, "  constraint pk_", pkname, " primary key (")
		// 	} else {
		// 		utils.StringAppender(&builder, "  constraint ", primaryKeyName, " primary key (")
		// 	}
		// }

		for i := 0; i < primaryKeysLen; i++ {
			if i == 0 {
				utils.StringAppender(&builder, primaryKeys[i])
			} else {
				utils.StringAppender(&builder, utils.COMMA, primaryKeys[i])
			}
		}
		if uniqueKeysLen > utils.INT_ZERO {
			// 有唯一索引
			utils.StringAppender(&builder, ")", usingBtree, utils.COMMA, utils.LF)
		} else {
			utils.StringAppender(&builder, ")", usingBtree, utils.LF)
		}
	}
	if uniqueKeysLen > utils.INT_ZERO {
		// 有唯一索引
		for i := 0; i < uniqueKeysLen; i++ {
			if i < (uniqueKeysLen - 1) {
				// 不是最后一个
				utils.StringAppender(&builder, uniqueKeys[i], utils.COMMA, utils.LF)
			} else {
				// 最后一个
				utils.StringAppender(&builder, uniqueKeys[i], utils.LF)
			}
		}
	}
	if remarks == utils.EMPTY {
		utils.StringAppender(&builder, ");", utils.LF)
	} else {
		switch *dbmsName {
		case MySQL, MariaDB, TiDB:
			utils.StringAppender(&builder, ") comment = '", remarks, "';", utils.LF)
		default:
			utils.StringAppender(&builder, ");", utils.LF)
			utils.StringAppender(&builder, "comment on table ", tableName, " is '", remarks, "';", utils.LF)
		}
	}
	return builder.String(), nil
}

// 目前适用于PostgreSQL, Kingbase, Dameng, Oracle
func getPostgresCreateTableSql(dbmsName *string, createTableEle *etree.Element) (string, error) {
	tableName := createTableEle.SelectAttrValue("tableName", utils.EMPTY)
	SetPropertyValue(dbmsName, &tableName)
	remarks := createTableEle.SelectAttrValue("remarks", utils.EMPTY)
	remarks = strings.ReplaceAll(remarks, utils.SINGLE_QUOTE, utils.EMPTY)
	var builder strings.Builder
	columnElements := createTableEle.SelectElements("column")
	columnLen := len(columnElements)
	builder.Grow(columnLen * utils.ONE_HUNDRED)

	utils.StringAppender(&builder, "create table ", tableName, " (", utils.LF)

	var columnElem *etree.Element
	columnRemarks := make([]string, 0, 10)
	primaryKeys := make([]string, 0, 10)
	uniqueKeys := make([]string, 0, 10)
	var primaryKeyName string

	for i := 0; i < columnLen; i++ {
		columnElem = columnElements[i]
		cname := columnElem.SelectAttrValue("name", utils.EMPTY)
		SetPropertyValue(dbmsName, &cname)
		ctype := columnElem.SelectAttrValue("type", utils.EMPTY)
		SetPropertyValue(dbmsName, &ctype)
		SetColumnType(dbmsName, &ctype)

		defaultValue := columnElem.SelectAttrValue("defaultValue", utils.EMPTY)
		cremarks := columnElem.SelectAttrValue("remarks", utils.EMPTY)
		cremarks = strings.ReplaceAll(cremarks, utils.SINGLE_QUOTE, utils.EMPTY)
		utils.StringAppender(&builder, "  ", cname, utils.SPACE, ctype)

		if *dbmsName == Oracle && defaultValue != utils.EMPTY {
			// Oracle null/not null必须在default后面
			// new_col1 varchar(20) default '默认值1' not null;
			SetPropertyValue(dbmsName, &defaultValue)
			utils.StringAppender(&builder, GetDefaultValueClause(&defaultValue, &ctype))
		}

		constraintsElem := columnElem.SelectElement("constraints")
		if constraintsElem != nil {
			// <constraints primaryKey="true" nullable="false" primaryKeyName="pk_sum_role" />
			// <constraints unique="true" nullable="false" uniqueConstraintName="uk_sum_role_name" />
			nullable := constraintsElem.SelectAttrValue("nullable", utils.TRUE)
			primaryKey := constraintsElem.SelectAttrValue("primaryKey", utils.FALSE)
			if primaryKeyName == utils.EMPTY {
				primaryKeyName = constraintsElem.SelectAttrValue("primaryKeyName", utils.EMPTY)
				SetPropertyValue(dbmsName, &primaryKeyName)
			}
			unique := constraintsElem.SelectAttrValue("unique", utils.FALSE)
			uniqueConstraintName := constraintsElem.SelectAttrValue("uniqueConstraintName", utils.EMPTY)
			SetPropertyValue(dbmsName, &uniqueConstraintName)

			if nullable == utils.FALSE {
				utils.StringAppender(&builder, " not null")
			}

			if unique == utils.TRUE {
				// unique index uk_test_role_uuid(uuid) using btree
				if uniqueConstraintName == utils.EMPTY {
					uni := tableName + cname
					sum := utils.Sha256(&uni)
					ukname := sum[1:20]
					uniqueKeys = append(uniqueKeys, "create unique index uk_"+ukname+" on "+tableName+" ("+cname+");"+utils.LF)
				} else {
					uniqueKeys = append(uniqueKeys, "create unique index "+uniqueConstraintName+" on "+tableName+" ("+cname+");"+utils.LF)
				}
			}

			if primaryKey == utils.TRUE {
				primaryKeys = append(primaryKeys, cname)
			}
		}

		if *dbmsName != Oracle && defaultValue != utils.EMPTY {
			SetPropertyValue(dbmsName, &defaultValue)
			utils.StringAppender(&builder, GetDefaultValueClause(&defaultValue, &ctype))
		}

		if utils.EMPTY != cremarks {
			// comment on column test_user.user_name is '用户名称';
			columnRemarks = append(columnRemarks, "comment on column "+tableName+"."+cname+" is '"+cremarks+"';"+utils.LF)
		}
		if i < (columnLen - 1) {
			utils.StringAppender(&builder, utils.COMMA)
		} else {
			// 最后一列
			if len(primaryKeys) > utils.INT_ZERO {
				// 后续包含主键
				utils.StringAppender(&builder, utils.COMMA)
			}
		}
		utils.StringAppender(&builder, utils.LF)
	}
	primaryKeysLen := len(primaryKeys)
	if primaryKeysLen > utils.INT_ZERO {
		// constraint pk_name primary key (id, uuid)
		if primaryKeyName == utils.EMPTY {
			uni := tableName
			sum := utils.Sha256(&uni)
			pkname := sum[1:20]
			utils.StringAppender(&builder, "  constraint pk_", pkname, " primary key (")
		} else {
			utils.StringAppender(&builder, "  constraint ", primaryKeyName, " primary key (")
		}

		for i := 0; i < primaryKeysLen; i++ {
			if i == 0 {
				utils.StringAppender(&builder, primaryKeys[i])
			} else {
				utils.StringAppender(&builder, utils.COMMA, primaryKeys[i])
			}
		}
		utils.StringAppender(&builder, ")", utils.LF)
		// if uniqueKeysLen > utils.INT_ZERO {
		// 	// 有唯一索引
		// 	utils.StringAppender(&builder, ")", usingBtree, utils.COMMA, utils.LF)
		// } else {
		// 	utils.StringAppender(&builder, ")", usingBtree, utils.LF)
		// }
	}

	if remarks == utils.EMPTY {
		utils.StringAppender(&builder, ");", utils.LF)
	} else {
		utils.StringAppender(&builder, ");", utils.LF)
		utils.StringAppender(&builder, "comment on table ", tableName, " is '", remarks, "';", utils.LF)
	}
	uniqueKeysLen := len(uniqueKeys)
	if uniqueKeysLen > utils.INT_ZERO {
		// 有唯一索引
		for _, uniqueKey := range uniqueKeys {
			utils.StringAppender(&builder, uniqueKey)
		}
	}

	columnRemarksLen := len(columnRemarks)
	if columnRemarksLen > utils.INT_ZERO {
		// 有字段注释
		for _, colRemarks := range columnRemarks {
			utils.StringAppender(&builder, colRemarks)
		}
	}
	return builder.String(), nil
}

// 适用于sqlite
func getSqliteCreateTableSql(dbmsName *string, createTableEle *etree.Element) (string, error) {
	tableName := createTableEle.SelectAttrValue("tableName", utils.EMPTY)
	SetPropertyValue(dbmsName, &tableName)
	remarks := createTableEle.SelectAttrValue("remarks", utils.EMPTY)
	remarks = strings.ReplaceAll(remarks, utils.SINGLE_QUOTE, utils.EMPTY)
	var builder strings.Builder
	columnElements := createTableEle.SelectElements("column")
	columnLen := len(columnElements)
	builder.Grow(columnLen * utils.ONE_HUNDRED)

	utils.StringAppender(&builder, "create table ", tableName)
	if remarks != utils.EMPTY {
		// -- '用户表'
		utils.StringAppender(&builder, " -- '", remarks, "'", utils.LF)
	}
	utils.StringAppender(&builder, "(", utils.LF)

	var columnElem *etree.Element
	primaryKeys := make([]string, 0, 4)
	uniqueKeys := make([]string, 0, 4)
	var primaryKeyName string

	for i := 0; i < columnLen; i++ {
		columnElem = columnElements[i]
		cname := columnElem.SelectAttrValue("name", utils.EMPTY)
		SetPropertyValue(dbmsName, &cname)
		ctype := columnElem.SelectAttrValue("type", utils.EMPTY)
		SetPropertyValue(dbmsName, &ctype)
		SetColumnType(dbmsName, &ctype)

		defaultValue := columnElem.SelectAttrValue("defaultValue", utils.EMPTY)
		cremarks := columnElem.SelectAttrValue("remarks", utils.EMPTY)
		cremarks = strings.ReplaceAll(cremarks, utils.SINGLE_QUOTE, utils.EMPTY)
		utils.StringAppender(&builder, "  ", cname, utils.SPACE, ctype)

		constraintsElem := columnElem.SelectElement("constraints")
		if constraintsElem != nil {
			// <constraints primaryKey="true" nullable="false" primaryKeyName="pk_sum_role" />
			// <constraints unique="true" nullable="false" uniqueConstraintName="uk_sum_role_name" />
			nullable := constraintsElem.SelectAttrValue("nullable", utils.TRUE)
			primaryKey := constraintsElem.SelectAttrValue("primaryKey", utils.FALSE)
			if primaryKeyName == utils.EMPTY {
				primaryKeyName = constraintsElem.SelectAttrValue("primaryKeyName", utils.EMPTY)
				SetPropertyValue(dbmsName, &primaryKeyName)
			}
			unique := constraintsElem.SelectAttrValue("unique", utils.FALSE)
			uniqueConstraintName := constraintsElem.SelectAttrValue("uniqueConstraintName", utils.EMPTY)
			SetPropertyValue(dbmsName, &uniqueConstraintName)

			if nullable == utils.FALSE {
				utils.StringAppender(&builder, " not null")
			}

			if unique == utils.TRUE {
				// create unique index uk_test_role_uuid on test_role (uuid);
				if uniqueConstraintName == utils.EMPTY {
					uni := tableName + cname
					sum := utils.Sha256(&uni)
					ukname := sum[1:20]
					uniqueKeys = append(uniqueKeys, "create unique index uk_"+ukname+" on "+tableName+" ("+cname+");")
				} else {
					uniqueKeys = append(uniqueKeys, "create unique index "+uniqueConstraintName+" on "+tableName+" ("+cname+");")
				}
			}

			if primaryKey == utils.TRUE {
				primaryKeys = append(primaryKeys, cname)
			}
		}

		if defaultValue != utils.EMPTY {
			SetPropertyValue(dbmsName, &defaultValue)
			utils.StringAppender(&builder, GetDefaultValueClause(&defaultValue, &ctype))
		}

		if i < (columnLen - 1) {
			// 不是最后一列
			if utils.EMPTY != cremarks {
				// 有注释
				utils.StringAppender(&builder, ", -- '", cremarks, "'", utils.LF)
			} else {
				// 无注释
				utils.StringAppender(&builder, utils.COMMA, utils.LF)
			}
			// utils.StringAppender(&builder, utils.COMMA)
		} else {
			// 最后一列
			if len(primaryKeys) > utils.INT_ZERO {
				// 有主键约束
				if utils.EMPTY != cremarks {
					// 有注释
					utils.StringAppender(&builder, ", -- '", cremarks, "'", utils.LF)
				} else {
					// 无注释
					utils.StringAppender(&builder, utils.COMMA, utils.LF)
				}
			} else {
				// 无主键约束
				if utils.EMPTY != cremarks {
					// 有注释
					utils.StringAppender(&builder, " -- '", cremarks, "'", utils.LF)
				} else {
					// 无注释
					utils.StringAppender(&builder, utils.LF)
				}
			}
		}
	}

	primaryKeysLen := len(primaryKeys)
	if primaryKeysLen > utils.INT_ZERO {
		// constraint pk_*** primary key (id, uuid)
		if primaryKeyName == utils.EMPTY {
			uni := tableName
			sum := utils.Sha256(&uni)
			pkname := sum[1:20]
			utils.StringAppender(&builder, "  constraint pk_", pkname, " primary key (")
		} else {
			utils.StringAppender(&builder, "  constraint ", primaryKeyName, " primary key (")
		}

		for i := 0; i < primaryKeysLen; i++ {
			if i == 0 {
				utils.StringAppender(&builder, primaryKeys[i])
			} else {
				utils.StringAppender(&builder, utils.COMMA, primaryKeys[i])
			}
		}
		utils.StringAppender(&builder, ")", utils.LF)
	}
	utils.StringAppender(&builder, ");", utils.LF)

	// create unique index uk_test_role_uuid on test_role (uuid);
	for _, uniqueKey := range uniqueKeys {
		utils.StringAppender(&builder, uniqueKey, utils.LF)
	}

	return builder.String(), nil
}

// 获取字段默认值的sql子句
func GetDefaultValueClause(defaultValue *string, ctype *string) string {
	if isStringType(ctype) {
		// 字符串的默认值使用单引号包裹
		return " default '" + *defaultValue + "'"
	} else {
		return " default " + *defaultValue
	}
}

// 是否是字符串的字段类型
// ctype *string 字段类型字符串指针
// return bool
func isStringType(ctype *string) bool {
	for _, str := range strKeys {
		if strings.Contains(*ctype, str) {
			return true
		}
	}
	return false
}