ai_site/platform/internal/schema/ddl.go

package schema

import (
	"fmt"
	"strings"

	"aijianzhan/platform/internal/blueprint"
)

// BuildPostgresDDL 仅拼接白名单标识符与固定类型映射。
func BuildPostgresDDL(bp *blueprint.Blueprint) ([]string, error) {
	if bp.Storage.SchemaName == "" {
		return nil, fmt.Errorf("schema_name empty")
	}
	schema := bp.Storage.SchemaName
	stmts := []string{
		fmt.Sprintf("CREATE SCHEMA IF NOT EXISTS %s", quoteIdent(schema)),
	}

	for _, e := range bp.Entities {
		cols := make([]string, 0, len(e.Fields)+4)
		hasTenant := false
		hasOrgUnit := false
		hasCreatedAt := false
		hasUpdatedAt := false
		hasCreatedBy := false

		for _, f := range e.Fields {
			sqlType, err := mapType(f)
			if err != nil {
				return nil, err
			}

			var col string
			switch {
			case f.Name == e.PrimaryKey && f.Type == "bigint":
				col = fmt.Sprintf("%s BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY", quoteIdent(f.Name))
			case f.Name == e.PrimaryKey && f.Type == "int":
				col = fmt.Sprintf("%s INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY", quoteIdent(f.Name))
			default:
				nullSQL := "NULL"
				if !blueprint.BoolOr(f.Nullable, true) {
					nullSQL = "NOT NULL"
				}
				col = fmt.Sprintf("%s %s %s", quoteIdent(f.Name), sqlType, nullSQL)
				if f.Name == e.PrimaryKey {
					col += " PRIMARY KEY"
				} else if f.Unique {
					col += " UNIQUE"
				}
				if f.Type == "enum" && len(f.EnumValues) > 0 {
					// 枚举值仅作 UI 提示，不写死 CHECK，避免导入新值被拒
				}
			}
			cols = append(cols, col)
			switch f.Name {
			case "tenant_id":
				hasTenant = true
			case "org_unit_id":
				hasOrgUnit = true
			case "created_at":
				hasCreatedAt = true
			case "updated_at":
				hasUpdatedAt = true
			case "created_by":
				hasCreatedBy = true
			}
		}

		// 系统强制列
		if !hasTenant {
			cols = append(cols, "tenant_id BIGINT NOT NULL")
		}
		if !hasOrgUnit {
			cols = append(cols, "org_unit_id BIGINT")
		}
		if !hasCreatedBy {
			cols = append(cols, "created_by BIGINT")
		}
		if !hasCreatedAt {
			cols = append(cols, "created_at TIMESTAMPTZ NOT NULL DEFAULT now()")
		}
		if !hasUpdatedAt {
			cols = append(cols, "updated_at TIMESTAMPTZ NOT NULL DEFAULT now()")
		}

		create := fmt.Sprintf(
			"CREATE TABLE IF NOT EXISTS %s.%s (\n  %s\n)",
			quoteIdent(schema),
			quoteIdent(e.Table),
			strings.Join(cols, ",\n  "),
		)
		stmts = append(stmts, create)

		for _, idx := range e.Indexes {
			unique := ""
			if idx.Unique {
				unique = "UNIQUE "
			}
			colsQuoted := make([]string, 0, len(idx.Columns))
			for _, c := range idx.Columns {
				colsQuoted = append(colsQuoted, quoteIdent(c))
			}
			stmts = append(stmts, fmt.Sprintf(
				"CREATE %sINDEX IF NOT EXISTS %s ON %s.%s (%s)",
				unique,
				quoteIdent(idx.Name),
				quoteIdent(schema),
				quoteIdent(e.Table),
				strings.Join(colsQuoted, ", "),
			))
		}
	}
	return stmts, nil
}

func mapType(f blueprint.Field) (string, error) {
	switch f.Type {
	case "string":
		n := f.MaxLength
		if n <= 0 {
			n = 255
		}
		return fmt.Sprintf("VARCHAR(%d)", n), nil
	case "text":
		return "TEXT", nil
	case "int":
		return "INTEGER", nil
	case "bigint":
		return "BIGINT", nil
	case "decimal":
		p, s := f.Precision, f.Scale
		if p <= 0 {
			p = 18
		}
		if s < 0 {
			s = 2
		}
		return fmt.Sprintf("NUMERIC(%d,%d)", p, s), nil
	case "boolean":
		return "BOOLEAN", nil
	case "date":
		return "DATE", nil
	case "datetime":
		return "TIMESTAMPTZ", nil
	case "enum":
		return "VARCHAR(64)", nil
	case "json":
		return "JSONB", nil
	case "file_ref":
		return "VARCHAR(512)", nil
	default:
		return "", fmt.Errorf("unknown field type: %s", f.Type)
	}
}

func enumCheck(col string, values []string) string {
	quoted := make([]string, 0, len(values))
	for _, v := range values {
		quoted = append(quoted, "'"+strings.ReplaceAll(v, "'", "''")+"'")
	}
	return fmt.Sprintf("CHECK (%s IN (%s))", quoteIdent(col), strings.Join(quoted, ", "))
}

func quoteIdent(name string) string {
	// 调用方已白名单校验；仍用双引号包裹防止关键字冲突
	return `"` + strings.ReplaceAll(name, `"`, ``) + `"`
}