Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

32.3 KB
Raw Blame
package tidb

import (
	"fmt"
	"strings"

	metadatapb "github.com/bytebase/omni/metadata"

	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/schema"
)

func init() {
	schema.RegisterGenerateMigration(storepb.Engine_TIDB, generateMigration)
}

func generateMigration(diff *schema.MetadataDiff) (string, error) {
	var buf strings.Builder

	// TiDB doesn't have schemas like PostgreSQL, so we skip schema-level changes
	// We'll focus on table-level changes

	// Phase 1: Drop dependent objects first
	if err := dropObjectsInOrder(diff, &buf); err != nil {
		return "", err
	}

	// Only add blank line if we have drops AND we're about to create something
	dropPhaseHasContent := buf.Len() > 0
	createPhaseWillHaveContent := hasCreateOrAlterObjects(diff)

	if dropPhaseHasContent && createPhaseWillHaveContent {
		_, _ = buf.WriteString("\n")
	}

	// Phase 2: Create/Alter objects
	if err := createObjectsInOrder(diff, &buf); err != nil {
		return "", err
	}

	return buf.String(), nil
}

// dropObjectsInOrder drops all objects in the correct order
func dropObjectsInOrder(diff *schema.MetadataDiff, buf *strings.Builder) error {
	// Drop triggers first (they depend on tables)
	for _, tableDiff := range diff.TableChanges {
		if tableDiff.Action == schema.MetadataDiffActionDrop && tableDiff.OldTable != nil {
			for _, trigger := range tableDiff.OldTable.Triggers {
				if err := writeDropTrigger(buf, trigger.Name); err != nil {
					return err
				}
			}
		} else if tableDiff.Action == schema.MetadataDiffActionAlter {
			// Drop triggers that are being removed from altered tables
			for _, triggerDiff := range tableDiff.TriggerChanges {
				if triggerDiff.Action == schema.MetadataDiffActionDrop {
					if err := writeDropTrigger(buf, triggerDiff.OldTrigger.Name); err != nil {
						return err
					}
				}
			}
		}
	}

	// Drop foreign keys from tables being altered
	for _, tableDiff := range diff.TableChanges {
		if tableDiff.Action == schema.MetadataDiffActionAlter {
			for _, fkDiff := range tableDiff.ForeignKeyChanges {
				if fkDiff.Action == schema.MetadataDiffActionDrop {
					if err := writeDropForeignKey(buf, tableDiff.TableName, fkDiff.OldForeignKey.Name); err != nil {
						return err
					}
				}
			}
		}
	}

	// Create temporary views for views being dropped to handle dependencies
	for _, viewDiff := range diff.ViewChanges {
		if viewDiff.Action == schema.MetadataDiffActionDrop && viewDiff.OldView != nil {
			if err := writeTemporaryViewForDrop(buf, viewDiff.ViewName, viewDiff.OldView); err != nil {
				return err
			}
		}
	}

	// Drop events first (they can reference tables/views)
	for _, eventDiff := range diff.EventChanges {
		if eventDiff.Action == schema.MetadataDiffActionDrop {
			if err := writeDropEvent(buf, eventDiff.EventName); err != nil {
				return err
			}
		}
	}

	// Drop views
	for _, viewDiff := range diff.ViewChanges {
		if viewDiff.Action == schema.MetadataDiffActionDrop {
			if err := writeDropView(buf, viewDiff.ViewName); err != nil {
				return err
			}
		}
	}

	// Drop procedures (not supported in TiDB)
	for _, procDiff := range diff.ProcedureChanges {
		if procDiff.Action == schema.MetadataDiffActionDrop {
			if err := writeDropProcedure(buf, procDiff.ProcedureName); err != nil {
				return err
			}
		}
	}

	// Drop functions (not supported in TiDB)
	for _, funcDiff := range diff.FunctionChanges {
		if funcDiff.Action == schema.MetadataDiffActionDrop {
			if err := writeDropFunction(buf, funcDiff.FunctionName); err != nil {
				return err
			}
		}
	}

	// Drop sequences
	for _, seqDiff := range diff.SequenceChanges {
		if seqDiff.Action == schema.MetadataDiffActionDrop {
			if err := writeDropSequence(buf, seqDiff.SequenceName); err != nil {
				return err
			}
		}
	}

	// Drop tables
	for _, tableDiff := range diff.TableChanges {
		if tableDiff.Action == schema.MetadataDiffActionDrop {
			if err := writeDropTable(buf, tableDiff.TableName); err != nil {
				return err
			}
		}
	}

	// Handle ALTER table drops (constraints, indexes, columns)
	for _, tableDiff := range diff.TableChanges {
		if tableDiff.Action == schema.MetadataDiffActionAlter {
			// Drop check constraints
			for _, checkDiff := range tableDiff.CheckConstraintChanges {
				if checkDiff.Action == schema.MetadataDiffActionDrop {
					if err := writeDropCheckConstraint(buf, tableDiff.TableName, checkDiff.OldCheckConstraint.Name); err != nil {
						return err
					}
				}
			}

			// Drop indexes
			for _, indexDiff := range tableDiff.IndexChanges {
				if indexDiff.Action == schema.MetadataDiffActionDrop {
					if err := writeDropIndex(buf, tableDiff.TableName, indexDiff.OldIndex.Name); err != nil {
						return err
					}
				}
			}

			// Drop columns
			for _, colDiff := range tableDiff.ColumnChanges {
				if colDiff.Action == schema.MetadataDiffActionDrop {
					if err := writeDropColumn(buf, tableDiff.TableName, colDiff.OldColumn.Name); err != nil {
						return err
					}
				}
			}
		}
	}

	return nil
}

// createObjectsInOrder creates all objects in the correct order
func createObjectsInOrder(diff *schema.MetadataDiff, buf *strings.Builder) error {
	// Create tables (without foreign keys first)
	for _, tableDiff := range diff.TableChanges {
		if tableDiff.Action == schema.MetadataDiffActionCreate {
			if err := writeCreateTableWithoutForeignKeys(buf, tableDiff.TableName, tableDiff.NewTable); err != nil {
				return err
			}
		}
	}

	// Add foreign keys after all tables are created
	for _, tableDiff := range diff.TableChanges {
		if tableDiff.Action == schema.MetadataDiffActionCreate && tableDiff.NewTable != nil {
			for _, fk := range tableDiff.NewTable.ForeignKeys {
				if err := writeAddForeignKey(buf, tableDiff.TableName, fk); err != nil {
					return err
				}
			}
		}
	}

	// Handle ALTER table operations
	for _, tableDiff := range diff.TableChanges {
		if tableDiff.Action == schema.MetadataDiffActionAlter {
			if err := generateAlterTable(tableDiff, buf); err != nil {
				return err
			}
		}
	}

	// Create views
	for _, viewDiff := range diff.ViewChanges {
		switch viewDiff.Action {
		case schema.MetadataDiffActionCreate:
			if err := writeCreateView(buf, viewDiff.ViewName, viewDiff.NewView); err != nil {
				return err
			}
		case schema.MetadataDiffActionAlter:
			// TiDB supports CREATE OR REPLACE for views like MySQL
			if err := writeCreateOrReplaceView(buf, viewDiff.ViewName, viewDiff.NewView); err != nil {
				return err
			}
		default:
			// Ignore other view actions
		}
	}

	// Create sequences
	for _, seqDiff := range diff.SequenceChanges {
		if seqDiff.Action == schema.MetadataDiffActionCreate || seqDiff.Action == schema.MetadataDiffActionAlter {
			if err := writeSequenceDiff(buf, seqDiff); err != nil {
				return err
			}
		}
	}

	// Create functions (not supported in TiDB)
	for _, funcDiff := range diff.FunctionChanges {
		if funcDiff.Action == schema.MetadataDiffActionCreate || funcDiff.Action == schema.MetadataDiffActionAlter {
			if err := writeFunctionDiff(buf, funcDiff); err != nil {
				return err
			}
		}
	}

	// Create procedures (not supported in TiDB)
	for _, procDiff := range diff.ProcedureChanges {
		if procDiff.Action == schema.MetadataDiffActionCreate || procDiff.Action == schema.MetadataDiffActionAlter {
			if err := writeProcedureDiff(buf, procDiff); err != nil {
				return err
			}
		}
	}

	// Create events
	for _, eventDiff := range diff.EventChanges {
		if eventDiff.Action == schema.MetadataDiffActionCreate || eventDiff.Action == schema.MetadataDiffActionAlter {
			if err := writeEventDiff(buf, eventDiff); err != nil {
				return err
			}
		}
	}

	return nil
}

func generateAlterTable(tableDiff *schema.TableDiff, buf *strings.Builder) error {
	// Handle table comment changes first
	if tableDiff.OldTable != nil && tableDiff.NewTable != nil {
		if tableDiff.OldTable.Comment != tableDiff.NewTable.Comment {
			if err := writeAlterTableComment(buf, tableDiff.TableName, tableDiff.NewTable.Comment); err != nil {
				return err
			}
		}
	}

	// Add columns first
	for _, colDiff := range tableDiff.ColumnChanges {
		if colDiff.Action == schema.MetadataDiffActionCreate {
			if err := writeAddColumn(buf, tableDiff.TableName, colDiff.NewColumn); err != nil {
				return err
			}
		}
	}

	// Modify columns
	for _, colDiff := range tableDiff.ColumnChanges {
		if colDiff.Action == schema.MetadataDiffActionAlter {
			if err := writeModifyColumn(buf, tableDiff.TableName, colDiff.NewColumn); err != nil {
				return err
			}
		}
	}

	// Add indexes
	for _, indexDiff := range tableDiff.IndexChanges {
		if indexDiff.Action == schema.MetadataDiffActionCreate {
			if indexDiff.NewIndex.Primary {
				if err := writeAddPrimaryKey(buf, tableDiff.TableName, indexDiff.NewIndex); err != nil {
					return err
				}
			} else if indexDiff.NewIndex.Unique {
				if err := writeAddUniqueKey(buf, tableDiff.TableName, indexDiff.NewIndex); err != nil {
					return err
				}
			} else {
				if err := writeCreateIndex(buf, tableDiff.TableName, indexDiff.NewIndex); err != nil {
					return err
				}
			}
		}
	}

	// Add check constraints
	for _, checkDiff := range tableDiff.CheckConstraintChanges {
		if checkDiff.Action == schema.MetadataDiffActionCreate {
			if err := writeAddCheckConstraint(buf, tableDiff.TableName, checkDiff.NewCheckConstraint); err != nil {
				return err
			}
		}
	}

	// Add foreign keys last
	for _, fkDiff := range tableDiff.ForeignKeyChanges {
		if fkDiff.Action == schema.MetadataDiffActionCreate {
			if err := writeAddForeignKey(buf, tableDiff.TableName, fkDiff.NewForeignKey); err != nil {
				return err
			}
		}
	}

	// Add triggers last
	for _, triggerDiff := range tableDiff.TriggerChanges {
		if triggerDiff.Action == schema.MetadataDiffActionCreate {
			if err := writeCreateTrigger(buf, tableDiff.TableName, triggerDiff.NewTrigger); err != nil {
				return err
			}
		}
	}

	return nil
}

// Write functions for various DDL statements

func writeAlterTableComment(buf *strings.Builder, tableName, comment string) error {
	_, _ = buf.WriteString("ALTER TABLE `")
	_, _ = buf.WriteString(tableName)
	_, _ = buf.WriteString("` COMMENT = '")
	_, _ = buf.WriteString(comment)
	_, _ = buf.WriteString("';\n\n")
	return nil
}

func writeDropTrigger(buf *strings.Builder, trigger string) error {
	_, _ = buf.WriteString("DROP TRIGGER IF EXISTS `")
	_, _ = buf.WriteString(trigger)
	_, _ = buf.WriteString("`;\n\n")
	return nil
}

func writeDropForeignKey(buf *strings.Builder, table, constraint string) error {
	_, _ = buf.WriteString("ALTER TABLE `")
	_, _ = buf.WriteString(table)
	_, _ = buf.WriteString("` DROP FOREIGN KEY `")
	_, _ = buf.WriteString(constraint)
	_, _ = buf.WriteString("`;\n\n")
	return nil
}

func writeDropView(buf *strings.Builder, view string) error {
	_, _ = buf.WriteString("DROP VIEW IF EXISTS `")
	_, _ = buf.WriteString(view)
	_, _ = buf.WriteString("`;\n\n")
	return nil
}

func writeDropProcedure(buf *strings.Builder, procedure string) error {
	_, _ = buf.WriteString("DROP PROCEDURE IF EXISTS `")
	_, _ = buf.WriteString(procedure)
	_, _ = buf.WriteString("`;\n\n")
	return nil
}

func writeDropFunction(buf *strings.Builder, function string) error {
	_, _ = buf.WriteString("DROP FUNCTION IF EXISTS `")
	_, _ = buf.WriteString(function)
	_, _ = buf.WriteString("`;\n\n")
	return nil
}

func writeDropEvent(buf *strings.Builder, event string) error {
	_, _ = buf.WriteString("DROP EVENT IF EXISTS `")
	_, _ = buf.WriteString(event)
	_, _ = buf.WriteString("`;\n\n")
	return nil
}

func writeDropTable(buf *strings.Builder, table string) error {
	_, _ = buf.WriteString("DROP TABLE IF EXISTS `")
	_, _ = buf.WriteString(table)
	_, _ = buf.WriteString("`;\n\n")
	return nil
}

func writeDropCheckConstraint(buf *strings.Builder, table, constraint string) error {
	_, _ = buf.WriteString("ALTER TABLE `")
	_, _ = buf.WriteString(table)
	_, _ = buf.WriteString("` DROP CHECK `")
	_, _ = buf.WriteString(constraint)
	_, _ = buf.WriteString("`;\n\n")
	return nil
}

func writeDropIndex(buf *strings.Builder, table, index string) error {
	_, _ = buf.WriteString("DROP INDEX `")
	_, _ = buf.WriteString(index)
	_, _ = buf.WriteString("` ON `")
	_, _ = buf.WriteString(table)
	_, _ = buf.WriteString("`;\n\n")
	return nil
}

func writeDropColumn(buf *strings.Builder, table, column string) error {
	_, _ = buf.WriteString("ALTER TABLE `")
	_, _ = buf.WriteString(table)
	_, _ = buf.WriteString("` DROP COLUMN `")
	_, _ = buf.WriteString(column)
	_, _ = buf.WriteString("`;\n\n")
	return nil
}

func writeCreateTableWithoutForeignKeys(buf *strings.Builder, tableName string, table *metadatapb.TableMetadata) error {
	_, _ = buf.WriteString("CREATE TABLE IF NOT EXISTS `")
	_, _ = buf.WriteString(tableName)
	_, _ = buf.WriteString("` (\n")

	// Write columns
	for i, col := range table.Columns {
		if i > 0 {
			_, _ = buf.WriteString(",\n")
		}
		_, _ = buf.WriteString("  `")
		_, _ = buf.WriteString(col.Name)
		_, _ = buf.WriteString("` ")
		_, _ = buf.WriteString(col.Type)

		if col.CharacterSet != "" {
			_, _ = buf.WriteString(" CHARACTER SET ")
			_, _ = buf.WriteString(col.CharacterSet)
		}
		if col.Collation != "" {
			_, _ = buf.WriteString(" COLLATE ")
			_, _ = buf.WriteString(col.Collation)
		}

		if !col.Nullable {
			_, _ = buf.WriteString(" NOT NULL")
		}

		// Handle AUTO_INCREMENT and AUTO_RANDOM (TiDB specific)
		if hasAutoIncrement(col) {
			_, _ = buf.WriteString(" AUTO_INCREMENT")
		} else if hasAutoRandom(col) {
			_, _ = buf.WriteString(" /*T![auto_rand] ")
			_, _ = buf.WriteString(col.GetDefault())
			_, _ = buf.WriteString(" */")
		} else if hasDefaultValue(col) && !hasAutoIncrement(col) && !hasAutoRandom(col) {
			if e := getDefaultExpression(col); e != "" {
				_, _ = buf.WriteString(" DEFAULT ")
				_, _ = buf.WriteString(e)
			}
		}

		// Handle ON UPDATE
		if col.OnUpdate != "" {
			_, _ = buf.WriteString(" ON UPDATE ")
			_, _ = buf.WriteString(col.OnUpdate)
		}

		if col.Comment != "" {
			_, _ = buf.WriteString(" COMMENT '")
			_, _ = buf.WriteString(col.Comment)
			_, _ = buf.WriteString("'")
		}
	}

	// Write primary key constraint inline if exists
	for _, index := range table.Indexes {
		if index.Primary {
			_, _ = buf.WriteString(",\n  PRIMARY KEY (")
			for i, expr := range index.Expressions {
				if i > 0 {
					_, _ = buf.WriteString(", ")
				}
				_, _ = buf.WriteString("`")
				_, _ = buf.WriteString(expr)
				_, _ = buf.WriteString("`")
			}
			_, _ = buf.WriteString(")")
			// Add TiDB specific clustered index comment
			if table.PrimaryKeyType != "" {
				_, _ = buf.WriteString(" /*T![clustered_index] ")
				_, _ = buf.WriteString(table.PrimaryKeyType)
				_, _ = buf.WriteString(" */")
			}
			break
		}
	}

	// Write unique constraints inline
	for _, index := range table.Indexes {
		if index.Unique && !index.Primary {
			_, _ = buf.WriteString(",\n  UNIQUE KEY `")
			_, _ = buf.WriteString(index.Name)
			_, _ = buf.WriteString("` (")
			for i, expr := range index.Expressions {
				if i > 0 {
					_, _ = buf.WriteString(", ")
				}
				_, _ = buf.WriteString("`")
				_, _ = buf.WriteString(expr)
				_, _ = buf.WriteString("`")
			}
			_, _ = buf.WriteString(")")
		}
	}

	// Write check constraints
	for _, check := range table.CheckConstraints {
		_, _ = buf.WriteString(",\n  CONSTRAINT `")
		_, _ = buf.WriteString(check.Name)
		_, _ = buf.WriteString("` CHECK (")
		_, _ = buf.WriteString(check.Expression)
		_, _ = buf.WriteString(")")
	}

	_, _ = buf.WriteString("\n)")

	// Write table options
	if table.Engine != "" {
		_, _ = buf.WriteString(" ENGINE=")
		_, _ = buf.WriteString(table.Engine)
	}
	if table.Charset != "" {
		_, _ = buf.WriteString(" DEFAULT CHARSET=")
		_, _ = buf.WriteString(table.Charset)
	}
	if table.Collation != "" {
		_, _ = buf.WriteString(" COLLATE=")
		_, _ = buf.WriteString(table.Collation)
	}

	// TiDB specific: Add SHARD_ROW_ID_BITS if present
	if strings.HasPrefix(table.ShardingInfo, "SHARD_BITS=") {
		_, _ = buf.WriteString(" /*T! ")
		_, _ = buf.WriteString(strings.ReplaceAll(table.ShardingInfo, "SHARD_BITS", "SHARD_ROW_ID_BITS"))
		_, _ = buf.WriteString(" */")
	}

	if table.Comment != "" {
		_, _ = buf.WriteString(" COMMENT='")
		_, _ = buf.WriteString(table.Comment)
		_, _ = buf.WriteString("'")
	}

	// Add partition clause if present
	if len(table.Partitions) > 0 {
		if err := writePartitionClause(buf, table.Partitions); err != nil {
			return err
		}
	}

	_, _ = buf.WriteString(";\n")

	// Create non-unique indexes separately
	for _, index := range table.Indexes {
		if !index.Primary && !index.Unique {
			if err := writeCreateIndex(buf, tableName, index); err != nil {
				return err
			}
		}
	}

	// Note: Foreign keys are NOT created here - they will be added separately

	return nil
}

// TiDB specific partition clause (similar to MySQL but with TiDB extensions)
func writePartitionClause(buf *strings.Builder, partitions []*metadatapb.TablePartitionMetadata) error {
	if len(partitions) == 0 {
		return nil
	}

	_, _ = buf.WriteString("\n/*T![auto_rand] PARTITION BY ")

	switch partitions[0].Type {
	case metadatapb.TablePartitionMetadata_RANGE:
		_, _ = fmt.Fprintf(buf, "RANGE (%s)", partitions[0].Expression)
	case metadatapb.TablePartitionMetadata_RANGE_COLUMNS:
		_, _ = fmt.Fprintf(buf, "RANGE COLUMNS (%s)", partitions[0].Expression)
	case metadatapb.TablePartitionMetadata_LIST:
		_, _ = fmt.Fprintf(buf, "LIST (%s)", partitions[0].Expression)
	case metadatapb.TablePartitionMetadata_LIST_COLUMNS:
		_, _ = fmt.Fprintf(buf, "LIST COLUMNS (%s)", partitions[0].Expression)
	case metadatapb.TablePartitionMetadata_HASH:
		_, _ = fmt.Fprintf(buf, "HASH (%s)", partitions[0].Expression)
	case metadatapb.TablePartitionMetadata_KEY:
		_, _ = fmt.Fprintf(buf, "KEY (%s)", partitions[0].Expression)
	default:
		// Unsupported partition type
	}

	// Add partitions count if specified
	if partitions[0].UseDefault != "" && partitions[0].UseDefault != "0" {
		_, _ = fmt.Fprintf(buf, "\nPARTITIONS %s", partitions[0].UseDefault)
	} else {
		// Write individual partitions
		_, _ = buf.WriteString("\n(")
		for i, partition := range partitions {
			if i > 0 {
				_, _ = buf.WriteString(",\n ")
			}
			_, _ = fmt.Fprintf(buf, "PARTITION %s", partition.Name)
			if partition.Value != "" {
				switch partitions[0].Type {
				case metadatapb.TablePartitionMetadata_RANGE, metadatapb.TablePartitionMetadata_RANGE_COLUMNS:
					if partition.Value != "MAXVALUE" {
						_, _ = fmt.Fprintf(buf, " VALUES LESS THAN (%s)", partition.Value)
					} else {
						_, _ = fmt.Fprintf(buf, " VALUES LESS THAN %s", partition.Value)
					}
				case metadatapb.TablePartitionMetadata_LIST, metadatapb.TablePartitionMetadata_LIST_COLUMNS:
					_, _ = fmt.Fprintf(buf, " VALUES IN (%s)", partition.Value)
				default:
					// No VALUES clause for other partition types like HASH/KEY
				}
			}
		}
		_, _ = buf.WriteString(")")
	}

	_, _ = buf.WriteString(" */")
	return nil
}

func writeAddColumn(buf *strings.Builder, table string, column *metadatapb.ColumnMetadata) error {
	_, _ = buf.WriteString("ALTER TABLE `")
	_, _ = buf.WriteString(table)
	_, _ = buf.WriteString("` ADD COLUMN `")
	_, _ = buf.WriteString(column.Name)
	_, _ = buf.WriteString("` ")
	_, _ = buf.WriteString(column.Type)

	if column.CharacterSet != "" {
		_, _ = buf.WriteString(" CHARACTER SET ")
		_, _ = buf.WriteString(column.CharacterSet)
	}
	if column.Collation != "" {
		_, _ = buf.WriteString(" COLLATE ")
		_, _ = buf.WriteString(column.Collation)
	}

	if !column.Nullable {
		_, _ = buf.WriteString(" NOT NULL")
	}

	// Handle AUTO_INCREMENT and AUTO_RANDOM (TiDB specific)
	if hasAutoIncrement(column) {
		_, _ = buf.WriteString(" AUTO_INCREMENT")
	} else if hasAutoRandom(column) {
		_, _ = buf.WriteString(" /*T![auto_rand] ")
		_, _ = buf.WriteString(column.GetDefault())
		_, _ = buf.WriteString(" */")
	} else if hasDefaultValue(column) && !hasAutoIncrement(column) && !hasAutoRandom(column) {
		_, _ = buf.WriteString(" DEFAULT ")
		_, _ = buf.WriteString(getDefaultExpression(column))
	}

	// Handle ON UPDATE
	if column.OnUpdate != "" {
		_, _ = buf.WriteString(" ON UPDATE ")
		_, _ = buf.WriteString(column.OnUpdate)
	}

	if column.Comment != "" {
		_, _ = buf.WriteString(" COMMENT '")
		_, _ = buf.WriteString(column.Comment)
		_, _ = buf.WriteString("'")
	}

	_, _ = buf.WriteString(";\n")
	return nil
}

func writeModifyColumn(buf *strings.Builder, table string, column *metadatapb.ColumnMetadata) error {
	_, _ = buf.WriteString("ALTER TABLE `")
	_, _ = buf.WriteString(table)
	_, _ = buf.WriteString("` MODIFY COLUMN `")
	_, _ = buf.WriteString(column.Name)
	_, _ = buf.WriteString("` ")
	_, _ = buf.WriteString(column.Type)

	if column.CharacterSet != "" {
		_, _ = buf.WriteString(" CHARACTER SET ")
		_, _ = buf.WriteString(column.CharacterSet)
	}
	if column.Collation != "" {
		_, _ = buf.WriteString(" COLLATE ")
		_, _ = buf.WriteString(column.Collation)
	}

	if !column.Nullable {
		_, _ = buf.WriteString(" NOT NULL")
	}

	// Handle AUTO_INCREMENT and AUTO_RANDOM (TiDB specific)
	if hasAutoIncrement(column) {
		_, _ = buf.WriteString(" AUTO_INCREMENT")
	} else if hasAutoRandom(column) {
		_, _ = buf.WriteString(" /*T![auto_rand] ")
		_, _ = buf.WriteString(column.GetDefault())
		_, _ = buf.WriteString(" */")
	} else if hasDefaultValue(column) && !hasAutoIncrement(column) && !hasAutoRandom(column) {
		_, _ = buf.WriteString(" DEFAULT ")
		_, _ = buf.WriteString(getDefaultExpression(column))
	}

	// Handle ON UPDATE
	if column.OnUpdate != "" {
		_, _ = buf.WriteString(" ON UPDATE ")
		_, _ = buf.WriteString(column.OnUpdate)
	}

	if column.Comment != "" {
		_, _ = buf.WriteString(" COMMENT '")
		_, _ = buf.WriteString(column.Comment)
		_, _ = buf.WriteString("'")
	}

	_, _ = buf.WriteString(";\n")
	return nil
}

func writeCreateIndex(buf *strings.Builder, table string, index *metadatapb.IndexMetadata) error {
	_, _ = buf.WriteString("CREATE ")
	// Handle special index types
	if strings.ToUpper(index.Type) == "FULLTEXT" {
		_, _ = buf.WriteString("FULLTEXT ")
	} else if strings.ToUpper(index.Type) == "SPATIAL" {
		_, _ = buf.WriteString("SPATIAL ")
	}

	_, _ = buf.WriteString("INDEX `")
	_, _ = buf.WriteString(index.Name)
	_, _ = buf.WriteString("` ON `")
	_, _ = buf.WriteString(table)
	_, _ = buf.WriteString("` (")

	for i, expr := range index.Expressions {
		if i > 0 {
			_, _ = buf.WriteString(", ")
		}
		_, _ = buf.WriteString("`")
		_, _ = buf.WriteString(expr)
		_, _ = buf.WriteString("`")

		// Handle column length for text/blob columns
		if i < len(index.KeyLength) && index.KeyLength[i] > 0 {
			_, _ = fmt.Fprintf(buf, "(%d)", index.KeyLength[i])
		}
	}

	_, _ = buf.WriteString(")")

	// Add index type if specified and not default
	if index.Type != "" && index.Type != "BTREE" &&
		strings.ToUpper(index.Type) != "FULLTEXT" &&
		strings.ToUpper(index.Type) != "SPATIAL" {
		_, _ = buf.WriteString(" USING ")
		_, _ = buf.WriteString(index.Type)
	}

	if index.Comment != "" {
		_, _ = buf.WriteString(" COMMENT '")
		_, _ = buf.WriteString(index.Comment)
		_, _ = buf.WriteString("'")
	}

	_, _ = buf.WriteString(";\n")
	return nil
}

func writeAddPrimaryKey(buf *strings.Builder, table string, index *metadatapb.IndexMetadata) error {
	_, _ = buf.WriteString("ALTER TABLE `")
	_, _ = buf.WriteString(table)
	_, _ = buf.WriteString("` ADD PRIMARY KEY (")

	for i, expr := range index.Expressions {
		if i > 0 {
			_, _ = buf.WriteString(", ")
		}
		_, _ = buf.WriteString("`")
		_, _ = buf.WriteString(expr)
		_, _ = buf.WriteString("`")
	}

	_, _ = buf.WriteString(");\n")
	return nil
}

func writeAddUniqueKey(buf *strings.Builder, table string, index *metadatapb.IndexMetadata) error {
	_, _ = buf.WriteString("ALTER TABLE `")
	_, _ = buf.WriteString(table)
	_, _ = buf.WriteString("` ADD UNIQUE KEY `")
	_, _ = buf.WriteString(index.Name)
	_, _ = buf.WriteString("` (")

	for i, expr := range index.Expressions {
		if i > 0 {
			_, _ = buf.WriteString(", ")
		}
		_, _ = buf.WriteString("`")
		_, _ = buf.WriteString(expr)
		_, _ = buf.WriteString("`")
	}

	_, _ = buf.WriteString(");\n")
	return nil
}

func writeAddCheckConstraint(buf *strings.Builder, table string, check *metadatapb.CheckConstraintMetadata) error {
	_, _ = buf.WriteString("ALTER TABLE `")
	_, _ = buf.WriteString(table)
	_, _ = buf.WriteString("` ADD CONSTRAINT `")
	_, _ = buf.WriteString(check.Name)
	_, _ = buf.WriteString("` CHECK (")
	_, _ = buf.WriteString(check.Expression)
	_, _ = buf.WriteString(");\n")
	return nil
}

func writeAddForeignKey(buf *strings.Builder, table string, fk *metadatapb.ForeignKeyMetadata) error {
	_, _ = buf.WriteString("ALTER TABLE `")
	_, _ = buf.WriteString(table)
	_, _ = buf.WriteString("` ADD CONSTRAINT `")
	_, _ = buf.WriteString(fk.Name)
	_, _ = buf.WriteString("` FOREIGN KEY (")

	for i, col := range fk.Columns {
		if i > 0 {
			_, _ = buf.WriteString(", ")
		}
		_, _ = buf.WriteString("`")
		_, _ = buf.WriteString(col)
		_, _ = buf.WriteString("`")
	}

	_, _ = buf.WriteString(") REFERENCES `")
	_, _ = buf.WriteString(fk.ReferencedTable)
	_, _ = buf.WriteString("` (")

	for i, col := range fk.ReferencedColumns {
		if i > 0 {
			_, _ = buf.WriteString(", ")
		}
		_, _ = buf.WriteString("`")
		_, _ = buf.WriteString(col)
		_, _ = buf.WriteString("`")
	}

	_, _ = buf.WriteString(")")

	if fk.OnUpdate != "" {
		_, _ = buf.WriteString(" ON UPDATE ")
		_, _ = buf.WriteString(fk.OnUpdate)
	}
	if fk.OnDelete != "" {
		_, _ = buf.WriteString(" ON DELETE ")
		_, _ = buf.WriteString(fk.OnDelete)
	}

	_, _ = buf.WriteString(";\n")
	return nil
}

func writeCreateView(buf *strings.Builder, viewName string, view *metadatapb.ViewMetadata) error {
	_, _ = buf.WriteString("CREATE VIEW `")
	_, _ = buf.WriteString(viewName)
	_, _ = buf.WriteString("` AS ")
	_, _ = buf.WriteString(view.Definition)

	if !strings.HasSuffix(strings.TrimSpace(view.Definition), ";") {
		_, _ = buf.WriteString(";")
	}
	_, _ = buf.WriteString("\n")
	return nil
}

func writeCreateOrReplaceView(buf *strings.Builder, viewName string, view *metadatapb.ViewMetadata) error {
	_, _ = buf.WriteString("CREATE OR REPLACE VIEW `")
	_, _ = buf.WriteString(viewName)
	_, _ = buf.WriteString("` AS ")
	_, _ = buf.WriteString(view.Definition)

	if !strings.HasSuffix(strings.TrimSpace(view.Definition), ";") {
		_, _ = buf.WriteString(";")
	}
	_, _ = buf.WriteString("\n")
	return nil
}

func writeFunctionDiff(buf *strings.Builder, funcDiff *schema.FunctionDiff) error {
	if funcDiff.Action == schema.MetadataDiffActionCreate {
		_, _ = buf.WriteString(funcDiff.NewFunction.Definition)
		if !strings.HasSuffix(strings.TrimSpace(funcDiff.NewFunction.Definition), ";") {
			_, _ = buf.WriteString(";")
		}
		_, _ = buf.WriteString("\n")
	}
	return nil
}

func writeProcedureDiff(buf *strings.Builder, procDiff *schema.ProcedureDiff) error {
	if procDiff.Action == schema.MetadataDiffActionCreate {
		_, _ = buf.WriteString(procDiff.NewProcedure.Definition)
		if !strings.HasSuffix(strings.TrimSpace(procDiff.NewProcedure.Definition), ";") {
			_, _ = buf.WriteString(";")
		}
		_, _ = buf.WriteString("\n")
	}
	return nil
}

func writeEventDiff(buf *strings.Builder, eventDiff *schema.EventDiff) error {
	if eventDiff.Action == schema.MetadataDiffActionCreate || eventDiff.Action == schema.MetadataDiffActionAlter {
		_, _ = buf.WriteString(eventDiff.NewEvent.Definition)
		if !strings.HasSuffix(strings.TrimSpace(eventDiff.NewEvent.Definition), ";") {
			_, _ = buf.WriteString(";")
		}
		_, _ = buf.WriteString("\n")
	}
	return nil
}

// Helper functions

func hasCreateOrAlterObjects(diff *schema.MetadataDiff) bool {
	for _, tableDiff := range diff.TableChanges {
		if tableDiff.Action == schema.MetadataDiffActionCreate || tableDiff.Action == schema.MetadataDiffActionAlter {
			return true
		}
	}
	for _, viewDiff := range diff.ViewChanges {
		if viewDiff.Action == schema.MetadataDiffActionCreate || viewDiff.Action == schema.MetadataDiffActionAlter {
			return true
		}
	}
	for _, seqDiff := range diff.SequenceChanges {
		if seqDiff.Action == schema.MetadataDiffActionCreate || seqDiff.Action == schema.MetadataDiffActionAlter {
			return true
		}
	}
	for _, funcDiff := range diff.FunctionChanges {
		if funcDiff.Action == schema.MetadataDiffActionCreate || funcDiff.Action == schema.MetadataDiffActionAlter {
			return true
		}
	}
	for _, procDiff := range diff.ProcedureChanges {
		if procDiff.Action == schema.MetadataDiffActionCreate || procDiff.Action == schema.MetadataDiffActionAlter {
			return true
		}
	}
	for _, eventDiff := range diff.EventChanges {
		if eventDiff.Action == schema.MetadataDiffActionCreate || eventDiff.Action == schema.MetadataDiffActionAlter {
			return true
		}
	}
	return false
}

func getDefaultExpression(column *metadatapb.ColumnMetadata) string {
	if column == nil {
		return ""
	}

	if column.GetDefault() != "" {
		return column.GetDefault()
	}

	return ""
}

func hasDefaultValue(column *metadatapb.ColumnMetadata) bool {
	if column == nil {
		return false
	}

	// Don't treat AUTO_INCREMENT as a default value
	if strings.EqualFold(column.GetDefault(), "AUTO_INCREMENT") || strings.HasPrefix(column.GetDefault(), "AUTO_RANDOM") {
		return false
	}

	return column.Default != ""
}

func hasAutoIncrement(column *metadatapb.ColumnMetadata) bool {
	if column == nil {
		return false
	}

	// Check if column has AUTO_INCREMENT in default field
	return strings.EqualFold(column.GetDefault(), "AUTO_INCREMENT")
}

func hasAutoRandom(column *metadatapb.ColumnMetadata) bool {
	if column == nil {
		return false
	}
	return strings.HasPrefix(column.GetDefault(), "AUTO_RANDOM")
}

func writeTemporaryViewForDrop(buf *strings.Builder, viewName string, view *metadatapb.ViewMetadata) error {
	// Create a temporary view with SELECT 1 AS column_name structure
	// to satisfy other views that depend on this view
	_, _ = buf.WriteString("CREATE OR REPLACE VIEW `")
	_, _ = buf.WriteString(viewName)
	_, _ = buf.WriteString("` AS SELECT")

	for i, column := range view.Columns {
		if i > 0 {
			_, _ = buf.WriteString(",")
		}
		_, _ = buf.WriteString(" 1 AS `")
		_, _ = buf.WriteString(column.Name)
		_, _ = buf.WriteString("`")
	}

	// If no columns, create a dummy view
	if len(view.Columns) == 0 {
		_, _ = buf.WriteString(" 1")
	}

	_, _ = buf.WriteString(";\n")
	return nil
}

func writeCreateTrigger(buf *strings.Builder, tableName string, trigger *metadatapb.TriggerMetadata) error {
	// Construct the complete trigger statement
	_, _ = buf.WriteString("CREATE TRIGGER `")
	_, _ = buf.WriteString(trigger.Name)
	_, _ = buf.WriteString("` ")
	_, _ = buf.WriteString(trigger.Timing)
	_, _ = buf.WriteString(" ")
	_, _ = buf.WriteString(trigger.Event)
	_, _ = buf.WriteString(" ON `")
	_, _ = buf.WriteString(tableName)
	_, _ = buf.WriteString("` FOR EACH ROW ")
	_, _ = buf.WriteString(trigger.Body)
	if !strings.HasSuffix(strings.TrimSpace(trigger.Body), ";") {
		_, _ = buf.WriteString(";")
	}
	_, _ = buf.WriteString("\n")
	return nil
}

func writeDropSequence(buf *strings.Builder, sequence string) error {
	_, _ = buf.WriteString("DROP SEQUENCE IF EXISTS `")
	_, _ = buf.WriteString(sequence)
	_, _ = buf.WriteString("`;\n\n")
	return nil
}

func writeSequenceDiff(buf *strings.Builder, seqDiff *schema.SequenceDiff) error {
	switch seqDiff.Action {
	case schema.MetadataDiffActionCreate:
		return writeCreateSequence(buf, seqDiff.NewSequence)
	case schema.MetadataDiffActionAlter:
		return writeAlterSequence(buf, seqDiff.SequenceName, seqDiff.NewSequence)
	default:
		// Ignore other sequence actions
	}
	return nil
}

func writeCreateSequence(buf *strings.Builder, sequence *metadatapb.SequenceMetadata) error {
	_, _ = buf.WriteString("CREATE SEQUENCE `")
	_, _ = buf.WriteString(sequence.Name)
	_, _ = buf.WriteString("`")

	if sequence.Start != "" && sequence.Start != "1" {
		_, _ = buf.WriteString(" START ")
		_, _ = buf.WriteString(sequence.Start)
	}

	if sequence.Increment != "" && sequence.Increment != "1" {
		_, _ = buf.WriteString(" INCREMENT ")
		_, _ = buf.WriteString(sequence.Increment)
	}

	if sequence.MinValue != "" {
		_, _ = buf.WriteString(" MINVALUE ")
		_, _ = buf.WriteString(sequence.MinValue)
	}

	if sequence.MaxValue != "" {
		_, _ = buf.WriteString(" MAXVALUE ")
		_, _ = buf.WriteString(sequence.MaxValue)
	}

	if sequence.CacheSize != "" && sequence.CacheSize != "1" {
		_, _ = buf.WriteString(" CACHE ")
		_, _ = buf.WriteString(sequence.CacheSize)
	}

	if sequence.Cycle {
		_, _ = buf.WriteString(" CYCLE")
	} else {
		_, _ = buf.WriteString(" NOCYCLE")
	}

	if sequence.Comment != "" {
		_, _ = buf.WriteString(" COMMENT '")
		_, _ = buf.WriteString(sequence.Comment)
		_, _ = buf.WriteString("'")
	}

	_, _ = buf.WriteString(";\n")
	return nil
}

func writeAlterSequence(buf *strings.Builder, sequenceName string, sequence *metadatapb.SequenceMetadata) error {
	// TiDB does not support ALTER SEQUENCE, so we need to DROP and CREATE
	if err := writeDropSequence(buf, sequenceName); err != nil {
		return err
	}
	return writeCreateSequence(buf, sequence)
}