package metadata

import (
	"context"

	"github.com/nexuscrm/backend/internal/domain/ports"
	"github.com/nexuscrm/backend/internal/domain/schema"
	"github.com/nexuscrm/shared/pkg/models"
)

// SchemaManager handles all table creation and schema operations
type SchemaManager struct {
	repo ports.SchemaRepository
}

// NewSchemaManager creates a new schema manager
// Accepts ports.SchemaRepository interface
func NewSchemaManager(repo ports.SchemaRepository) *SchemaManager {
	return &SchemaManager{
		repo: repo,
	}
}

// CreatePhysicalTable creates the table structure without registering metadata
func (sm *SchemaManager) CreatePhysicalTable(ctx context.Context, def schema.TableDefinition) error {
	return sm.repo.CreatePhysicalTable(ctx, def)
}

// CreateTableWithStrictMetadata creates a table ensuring metadata uniqueness (Strict Insert)
func (sm *SchemaManager) CreateTableWithStrictMetadata(ctx context.Context, def schema.TableDefinition, objectMeta *models.ObjectMetadata) (err error) {
	return sm.repo.CreateTableWithStrictMetadata(ctx, def, objectMeta)
}

// BatchCreatePhysicalTables performs parallel DDL creation and then batch registers in _System_Table
func (sm *SchemaManager) BatchCreatePhysicalTables(ctx context.Context, defs []schema.TableDefinition) error {
	return sm.repo.BatchCreatePhysicalTables(ctx, defs)
}

// DropTable drops a table and removes it from the registry
func (sm *SchemaManager) DropTable(tableName string) error {
	return sm.repo.DropTable(tableName)
}

// ValidateSchema checks if a table matches its expected definition
func (sm *SchemaManager) ValidateSchema(tableName string) error {
	return sm.repo.ValidateSchema(tableName)
}

// ValidateFieldDefinition validates the field schema against core assertions
func (sm *SchemaManager) ValidateFieldDefinition(field schema.ColumnDefinition) error {
	return sm.repo.ValidateFieldDefinition(field)
}

// ValidateSchemaRegistry compares the registry against actual DB tables
func (sm *SchemaManager) ValidateSchemaRegistry() (*schema.SchemaHealth, error) {
	return sm.repo.ValidateSchemaRegistry()
}

// Proxy Methods (Interface Consistency)

// Type Aliases for consistent API
type FieldWithContext = schema.FieldWithContext
type TableRegistryItem = schema.TableRegistryItem
type SchemaHealth = schema.SchemaHealth

// BatchRegisterTables registers multiple tables in _System_Table registry
// Uses interface{} for tx parameter to avoid leaking database/sql into feature layer.
// Repository layer handles the type assertion to *sql.Tx internally.
func (sm *SchemaManager) BatchRegisterTables(defs []schema.TableDefinition, tx interface{}) error {
	return sm.repo.BatchRegisterTables(defs, tx)
}

// AddColumn adds a column to the table and registers it
func (sm *SchemaManager) AddColumn(tableName string, col schema.ColumnDefinition) error {
	return sm.repo.AddColumn(tableName, col)
}

// EnsureColumn checks if a column exists and adds it if missing
func (sm *SchemaManager) EnsureColumn(tableName string, col schema.ColumnDefinition) error {
	return sm.repo.EnsureColumn(tableName, col)
}

// RegisterFieldOnly writes metadata to _System_Field without creating a physical column.
// Used for active formula fields where the View computes the value.
func (sm *SchemaManager) RegisterFieldOnly(tableName string, col schema.ColumnDefinition) error {
	return sm.repo.RegisterFieldOnly(tableName, col)
}

// DropColumn drops a column from the table and removes metadata
func (sm *SchemaManager) DropColumn(tableName string, colName string) error {
	return sm.repo.DropColumn(tableName, colName)
}

// ModifyColumn modifies a column's type (for schema auto-correction during import)
func (sm *SchemaManager) ModifyColumn(tableName, colName string, col schema.ColumnDefinition) error {
	return sm.repo.ModifyColumn(tableName, colName, col)
}

// IsSystemColumn returns true for columns that are automatically populated
func (sm *SchemaManager) IsSystemColumn(name string) bool {
	return sm.repo.IsSystemColumn(name)
}

// GetStandardSystemColumns returns the default columns for every custom object
func (sm *SchemaManager) GetStandardSystemColumns() []schema.ColumnDefinition {
	return sm.repo.GetStandardSystemColumns()
}

// GetStandardFieldMetadata returns field metadata for standard system columns
func (sm *SchemaManager) GetStandardFieldMetadata() []models.FieldMetadata {
	return sm.repo.GetStandardFieldMetadata()
}

// SaveObjectMetadata upserts object metadata into _System_Object
// Uses interface{} for tx parameter to avoid leaking database/sql into feature layer.
func (sm *SchemaManager) SaveObjectMetadata(ctx context.Context, obj *models.ObjectMetadata, tx interface{}) error {
	return sm.repo.SaveObjectMetadata(ctx, obj, tx)
}

// InsertObjectMetadata inserts object metadata (Strict)
// Uses interface{} for tx parameter to avoid leaking database/sql into feature layer.
func (sm *SchemaManager) InsertObjectMetadata(ctx context.Context, obj *models.ObjectMetadata, tx interface{}) error {
	return sm.repo.InsertObjectMetadata(ctx, obj, tx)
}

// BatchSaveObjectMetadata inserts multiple objects in a single statement
// Uses interface{} for tx parameter to avoid leaking database/sql into feature layer.
func (sm *SchemaManager) BatchSaveObjectMetadata(ctx context.Context, objs []*models.ObjectMetadata, tx interface{}) error {
	return sm.repo.BatchSaveObjectMetadata(ctx, objs, tx)
}

// SaveFieldMetadataWithIDs upserts field metadata with explicit IDs.
// objectAPIName is needed to compute object-specific intrinsic capabilities.
// Uses interface{} for tx parameter to avoid leaking database/sql into feature layer.
func (sm *SchemaManager) SaveFieldMetadataWithIDs(ctx context.Context, field *models.FieldMetadata, objectID string, objectAPIName string, fieldID string, tx interface{}) error {
	return sm.repo.SaveFieldMetadataWithIDs(ctx, field, objectID, objectAPIName, fieldID, tx)
}

// BatchSaveFieldMetadata inserts multiple fields in a single statement
// Uses interface{} for tx parameter to avoid leaking database/sql into feature layer.
func (sm *SchemaManager) BatchSaveFieldMetadata(ctx context.Context, fields []FieldWithContext, tx interface{}) error {
	return sm.repo.BatchSaveFieldMetadata(ctx, fields, tx)
}

// PrepareFieldForBatch converts a column definition to FieldWithContext
func (sm *SchemaManager) PrepareFieldForBatch(tableName string, col schema.ColumnDefinition) FieldWithContext {
	return sm.repo.PrepareFieldForBatch(tableName, col)
}

// GetTableRegistry retrieves all registered tables
func (sm *SchemaManager) GetTableRegistry(ctx context.Context) ([]*TableRegistryItem, error) {
	return sm.repo.GetTableRegistry(ctx)
}

// MapFieldTypeToSQL converts logical field types to SQL column types
func (sm *SchemaManager) MapFieldTypeToSQL(fieldType string) string {
	return sm.repo.MapFieldTypeToSQL(fieldType)
}

// ValidateFormula validates a formula expression syntax
func (sm *SchemaManager) ValidateFormula(formulaStr string, env map[string]interface{}) error {
	return sm.repo.ValidateFormula(formulaStr, env)
}

// BatchAddColumns adds multiple columns to the table in a single DDL statement and registers them
func (sm *SchemaManager) BatchAddColumns(tableName string, fields []models.FieldMetadata) error {
	return sm.repo.BatchAddColumns(tableName, fields)
}