Skip to content

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

134 KB
Raw Blame
package pg

import (
	"fmt"
	"io"
	"slices"
	"strings"

	"github.com/pkg/errors"
	"google.golang.org/protobuf/proto"

	metadatapb "github.com/bytebase/omni/metadata"
	omnipg "github.com/bytebase/omni/pg"
	"github.com/bytebase/omni/pg/ast"

	"github.com/bytebase/bytebase/backend/common"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	parserbase "github.com/bytebase/bytebase/backend/plugin/parser/base"
	"github.com/bytebase/bytebase/backend/plugin/schema"
)

const (
	header = `
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

`

	setDefaultTableSpace = "SET default_tablespace = '';\n\n"
)

func init() {
	for _, engine := range []storepb.Engine{storepb.Engine_POSTGRES, storepb.Engine_COCKROACHDB} {
		schema.RegisterGetDatabaseDefinition(engine, GetDatabaseDefinition)
		schema.RegisterGetSchemaDefinition(engine, GetSchemaDefinition)
		schema.RegisterGetTableDefinition(engine, GetTableDefinition)
		schema.RegisterGetViewDefinition(engine, GetViewDefinition)
		schema.RegisterGetMaterializedViewDefinition(engine, GetMaterializedViewDefinition)
		schema.RegisterGetFunctionDefinition(engine, GetFunctionDefinition)
		schema.RegisterGetSequenceDefinition(engine, GetSequenceDefinition)
		schema.RegisterGetMultiFileDatabaseDefinition(engine, GetMultiFileDatabaseDefinition)
	}
}

func GetDatabaseDefinition(ctx schema.GetDefinitionContext, metadata *metadatapb.DatabaseSchemaMetadata) (string, error) {
	metadata = filterBackupSchemaIfNecessary(ctx, metadata)

	// Clone before mutating: the caller's *DatabaseSchemaMetadata is often a
	// shared pointer returned from store.dbSchemaCache (see
	// backend/store/model/database.go:GetProto), so concurrent callers could
	// race on in-place normalization writes below.
	metadata = proto.CloneOf(metadata)

	// Repair historical non-canonical metadata shapes (e.g. index key
	// expressions stored without outer parens) so emission produces valid SQL.
	// See legacy_normalize.go for removal criteria.
	normalizeLegacyMetadata(metadata)

	if len(metadata.Schemas) == 0 {
		return "", nil
	}

	var buf strings.Builder

	if ctx.SDLFormat {
		return getSDLFormat(metadata)
	}

	if ctx.PrintHeader {
		if _, err := buf.WriteString(header); err != nil {
			return "", err
		}
	}

	// Construct schemas.
	for _, schema := range metadata.Schemas {
		if schema.SkipDump {
			continue
		}
		if err := writeSchema(&buf, schema); err != nil {
			return "", err
		}
	}

	// Construct extensions.
	for _, extension := range metadata.Extensions {
		if err := writeExtension(&buf, extension); err != nil {
			return "", err
		}
	}

	// Construct enums.
	for _, schema := range metadata.Schemas {
		for _, enum := range schema.EnumTypes {
			if enum.SkipDump {
				continue
			}
			if err := writeEnum(&buf, schema.Name, enum); err != nil {
				return "", err
			}
			if _, err := buf.WriteString(";\n\n"); err != nil {
				return "", err
			}
			// Write enum comment if present
			if len(enum.Comment) > 0 {
				if err := writeEnumComment(&buf, schema.Name, enum); err != nil {
					return "", err
				}
			}
		}
	}

	// Construct composite types.
	if err := writeCompositeTypesSection(&buf, collectCompositeTypes(metadata.Schemas)); err != nil {
		return "", err
	}

	// Build the graph for topological sort.
	graph := parserbase.NewGraph()
	functionMap := make(map[string]*metadatapb.FunctionMetadata)
	tableMap := make(map[string]*metadatapb.TableMetadata)
	viewMap := make(map[string]*metadatapb.ViewMetadata)
	materializedViewMap := make(map[string]*metadatapb.MaterializedViewMetadata)

	// Construct functions.
	for _, schema := range metadata.Schemas {
		for _, function := range schema.Functions {
			if function.SkipDump {
				continue
			}
			funcID := getObjectID(schema.Name, funcIdentity(function))
			functionMap[funcID] = function
			graph.AddNode(funcID)
			for _, dependency := range function.DependencyTables {
				dependencyID := getObjectID(dependency.Schema, dependency.Table)
				graph.AddEdge(dependencyID, funcID)
			}
		}
	}

	// Create non-identity sequences before tables to prevent errors when tables reference
	// sequences owned by other tables. Identity sequences are created via writeColumnIdentityGeneration.
	// Build identity column map once for O(m*c) instead of O(s*m*c) when checking each sequence.
	identityColumnMap := buildIdentityColumnMap(metadata)

	sequenceOwnershipMap := make(map[string][]*metadatapb.SequenceMetadata)
	for _, schema := range metadata.Schemas {
		for _, sequence := range schema.Sequences {
			if sequence.SkipDump {
				continue
			}
			// Check if this sequence belongs to an identity column using the prebuilt map
			isIdentity := false
			if sequence.OwnerTable != "" && sequence.OwnerColumn != "" {
				key := schema.Name + "." + sequence.OwnerTable + "." + sequence.OwnerColumn
				isIdentity = identityColumnMap[key]
			}

			if isIdentity {
				tableID := getObjectID(schema.Name, sequence.OwnerTable)
				sequenceOwnershipMap[tableID] = append(sequenceOwnershipMap[tableID], sequence)
				continue
			}
			if err := writeCreateSequence(&buf, schema.Name, sequence); err != nil {
				return "", err
			}
			if sequence.OwnerTable != "" && sequence.OwnerColumn != "" {
				tableID := getObjectID(schema.Name, sequence.OwnerTable)
				sequenceOwnershipMap[tableID] = append(sequenceOwnershipMap[tableID], sequence)
			}
		}
	}

	if ctx.PrintHeader {
		if _, err := buf.WriteString(setDefaultTableSpace); err != nil {
			return "", err
		}
	}

	// Construct tables, views and materialized views.
	for _, schema := range metadata.Schemas {
		for _, table := range schema.Tables {
			if table.SkipDump {
				continue
			}
			tableID := getObjectID(schema.Name, table.Name)
			tableMap[tableID] = table
			graph.AddNode(tableID)
		}

		for _, view := range schema.Views {
			if view.SkipDump {
				continue
			}
			viewID := getObjectID(schema.Name, view.Name)
			viewMap[viewID] = view
			graph.AddNode(viewID)
			for _, dependency := range view.DependencyColumns {
				dependencyID := getObjectID(dependency.Schema, dependency.Table)
				graph.AddEdge(dependencyID, viewID)
			}
		}

		for _, view := range schema.MaterializedViews {
			if view.SkipDump {
				continue
			}
			viewID := getObjectID(schema.Name, view.Name)
			materializedViewMap[viewID] = view
			graph.AddNode(viewID)
			for _, dependency := range view.DependencyColumns {
				dependencyID := getObjectID(dependency.Schema, dependency.Table)
				graph.AddEdge(dependencyID, viewID)
			}
		}
	}

	orderedList, err := graph.TopologicalSort()
	if err != nil {
		// If topological sort fails (e.g., due to circular dependencies),
		// fall back to a safe dependency order: tables -> views -> materialized views -> functions
		orderedList = make([]string, 0, len(functionMap)+len(tableMap)+len(viewMap)+len(materializedViewMap))

		// Add tables first (sorted alphabetically within each category)
		var tableIDs []string
		for id := range tableMap {
			tableIDs = append(tableIDs, id)
		}
		slices.Sort(tableIDs)
		orderedList = append(orderedList, tableIDs...)

		// Add views second
		var viewIDs []string
		for id := range viewMap {
			viewIDs = append(viewIDs, id)
		}
		slices.Sort(viewIDs)
		orderedList = append(orderedList, viewIDs...)

		// Add materialized views third
		var materializedViewIDs []string
		for id := range materializedViewMap {
			materializedViewIDs = append(materializedViewIDs, id)
		}
		slices.Sort(materializedViewIDs)
		orderedList = append(orderedList, materializedViewIDs...)

		// Add functions last
		var functionIDs []string
		for id := range functionMap {
			functionIDs = append(functionIDs, id)
		}
		slices.Sort(functionIDs)
		orderedList = append(orderedList, functionIDs...)
	}

	// Construct functions, tables, views and materialized views in order.
	for _, objectID := range orderedList {
		if function, ok := functionMap[objectID]; ok {
			if err := writeFunction(&buf, getSchemaNameFromID(objectID), function); err != nil {
				return "", err
			}
			continue
		}
		if table, ok := tableMap[objectID]; ok {
			if err := writeTable(&buf, getSchemaNameFromID(objectID), table, sequenceOwnershipMap[objectID]); err != nil {
				return "", err
			}
		}
		if view, ok := viewMap[objectID]; ok {
			if err := writeView(&buf, getSchemaNameFromID(objectID), view); err != nil {
				return "", err
			}
			continue
		}
		if view, ok := materializedViewMap[objectID]; ok {
			if err := writeMaterializedView(&buf, getSchemaNameFromID(objectID), view); err != nil {
				return "", err
			}
		}
	}

	// Construct triggers.
	for _, schema := range metadata.Schemas {
		if err := writeSchemaTriggers(&buf, schema); err != nil {
			return "", err
		}
	}

	// Construct rules.
	for _, schema := range metadata.Schemas {
		if err := writeSchemaRules(&buf, schema); err != nil {
			return "", err
		}
	}

	// Construct foreign keys.
	tablesMissingColumns := collectTables(metadata.Schemas, tableMissingColumnMetadata)
	for _, schema := range metadata.Schemas {
		if err := writeSchemaForeignKeys(&buf, schema, tablesMissingColumns); err != nil {
			return "", err
		}
	}

	// Construct event triggers (last, as they depend on functions).
	for _, eventTrigger := range metadata.EventTriggers {
		if eventTrigger.SkipDump {
			continue
		}
		if err := writeEventTrigger(&buf, eventTrigger); err != nil {
			return "", err
		}
	}

	return buf.String(), nil
}

func GetSchemaSDLDefinition(schema *metadatapb.SchemaMetadata) (string, error) {
	// Create a temporary database metadata containing just this schema
	tempMetadata := &metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{schema},
	}

	return getSDLFormat(tempMetadata)
}

func GetSchemaDefinition(schema *metadatapb.SchemaMetadata) (string, error) {
	var buf strings.Builder
	if err := writeSchema(&buf, schema); err != nil {
		return "", err
	}

	// Construct enums.
	for _, enum := range schema.EnumTypes {
		if enum.SkipDump {
			continue
		}
		if err := writeEnum(&buf, schema.Name, enum); err != nil {
			return "", err
		}
		if _, err := buf.WriteString(";\n\n"); err != nil {
			return "", err
		}
		// Write enum comment if present
		if len(enum.Comment) > 0 {
			if err := writeEnumComment(&buf, schema.Name, enum); err != nil {
				return "", err
			}
		}
	}

	// Construct composite types. The schema-level SkipDump filter does not
	// apply here: the caller asked for this specific schema's definition.
	var compositeTypes []qualifiedCompositeType
	for _, composite := range schema.CompositeTypes {
		if composite.SkipDump {
			continue
		}
		compositeTypes = append(compositeTypes, qualifiedCompositeType{Schema: schema.Name, Composite: composite})
	}
	if err := writeCompositeTypesSection(&buf, compositeTypes); err != nil {
		return "", err
	}

	// Build the graph for topological sort.
	graph := parserbase.NewGraph()
	functionMap := make(map[string]*metadatapb.FunctionMetadata)
	tableMap := make(map[string]*metadatapb.TableMetadata)
	viewMap := make(map[string]*metadatapb.ViewMetadata)
	materializedViewMap := make(map[string]*metadatapb.MaterializedViewMetadata)

	// Construct functions.
	for _, function := range schema.Functions {
		if function.SkipDump {
			continue
		}
		funcID := getObjectID(schema.Name, funcIdentity(function))
		functionMap[funcID] = function
		graph.AddNode(funcID)
		for _, dependency := range function.DependencyTables {
			dependencyID := getObjectID(dependency.Schema, dependency.Table)
			graph.AddEdge(dependencyID, funcID)
		}
	}

	// Create non-identity sequences before tables to prevent errors when tables reference
	// sequences owned by other tables. Identity sequences are created via writeColumnIdentityGeneration.
	// Build identity column map once for O(m*c) instead of O(s*m*c) when checking each sequence.
	identityColumnMap := buildIdentityColumnMapForSchema(schema)

	sequenceOwnershipMap := make(map[string][]*metadatapb.SequenceMetadata)
	for _, sequence := range schema.Sequences {
		if sequence.SkipDump {
			continue
		}
		// Check if this sequence belongs to an identity column using the prebuilt map
		isIdentity := false
		if sequence.OwnerTable != "" && sequence.OwnerColumn != "" {
			key := sequence.OwnerTable + "." + sequence.OwnerColumn
			isIdentity = identityColumnMap[key]
		}

		if isIdentity {
			tableID := getObjectID(schema.Name, sequence.OwnerTable)
			sequenceOwnershipMap[tableID] = append(sequenceOwnershipMap[tableID], sequence)
			continue
		}
		if err := writeCreateSequence(&buf, schema.Name, sequence); err != nil {
			return "", err
		}
		if sequence.OwnerTable != "" && sequence.OwnerColumn != "" {
			tableID := getObjectID(schema.Name, sequence.OwnerTable)
			sequenceOwnershipMap[tableID] = append(sequenceOwnershipMap[tableID], sequence)
		}
	}

	// Construct tables, views and materialized views.
	for _, table := range schema.Tables {
		if table.SkipDump {
			continue
		}
		tableID := getObjectID(schema.Name, table.Name)
		tableMap[tableID] = table
		graph.AddNode(tableID)
	}

	for _, view := range schema.Views {
		if view.SkipDump {
			continue
		}
		viewID := getObjectID(schema.Name, view.Name)
		viewMap[viewID] = view
		graph.AddNode(viewID)
		for _, dependency := range view.DependencyColumns {
			dependencyID := getObjectID(dependency.Schema, dependency.Table)
			graph.AddEdge(dependencyID, viewID)
		}
	}

	for _, view := range schema.MaterializedViews {
		if view.SkipDump {
			continue
		}
		viewID := getObjectID(schema.Name, view.Name)
		materializedViewMap[viewID] = view
		graph.AddNode(viewID)
		for _, dependency := range view.DependencyColumns {
			dependencyID := getObjectID(dependency.Schema, dependency.Table)
			graph.AddEdge(dependencyID, viewID)
		}
	}

	orderedList, err := graph.TopologicalSort()
	if err != nil {
		// If topological sort fails (e.g., due to circular dependencies),
		// fall back to a safe dependency order: tables -> views -> materialized views -> functions
		orderedList = make([]string, 0, len(functionMap)+len(tableMap)+len(viewMap)+len(materializedViewMap))

		// Add tables first (sorted alphabetically within each category)
		var tableIDs []string
		for id := range tableMap {
			tableIDs = append(tableIDs, id)
		}
		slices.Sort(tableIDs)
		orderedList = append(orderedList, tableIDs...)

		// Add views second
		var viewIDs []string
		for id := range viewMap {
			viewIDs = append(viewIDs, id)
		}
		slices.Sort(viewIDs)
		orderedList = append(orderedList, viewIDs...)

		// Add materialized views third
		var materializedViewIDs []string
		for id := range materializedViewMap {
			materializedViewIDs = append(materializedViewIDs, id)
		}
		slices.Sort(materializedViewIDs)
		orderedList = append(orderedList, materializedViewIDs...)

		// Add functions last
		var functionIDs []string
		for id := range functionMap {
			functionIDs = append(functionIDs, id)
		}
		slices.Sort(functionIDs)
		orderedList = append(orderedList, functionIDs...)
	}

	// Construct functions, tables, views and materialized views in order.
	for _, objectID := range orderedList {
		if function, ok := functionMap[objectID]; ok {
			if err := writeFunction(&buf, getSchemaNameFromID(objectID), function); err != nil {
				return "", err
			}
			continue
		}
		if table, ok := tableMap[objectID]; ok {
			if err := writeTable(&buf, getSchemaNameFromID(objectID), table, sequenceOwnershipMap[objectID]); err != nil {
				return "", err
			}
		}
		if view, ok := viewMap[objectID]; ok {
			if err := writeView(&buf, getSchemaNameFromID(objectID), view); err != nil {
				return "", err
			}
			continue
		}
		if view, ok := materializedViewMap[objectID]; ok {
			if err := writeMaterializedView(&buf, getSchemaNameFromID(objectID), view); err != nil {
				return "", err
			}
		}
	}

	// Construct triggers.
	if err := writeSchemaTriggers(&buf, schema); err != nil {
		return "", err
	}

	// Construct rules.
	if err := writeSchemaRules(&buf, schema); err != nil {
		return "", err
	}

	// Construct foreign keys.
	if err := writeSchemaForeignKeys(&buf, schema, collectTables([]*metadatapb.SchemaMetadata{schema}, tableMissingColumnMetadata)); err != nil {
		return "", err
	}

	return buf.String(), nil
}

func GetTableDefinition(schema string, table *metadatapb.TableMetadata, sequences []*metadatapb.SequenceMetadata) (string, error) {
	var buf strings.Builder
	if err := writeTable(&buf, schema, table, sequences); err != nil {
		return "", err
	}
	// Skip triggers, rules, and foreign keys for a table without column
	// metadata; their definitions can reference the missing columns.
	if tableMissingColumnMetadata(table) {
		return buf.String(), nil
	}
	// Construct triggers.
	for _, trigger := range table.Triggers {
		if trigger.SkipDump {
			continue
		}
		if err := writeTrigger(&buf, schema, table.Name, trigger); err != nil {
			return "", err
		}
	}
	// Construct rules.
	if len(table.Rules) > 0 {
		if err := writeRules(&buf, schema, table.Name, table.Rules); err != nil {
			return "", err
		}
	}
	// Construct foreign keys.
	for _, fk := range table.ForeignKeys {
		if err := writeForeignKey(&buf, schema, table.Name, fk); err != nil {
			return "", err
		}
	}
	return buf.String(), nil
}

func GetViewDefinition(schema string, view *metadatapb.ViewMetadata) (string, error) {
	var buf strings.Builder
	if err := writeView(&buf, schema, view); err != nil {
		return "", err
	}
	// Construct triggers.
	for _, trigger := range view.Triggers {
		if trigger.SkipDump {
			continue
		}
		if err := writeTrigger(&buf, schema, view.Name, trigger); err != nil {
			return "", err
		}
	}
	// Construct rules (non-SELECT rules only, as SELECT rules are part of the view definition).
	var nonSelectRules []*metadatapb.RuleMetadata
	for _, rule := range view.Rules {
		if rule.Event != "SELECT" {
			nonSelectRules = append(nonSelectRules, rule)
		}
	}
	if len(nonSelectRules) > 0 {
		if err := writeRules(&buf, schema, view.Name, nonSelectRules); err != nil {
			return "", err
		}
	}
	return buf.String(), nil
}

func GetMaterializedViewDefinition(schema string, view *metadatapb.MaterializedViewMetadata) (string, error) {
	var buf strings.Builder
	if err := writeMaterializedView(&buf, schema, view); err != nil {
		return "", err
	}
	// Construct triggers.
	for _, trigger := range view.Triggers {
		if trigger.SkipDump {
			continue
		}
		if err := writeTrigger(&buf, schema, view.Name, trigger); err != nil {
			return "", err
		}
	}
	return buf.String(), nil
}

func GetFunctionDefinition(schema string, function *metadatapb.FunctionMetadata) (string, error) {
	var buf strings.Builder
	if err := writeFunction(&buf, schema, function); err != nil {
		return "", err
	}
	return buf.String(), nil
}

func GetSequenceDefinition(schema string, sequence *metadatapb.SequenceMetadata) (string, error) {
	var buf strings.Builder
	if err := writeCreateSequence(&buf, schema, sequence); err != nil {
		return "", err
	}
	if sequence.OwnerColumn != "" && sequence.OwnerTable != "" {
		if err := writeAlterSequenceOwnedBy(&buf, schema, sequence); err != nil {
			return "", err
		}
	}
	return buf.String(), nil
}

func filterBackupSchemaIfNecessary(ctx schema.GetDefinitionContext, metadata *metadatapb.DatabaseSchemaMetadata) *metadatapb.DatabaseSchemaMetadata {
	if !ctx.SkipBackupSchema {
		return metadata
	}

	filtered := &metadatapb.DatabaseSchemaMetadata{
		Extensions:    metadata.Extensions,
		EventTriggers: metadata.EventTriggers,
	}
	for _, schema := range metadata.Schemas {
		if schema.Name == common.BackupDatabaseNameOfEngine(storepb.Engine_POSTGRES) {
			continue
		}
		filtered.Schemas = append(filtered.Schemas, schema)
	}
	return filtered
}

func writeTrigger(out io.Writer, schema string, table string, trigger *metadatapb.TriggerMetadata) error {
	if _, err := io.WriteString(out, trigger.Body); err != nil {
		return err
	}
	if _, err := io.WriteString(out, ";\n\n"); err != nil {
		return err
	}

	if len(trigger.Comment) > 0 {
		if err := writeTriggerComment(out, schema, table, trigger); err != nil {
			return err
		}
	}

	return nil
}

func writeTriggerComment(out io.Writer, schema string, table string, trigger *metadatapb.TriggerMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON TRIGGER "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, trigger.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" ON "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, table); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, escapeSingleQuote(trigger.Comment)); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}

	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeEventTrigger(out io.Writer, eventTrigger *metadatapb.EventTriggerMetadata) error {
	// Use the stored definition if available
	if eventTrigger.Definition != "" {
		if _, err := io.WriteString(out, eventTrigger.Definition); err != nil {
			return err
		}
		if _, err := io.WriteString(out, ";\n\n"); err != nil {
			return err
		}
	} else {
		// Build the CREATE EVENT TRIGGER statement
		if _, err := io.WriteString(out, `CREATE EVENT TRIGGER "`); err != nil {
			return err
		}
		if _, err := io.WriteString(out, eventTrigger.Name); err != nil {
			return err
		}
		if _, err := io.WriteString(out, `" ON `); err != nil {
			return err
		}
		if _, err := io.WriteString(out, eventTrigger.Event); err != nil {
			return err
		}

		// Add WHEN TAG IN clause if tags are specified
		if len(eventTrigger.Tags) > 0 {
			if _, err := io.WriteString(out, "\n  WHEN TAG IN ("); err != nil {
				return err
			}
			for i, tag := range eventTrigger.Tags {
				if i > 0 {
					if _, err := io.WriteString(out, ", "); err != nil {
						return err
					}
				}
				if _, err := io.WriteString(out, "'"); err != nil {
					return err
				}
				if _, err := io.WriteString(out, tag); err != nil {
					return err
				}
				if _, err := io.WriteString(out, "'"); err != nil {
					return err
				}
			}
			if _, err := io.WriteString(out, ")"); err != nil {
				return err
			}
		}

		// Add EXECUTE FUNCTION clause
		if _, err := io.WriteString(out, "\n  EXECUTE FUNCTION "); err != nil {
			return err
		}
		if eventTrigger.FunctionSchema != "" {
			if _, err := io.WriteString(out, `"`); err != nil {
				return err
			}
			if _, err := io.WriteString(out, eventTrigger.FunctionSchema); err != nil {
				return err
			}
			if _, err := io.WriteString(out, `".`); err != nil {
				return err
			}
		}
		if _, err := io.WriteString(out, `"`); err != nil {
			return err
		}
		if _, err := io.WriteString(out, eventTrigger.FunctionName); err != nil {
			return err
		}
		if _, err := io.WriteString(out, `"();\n\n`); err != nil {
			return err
		}
	}

	// Write event trigger comment if present
	if eventTrigger.Comment != "" {
		if err := writeEventTriggerComment(out, eventTrigger); err != nil {
			return err
		}
	}

	return nil
}

func writeEventTriggerComment(out io.Writer, eventTrigger *metadatapb.EventTriggerMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON EVENT TRIGGER "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, eventTrigger.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, escapeSingleQuote(eventTrigger.Comment)); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}

	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeEnum(out io.Writer, schema string, enum *metadatapb.EnumTypeMetadata) error {
	if _, err := io.WriteString(out, `CREATE TYPE "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, enum.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\" AS ENUM (\n"); err != nil {
		return err
	}
	for i, value := range enum.Values {
		if i > 0 {
			if _, err := io.WriteString(out, ",\n"); err != nil {
				return err
			}
		}
		if _, err := io.WriteString(out, `    '`); err != nil {
			return err
		}
		if _, err := io.WriteString(out, escapeSingleQuote(value)); err != nil {
			return err
		}
		if _, err := io.WriteString(out, `'`); err != nil {
			return err
		}
	}

	if _, err := io.WriteString(out, "\n)"); err != nil {
		return err
	}

	return nil
}

func escapeSingleQuote(s string) string {
	return strings.ReplaceAll(s, "'", "''")
}

func writeCompositeType(out io.Writer, schema string, composite *metadatapb.CompositeTypeMetadata) error {
	if _, err := io.WriteString(out, `CREATE TYPE "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, composite.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\" AS (\n"); err != nil {
		return err
	}
	for i, attribute := range composite.Attributes {
		if i > 0 {
			if _, err := io.WriteString(out, ",\n"); err != nil {
				return err
			}
		}
		if _, err := io.WriteString(out, `    "`); err != nil {
			return err
		}
		if _, err := io.WriteString(out, attribute.Name); err != nil {
			return err
		}
		if _, err := io.WriteString(out, `" `); err != nil {
			return err
		}
		if _, err := io.WriteString(out, attribute.Type); err != nil {
			return err
		}
		// The collation is stored as an emit-ready identifier reference
		// (quoted as needed, schema-qualified outside pg_catalog).
		if attribute.Collation != "" {
			if _, err := io.WriteString(out, ` COLLATE `); err != nil {
				return err
			}
			if _, err := io.WriteString(out, attribute.Collation); err != nil {
				return err
			}
		}
	}

	if _, err := io.WriteString(out, "\n)"); err != nil {
		return err
	}

	return nil
}

func compositeTypeHasComments(composite *metadatapb.CompositeTypeMetadata) bool {
	if composite.Comment != "" {
		return true
	}
	for _, attribute := range composite.Attributes {
		if attribute.Comment != "" {
			return true
		}
	}
	return false
}

func writeCompositeTypeComments(out io.Writer, schema string, composite *metadatapb.CompositeTypeMetadata) error {
	if composite.Comment != "" {
		if _, err := fmt.Fprintf(out, "COMMENT ON TYPE \"%s\".\"%s\" IS '%s';\n\n", schema, composite.Name, escapeSingleQuote(composite.Comment)); err != nil {
			return err
		}
	}
	for _, attribute := range composite.Attributes {
		if attribute.Comment == "" {
			continue
		}
		if _, err := fmt.Fprintf(out, "COMMENT ON COLUMN \"%s\".\"%s\".\"%s\" IS '%s';\n\n", schema, composite.Name, attribute.Name, escapeSingleQuote(attribute.Comment)); err != nil {
			return err
		}
	}
	return nil
}

// qualifiedCompositeType pairs a composite type with its schema for
// cross-schema ordering.
type qualifiedCompositeType struct {
	Schema    string
	Composite *metadatapb.CompositeTypeMetadata
}

// collectCompositeTypes gathers the dumpable composite types of the given
// schemas. Schemas with SkipDump never get a CREATE SCHEMA statement, so
// their types must not be emitted either.
func collectCompositeTypes(schemas []*metadatapb.SchemaMetadata) []qualifiedCompositeType {
	var compositeTypes []qualifiedCompositeType
	for _, schema := range schemas {
		if schema.SkipDump {
			continue
		}
		for _, composite := range schema.CompositeTypes {
			if composite.SkipDump {
				continue
			}
			compositeTypes = append(compositeTypes, qualifiedCompositeType{Schema: schema.Name, Composite: composite})
		}
	}
	return compositeTypes
}

// writeCompositeTypesSection emits the composite types with their comments,
// ordered so referenced composites come first.
func writeCompositeTypesSection(buf *strings.Builder, compositeTypes []qualifiedCompositeType) error {
	for _, t := range sortCompositeTypesTopologically(compositeTypes) {
		if err := writeCompositeType(buf, t.Schema, t.Composite); err != nil {
			return err
		}
		if _, err := buf.WriteString(";\n\n"); err != nil {
			return err
		}
		if compositeTypeHasComments(t.Composite) {
			if err := writeCompositeTypeComments(buf, t.Schema, t.Composite); err != nil {
				return err
			}
		}
	}
	return nil
}

// parseQualifiedTypeIdent splits a type reference produced by the sync layer
// (format_type output, schema-qualified for user-defined types) into
// (schema, name), ignoring any typmod or array suffix. It returns ok=false
// for unqualified references — built-in types are never schema-qualified.
func parseQualifiedTypeIdent(s string) (string, string, bool) {
	var parts []string
	for i := 0; i < len(s); {
		if s[i] == '"' {
			var b strings.Builder
			i++
			for i < len(s) {
				if s[i] == '"' {
					if i+1 < len(s) && s[i+1] == '"' {
						b.WriteByte('"')
						i += 2
						continue
					}
					i++
					break
				}
				b.WriteByte(s[i])
				i++
			}
			parts = append(parts, b.String())
		} else {
			start := i
			for i < len(s) && s[i] != '.' && s[i] != '(' && s[i] != '[' {
				i++
			}
			parts = append(parts, strings.TrimSpace(s[start:i]))
		}
		if i < len(s) && s[i] == '.' {
			i++
			continue
		}
		break
	}
	if len(parts) == 2 && parts[0] != "" && parts[1] != "" {
		return parts[0], parts[1], true
	}
	return "", "", false
}

// sortCompositeTypesTopologically orders composite types so that a composite
// referenced by another composite's attribute is emitted first. PostgreSQL
// rejects self-containment, so the graph is acyclic; any leftover on an
// unexpected cycle falls back to (schema, name) order. A composite attribute
// typed with a table or view row type is not reordered here — that requires
// catalog-grade dependency data (see pg_dump's pg_depend-driven sort).
func sortCompositeTypesTopologically(types []qualifiedCompositeType) []qualifiedCompositeType {
	key := func(schema, name string) string { return schema + "\x00" + name }
	nodes := make(map[string]qualifiedCompositeType, len(types))
	for _, t := range types {
		nodes[key(t.Schema, t.Composite.Name)] = t
	}
	inDegree := make(map[string]int, len(nodes))
	dependents := make(map[string][]string, len(nodes))
	for k := range nodes {
		inDegree[k] = 0
	}
	for k, t := range nodes {
		for _, attribute := range t.Composite.Attributes {
			depSchema, depName, ok := parseQualifiedTypeIdent(attribute.Type)
			if !ok {
				continue
			}
			depKey := key(depSchema, depName)
			if depKey == k {
				continue
			}
			if _, exists := nodes[depKey]; !exists {
				continue
			}
			dependents[depKey] = append(dependents[depKey], k)
			inDegree[k]++
		}
	}

	var ready []string
	for k, d := range inDegree {
		if d == 0 {
			ready = append(ready, k)
		}
	}
	slices.Sort(ready)

	ordered := make([]qualifiedCompositeType, 0, len(nodes))
	for len(ready) > 0 {
		k := ready[0]
		ready = ready[1:]
		ordered = append(ordered, nodes[k])
		delete(inDegree, k)
		for _, dependent := range dependents[k] {
			if _, remaining := inDegree[dependent]; !remaining {
				continue
			}
			inDegree[dependent]--
			if inDegree[dependent] == 0 {
				ready = append(ready, dependent)
			}
		}
		slices.Sort(ready)
	}

	if len(ordered) < len(nodes) {
		var rest []string
		for k := range inDegree {
			rest = append(rest, k)
		}
		slices.Sort(rest)
		for _, k := range rest {
			ordered = append(ordered, nodes[k])
		}
	}

	return ordered
}

// signatureTypesOnly strips parameter names from a function/procedure signature,
// keeping only types. PostgreSQL's COMMENT ON FUNCTION/PROCEDURE requires
// type-only signatures: "func(integer, text)" not "func(id integer, name text)".
//
// It works by wrapping the signature in a CREATE FUNCTION statement, parsing it
// with omni to discover parameter names, then removing those names from the
// original signature text to preserve the original type spelling.
func signatureTypesOnly(signature string) string {
	openParen := strings.Index(signature, "(")
	if openParen < 0 {
		return signature
	}
	closeParen := strings.LastIndex(signature, ")")
	if closeParen <= openParen {
		return signature
	}
	funcName := signature[:openParen]
	paramStr := signature[openParen+1 : closeParen]
	if strings.TrimSpace(paramStr) == "" {
		return signature
	}

	// Parse the signature as a CREATE FUNCTION to extract param names from AST.
	sql := fmt.Sprintf("CREATE FUNCTION %s RETURNS void LANGUAGE sql AS 'SELECT 1'", signature)
	stmts, err := omnipg.Parse(sql)
	if err != nil {
		return signature
	}
	fn, ok := stmts[0].AST.(*ast.CreateFunctionStmt)
	if !ok || fn.Parameters == nil {
		return signature
	}

	// Collect parameter names from AST.
	var paramNames []string
	for _, item := range fn.Parameters.Items {
		if fp, ok := item.(*ast.FunctionParameter); ok && fp.Name != "" {
			paramNames = append(paramNames, fp.Name)
		}
	}

	// Strip parameter names from the original text, preserving original type spelling.
	rawParams := strings.Split(paramStr, ",")
	var typesOnly []string
	nameIdx := 0
	for _, raw := range rawParams {
		param := strings.TrimSpace(raw)
		words := strings.Fields(param)
		if len(words) == 0 {
			continue
		}
		// Skip mode keywords (IN, OUT, INOUT, VARIADIC).
		startIdx := 0
		for startIdx < len(words) {
			w := strings.ToLower(words[startIdx])
			if w == "in" || w == "out" || w == "inout" || w == "variadic" {
				startIdx++
			} else {
				break
			}
		}
		// Check if first non-mode word matches a known param name from AST.
		if startIdx < len(words) && nameIdx < len(paramNames) && words[startIdx] == paramNames[nameIdx] {
			typesOnly = append(typesOnly, strings.Join(words[startIdx+1:], " "))
			nameIdx++
		} else {
			typesOnly = append(typesOnly, strings.Join(words[startIdx:], " "))
		}
	}

	return funcName + "(" + strings.Join(typesOnly, ", ") + ")"
}

func writeEnumComment(out io.Writer, schema string, enum *metadatapb.EnumTypeMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON TYPE "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, enum.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, escapeSingleQuote(enum.Comment)); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}

	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeMaterializedView(out io.Writer, schema string, view *metadatapb.MaterializedViewMetadata) error {
	if _, err := io.WriteString(out, `CREATE MATERIALIZED VIEW "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, view.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\" AS \n"); err != nil {
		return err
	}
	if _, err := io.WriteString(out, strings.TrimRight(view.Definition, ";")); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\n  WITH NO DATA;\n\n"); err != nil {
		return err
	}

	for _, index := range view.Indexes {
		if err := writeIndex(out, schema, view.Name, index, false); err != nil {
			return err
		}
	}

	if len(view.Comment) > 0 {
		if err := writeMaterializedViewComment(out, schema, view); err != nil {
			return err
		}
	}

	return nil
}

func writeMaterializedViewComment(out io.Writer, schema string, view *metadatapb.MaterializedViewMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON MATERIALIZED VIEW "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, view.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, escapeSingleQuote(view.Comment)); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}

	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeView(out io.Writer, schema string, view *metadatapb.ViewMetadata) error {
	if _, err := io.WriteString(out, `CREATE VIEW "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, view.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\" AS \n"); err != nil {
		return err
	}
	if _, err := io.WriteString(out, view.Definition); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\n\n"); err != nil {
		return err
	}

	if len(view.Comment) > 0 {
		if err := writeViewComment(out, schema, view); err != nil {
			return err
		}
	}

	return nil
}

func writeViewComment(out io.Writer, schema string, view *metadatapb.ViewMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON VIEW "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, view.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, escapeSingleQuote(view.Comment)); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}

	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeRules(out io.Writer, _ string, _ string, rules []*metadatapb.RuleMetadata) error {
	for _, rule := range rules {
		// Write the full rule definition
		if _, err := io.WriteString(out, rule.Definition); err != nil {
			return err
		}
		if _, err := io.WriteString(out, "\n\n"); err != nil {
			return err
		}
	}
	return nil
}

// objectIDSeparator joins schema + object into a map/graph key. NUL cannot
// legally appear in a PostgreSQL identifier, so this avoids ambiguity when
// either half itself contains a dot (e.g. a table literally named "a.b").
const objectIDSeparator = "\x00"

func getSchemaNameFromID(id string) string {
	if schema, _, ok := strings.Cut(id, objectIDSeparator); ok {
		return schema
	}
	return ""
}

func writeColumnIdentityGeneration(out io.Writer, schema string, generationTypes metadatapb.ColumnMetadata_IdentityGeneration, sequence *metadatapb.SequenceMetadata) error {
	if _, err := io.WriteString(out, `ALTER TABLE "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.OwnerTable); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" ALTER COLUMN "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.OwnerColumn); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" ADD GENERATED `); err != nil {
		return err
	}
	if generationTypes == metadatapb.ColumnMetadata_ALWAYS {
		if _, err := io.WriteString(out, "ALWAYS "); err != nil {
			return err
		}
	} else {
		if _, err := io.WriteString(out, "BY DEFAULT "); err != nil {
			return err
		}
	}
	if _, err := io.WriteString(out, "AS IDENTITY (\n"); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "    SEQUENCE NAME \""); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\"\n"); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "    START WITH "); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.Start); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\n    INCREMENT BY "); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.Increment); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\n    MINVALUE "); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.MinValue); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\n    MAXVALUE "); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.MaxValue); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\n    CACHE "); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.CacheSize); err != nil {
		return err
	}
	_, err := io.WriteString(out, "\n);\n\n")
	return err
}

func writeCreateSequence(out io.Writer, schema string, sequence *metadatapb.SequenceMetadata) error {
	if _, err := io.WriteString(out, `CREATE SEQUENCE "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\"\n    "); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "AS "); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.DataType); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\n	START WITH "); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.Start); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\n	INCREMENT BY "); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.Increment); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\n	MINVALUE "); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.MinValue); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\n	MAXVALUE "); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.MaxValue); err != nil {
		return err
	}
	if sequence.Cycle {
		if _, err := io.WriteString(out, "\n	CYCLE"); err != nil {
			return err
		}
	} else {
		if _, err := io.WriteString(out, "\n	NO CYCLE"); err != nil {
			return err
		}
	}
	if _, err := io.WriteString(out, ";\n\n"); err != nil {
		return err
	}

	if len(sequence.Comment) > 0 {
		if err := writeSequenceComment(out, schema, sequence); err != nil {
			return err
		}
	}

	return nil
}

func writeSequenceComment(out io.Writer, schema string, sequence *metadatapb.SequenceMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON SEQUENCE "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, sequence.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, escapeSingleQuote(sequence.Comment)); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}

	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeAlterSequenceOwnedBy(out io.Writer, schema string, sequence *metadatapb.SequenceMetadata) error {
	if _, err := io.WriteString(out, `ALTER SEQUENCE "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\" OWNED BY \""); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.OwnerTable); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.OwnerColumn); err != nil {
		return err
	}
	_, err := io.WriteString(out, "\";\n\n")
	return err
}

// funcIdentity returns the unique identity for a function, preferring
// the full signature (name + parameter types) to support overloaded functions.
func funcIdentity(f *metadatapb.FunctionMetadata) string {
	if f.Signature != "" {
		return f.Signature
	}
	return f.Name
}

func getObjectID(schema string, object string) string {
	var buf strings.Builder
	_, _ = buf.WriteString(schema)
	_, _ = buf.WriteString(objectIDSeparator)
	_, _ = buf.WriteString(object)
	return buf.String()
}

// writePrimaryKeyConstraintSDL writes a single primary key constraint SDL
func writePrimaryKeyConstraintSDL(out io.Writer, index *metadatapb.IndexMetadata) error {
	if index == nil || !index.Primary {
		return errors.New("invalid primary key constraint")
	}

	if _, err := io.WriteString(out, `CONSTRAINT "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, index.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" PRIMARY KEY (`); err != nil {
		return err
	}
	for i, expression := range index.Expressions {
		if i > 0 {
			if _, err := io.WriteString(out, ", "); err != nil {
				return err
			}
		}
		if _, err := io.WriteString(out, expression); err != nil {
			return err
		}
	}
	if _, err := io.WriteString(out, ")"); err != nil {
		return err
	}
	return nil
}

// writeUniqueKeyConstraintSDL writes a single unique key constraint SDL
func writeUniqueKeyConstraintSDL(out io.Writer, index *metadatapb.IndexMetadata) error {
	if index == nil || !index.Unique || index.Primary || !index.IsConstraint {
		return errors.New("invalid unique key constraint")
	}

	if _, err := io.WriteString(out, `CONSTRAINT "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, index.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" UNIQUE (`); err != nil {
		return err
	}
	for i, expression := range index.Expressions {
		if i > 0 {
			if _, err := io.WriteString(out, ", "); err != nil {
				return err
			}
		}
		if _, err := io.WriteString(out, expression); err != nil {
			return err
		}
	}
	if _, err := io.WriteString(out, ")"); err != nil {
		return err
	}
	return nil
}

func writeCreateTable(out io.Writer, schema string, tableName string, columns []*metadatapb.ColumnMetadata, checks []*metadatapb.CheckConstraintMetadata, excludes []*metadatapb.ExcludeConstraintMetadata) error {
	if _, err := io.WriteString(out, `CREATE TABLE "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, tableName); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" (`); err != nil {
		return err
	}

	// first tracks whether we've written any element inside the parens yet so
	// the leading comma on constraints is suppressed when there are no columns
	// (e.g. tables that only carry CHECK constraints).
	first := true
	writeItemSep := func() error {
		if first {
			first = false
			_, err := io.WriteString(out, "\n    ")
			return err
		}
		_, err := io.WriteString(out, ",\n    ")
		return err
	}

	for _, column := range columns {
		if err := writeItemSep(); err != nil {
			return err
		}

		if _, err := io.WriteString(out, `"`); err != nil {
			return err
		}

		if _, err := io.WriteString(out, column.Name); err != nil {
			return err
		}

		if _, err := io.WriteString(out, `" `); err != nil {
			return err
		}

		if _, err := io.WriteString(out, column.Type); err != nil {
			return err
		}

		// Handle default values
		if column.Default != "" {
			if _, err := io.WriteString(out, ` DEFAULT `); err != nil {
				return err
			}
			if _, err := io.WriteString(out, column.Default); err != nil {
				return err
			}
		}

		if !column.Nullable {
			if _, err := io.WriteString(out, ` NOT NULL`); err != nil {
				return err
			}
		}
	}

	for _, check := range checks {
		if err := writeItemSep(); err != nil {
			return err
		}
		_, _ = io.WriteString(out, `CONSTRAINT "`)
		_, _ = io.WriteString(out, check.Name)
		_, _ = io.WriteString(out, `" CHECK `)
		_, _ = io.WriteString(out, check.Expression)
	}

	for _, exclude := range excludes {
		if err := writeItemSep(); err != nil {
			return err
		}
		_, _ = io.WriteString(out, `CONSTRAINT "`)
		_, _ = io.WriteString(out, exclude.Name)
		_, _ = io.WriteString(out, `" `)
		_, _ = io.WriteString(out, exclude.Expression) // Already includes "EXCLUDE USING ..."
	}

	_, err := io.WriteString(out, "\n)")
	return err
}

// isIdentityColumn checks if a column is an identity column.
func isIdentityColumn(column *metadatapb.ColumnMetadata) bool {
	return column.IdentityGeneration == metadatapb.ColumnMetadata_ALWAYS ||
		column.IdentityGeneration == metadatapb.ColumnMetadata_BY_DEFAULT
}

// buildIdentityColumnMap builds a map of identity columns for the entire database.
// Key format: "schemaName.tableName.columnName" -> true
// This is O(m*c) which is done once, vs O(s*m*c) when checking each sequence individually.
func buildIdentityColumnMap(metadata *metadatapb.DatabaseSchemaMetadata) map[string]bool {
	identityMap := make(map[string]bool)
	for _, schema := range metadata.Schemas {
		for _, table := range schema.Tables {
			for _, column := range table.Columns {
				if isIdentityColumn(column) {
					key := schema.Name + "." + table.Name + "." + column.Name
					identityMap[key] = true
				}
			}
		}
	}
	return identityMap
}

// buildIdentityColumnMapForSchema builds a map of identity columns for a single schema.
// Key format: "tableName.columnName" -> true
func buildIdentityColumnMapForSchema(schema *metadatapb.SchemaMetadata) map[string]bool {
	identityMap := make(map[string]bool)
	for _, table := range schema.Tables {
		for _, column := range table.Columns {
			if isIdentityColumn(column) {
				key := table.Name + "." + column.Name
				identityMap[key] = true
			}
		}
	}
	return identityMap
}

func splitSequencesByIdentityOrNot(table *metadatapb.TableMetadata, sequences []*metadatapb.SequenceMetadata) ([]metadatapb.ColumnMetadata_IdentityGeneration, []*metadatapb.SequenceMetadata, []*metadatapb.SequenceMetadata) {
	columnMap := make(map[string]*metadatapb.ColumnMetadata)
	for _, column := range table.Columns {
		columnMap[column.Name] = column
	}
	var generationType []metadatapb.ColumnMetadata_IdentityGeneration
	var identitySequences []*metadatapb.SequenceMetadata
	var nonIdentitySequences []*metadatapb.SequenceMetadata
	for _, sequence := range sequences {
		if column, ok := columnMap[sequence.OwnerColumn]; ok {
			if isIdentityColumn(column) {
				generationType = append(generationType, column.IdentityGeneration)
				identitySequences = append(identitySequences, sequence)
				continue
			}
		}
		nonIdentitySequences = append(nonIdentitySequences, sequence)
	}
	return generationType, identitySequences, nonIdentitySequences
}

// tableMissingColumnMetadata reports whether a synced table's column list
// was filtered away. information_schema.columns is privilege-filtered, so a
// sync user without column privileges on a table sees zero columns while the
// pg_catalog-based queries still return the table's constraints, indexes,
// triggers, and rules. DDL referencing the missing columns would make the
// dump non-replayable, so writers skip column-dependent statements for such
// tables.
//
// A genuine zero-column table (CREATE TABLE t ()) is legal in PostgreSQL and
// may carry column-independent objects such as CHECK (1 = 1), but it cannot
// carry primary keys, unique constraints, foreign keys, or a partition key
// (PostgreSQL rejects constant partition-key expressions); indexes on it are
// limited to constant-expression corner cases. Treat zero columns as missing
// metadata only when such objects are present, so genuine zero-column tables
// keep their objects.
func tableMissingColumnMetadata(table *metadatapb.TableMetadata) bool {
	return len(table.Columns) == 0 &&
		(len(table.Indexes) > 0 || len(table.ForeignKeys) > 0 || len(table.Partitions) > 0)
}

// tableHasNoColumns reports an empty synced column list, whether genuinely
// zero-column or privilege-filtered. A sequence cannot be owned by a column
// of such a table, so ALTER SEQUENCE ... OWNED BY is skipped either way.
func tableHasNoColumns(table *metadatapb.TableMetadata) bool {
	return len(table.Columns) == 0
}

// collectTables returns the tables matching include, keyed by
// getObjectID(schema, table).
func collectTables(schemas []*metadatapb.SchemaMetadata, include func(*metadatapb.TableMetadata) bool) map[string]bool {
	set := make(map[string]bool)
	for _, schema := range schemas {
		for _, table := range schema.Tables {
			if include(table) {
				set[getObjectID(schema.Name, table.Name)] = true
			}
		}
	}
	return set
}

// writeSchemaTriggers writes the triggers of every table, view, and
// materialized view in the schema. Triggers on tables without column
// metadata are skipped; their definitions (UPDATE OF, WHEN clauses) can
// reference the missing columns.
func writeSchemaTriggers(out io.Writer, schema *metadatapb.SchemaMetadata) error {
	for _, table := range schema.Tables {
		if tableMissingColumnMetadata(table) {
			continue
		}
		for _, trigger := range table.Triggers {
			if trigger.SkipDump {
				continue
			}
			if err := writeTrigger(out, schema.Name, table.Name, trigger); err != nil {
				return err
			}
		}
	}

	for _, view := range schema.Views {
		for _, trigger := range view.Triggers {
			if trigger.SkipDump {
				continue
			}
			if err := writeTrigger(out, schema.Name, view.Name, trigger); err != nil {
				return err
			}
		}
	}

	for _, materializedView := range schema.MaterializedViews {
		for _, trigger := range materializedView.Triggers {
			if trigger.SkipDump {
				continue
			}
			if err := writeTrigger(out, schema.Name, materializedView.Name, trigger); err != nil {
				return err
			}
		}
	}
	return nil
}

// writeSchemaRules writes the rules of every table and the non-SELECT rules
// of every view in the schema (SELECT rules are part of the view
// definition). Rules on tables without column metadata are skipped; their
// definitions can reference the missing columns.
func writeSchemaRules(out io.Writer, schema *metadatapb.SchemaMetadata) error {
	for _, table := range schema.Tables {
		if len(table.Rules) > 0 && !tableMissingColumnMetadata(table) {
			if err := writeRules(out, schema.Name, table.Name, table.Rules); err != nil {
				return err
			}
		}
	}

	for _, view := range schema.Views {
		var nonSelectRules []*metadatapb.RuleMetadata
		for _, rule := range view.Rules {
			if rule.Event != "SELECT" {
				nonSelectRules = append(nonSelectRules, rule)
			}
		}
		if len(nonSelectRules) > 0 {
			if err := writeRules(out, schema.Name, view.Name, nonSelectRules); err != nil {
				return err
			}
		}
	}
	return nil
}

// writeSchemaForeignKeys writes the foreign keys of every table in the
// schema, skipping foreign keys touching a table without column metadata on
// either side; they would reference columns absent from the dump.
func writeSchemaForeignKeys(out io.Writer, schema *metadatapb.SchemaMetadata, tablesMissingColumns map[string]bool) error {
	for _, table := range schema.Tables {
		if table.SkipDump || tablesMissingColumns[getObjectID(schema.Name, table.Name)] {
			continue
		}
		for _, fk := range table.ForeignKeys {
			if tablesMissingColumns[getObjectID(fk.ReferencedSchema, fk.ReferencedTable)] {
				continue
			}
			if err := writeForeignKey(out, schema.Name, table.Name, fk); err != nil {
				return err
			}
		}
	}
	return nil
}

func writeTable(out io.Writer, schema string, table *metadatapb.TableMetadata, sequences []*metadatapb.SequenceMetadata) error {
	if tableMissingColumnMetadata(table) {
		// Emit a bare CREATE TABLE so the table still exists on replay, and
		// skip constraints, indexes, partitions, and sequence ownership —
		// they all reference columns absent from the dump.
		if err := writeCreateTable(out, schema, table.Name, nil, nil, nil); err != nil {
			return err
		}
		if _, err := io.WriteString(out, ";\n\n"); err != nil {
			return err
		}
		if len(table.Comment) > 0 {
			return writeTableComment(out, schema, table)
		}
		return nil
	}

	generationTypes, identitySequences, nonIdentitySequences := splitSequencesByIdentityOrNot(table, sequences)

	if err := writeCreateTable(out, schema, table.Name, table.Columns, table.CheckConstraints, table.ExcludeConstraints); err != nil {
		return err
	}

	if len(table.Partitions) > 0 {
		if err := writePartitionClause(out, table.Partitions[0]); err != nil {
			return err
		}
	}

	if _, err := io.WriteString(out, ";\n\n"); err != nil {
		return err
	}

	// Skip OWNED BY for a table with no columns; the clause would reference
	// a column absent from the dump.
	if !tableHasNoColumns(table) {
		for _, sequence := range nonIdentitySequences {
			if err := writeAlterSequenceOwnedBy(out, schema, sequence); err != nil {
				return err
			}
		}
	}

	// Construct comments.
	if len(table.Comment) > 0 {
		if err := writeTableComment(out, schema, table); err != nil {
			return err
		}
	}

	for _, column := range table.Columns {
		if len(column.Comment) > 0 {
			if err := writeColumnComment(out, schema, table.Name, column); err != nil {
				return err
			}
		}
	}

	for i, sequence := range identitySequences {
		if err := writeColumnIdentityGeneration(out, schema, generationTypes[i], sequence); err != nil {
			return err
		}
	}

	// Construct partition tables.
	for _, partition := range table.Partitions {
		if err := writePartitionTable(out, schema, table.Columns, partition); err != nil {
			return err
		}
	}

	for _, partition := range table.Partitions {
		if err := writeAttachPartition(out, schema, table.Name, partition); err != nil {
			return err
		}
	}

	// Construct Primary Key.
	for _, index := range table.Indexes {
		if index.Primary {
			if err := writePrimaryKey(out, schema, table.Name, index); err != nil {
				return err
			}
		}
	}

	// Construct Partition table primary key.
	for _, partition := range table.Partitions {
		if err := writePartitionPrimaryKey(out, schema, partition); err != nil {
			return err
		}
	}

	// Construct Unique Key.
	for _, index := range table.Indexes {
		if index.Unique && !index.Primary && index.IsConstraint {
			if err := writeUniqueKey(out, schema, table.Name, index); err != nil {
				return err
			}
		}
	}

	// Construct Partition table unique key.
	for _, partition := range table.Partitions {
		if err := writePartitionUniqueKey(out, schema, partition); err != nil {
			return err
		}
	}

	// Construct Index.
	for _, index := range table.Indexes {
		if !index.Primary && !index.IsConstraint {
			if err := writeIndex(out, schema, table.Name, index, len(table.Partitions) > 0); err != nil {
				return err
			}
		}
	}

	// Construct Partition table index.
	for _, partition := range table.Partitions {
		if err := writePartitionIndex(out, schema, partition); err != nil {
			return err
		}
	}

	// Construct index attach partition.
	for _, partition := range table.Partitions {
		if err := writeAttachPartitionIndex(out, schema, partition); err != nil {
			return err
		}
	}

	return nil
}

func writeForeignKey(out io.Writer, schema string, table string, fk *metadatapb.ForeignKeyMetadata) error {
	if _, err := io.WriteString(out, `ALTER TABLE "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, table); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\"\n    ADD CONSTRAINT \""); err != nil {
		return err
	}
	if _, err := io.WriteString(out, fk.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\" FOREIGN KEY ("); err != nil {
		return err
	}
	for i, column := range fk.Columns {
		if i > 0 {
			if _, err := io.WriteString(out, ", "); err != nil {
				return err
			}
		}
		if _, err := io.WriteString(out, `"`); err != nil {
			return err
		}
		if _, err := io.WriteString(out, column); err != nil {
			return err
		}
		if _, err := io.WriteString(out, `"`); err != nil {
			return err
		}
	}
	if _, err := io.WriteString(out, ")\n    REFERENCES \""); err != nil {
		return err
	}
	if _, err := io.WriteString(out, fk.ReferencedSchema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, fk.ReferencedTable); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\" ("); err != nil {
		return err
	}
	for i, column := range fk.ReferencedColumns {
		if i > 0 {
			if _, err := io.WriteString(out, ", "); err != nil {
				return err
			}
		}
		if _, err := io.WriteString(out, `"`); err != nil {
			return err
		}
		if _, err := io.WriteString(out, column); err != nil {
			return err
		}
		if _, err := io.WriteString(out, `"`); err != nil {
			return err
		}
	}
	if _, err := io.WriteString(out, ")"); err != nil {
		return err
	}

	// Add ON DELETE clause if specified
	if fk.OnDelete != "" && fk.OnDelete != "NO ACTION" {
		if _, err := io.WriteString(out, "\n    ON DELETE "); err != nil {
			return err
		}
		if _, err := io.WriteString(out, fk.OnDelete); err != nil {
			return err
		}
	}

	// Add ON UPDATE clause if specified
	if fk.OnUpdate != "" && fk.OnUpdate != "NO ACTION" {
		if _, err := io.WriteString(out, "\n    ON UPDATE "); err != nil {
			return err
		}
		if _, err := io.WriteString(out, fk.OnUpdate); err != nil {
			return err
		}
	}

	_, err := io.WriteString(out, ";\n\n")
	return err
}

func writeAttachPartitionIndex(out io.Writer, schema string, partition *metadatapb.TablePartitionMetadata) error {
	for _, index := range partition.Indexes {
		if err := writeAttachIndex(out, schema, index); err != nil {
			return err
		}
	}

	for _, subpartition := range partition.Subpartitions {
		if err := writeAttachPartitionIndex(out, schema, subpartition); err != nil {
			return err
		}
	}
	return nil
}

func writeAttachIndex(out io.Writer, schema string, index *metadatapb.IndexMetadata) error {
	if len(index.ParentIndexName) == 0 || len(index.ParentIndexSchema) == 0 {
		return nil
	}

	if _, err := io.WriteString(out, `ALTER INDEX "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, index.ParentIndexSchema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, index.ParentIndexName); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" ATTACH PARTITION "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, index.Name); err != nil {
		return err
	}
	_, err := io.WriteString(out, "\";\n\n")
	return err
}

func writePartitionIndex(out io.Writer, schema string, partition *metadatapb.TablePartitionMetadata) error {
	for _, index := range partition.Indexes {
		if !index.IsConstraint && !index.Primary {
			if err := writeIndex(out, schema, partition.Name, index, len(partition.Subpartitions) > 0); err != nil {
				return err
			}
		}
	}

	for _, subpartition := range partition.Subpartitions {
		if err := writePartitionIndex(out, schema, subpartition); err != nil {
			return err
		}
	}
	return nil
}

func writeIndex(out io.Writer, schema string, table string, index *metadatapb.IndexMetadata, useOnlyClause bool) error {
	return writeIndexInternal(out, schema, table, index, useOnlyClause, true)
}

func writeIndexKeyList(out io.Writer, index *metadatapb.IndexMetadata) error {
	if _, err := io.WriteString(out, `(`); err != nil {
		return err
	}

	for i, expression := range index.Expressions {
		if i > 0 {
			if _, err := io.WriteString(out, ", "); err != nil {
				return err
			}
		}

		// expression is expected to be in canonical pg_get_indexdef form —
		// see IndexMetadata.expressions in proto/store/store/database.proto.
		// normalizeLegacyMetadata (called at the top of GetDatabaseDefinition)
		// repairs historical rows that pre-date the contract.
		if _, err := io.WriteString(out, expression); err != nil {
			return err
		}

		// Add opclass if it's not the default
		if i < len(index.OpclassNames) && i < len(index.OpclassDefaults) {
			if !index.OpclassDefaults[i] && index.OpclassNames[i] != "" {
				if _, err := io.WriteString(out, " "); err != nil {
					return err
				}
				if _, err := io.WriteString(out, index.OpclassNames[i]); err != nil {
					return err
				}
			}
		}

		// Add DESC if this column is marked as descending
		if i < len(index.Descending) && index.Descending[i] {
			if _, err := io.WriteString(out, " DESC"); err != nil {
				return err
			}
		}
		// Note: NULLS ordering information is not available in IndexMetadata
		// so we omit it in the generated DDL
	}

	_, err := io.WriteString(out, ")")
	return err
}

func writeIndexComment(out io.Writer, schema string, index *metadatapb.IndexMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON INDEX "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, index.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, escapeSingleQuote(index.Comment)); err != nil {
		return err
	}

	_, err := io.WriteString(out, "';\n\n")
	return err
}

func writePartitionUniqueKey(out io.Writer, schema string, partition *metadatapb.TablePartitionMetadata) error {
	for _, index := range partition.Indexes {
		if index.Unique && !index.Primary && index.IsConstraint {
			if err := writeUniqueKey(out, schema, partition.Name, index); err != nil {
				return err
			}
		}
	}

	for _, subpartition := range partition.Subpartitions {
		if err := writePartitionUniqueKey(out, schema, subpartition); err != nil {
			return err
		}
	}
	return nil
}

func writeUniqueKey(out io.Writer, schema string, table string, index *metadatapb.IndexMetadata) error {
	if _, err := io.WriteString(out, `ALTER TABLE ONLY "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, table); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" ADD CONSTRAINT "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, index.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" UNIQUE (`); err != nil {
		return err
	}
	for i, expression := range index.Expressions {
		if i > 0 {
			if _, err := io.WriteString(out, ", "); err != nil {
				return err
			}
		}
		if _, err := io.WriteString(out, expression); err != nil {
			return err
		}
	}
	if _, err := io.WriteString(out, ");\n\n"); err != nil {
		return err
	}

	if len(index.Comment) > 0 {
		if err := writeConstraintComment(out, schema, table, index); err != nil {
			return err
		}
	}

	return nil
}

func writePartitionPrimaryKey(out io.Writer, schema string, partition *metadatapb.TablePartitionMetadata) error {
	for _, index := range partition.Indexes {
		if index.Primary {
			if err := writePrimaryKey(out, schema, partition.Name, index); err != nil {
				return err
			}
		}
	}

	for _, subpartition := range partition.Subpartitions {
		if err := writePartitionPrimaryKey(out, schema, subpartition); err != nil {
			return err
		}
	}
	return nil
}

func writePrimaryKey(out io.Writer, schema string, table string, index *metadatapb.IndexMetadata) error {
	if _, err := io.WriteString(out, `ALTER TABLE ONLY "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, table); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" ADD CONSTRAINT "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, index.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" PRIMARY KEY (`); err != nil {
		return err
	}
	for i, expression := range index.Expressions {
		if i > 0 {
			if _, err := io.WriteString(out, ", "); err != nil {
				return err
			}
		}
		if _, err := io.WriteString(out, expression); err != nil {
			return err
		}
	}
	if _, err := io.WriteString(out, ");\n\n"); err != nil {
		return err
	}

	if len(index.Comment) > 0 {
		if err := writeConstraintComment(out, schema, table, index); err != nil {
			return err
		}
	}

	return nil
}

func writeConstraintComment(out io.Writer, schema string, table string, index *metadatapb.IndexMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON CONSTRAINT "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, index.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" ON "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, table); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, escapeSingleQuote(index.Comment)); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}

	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeColumnComment(out io.Writer, schema string, table string, column *metadatapb.ColumnMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON COLUMN "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, table); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, column.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, escapeSingleQuote(column.Comment)); err != nil {
		return err
	}
	_, err := io.WriteString(out, "';\n\n")
	return err
}

func writeTableComment(out io.Writer, schema string, table *metadatapb.TableMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON TABLE "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, table.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, escapeSingleQuote(table.Comment)); err != nil {
		return err
	}
	_, err := io.WriteString(out, "';\n\n")
	return err
}

func writePartitionClause(out io.Writer, partition *metadatapb.TablePartitionMetadata) error {
	if _, err := io.WriteString(out, " PARTITION BY "); err != nil {
		return err
	}
	_, err := io.WriteString(out, partition.Expression)
	return err
}

func writeAttachPartition(out io.Writer, schema string, tableName string, partition *metadatapb.TablePartitionMetadata) error {
	if _, err := io.WriteString(out, `ALTER TABLE ONLY "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, tableName); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" ATTACH PARTITION "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, partition.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\" "); err != nil {
		return err
	}
	if _, err := io.WriteString(out, partition.Value); err != nil {
		return err
	}
	_, err := io.WriteString(out, ";\n\n")
	return err
}

func writePartitionTable(out io.Writer, schema string, columns []*metadatapb.ColumnMetadata, partition *metadatapb.TablePartitionMetadata) error {
	if err := writeCreateTable(out, schema, partition.Name, columns, partition.CheckConstraints, partition.ExcludeConstraints); err != nil {
		return err
	}

	if len(partition.Subpartitions) > 0 {
		if err := writePartitionClause(out, partition.Subpartitions[0]); err != nil {
			return err
		}
	}

	if _, err := io.WriteString(out, ";\n\n"); err != nil {
		return err
	}

	// Construct subpartition tables.
	for _, subpartition := range partition.Subpartitions {
		if err := writePartitionTable(out, schema, columns, subpartition); err != nil {
			return err
		}
	}

	for _, subpartition := range partition.Subpartitions {
		if err := writeAttachPartition(out, schema, partition.Name, subpartition); err != nil {
			return err
		}
	}

	return nil
}

func writeFunction(out io.Writer, schema string, function *metadatapb.FunctionMetadata) error {
	if _, err := io.WriteString(out, function.Definition); err != nil {
		return err
	}

	if _, err := io.WriteString(out, ";\n\n"); err != nil {
		return err
	}

	if len(function.Comment) > 0 {
		if err := writeFunctionComment(out, schema, function); err != nil {
			return err
		}
	}

	return nil
}

// isDefinitionProcedure checks if the definition string represents a PROCEDURE (not a FUNCTION).
// Uses omni parser: procedures have a DefElem{Defname: "isProcedure"} in Options.
func isDefinitionProcedure(definition string) bool {
	if definition == "" {
		return false
	}
	stmts, err := omnipg.Parse(definition)
	if err != nil || len(stmts) == 0 {
		return false
	}
	fn, ok := stmts[0].AST.(*ast.CreateFunctionStmt)
	if !ok || fn == nil || fn.Options == nil {
		return false
	}
	for _, item := range fn.Options.Items {
		if def, ok := item.(*ast.DefElem); ok && def.Defname == "isProcedure" {
			return true
		}
	}
	return false
}

func writeFunctionComment(out io.Writer, schema string, function *metadatapb.FunctionMetadata) error {
	// Determine if this is a PROCEDURE or FUNCTION by checking the definition
	objectType := "FUNCTION"
	if isDefinitionProcedure(function.Definition) {
		objectType = "PROCEDURE"
	}

	if _, err := io.WriteString(out, "COMMENT ON "+objectType+" \""); err != nil {
		return err
	}

	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `".`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, signatureTypesOnly(function.Signature)); err != nil {
		return err
	}

	if _, err := io.WriteString(out, ` IS '`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, escapeSingleQuote(function.Comment)); err != nil {
		return err
	}

	_, err := io.WriteString(out, "';\n\n")
	return err
}

func writeExtension(out io.Writer, extension *metadatapb.ExtensionMetadata) error {
	if _, err := io.WriteString(out, `CREATE EXTENSION IF NOT EXISTS "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, extension.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `"`); err != nil {
		return err
	}

	// Add WITH SCHEMA clause if schema is specified and not empty
	if extension.Schema != "" {
		if _, err := io.WriteString(out, ` WITH SCHEMA "`); err != nil {
			return err
		}
		if _, err := io.WriteString(out, extension.Schema); err != nil {
			return err
		}
		if _, err := io.WriteString(out, `"`); err != nil {
			return err
		}
	}

	// Add VERSION clause if version is specified
	if extension.Version != "" {
		if _, err := io.WriteString(out, ` VERSION '`); err != nil {
			return err
		}
		if _, err := io.WriteString(out, extension.Version); err != nil {
			return err
		}
		if _, err := io.WriteString(out, `'`); err != nil {
			return err
		}
	}

	if _, err := io.WriteString(out, `;`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, "\n"); err != nil {
		return err
	}

	// Write extension description (comment) if present
	if extension.Description != "" {
		if _, err := io.WriteString(out, "\n"); err != nil {
			return err
		}
		if err := writeExtensionComment(out, extension); err != nil {
			return err
		}
	} else {
		// Add blank line even if no description
		if _, err := io.WriteString(out, "\n"); err != nil {
			return err
		}
	}

	return nil
}

func writeExtensionComment(out io.Writer, extension *metadatapb.ExtensionMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON EXTENSION "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, extension.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, escapeSingleQuote(extension.Description)); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}

	_, err := io.WriteString(out, "\n\n")
	return err
}

func getSDLFormat(metadata *metadatapb.DatabaseSchemaMetadata) (string, error) {
	var buf strings.Builder

	tablesMissingColumns := collectTables(metadata.Schemas, tableMissingColumnMetadata)
	tablesWithoutColumns := collectTables(metadata.Schemas, tableHasNoColumns)

	// Write CREATE SCHEMA statements first for non-public schemas
	for _, schema := range metadata.Schemas {
		if schema.SkipDump {
			continue
		}
		if err := writeSchema(&buf, schema); err != nil {
			return "", err
		}

		// Write schema comment if present
		if len(schema.Comment) > 0 {
			if err := writeSchemaCommentSDL(&buf, schema); err != nil {
				return "", err
			}
		}
	}

	// Write extensions (before enum types and tables as they might provide types used in definitions)
	for _, extension := range metadata.Extensions {
		if err := writeExtension(&buf, extension); err != nil {
			return "", err
		}
	}

	// Build a map of serial and identity sequences that should be skipped
	// Serial sequences are identified by checking if their owner columns match the serial pattern
	// Identity sequences are identified by checking if their owner columns have IdentityGeneration set
	skipSequences := make(map[string]bool)
	for _, schema := range metadata.Schemas {
		if schema.SkipDump {
			continue
		}
		for _, table := range schema.Tables {
			if table.SkipDump {
				continue
			}
			for _, column := range table.Columns {
				// Check for serial columns
				isSerial, _ := isSerialColumn(column, table.Name, schema.Sequences)
				if isSerial {
					// Extract the sequence name from the DEFAULT clause to match the exact sequence
					// This ensures we skip the correct sequence, especially when multiple sequences
					// claim ownership of the same column.
					sequenceName := extractSequenceNameFromNextval(column.Default)

					// Find the sequence that belongs to this serial column
					for _, sequence := range schema.Sequences {
						// Match by sequence name AND ownership to ensure we skip the exact sequence
						// referenced in the DEFAULT clause
						if sequence.Name == sequenceName && sequence.OwnerTable == table.Name && sequence.OwnerColumn == column.Name {
							sequenceKey := schema.Name + "." + sequence.Name
							skipSequences[sequenceKey] = true
							break
						}
					}
				}
				// Check for identity columns
				if isIdentityColumn(column) {
					// Find the sequence that belongs to this identity column
					for _, sequence := range schema.Sequences {
						if sequence.OwnerTable == table.Name && sequence.OwnerColumn == column.Name {
							sequenceKey := schema.Name + "." + sequence.Name
							skipSequences[sequenceKey] = true
							break
						}
					}
				}
			}
		}
	}

	// Write all enum types before sequences and tables
	for _, schema := range metadata.Schemas {
		if schema.SkipDump {
			continue
		}
		for _, enumType := range schema.EnumTypes {
			if enumType.SkipDump {
				continue
			}
			if err := writeEnum(&buf, schema.Name, enumType); err != nil {
				return "", err
			}
			if _, err := buf.WriteString(";\n\n"); err != nil {
				return "", err
			}

			// Write enum type comment if present
			if len(enumType.Comment) > 0 {
				if err := writeEnumComment(&buf, schema.Name, enumType); err != nil {
					return "", err
				}
			}
		}
	}

	// Write all composite types before sequences and tables.
	if err := writeCompositeTypesSection(&buf, collectCompositeTypes(metadata.Schemas)); err != nil {
		return "", err
	}

	// Write all sequences before tables to ensure they exist before any table references them.
	// Skip sequences that belong to serial or identity columns as they will be implicitly created.
	sequenceOwnershipMap := make(map[string][]*metadatapb.SequenceMetadata)

	for _, schema := range metadata.Schemas {
		if schema.SkipDump {
			continue
		}

		for _, sequence := range schema.Sequences {
			if sequence.SkipDump {
				continue
			}
			// Skip sequences that belong to serial or identity columns
			sequenceKey := schema.Name + "." + sequence.Name
			if skipSequences[sequenceKey] {
				continue
			}
			if err := writeSequenceSDL(&buf, schema.Name, sequence); err != nil {
				return "", err
			}
			if _, err := buf.WriteString(";\n\n"); err != nil {
				return "", err
			}

			// Write sequence comment if present
			if len(sequence.Comment) > 0 {
				if err := writeSequenceCommentSDL(&buf, schema.Name, sequence); err != nil {
					return "", err
				}
			}

			// Track sequences with owners for later ownership establishment
			if sequence.OwnerTable != "" && sequence.OwnerColumn != "" {
				tableID := getObjectID(schema.Name, sequence.OwnerTable)
				sequenceOwnershipMap[tableID] = append(sequenceOwnershipMap[tableID], sequence)
			}
		}
	}

	// Build a map of sequences by table for easy lookup during table creation
	tableSequencesMap := make(map[string][]*metadatapb.SequenceMetadata)
	for _, schema := range metadata.Schemas {
		if schema.SkipDump {
			continue
		}
		for _, sequence := range schema.Sequences {
			if sequence.SkipDump {
				continue
			}
			if sequence.OwnerTable != "" {
				tableKey := schema.Name + "." + sequence.OwnerTable
				tableSequencesMap[tableKey] = append(tableSequencesMap[tableKey], sequence)
			}
		}
	}

	for _, schema := range metadata.Schemas {
		if schema.SkipDump {
			continue
		}

		// Write tables (sequences are already written above)
		for _, table := range schema.Tables {
			if table.SkipDump {
				continue
			}

			// Get sequences for this table
			tableKey := schema.Name + "." + table.Name
			tableSequences := tableSequencesMap[tableKey]

			// Write CREATE TABLE statement
			if err := writeCreateTableSDL(&buf, schema.Name, table, tableSequences, tablesMissingColumns); err != nil {
				return "", err
			}

			if _, err := buf.WriteString(";\n\n"); err != nil {
				return "", err
			}

			// Write table comment if present
			if len(table.Comment) > 0 {
				if err := writeTableCommentSDL(&buf, schema.Name, table); err != nil {
					return "", err
				}
			}

			// Write column comments if present
			for _, column := range table.Columns {
				if len(column.Comment) > 0 {
					if err := writeColumnCommentSDL(&buf, schema.Name, table.Name, column); err != nil {
						return "", err
					}
				}
			}

			// Write CREATE INDEX statements for non-constraint indexes
			if err := writeIndexesSDL(&buf, schema.Name, table); err != nil {
				return "", err
			}

			// Write index comments if present. Skip when the table has no
			// column metadata — its indexes were not emitted, so a COMMENT ON
			// INDEX would target a nonexistent index.
			if !tableMissingColumnMetadata(table) {
				for _, index := range table.Indexes {
					// Only write comment for standalone indexes (not primary key or unique constraint indexes)
					if !index.Primary && !index.Unique {
						if len(index.Comment) > 0 {
							if err := writeIndexCommentSDL(&buf, schema.Name, index); err != nil {
								return "", err
							}
						}
					}
				}
			}

			// Write CREATE TRIGGER statements
			if err := writeTriggersSDL(&buf, schema.Name, table); err != nil {
				return "", err
			}

			// Write trigger comments if present. Skip when the table has no
			// column metadata — its triggers were not emitted.
			if !tableMissingColumnMetadata(table) {
				for _, trigger := range table.Triggers {
					if len(trigger.Comment) > 0 {
						if err := writeTriggerCommentSDL(&buf, schema.Name, table.Name, trigger); err != nil {
							return "", err
						}
					}
				}
			}
		}

		// Write ALTER SEQUENCE OWNED BY statements for sequences that have owners
		// but are not serial or identity sequences
		for _, sequence := range schema.Sequences {
			if sequence.SkipDump {
				continue
			}
			// Skip sequences that belong to serial or identity columns
			sequenceKey := schema.Name + "." + sequence.Name
			if skipSequences[sequenceKey] {
				continue
			}
			// Write ALTER SEQUENCE OWNED BY for sequences with owners. Skip
			// when the owning table has no columns; the OWNED BY clause
			// would reference a column absent from the dump.
			if sequence.OwnerTable != "" && sequence.OwnerColumn != "" &&
				!tablesWithoutColumns[getObjectID(schema.Name, sequence.OwnerTable)] {
				if err := writeAlterSequenceOwnedBy(&buf, schema.Name, sequence); err != nil {
					return "", err
				}
			}
		}

		// Write views after tables
		for _, view := range schema.Views {
			if view.SkipDump {
				continue
			}

			if err := writeViewSDL(&buf, schema.Name, view); err != nil {
				return "", err
			}

			if _, err := buf.WriteString(";\n\n"); err != nil {
				return "", err
			}

			// Write view comment if present
			if len(view.Comment) > 0 {
				if err := writeViewCommentSDL(&buf, schema.Name, view); err != nil {
					return "", err
				}
			}
		}

		// Write materialized views after views
		for _, materializedView := range schema.MaterializedViews {
			if materializedView.SkipDump {
				continue
			}

			if err := writeMaterializedViewSDL(&buf, schema.Name, materializedView); err != nil {
				return "", err
			}

			if _, err := buf.WriteString(";\n\n"); err != nil {
				return "", err
			}

			// Write materialized view comment if present
			if len(materializedView.Comment) > 0 {
				if err := writeMaterializedViewCommentSDL(&buf, schema.Name, materializedView); err != nil {
					return "", err
				}
			}

			// Write indexes on materialized view
			for _, index := range materializedView.Indexes {
				// Skip constraint-based indexes as they are part of table definition
				if index.Primary || index.IsConstraint {
					continue
				}

				if err := writeIndexSDL(&buf, schema.Name, materializedView.Name, index, false); err != nil {
					return "", err
				}
				if _, err := buf.WriteString(";\n\n"); err != nil {
					return "", err
				}

				// Write index comment if present
				if len(index.Comment) > 0 {
					if err := writeIndexCommentSDL(&buf, schema.Name, index); err != nil {
						return "", err
					}
				}
			}
		}

		// Write functions and procedures after materialized views
		for _, function := range schema.Functions {
			if function.SkipDump {
				continue
			}

			if err := writeFunctionSDL(&buf, schema.Name, function); err != nil {
				return "", err
			}

			if _, err := buf.WriteString(";\n\n"); err != nil {
				return "", err
			}

			// Write function comment if present
			if len(function.Comment) > 0 {
				if err := writeFunctionCommentSDL(&buf, schema.Name, function); err != nil {
					return "", err
				}
			}
		}
	}

	// Write event triggers (last, as they depend on functions)
	for _, eventTrigger := range metadata.EventTriggers {
		if eventTrigger.SkipDump {
			continue
		}
		if err := writeEventTrigger(&buf, eventTrigger); err != nil {
			return "", err
		}
	}

	return buf.String(), nil
}

// isSerialColumn checks if a column is a serial/bigserial/smallserial type based on its properties
// Serial pattern: integer/bigint/smallint + NOT NULL + nextval(sequence) default
// extractSequenceNameFromNextval extracts the sequence name from a nextval() default value.
// It handles various PostgreSQL formats including:
// - nextval('sequence_name')
// - nextval('schema.sequence_name')
// - nextval('"Schema"."Sequence"')
// - nextval('public."MixedCase"')
// - nextval('sequence_name'::regclass)
// Returns the unquoted sequence name without schema qualification.
func extractSequenceNameFromNextval(defaultValue string) string {
	if defaultValue == "" {
		return ""
	}

	// Find nextval( - case insensitive
	defaultLower := strings.ToLower(defaultValue)
	nextvalIdx := strings.Index(defaultLower, "nextval(")
	if nextvalIdx == -1 {
		return ""
	}

	// Find the opening quote after nextval(
	startIdx := nextvalIdx + len("nextval(")
	if startIdx >= len(defaultValue) {
		return ""
	}

	// Determine quote type (' or ")
	quoteChar := byte(0)
	for startIdx < len(defaultValue) && (defaultValue[startIdx] == ' ' || defaultValue[startIdx] == '\t') {
		startIdx++
	}
	if startIdx >= len(defaultValue) {
		return ""
	}

	switch defaultValue[startIdx] {
	case '\'':
		quoteChar = '\''
	case '"':
		quoteChar = '"'
	default:
		return ""
	}

	startIdx++ // Skip opening quote

	// Find the closing quote
	endIdx := startIdx
	for endIdx < len(defaultValue) && defaultValue[endIdx] != quoteChar {
		endIdx++
	}
	if endIdx >= len(defaultValue) {
		return ""
	}

	// Extract the full sequence reference (may include schema and ::regclass)
	sequenceRef := defaultValue[startIdx:endIdx]

	// Remove ::regclass suffix if present
	if idx := strings.Index(sequenceRef, "::"); idx != -1 {
		sequenceRef = sequenceRef[:idx]
	}

	// Parse schema-qualified name: schema.sequence or "schema"."sequence"
	// We need to extract just the sequence name, handling quoted identifiers
	sequenceName := extractIdentifierFromQualifiedName(sequenceRef)

	// Remove surrounding quotes from the sequence name if present
	sequenceName = strings.TrimSpace(sequenceName)
	if len(sequenceName) >= 2 && sequenceName[0] == '"' && sequenceName[len(sequenceName)-1] == '"' {
		sequenceName = sequenceName[1 : len(sequenceName)-1]
	}

	return sequenceName
}

// extractIdentifierFromQualifiedName extracts the identifier (sequence name) from a qualified name.
// Handles cases like:
// - sequence_name -> sequence_name
// - schema.sequence_name -> sequence_name
// - "Schema"."Sequence" -> "Sequence"
// - public."MixedCase" -> "MixedCase"
func extractIdentifierFromQualifiedName(qualifiedName string) string {
	if qualifiedName == "" {
		return ""
	}

	// Look for the last dot that's not inside quotes
	inQuotes := false
	lastDotIdx := -1

	for i := 0; i < len(qualifiedName); i++ {
		if qualifiedName[i] == '"' {
			inQuotes = !inQuotes
		} else if qualifiedName[i] == '.' && !inQuotes {
			lastDotIdx = i
		}
	}

	// If we found a dot outside quotes, take everything after it
	if lastDotIdx != -1 {
		return qualifiedName[lastDotIdx+1:]
	}

	// No schema qualification found
	return qualifiedName
}

func isSerialColumn(column *metadatapb.ColumnMetadata, tableName string, sequences []*metadatapb.SequenceMetadata) (isSerial bool, serialType string) {
	// Serial columns must be NOT NULL
	if column.Nullable {
		return false, ""
	}

	// Serial columns must have a nextval() default
	if !strings.Contains(strings.ToLower(column.Default), "nextval(") {
		return false, ""
	}

	// Check type and map to serial type
	var expectedSerialType string
	switch strings.ToLower(column.Type) {
	case "integer":
		expectedSerialType = "serial"
	case "bigint":
		expectedSerialType = "bigserial"
	case "smallint":
		expectedSerialType = "smallserial"
	default:
		return false, ""
	}

	// IMPORTANT: Only treat as serial if the sequence is owned by this column
	// This prevents converting columns that use independent sequences to serial type
	sequenceName := extractSequenceNameFromNextval(column.Default)
	if sequenceName == "" {
		return false, ""
	}

	// Check if any sequence with this name is owned by this column
	for _, seq := range sequences {
		if seq.Name == sequenceName && seq.OwnerTable == tableName && seq.OwnerColumn == column.Name {
			return true, expectedSerialType
		}
	}

	// Sequence is not owned by this column, so don't convert to serial
	return false, ""
}

// writeColumnSDL writes a single column definition to the output writer
// This function is extracted from writeCreateTableSDL to enable code reuse
// sequences parameter is optional and used to find identity sequences for the column
// tableName is used to verify sequence ownership for serial column detection
func writeColumnSDL(out io.Writer, column *metadatapb.ColumnMetadata, tableName string, sequences []*metadatapb.SequenceMetadata) error {
	if _, err := io.WriteString(out, `"`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, column.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" `); err != nil {
		return err
	}

	// Check if this is an identity column
	if isIdentityColumn(column) {
		// Find the sequence for this identity column
		var identitySequence *metadatapb.SequenceMetadata
		for _, seq := range sequences {
			if seq.OwnerColumn == column.Name {
				identitySequence = seq
				break
			}
		}

		// Write type
		if _, err := io.WriteString(out, column.Type); err != nil {
			return err
		}

		// Write GENERATED ... AS IDENTITY
		if _, err := io.WriteString(out, " GENERATED "); err != nil {
			return err
		}
		if column.IdentityGeneration == metadatapb.ColumnMetadata_ALWAYS {
			if _, err := io.WriteString(out, "ALWAYS"); err != nil {
				return err
			}
		} else {
			if _, err := io.WriteString(out, "BY DEFAULT"); err != nil {
				return err
			}
		}
		if _, err := io.WriteString(out, " AS IDENTITY"); err != nil {
			return err
		}

		// Write identity options if we have the sequence
		if identitySequence != nil {
			hasOptions := false
			// Check if we need to write options (non-default values)
			if identitySequence.Start != "" && identitySequence.Start != "1" {
				hasOptions = true
			}
			if identitySequence.Increment != "" && identitySequence.Increment != "1" {
				hasOptions = true
			}

			if hasOptions {
				if _, err := io.WriteString(out, " ("); err != nil {
					return err
				}

				first := true
				if identitySequence.Start != "" && identitySequence.Start != "1" {
					if _, err := fmt.Fprintf(out, "START WITH %s", identitySequence.Start); err != nil {
						return err
					}
					first = false
				}
				if identitySequence.Increment != "" && identitySequence.Increment != "1" {
					if !first {
						if _, err := io.WriteString(out, " "); err != nil {
							return err
						}
					}
					if _, err := fmt.Fprintf(out, "INCREMENT BY %s", identitySequence.Increment); err != nil {
						return err
					}
				}

				if _, err := io.WriteString(out, ")"); err != nil {
					return err
				}
			}
		}

		// Identity columns are implicitly NOT NULL, skip that
		return nil
	}

	// Check if this is a serial column and convert it back to serial type
	isSerial, serialType := isSerialColumn(column, tableName, sequences)
	if isSerial {
		// Use serial type instead of integer + nextval + NOT NULL
		if _, err := io.WriteString(out, serialType); err != nil {
			return err
		}
		// Skip DEFAULT and NOT NULL as they are implicit in serial
	} else {
		// Normal column handling
		if _, err := io.WriteString(out, column.Type); err != nil {
			return err
		}

		if column.Default != "" {
			if _, err := io.WriteString(out, ` DEFAULT `); err != nil {
				return err
			}
			if _, err := io.WriteString(out, column.Default); err != nil {
				return err
			}
		}

		if !column.Nullable {
			if _, err := io.WriteString(out, ` NOT NULL`); err != nil {
				return err
			}
		}
	}

	if column.Collation != "" {
		if _, err := io.WriteString(out, ` COLLATE "`); err != nil {
			return err
		}
		if _, err := io.WriteString(out, column.Collation); err != nil {
			return err
		}
		if _, err := io.WriteString(out, `"`); err != nil {
			return err
		}
	}

	return nil
}

func writeCreateTableSDL(out io.Writer, schemaName string, table *metadatapb.TableMetadata, sequences []*metadatapb.SequenceMetadata, tablesMissingColumns map[string]bool) error {
	if _, err := io.WriteString(out, `CREATE TABLE "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, schemaName); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, table.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" (`); err != nil {
		return err
	}

	// first tracks whether any element has been written inside the parens so
	// the separator on constraints is suppressed when there are no columns.
	first := true
	writeItemSep := func() error {
		if first {
			first = false
			_, err := io.WriteString(out, "\n    ")
			return err
		}
		_, err := io.WriteString(out, ",\n    ")
		return err
	}

	// Write columns
	for _, column := range table.Columns {
		if err := writeItemSep(); err != nil {
			return err
		}
		if err := writeColumnSDL(out, column, table.Name, sequences); err != nil {
			return err
		}
	}

	// Write table constraints
	if err := writeTableConstraintsSDL(out, table, writeItemSep, tablesMissingColumns); err != nil {
		return err
	}

	_, err := io.WriteString(out, "\n)")
	return err
}

func writeTableConstraintsSDL(out io.Writer, table *metadatapb.TableMetadata, writeSep func() error, tablesMissingColumns map[string]bool) error {
	if tableMissingColumnMetadata(table) {
		// Constraints would reference columns absent from the dump.
		return nil
	}

	// Write primary key constraint
	for _, index := range table.Indexes {
		if index.Primary {
			if err := writeSep(); err != nil {
				return err
			}
			if err := writePrimaryKeyConstraintSDL(out, index); err != nil {
				return err
			}
		}
	}

	// Write unique constraints
	for _, index := range table.Indexes {
		if index.Unique && !index.Primary && index.IsConstraint {
			if err := writeSep(); err != nil {
				return err
			}
			if err := writeUniqueKeyConstraintSDL(out, index); err != nil {
				return err
			}
		}
	}

	// Write check constraints
	for _, check := range table.CheckConstraints {
		if err := writeSep(); err != nil {
			return err
		}
		if err := writeCheckConstraintSDL(out, check); err != nil {
			return err
		}
	}

	// Write EXCLUDE constraints
	for _, exclude := range table.ExcludeConstraints {
		if err := writeSep(); err != nil {
			return err
		}
		if err := writeExcludeConstraintSDL(out, exclude); err != nil {
			return err
		}
	}

	// Write foreign key constraints. Skip foreign keys whose referenced
	// table has no column metadata; the REFERENCES clause would name
	// columns absent from the dump.
	for _, fk := range table.ForeignKeys {
		if tablesMissingColumns[getObjectID(fk.ReferencedSchema, fk.ReferencedTable)] {
			continue
		}
		if err := writeSep(); err != nil {
			return err
		}
		if err := writeForeignKeyConstraintSDL(out, fk); err != nil {
			return err
		}
	}

	return nil
}

func writeIndexesSDL(out io.Writer, schemaName string, table *metadatapb.TableMetadata) error {
	if tableMissingColumnMetadata(table) {
		// Indexes would reference columns absent from the dump.
		return nil
	}
	for _, index := range table.Indexes {
		// Skip indexes that are constraints (primary key, unique constraints)
		// These are already handled in the CREATE TABLE statement
		if index.Primary || index.IsConstraint {
			continue
		}

		if err := writeIndexSDL(out, schemaName, table.Name, index, len(table.Partitions) > 0); err != nil {
			return err
		}

		if _, err := io.WriteString(out, ";\n\n"); err != nil {
			return err
		}
	}
	return nil
}

func writeIndexSDL(out io.Writer, schemaName string, tableName string, index *metadatapb.IndexMetadata, useOnlyClause bool) error {
	return writeIndexInternal(out, schemaName, tableName, index, useOnlyClause, false)
}

// writeIndexInternal is the core index writing function with options for different modes
func writeIndexInternal(out io.Writer, schema string, table string, index *metadatapb.IndexMetadata, useOnlyClause bool, includeTerminatorAndComment bool) error {
	if index.Unique {
		if _, err := io.WriteString(out, `CREATE UNIQUE INDEX "`); err != nil {
			return err
		}
	} else {
		if _, err := io.WriteString(out, `CREATE INDEX "`); err != nil {
			return err
		}
	}
	if _, err := io.WriteString(out, index.Name); err != nil {
		return err
	}

	// TODO: useOnlyClause assumes partition-structured metadata. If legacy
	// PostgreSQL table inheritance support is ever needed, revisit with
	// definition-driven ONLY extraction.
	if useOnlyClause {
		if _, err := io.WriteString(out, `" ON ONLY "`); err != nil {
			return err
		}
	} else {
		if _, err := io.WriteString(out, `" ON "`); err != nil {
			return err
		}
	}

	if _, err := io.WriteString(out, schema); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, table); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"`); err != nil {
		return err
	}

	// Add USING clause for non-btree indexes
	if index.Type != "" && index.Type != "btree" {
		if _, err := fmt.Fprintf(out, " USING %s", strings.ToUpper(index.Type)); err != nil {
			return err
		}
	}

	if _, err := io.WriteString(out, ` `); err != nil {
		return err
	}
	if err := writeIndexKeyList(out, index); err != nil {
		return err
	}

	// Write INCLUDE and WHERE clauses from the full index definition (from pg_get_indexdef).
	if index.Definition != "" {
		includeClause, whereClause := extractIndexClauses(index.Definition)
		if includeClause != "" {
			if _, err := fmt.Fprintf(out, " %s", includeClause); err != nil {
				return err
			}
		}
		if whereClause != "" {
			if _, err := fmt.Fprintf(out, " WHERE %s", whereClause); err != nil {
				return err
			}
		}
	}

	if includeTerminatorAndComment {
		if _, err := io.WriteString(out, ";\n\n"); err != nil {
			return err
		}

		if len(index.Comment) > 0 {
			if err := writeIndexComment(out, schema, index); err != nil {
				return err
			}
		}
	}

	return nil
}

func writeViewSDL(out io.Writer, schemaName string, view *metadatapb.ViewMetadata) error {
	if _, err := io.WriteString(out, `CREATE VIEW "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, schemaName); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, view.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" AS `); err != nil {
		return err
	}

	// The view definition should already include the SELECT statement
	definition := strings.TrimSpace(view.Definition)
	// Remove trailing semicolon if present
	definition = strings.TrimSuffix(definition, ";")

	_, err := io.WriteString(out, definition)
	return err
}

func writeMaterializedViewSDL(out io.Writer, schemaName string, view *metadatapb.MaterializedViewMetadata) error {
	if _, err := io.WriteString(out, `CREATE MATERIALIZED VIEW "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, schemaName); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, view.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `" AS `); err != nil {
		return err
	}

	// The materialized view definition should already include the SELECT statement
	definition := strings.TrimSpace(view.Definition)
	// Remove trailing semicolon if present
	definition = strings.TrimSuffix(definition, ";")

	_, err := io.WriteString(out, definition)
	return err
}

func writeFunctionSDL(out io.Writer, _ string, function *metadatapb.FunctionMetadata) error {
	// The function definition should already include the complete CREATE FUNCTION statement
	definition := strings.TrimSpace(function.Definition)
	// Remove trailing semicolon if present
	definition = strings.TrimSuffix(definition, ";")

	_, err := io.WriteString(out, definition)
	return err
}

func writeSequenceSDL(out io.Writer, schemaName string, sequence *metadatapb.SequenceMetadata) error {
	// Write CREATE SEQUENCE statement with schema and name
	if _, err := fmt.Fprintf(out, "CREATE SEQUENCE \"%s\".\"%s\"", schemaName, sequence.Name); err != nil {
		return err
	}

	// Add data type (always output for consistency with non-SDL format)
	if sequence.DataType != "" {
		if _, err := fmt.Fprintf(out, " AS %s", sequence.DataType); err != nil {
			return err
		}
	}

	// Add START WITH (always output for consistency with non-SDL format)
	if sequence.Start != "" {
		if _, err := fmt.Fprintf(out, " START WITH %s", sequence.Start); err != nil {
			return err
		}
	}

	// Add INCREMENT BY (always output for consistency with non-SDL format)
	if sequence.Increment != "" {
		if _, err := fmt.Fprintf(out, " INCREMENT BY %s", sequence.Increment); err != nil {
			return err
		}
	}

	// Add MINVALUE (always output for consistency with non-SDL format)
	if sequence.MinValue != "" {
		if _, err := fmt.Fprintf(out, " MINVALUE %s", sequence.MinValue); err != nil {
			return err
		}
	}

	// Add MAXVALUE (always output for consistency with non-SDL format)
	if sequence.MaxValue != "" {
		if _, err := fmt.Fprintf(out, " MAXVALUE %s", sequence.MaxValue); err != nil {
			return err
		}
	}

	// Add CYCLE/NO CYCLE (always output for consistency with non-SDL format)
	if sequence.Cycle {
		if _, err := io.WriteString(out, " CYCLE"); err != nil {
			return err
		}
	} else {
		if _, err := io.WriteString(out, " NO CYCLE"); err != nil {
			return err
		}
	}

	// Add CACHE (new field to match non-SDL format)
	if sequence.CacheSize != "" {
		if _, err := fmt.Fprintf(out, " CACHE %s", sequence.CacheSize); err != nil {
			return err
		}
	}

	return nil
}

func writeSchema(out io.Writer, schema *metadatapb.SchemaMetadata) error {
	if schema.Name == "public" {
		return nil
	}

	if _, err := io.WriteString(out, `CREATE SCHEMA IF NOT EXISTS "`); err != nil {
		return err
	}

	if _, err := io.WriteString(out, schema.Name); err != nil {
		return err
	}

	if _, err := io.WriteString(out, `";`); err != nil {
		return err
	}

	_, err := io.WriteString(out, "\n\n")
	return err
}

// writeCheckConstraintSDL writes a single check constraint SDL
func writeCheckConstraintSDL(out io.Writer, check *metadatapb.CheckConstraintMetadata) error {
	if _, err := io.WriteString(out, `CONSTRAINT "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, check.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" CHECK `); err != nil {
		return err
	}
	if _, err := io.WriteString(out, check.Expression); err != nil {
		return err
	}
	return nil
}

func writeExcludeConstraintSDL(out io.Writer, exclude *metadatapb.ExcludeConstraintMetadata) error {
	if _, err := io.WriteString(out, `CONSTRAINT "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, exclude.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" `); err != nil {
		return err
	}
	// Expression already includes "EXCLUDE USING ..." prefix
	if _, err := io.WriteString(out, exclude.Expression); err != nil {
		return err
	}
	return nil
}

// writeForeignKeyConstraintSDL writes a single foreign key constraint SDL
func writeForeignKeyConstraintSDL(out io.Writer, fk *metadatapb.ForeignKeyMetadata) error {
	if _, err := io.WriteString(out, `CONSTRAINT "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, fk.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" FOREIGN KEY (`); err != nil {
		return err
	}
	for i, column := range fk.Columns {
		if i > 0 {
			if _, err := io.WriteString(out, ", "); err != nil {
				return err
			}
		}
		if _, err := io.WriteString(out, `"`); err != nil {
			return err
		}
		if _, err := io.WriteString(out, column); err != nil {
			return err
		}
		if _, err := io.WriteString(out, `"`); err != nil {
			return err
		}
	}
	if _, err := io.WriteString(out, ") REFERENCES "); err != nil {
		return err
	}

	// Always add schema qualifier according to project principle
	if fk.ReferencedSchema != "" {
		if _, err := io.WriteString(out, `"`); err != nil {
			return err
		}
		if _, err := io.WriteString(out, fk.ReferencedSchema); err != nil {
			return err
		}
		if _, err := io.WriteString(out, `".`); err != nil {
			return err
		}
	}

	if _, err := io.WriteString(out, `"`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, fk.ReferencedTable); err != nil {
		return err
	}
	if _, err := io.WriteString(out, "\" ("); err != nil {
		return err
	}
	for i, column := range fk.ReferencedColumns {
		if i > 0 {
			if _, err := io.WriteString(out, ", "); err != nil {
				return err
			}
		}
		if _, err := io.WriteString(out, `"`); err != nil {
			return err
		}
		if _, err := io.WriteString(out, column); err != nil {
			return err
		}
		if _, err := io.WriteString(out, `"`); err != nil {
			return err
		}
	}
	if _, err := io.WriteString(out, ")"); err != nil {
		return err
	}

	// Add ON DELETE clause if specified
	if fk.OnDelete != "" && fk.OnDelete != "NO ACTION" {
		if _, err := io.WriteString(out, " ON DELETE "); err != nil {
			return err
		}
		if _, err := io.WriteString(out, fk.OnDelete); err != nil {
			return err
		}
	}

	// Add ON UPDATE clause if specified
	if fk.OnUpdate != "" && fk.OnUpdate != "NO ACTION" {
		if _, err := io.WriteString(out, " ON UPDATE "); err != nil {
			return err
		}
		if _, err := io.WriteString(out, fk.OnUpdate); err != nil {
			return err
		}
	}
	return nil
}

// GetMultiFileDatabaseDefinition generates multi-file SDL schema for PostgreSQL.
func GetMultiFileDatabaseDefinition(ctx schema.GetDefinitionContext, metadata *metadatapb.DatabaseSchemaMetadata) (*schema.MultiFileSchemaResult, error) {
	metadata = filterBackupSchemaIfNecessary(ctx, metadata)

	// Clone before mutating: the caller's *DatabaseSchemaMetadata is often a
	// shared pointer returned from store.dbSchemaCache (see
	// backend/store/model/database.go:GetProto), so concurrent callers could
	// race on in-place normalization writes below.
	metadata = proto.CloneOf(metadata)

	// Repair historical non-canonical metadata shapes (e.g. index key
	// expressions stored without outer parens) so emission produces valid SQL.
	// See legacy_normalize.go for removal criteria.
	normalizeLegacyMetadata(metadata)

	if len(metadata.Schemas) == 0 {
		return &schema.MultiFileSchemaResult{Files: []schema.File{}}, nil
	}

	var files []schema.File

	// Build skip sequences map (for serial and identity columns)
	skipSequences := buildSkipSequencesMap(metadata)

	// Build table sequences map for each table
	tableSequencesMap := buildTableSequencesMap(metadata)

	tablesMissingColumns := collectTables(metadata.Schemas, tableMissingColumnMetadata)

	// Generate every schema.sql first: extensions may install into these
	// schemas and all other files live inside them.
	for _, schemaMetadata := range metadata.Schemas {
		if schemaMetadata.SkipDump {
			continue
		}
		schemaName := schemaMetadata.Name
		if schemaName == "" {
			schemaName = "public"
		}
		schemaContent, err := getMultiFileSchemaSDL(schemaName, schemaMetadata)
		if err != nil {
			return nil, errors.Wrapf(err, "failed to generate schema SDL for %s", schemaName)
		}
		if schemaContent != "" {
			files = append(files, schema.File{
				Name:    fmt.Sprintf("schemas/%s/schema.sql", schemaName),
				Content: schemaContent,
			})
		}
	}

	// Generate extensions.sql next: composite type attributes and table
	// columns may use extension-provided types, and the extensions may
	// install into the schemas created above.
	if len(metadata.Extensions) > 0 {
		var buf strings.Builder
		for i, extension := range metadata.Extensions {
			if i > 0 {
				buf.WriteString("\n")
			}

			if err := writeExtension(&buf, extension); err != nil {
				return nil, errors.Wrapf(err, "failed to generate extension SDL for %s", extension.Name)
			}
		}

		files = append(files, schema.File{
			Name:    "extensions.sql",
			Content: buf.String(),
		})
	}

	// Generate files for each schema
	for _, schemaMetadata := range metadata.Schemas {
		if schemaMetadata.SkipDump {
			continue
		}

		schemaName := schemaMetadata.Name
		if schemaName == "" {
			schemaName = "public"
		}

		// Collect independent sequences (no owner) for this schema
		var independentSequences []*metadatapb.SequenceMetadata
		for _, sequence := range schemaMetadata.Sequences {
			if sequence.SkipDump {
				continue
			}
			// Skip sequences that belong to serial or identity columns
			sequenceKey := schemaName + "." + sequence.Name
			if skipSequences[sequenceKey] {
				continue
			}

			// Collect independent sequences (no owner)
			if sequence.OwnerTable == "" || sequence.OwnerColumn == "" {
				independentSequences = append(independentSequences, sequence)
			}
		}

		// Generate a single file for all enum and composite types in this
		// schema, listed before table files: table columns may use these types.
		if len(schemaMetadata.EnumTypes) > 0 || len(schemaMetadata.CompositeTypes) > 0 {
			var buf strings.Builder
			hasTypes := false
			for i, enumType := range schemaMetadata.EnumTypes {
				if enumType.SkipDump {
					continue
				}

				if hasTypes {
					buf.WriteString("\n")
				}
				hasTypes = true

				if i > 0 {
					buf.WriteString("\n")
				}

				if err := writeEnum(&buf, schemaName, enumType); err != nil {
					return nil, errors.Wrapf(err, "failed to generate enum type SDL for %s.%s", schemaName, enumType.Name)
				}
				buf.WriteString(";\n")

				// Add enum type comment if present
				if len(enumType.Comment) > 0 {
					buf.WriteString("\n")
					if err := writeEnumComment(&buf, schemaName, enumType); err != nil {
						return nil, errors.Wrapf(err, "failed to generate enum type comment for %s.%s", schemaName, enumType.Name)
					}
				}
			}

			// Composite types follow enums, ordered so referenced composites
			// come first.
			var compositeTypes []qualifiedCompositeType
			for _, composite := range schemaMetadata.CompositeTypes {
				if composite.SkipDump {
					continue
				}
				compositeTypes = append(compositeTypes, qualifiedCompositeType{Schema: schemaName, Composite: composite})
			}
			for _, t := range sortCompositeTypesTopologically(compositeTypes) {
				if hasTypes {
					buf.WriteString("\n")
				}
				hasTypes = true

				if err := writeCompositeType(&buf, schemaName, t.Composite); err != nil {
					return nil, errors.Wrapf(err, "failed to generate composite type SDL for %s.%s", schemaName, t.Composite.Name)
				}
				buf.WriteString(";\n")

				if compositeTypeHasComments(t.Composite) {
					buf.WriteString("\n")
					if err := writeCompositeTypeComments(&buf, schemaName, t.Composite); err != nil {
						return nil, errors.Wrapf(err, "failed to generate composite type comment for %s.%s", schemaName, t.Composite.Name)
					}
				}
			}

			if hasTypes {
				files = append(files, schema.File{
					Name:    fmt.Sprintf("schemas/%s/types.sql", schemaName),
					Content: buf.String(),
				})
			}
		}

		// Generate table files
		for _, table := range schemaMetadata.Tables {
			if table.SkipDump {
				continue
			}

			// Get sequences for this table
			tableKey := schemaName + "." + table.Name
			tableSequences := tableSequencesMap[tableKey]

			var buf strings.Builder
			// Write CREATE TABLE statement in SDL format
			if err := writeCreateTableSDL(&buf, schemaName, table, tableSequences, tablesMissingColumns); err != nil {
				return nil, errors.Wrapf(err, "failed to generate table SDL for %s.%s", schemaName, table.Name)
			}
			buf.WriteString(";\n\n")

			// Write table comment if present
			if len(table.Comment) > 0 {
				if err := writeTableCommentSDL(&buf, schemaName, table); err != nil {
					return nil, errors.Wrapf(err, "failed to generate table comment for %s.%s", schemaName, table.Name)
				}
			}

			// Write column comments if present
			for _, column := range table.Columns {
				if len(column.Comment) > 0 {
					if err := writeColumnCommentSDL(&buf, schemaName, table.Name, column); err != nil {
						return nil, errors.Wrapf(err, "failed to generate column comment for %s.%s.%s", schemaName, table.Name, column.Name)
					}
				}
			}

			// Write CREATE INDEX statements for non-constraint indexes
			if err := writeIndexesSDL(&buf, schemaName, table); err != nil {
				return nil, errors.Wrapf(err, "failed to generate indexes SDL for %s.%s", schemaName, table.Name)
			}

			// Write index comments if present. Skip when the table has no
			// column metadata — its indexes were not emitted, so a COMMENT ON
			// INDEX would target a nonexistent index.
			if !tableMissingColumnMetadata(table) {
				for _, index := range table.Indexes {
					// Only write comment for standalone indexes (not primary key or unique constraint indexes)
					if !index.Primary && !index.Unique {
						if len(index.Comment) > 0 {
							if err := writeIndexCommentSDL(&buf, schemaName, index); err != nil {
								return nil, errors.Wrapf(err, "failed to generate index comment for %s.%s", schemaName, index.Name)
							}
						}
					}
				}
			}

			// Write CREATE TRIGGER statements
			if err := writeTriggersSDL(&buf, schemaName, table); err != nil {
				return nil, errors.Wrapf(err, "failed to generate triggers SDL for %s.%s", schemaName, table.Name)
			}

			// Write trigger comments if present. Skip when the table has no
			// column metadata — its triggers were not emitted.
			if !tableMissingColumnMetadata(table) {
				for _, trigger := range table.Triggers {
					if len(trigger.Comment) > 0 {
						if err := writeTriggerCommentSDL(&buf, schemaName, table.Name, trigger); err != nil {
							return nil, errors.Wrapf(err, "failed to generate trigger comment for %s.%s.%s", schemaName, table.Name, trigger.Name)
						}
					}
				}
			}

			// Write owned sequences (non-serial/non-identity) for this table
			for _, sequence := range tableSequences {
				// Skip sequences that belong to serial or identity columns
				sequenceKey := schemaName + "." + sequence.Name
				if skipSequences[sequenceKey] {
					continue
				}

				// Only include sequences with owners (non-independent sequences)
				if sequence.OwnerTable != "" && sequence.OwnerColumn != "" {
					buf.WriteString("\n")
					if err := writeSequenceSDL(&buf, schemaName, sequence); err != nil {
						return nil, errors.Wrapf(err, "failed to generate sequence SDL for %s.%s", schemaName, sequence.Name)
					}
					buf.WriteString(";\n")

					// Add sequence comment if present
					if len(sequence.Comment) > 0 {
						buf.WriteString("\n")
						if err := writeSequenceCommentSDL(&buf, schemaName, sequence); err != nil {
							return nil, errors.Wrapf(err, "failed to generate sequence comment for %s.%s", schemaName, sequence.Name)
						}
					}

					// Add ALTER SEQUENCE OWNED BY. Skip when the owning table
					// has no columns; the OWNED BY clause would reference a
					// column absent from the dump.
					if !tableHasNoColumns(table) {
						buf.WriteString("\n")
						if err := writeAlterSequenceOwnedBy(&buf, schemaName, sequence); err != nil {
							return nil, errors.Wrapf(err, "failed to generate ALTER SEQUENCE OWNED BY for %s.%s", schemaName, sequence.Name)
						}
					}
				}
			}

			files = append(files, schema.File{
				Name:    fmt.Sprintf("schemas/%s/tables/%s.sql", schemaName, table.Name),
				Content: buf.String(),
			})
		}

		// Generate view files
		for _, view := range schemaMetadata.Views {
			if view.SkipDump {
				continue
			}

			var buf strings.Builder
			if err := writeViewSDL(&buf, schemaName, view); err != nil {
				return nil, errors.Wrapf(err, "failed to generate view SDL for %s.%s", schemaName, view.Name)
			}
			buf.WriteString(";\n")

			// Write view comment if present
			if len(view.Comment) > 0 {
				buf.WriteString("\n")
				if err := writeViewCommentSDL(&buf, schemaName, view); err != nil {
					return nil, errors.Wrapf(err, "failed to generate view comment for %s.%s", schemaName, view.Name)
				}
			}

			files = append(files, schema.File{
				Name:    fmt.Sprintf("schemas/%s/views/%s.sql", schemaName, view.Name),
				Content: buf.String(),
			})
		}

		// Generate materialized view files
		for _, materializedView := range schemaMetadata.MaterializedViews {
			if materializedView.SkipDump {
				continue
			}

			var buf strings.Builder
			if err := writeMaterializedViewSDL(&buf, schemaName, materializedView); err != nil {
				return nil, errors.Wrapf(err, "failed to generate materialized view SDL for %s.%s", schemaName, materializedView.Name)
			}
			buf.WriteString(";\n")

			// Write materialized view comment if present
			if len(materializedView.Comment) > 0 {
				buf.WriteString("\n")
				if err := writeMaterializedViewCommentSDL(&buf, schemaName, materializedView); err != nil {
					return nil, errors.Wrapf(err, "failed to generate materialized view comment for %s.%s", schemaName, materializedView.Name)
				}
			}

			// Write indexes on materialized view
			for _, index := range materializedView.Indexes {
				// Skip constraint-based indexes
				if index.Primary || index.IsConstraint {
					continue
				}

				buf.WriteString("\n")
				if err := writeIndexSDL(&buf, schemaName, materializedView.Name, index, false); err != nil {
					return nil, errors.Wrapf(err, "failed to generate index SDL for %s.%s", schemaName, index.Name)
				}
				buf.WriteString(";\n")

				// Write index comment if present
				if len(index.Comment) > 0 {
					buf.WriteString("\n")
					if err := writeIndexCommentSDL(&buf, schemaName, index); err != nil {
						return nil, errors.Wrapf(err, "failed to generate index comment for %s.%s", schemaName, index.Name)
					}
				}
			}

			files = append(files, schema.File{
				Name:    fmt.Sprintf("schemas/%s/materialized_views/%s.sql", schemaName, materializedView.Name),
				Content: buf.String(),
			})
		}

		// Generate function files
		for _, function := range schemaMetadata.Functions {
			if function.SkipDump {
				continue
			}

			var buf strings.Builder
			if err := writeFunctionSDL(&buf, schemaName, function); err != nil {
				return nil, errors.Wrapf(err, "failed to generate function SDL for %s.%s", schemaName, function.Name)
			}
			buf.WriteString(";\n")

			// Write function comment if present
			if len(function.Comment) > 0 {
				buf.WriteString("\n")
				if err := writeFunctionCommentSDL(&buf, schemaName, function); err != nil {
					return nil, errors.Wrapf(err, "failed to generate function comment for %s.%s", schemaName, function.Name)
				}
			}

			// Determine if this is a PROCEDURE or FUNCTION to use different folder
			folderName := "functions"
			if isDefinitionProcedure(function.Definition) {
				folderName = "procedures"
			}

			files = append(files, schema.File{
				Name:    fmt.Sprintf("schemas/%s/%s/%s.sql", schemaName, folderName, function.Name),
				Content: buf.String(),
			})
		}

		// Generate a single file for all independent sequences (no owner) in this schema
		if len(independentSequences) > 0 {
			var buf strings.Builder
			for i, sequence := range independentSequences {
				if i > 0 {
					buf.WriteString("\n")
				}

				if err := writeSequenceSDL(&buf, schemaName, sequence); err != nil {
					return nil, errors.Wrapf(err, "failed to generate sequence SDL for %s.%s", schemaName, sequence.Name)
				}
				buf.WriteString(";\n")

				// Add sequence comment if present
				if len(sequence.Comment) > 0 {
					buf.WriteString("\n")
					if err := writeSequenceCommentSDL(&buf, schemaName, sequence); err != nil {
						return nil, errors.Wrapf(err, "failed to generate sequence comment for %s.%s", schemaName, sequence.Name)
					}
				}
			}

			files = append(files, schema.File{
				Name:    fmt.Sprintf("schemas/%s/sequences.sql", schemaName),
				Content: buf.String(),
			})
		}
	}

	// Generate event_triggers.sql file if there are any event triggers
	if len(metadata.EventTriggers) > 0 {
		var buf strings.Builder
		for i, eventTrigger := range metadata.EventTriggers {
			if eventTrigger.SkipDump {
				continue
			}
			if i > 0 {
				buf.WriteString("\n")
			}

			if err := writeEventTrigger(&buf, eventTrigger); err != nil {
				return nil, errors.Wrapf(err, "failed to generate event trigger SDL for %s", eventTrigger.Name)
			}
		}

		if buf.Len() > 0 {
			files = append(files, schema.File{
				Name:    "event_triggers.sql",
				Content: buf.String(),
			})
		}
	}

	return &schema.MultiFileSchemaResult{Files: files}, nil
}

func getMultiFileSchemaSDL(schemaName string, schemaMetadata *metadatapb.SchemaMetadata) (string, error) {
	var buf strings.Builder

	if schemaName != "public" {
		if err := writeSchema(&buf, &metadatapb.SchemaMetadata{Name: schemaName}); err != nil {
			return "", err
		}
	}

	if schemaMetadata.GetComment() != "" {
		if err := writeSchemaCommentSDL(&buf, &metadatapb.SchemaMetadata{
			Name:    schemaName,
			Comment: schemaMetadata.GetComment(),
		}); err != nil {
			return "", err
		}
	}

	return buf.String(), nil
}

// buildSkipSequencesMap builds a map of sequences that should be skipped (serial and identity sequences).
func buildSkipSequencesMap(metadata *metadatapb.DatabaseSchemaMetadata) map[string]bool {
	skipSequences := make(map[string]bool)
	for _, schema := range metadata.Schemas {
		if schema.SkipDump {
			continue
		}
		for _, table := range schema.Tables {
			if table.SkipDump {
				continue
			}
			for _, column := range table.Columns {
				// Check for serial columns
				isSerial, _ := isSerialColumn(column, table.Name, schema.Sequences)
				if isSerial {
					// Extract the sequence name from the DEFAULT clause to match the exact sequence
					// This ensures we skip the correct sequence, especially when multiple sequences
					// claim ownership of the same column.
					sequenceName := extractSequenceNameFromNextval(column.Default)

					for _, sequence := range schema.Sequences {
						// Match by sequence name AND ownership to ensure we skip the exact sequence
						// referenced in the DEFAULT clause
						if sequence.Name == sequenceName && sequence.OwnerTable == table.Name && sequence.OwnerColumn == column.Name {
							sequenceKey := schema.Name + "." + sequence.Name
							skipSequences[sequenceKey] = true
							break
						}
					}
				}
				// Check for identity columns
				if isIdentityColumn(column) {
					for _, sequence := range schema.Sequences {
						if sequence.OwnerTable == table.Name && sequence.OwnerColumn == column.Name {
							sequenceKey := schema.Name + "." + sequence.Name
							skipSequences[sequenceKey] = true
							break
						}
					}
				}
			}
		}
	}
	return skipSequences
}

// buildTableSequencesMap builds a map of sequences by table for easy lookup during table creation.
func buildTableSequencesMap(metadata *metadatapb.DatabaseSchemaMetadata) map[string][]*metadatapb.SequenceMetadata {
	tableSequencesMap := make(map[string][]*metadatapb.SequenceMetadata)
	for _, schema := range metadata.Schemas {
		if schema.SkipDump {
			continue
		}
		for _, sequence := range schema.Sequences {
			if sequence.SkipDump {
				continue
			}
			if sequence.OwnerTable != "" {
				tableKey := schema.Name + "." + sequence.OwnerTable
				tableSequencesMap[tableKey] = append(tableSequencesMap[tableKey], sequence)
			}
		}
	}
	return tableSequencesMap
}

func writeMaterializedViewCommentSDL(out io.Writer, schemaName string, view *metadatapb.MaterializedViewMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON MATERIALIZED VIEW "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schemaName); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, view.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, escapeSingleQuote(view.Comment)); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}
	_, err := io.WriteString(out, "\n\n")
	return err
}

// SDL Comment Functions

func writeSchemaCommentSDL(out io.Writer, schema *metadatapb.SchemaMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON SCHEMA "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schema.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, escapeSingleQuote(schema.Comment)); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}
	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeTableCommentSDL(out io.Writer, schemaName string, table *metadatapb.TableMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON TABLE "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schemaName); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, table.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, escapeSingleQuote(table.Comment)); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}
	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeColumnCommentSDL(out io.Writer, schemaName, tableName string, column *metadatapb.ColumnMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON COLUMN "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schemaName); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, tableName); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, column.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, escapeSingleQuote(column.Comment)); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}
	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeViewCommentSDL(out io.Writer, schemaName string, view *metadatapb.ViewMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON VIEW "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schemaName); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, view.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, escapeSingleQuote(view.Comment)); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}
	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeFunctionCommentSDL(out io.Writer, schemaName string, function *metadatapb.FunctionMetadata) error {
	// Determine if this is a PROCEDURE or FUNCTION by checking the definition
	objectType := "FUNCTION"
	if isDefinitionProcedure(function.Definition) {
		objectType = "PROCEDURE"
	}

	if _, err := io.WriteString(out, "COMMENT ON "+objectType+" \""); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schemaName); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `".`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, signatureTypesOnly(function.Signature)); err != nil {
		return err
	}
	if _, err := io.WriteString(out, ` IS '`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, escapeSingleQuote(function.Comment)); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}
	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeSequenceCommentSDL(out io.Writer, schemaName string, sequence *metadatapb.SequenceMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON SEQUENCE "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schemaName); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, sequence.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, escapeSingleQuote(sequence.Comment)); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}
	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeIndexCommentSDL(out io.Writer, schemaName string, index *metadatapb.IndexMetadata) error {
	if _, err := io.WriteString(out, `COMMENT ON INDEX "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schemaName); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, index.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, escapeSingleQuote(index.Comment)); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}
	_, err := io.WriteString(out, "\n\n")
	return err
}

func writeTriggersSDL(out io.Writer, schemaName string, table *metadatapb.TableMetadata) error {
	if tableMissingColumnMetadata(table) {
		// Trigger definitions (UPDATE OF, WHEN clauses) can reference
		// columns absent from the dump.
		return nil
	}
	for _, trigger := range table.Triggers {
		if trigger.SkipDump {
			continue
		}

		if err := writeTriggerSDL(out, schemaName, table.Name, trigger); err != nil {
			return err
		}

		if _, err := io.WriteString(out, ";\n\n"); err != nil {
			return err
		}
	}
	return nil
}

func writeTriggerSDL(out io.Writer, _ /* schemaName */, _ /* tableName */ string, trigger *metadatapb.TriggerMetadata) error {
	// For PostgreSQL, trigger.Body contains the complete CREATE TRIGGER statement
	// built by buildTriggerDefinition in get_database_metadata.go
	_, err := io.WriteString(out, trigger.Body)
	return err
}

func writeTriggerCommentSDL(out io.Writer, schemaName, tableName string, trigger *metadatapb.TriggerMetadata) error {
	if len(trigger.Comment) == 0 {
		return nil
	}

	if _, err := io.WriteString(out, `COMMENT ON TRIGGER "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, trigger.Name); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" ON "`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, schemaName); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `"."`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, tableName); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `" IS '`); err != nil {
		return err
	}
	if _, err := io.WriteString(out, escapeSingleQuote(trigger.Comment)); err != nil {
		return err
	}
	if _, err := io.WriteString(out, `';`); err != nil {
		return err
	}

	_, err := io.WriteString(out, "\n\n")
	return err
}

// extractIndexClauses parses a CREATE INDEX statement using the omni parser and
// extracts the INCLUDE clause and WHERE clause as strings.
func extractIndexClauses(definition string) (includeClause, whereClause string) {
	stmts, err := omnipg.Parse(definition)
	if err != nil || len(stmts) == 0 {
		return "", ""
	}
	idx, ok := stmts[0].AST.(*ast.IndexStmt)
	if !ok || idx == nil {
		return "", ""
	}

	// Extract INCLUDE columns using Loc to preserve original quoting.
	if idx.IndexIncludingParams != nil {
		var cols []string
		for _, item := range idx.IndexIncludingParams.Items {
			if elem, ok := item.(*ast.IndexElem); ok && elem.Loc.Start >= 0 && elem.Loc.End > elem.Loc.Start {
				cols = append(cols, definition[elem.Loc.Start:elem.Loc.End])
			}
		}
		if len(cols) > 0 {
			includeClause = "INCLUDE (" + strings.Join(cols, ", ") + ")"
		}
	}

	// Extract WHERE clause using AST node Loc for precise extraction.
	if idx.WhereClause != nil {
		loc := ast.NodeLoc(idx.WhereClause)
		if loc.Start >= 0 && loc.End > loc.Start && loc.End <= len(definition) {
			raw := definition[loc.Start:loc.End]
			// Strip outer parentheses if the entire predicate is wrapped.
			if strings.HasPrefix(raw, "(") && strings.HasSuffix(raw, ")") {
				inner := raw[1 : len(raw)-1]
				// Only strip if parentheses are balanced.
				if isBalancedParens(inner) {
					raw = inner
				}
			}
			whereClause = strings.TrimSpace(raw)
		}
	}

	return includeClause, whereClause
}

// isBalancedParens checks if a string has balanced parentheses.
func isBalancedParens(s string) bool {
	depth := 0
	for _, c := range s {
		switch c {
		case '(':
			depth++
		case ')':
			depth--
			if depth < 0 {
				return false
			}
		default:
		}
	}
	return depth == 0
}