models

type ModelDB struct {
	Name       string
	Extensions map[base.DXDatabaseType][]string // Database-specific extensions/features
	Schemas    []*ModelDBSchema
}

type ModelDBAggregateType int

type ModelDBEntity struct {
	Name   string
	Type   ModelDBEntityType
	Order  int
	Schema *ModelDBSchema
}

type ModelDBEntityType int

type ModelDBField struct {
	Owner                  *ModelDBTable
	Order                  int
	Type                   types.DataType
	IsPrimaryKey           bool
	IsAutoIncrement        bool
	IsNotNull              bool
	IsUnique               bool
	DefaultValue           any                         // SQL expression for DEFAULT clause (used when DefaultValueByDBType not specified)
	DefaultValueByDBType   map[base.DXDatabaseType]any // Database-specific default values. Keys: "postgresql", "sqlserver", "oracle", "mariadb"
	References             string                      // Foreign key reference in format "schema.table.field"
	ResolvedReferenceField *ModelDBField               // Resolved reference field pointer (set by NewModelDBTable)

	// for encrypted field only
	DecryptedFieldName  string
	EncryptionKeySource ModelDBKeySource // "config" (PostgreSQL current_setting), "literal", "env"
	EncryptionKeyValue  string           // e.g., "app.encryption_key" for config, or literal key value
	HashFieldName       string           // e.g., "fullname_hashed" - companion hash field name
	HashSaltKeySource   ModelDBKeySource // "config", "literal", "env"
	HashSaltKeyValue    string           // salt value or config name

	// for generated/computed columns
	IsGenerated                 bool                           // true = this column is generated/computed
	GeneratedExpression         string                         // default SQL expression (used when GeneratedExpressionByDBType not specified)
	GeneratedExpressionByDBType map[base.DXDatabaseType]string // database-specific expressions (syntax varies across engines)
	IsStored                    bool                           // true = STORED/PERSISTED, false = VIRTUAL
}

type ModelDBFunction struct {
	ModelDBEntity
	Parameters      []ModelDBFunctionParameter
	ReturnType      string                         // Return type (e.g., "TRIGGER", "INTEGER", "TABLE", "VOID")
	ReturnTypeByDB  map[base.DXDatabaseType]string // Database-specific return type override
	Language        string                         // plpgsql, sql, etc.
	Body            string                         // Function body (between $$ and $$)
	BodyByDB        map[base.DXDatabaseType]string // Database-specific body override
	IsReplace       bool                           // CREATE OR REPLACE
	Volatility      string                         // VOLATILE, STABLE, IMMUTABLE (PostgreSQL)
	SecurityDefiner bool                           // SECURITY DEFINER vs SECURITY INVOKER
}

type ModelDBFunctionParameter struct {
	Name         string
	DataType     string                         // Generic type string
	DataTypeByDB map[base.DXDatabaseType]string // Database-specific type override
	Mode         string                         // IN, OUT, INOUT (default: IN)
	DefaultValue string                         // Optional default value
}

type ModelDBIndex struct {
	ModelDBEntity
	Columns     []ModelDBIndexColumn
	IsUnique    bool
	Method      ModelDBIndexMethod // BTREE, HASH, GIST, etc.
	Where       string             // Partial index condition (PostgreSQL)
	Include     []string           // INCLUDE columns (PostgreSQL 11+)
	Tablespace  string             // Optional tablespace
	Concurrent  bool               // CREATE INDEX CONCURRENTLY (PostgreSQL)
	IfNotExists bool               // CREATE INDEX IF NOT EXISTS

	// Owner reference - either Table or MaterializedView (set by NewDBIndex*)
	OwnerTable            *ModelDBTable
	OwnerMaterializedView *ModelDBMaterializedView
}

type ModelDBIndexColumn struct {
	Name       string
	Order      string // ASC, DESC, empty for default
	NullsOrder string // NULLS FIRST, NULLS LAST, empty for default
}

type ModelDBIndexMethod string

type ModelDBJoin struct {
	JoinType        ModelDBJoinType
	TargetTable     *ModelDBEntity // The table or view to join TO (changed from *ModelDBTable to support views)
	TargetAlias     string         // Optional: alias for the target table/view (e.g., "st", "st2" for multiple joins)
	FromLocalField  *ModelDBField  // ModelDBField from the source/left table
	ToTargetField   *ModelDBField  // ModelDBField from the target/right table
	TargetTableName string         // Optional: override target table name (for self-joins or subqueries)
}

type ModelDBJoinByName struct {
	JoinType      ModelDBJoinType
	TargetViewRef *ModelDBViewRef // The view/table to join TO
	FromFieldExpr string          // ModelDBField expression from source (e.g., "vfe.id" or just "id")
	ToFieldExpr   string          // ModelDBField expression from target (e.g., "fee.user_role_membership_id")
	OnCondition   string          // Optional: full ON condition override (e.g., "a.id = b.id AND a.type = b.type")
}

type ModelDBJoinType int

type ModelDBKeySource int

type ModelDBMaterializedView struct {
	ModelDBEntity               // Embedded base entity (Name, Type, Order, Schema)
	UseTDE             bool     // If true, use USING tde_heap (PostgreSQL specific)
	UniqueIndexColumns []string // Columns for unique index (required for CONCURRENTLY refresh)

	// Builder pattern fields - Source table/view (use ONE of these)
	FromTable   *ModelDBTable   // Main table to select from (when using ModelDBTable)
	FromViewRef *ModelDBViewRef // Main view/table to select from (when using view reference by name)

	// Builder pattern fields - Columns and clauses
	Columns     []ModelDBViewColumn // Columns to select (for ModelDBTable fields)
	ColumnExprs []string            // Column expressions as strings (for view references)
	Joins       []ModelDBJoin       // Join clauses (for ModelDBTable)
	JoinsByName []ModelDBJoinByName // Join clauses using view/table names
	Where       string              // WHERE clause (without the "WHERE" keyword)
	GroupBy     []string            // GROUP BY columns (field names or expressions)
	Having      string              // HAVING clause (without the "HAVING" keyword)
	OrderBy     []ModelDBOrderBy    // ORDER BY clause
	Distinct    bool                // SELECT DISTINCT

	// RawSQL for complex queries (CTEs, RECURSIVE, window functions, etc.)
	// When set, bypasses builder pattern entirely
	RawSQL string

	// Indexes on this materialized view
	Indexes []*ModelDBIndex
}

type ModelDBOrderBy struct {
	ColumnExpr  string // Can be field name or expression like "COUNT(*)"
	OrderByType ModelDBOrderByType
}

type ModelDBOrderByType int

type ModelDBSchema struct {
	Name              string
	Order             int
	DB                *ModelDB
	Functions         []*ModelDBFunction
	Tables            []*ModelDBTable
	Views             []*ModelDBView
	MaterializedViews []*ModelDBMaterializedView
	Triggers          []*ModelDBTrigger // Pointer list to all triggers in this schema (owned by tables)
}

type ModelDBTDEConfig struct {
	// PostgreSQL: Table access method name for TDE (e.g., "tde_heap")
	ModelDBPostgreSQLAccessMethod string

	// Oracle: Encrypted tablespace name where the table will be created
	OracleTablespace string

	// SQLServer: Flag indicating that databases-level TDE should be enabled
	// No per-table syntax is needed; this is for documentation/validation purposes
	SQLServerTDEEnabled bool

	// MariaDB: Encryption option for InnoDB tables ('Y' for enabled, 'N' or empty for disabled)
	MariaDBEncryption string
}

type ModelDBTable struct {
	ModelDBEntity
	Fields            map[string]*ModelDBField
	TDE               ModelDBTDEConfig // Database-specific TDE configuration
	UseTableSuffix    bool
	PhysicalTableName string
	Indexes           []*ModelDBIndex   // Indexes on this table
	Triggers          []*ModelDBTrigger // Triggers on this table
}

type ModelDBTrigger struct {
	ModelDBEntity
	Timing              ModelDBTriggerTiming
	Events              []ModelDBTriggerEvent // INSERT, UPDATE, DELETE, TRUNCATE
	UpdateColumns       []string              // Columns for UPDATE OF (optional)
	Scope               ModelDBTriggerScope   // FOR EACH ROW or FOR EACH STATEMENT
	When                string                // WHEN condition (optional)
	ExecuteFunction     *ModelDBFunction      // Reference to the function to execute
	ExecuteFunctionName string                // Or just the function name if not using ModelDBFunction reference
	IsConstraint        bool                  // Constraint trigger (PostgreSQL)
	Deferrable          bool                  // DEFERRABLE (constraint triggers)
	InitiallyDeferred   bool                  // INITIALLY DEFERRED (constraint triggers)

	// Owner reference - the table this trigger is on
	OwnerTable *ModelDBTable
}

type ModelDBTriggerEvent string

type ModelDBTriggerScope string

type ModelDBTriggerTiming string

type ModelDBView struct {
	ModelDBEntity                                          // Embedded base entity (Name, Type, Order, Schema)
	FromTable       *ModelDBTable                          // Main table to select from
	Columns         []ModelDBViewColumn                    // Columns to select
	Joins           []ModelDBJoin                          // Join clauses
	Where           string                                 // WHERE clause (without the "WHERE" keyword)
	GroupBy         []string                               // GROUP BY columns (field names or expressions)
	Having          string                                 // HAVING clause (without the "HAVING" keyword)
	OrderBy         []ModelDBOrderBy                       // ORDER BY clause
	Distinct        bool                                   // SELECT DISTINCT
	RawSQL          string                                 // Raw SQL for complex views (bypasses builder when set)
	RawSQLFunc      func(base.DXDatabaseType) string       // DB-specific raw SQL generator (overrides RawSQL and builder when set)
	ViewBuilderFunc func(base.DXDatabaseType) *ModelDBView // DB-specific structured view builder (overrides builder when set)
}

type ModelDBViewColumn struct {
	SourceTable *ModelDBEntity // Which table this column comes from (nil = from main table)
	SourceField *ModelDBField  // The field reference (nil if using Expression)
	Expression  string         // Raw SQL expression like "COUNT(*)", "SUM(amount)", etc.
	Alias       string         // AS alias_name (required if Expression is used)
}

type ModelDBViewRef struct {
	Schema *ModelDBSchema // Schema pointer (e.g., PartnerManagementSchema)
	Name   string         // View/table name (e.g., "v_field_executor")
	Alias  string         // Optional alias for use in queries (e.g., "vfe")
}