package metadata

import (
	"context"
	"fmt"
	"log"

	"github.com/nexuscrm/kernel/pkg/ports"
	"github.com/nexuscrm/kernel/pkg/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)
}

// HasDropPendingTombstone reports whether a DROP-PENDING tombstone
// (_System_Table row with IsDeleted=1, written ONLY when a physical DROP
// failed after the metadata purge) exists for the name. It is the sole proof
// the DeleteSchema orphan-reconcile accepts before it may DROP a table with no
// live object metadata (#3484); a successful drop leaves no such marker, and a
// non-platform table sharing the name (even a same-shape copy) never has one,
// so neither can ever be authorized for destruction. Exposed via an optional
// interface assertion so no ports.SchemaRepository signature change is needed;
// a repo that does not implement it (e.g. a bare mock) returns (false, error)
// so the caller fails CLOSED — a probe that cannot run must never authorize a
// destructive drop.
func (sm *SchemaManager) HasDropPendingTombstone(tableName string) (bool, error) {
	prober, ok := sm.repo.(interface {
		HasDropPendingTombstone(string) (bool, error)
	})
	if !ok {
		return false, fmt.Errorf("schema repository does not support drop-pending tombstone probing")
	}
	return prober.HasDropPendingTombstone(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

// 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)
}

// EnsureColumnIndex idempotently indexes a single column. Callers use it to keep
// relationship (lookup / master-detail) FK columns indexed.
func (sm *SchemaManager) EnsureColumnIndex(tableName string, columnName string) error {
	return sm.repo.EnsureColumnIndex(tableName, columnName)
}

// EnsureRelationshipFKIndexes idempotently indexes every non-system relationship
// FK column across the given table definitions. It is the shared terminal step
// each apply lane runs after materializing object definitions — pack apply
// (bundled, app, and source-backed install/reapply) and tenant overlay apply —
// so a relationship FK column is indexed regardless of which lane created it.
//
// The narrow apply lanes do not run the global EnsureStandardIndexes backfill
// (only full apply does), so without this they shipped unindexed FK columns and
// related-list counts full-scanned the child table (#1407). Routing them through
// the same schema.RelationshipFKColumns rule and the same EnsureColumnIndex
// primitive the admin CreateField lane already uses keeps one rule, one read
// path across every lane.
//
// Best-effort: indexing is a performance optimization, so a failure is logged
// and tolerated rather than failing the apply. EnsureColumnIndex is idempotent,
// so re-running an apply re-converges any column it could not index before.
func (sm *SchemaManager) EnsureRelationshipFKIndexes(defs []schema.TableDefinition) {
	for _, fk := range schema.RelationshipFKColumns(defs) {
		if err := sm.EnsureColumnIndex(fk.Table, fk.Column); err != nil {
			log.Printf("⚠️ Failed to index relationship FK %s.%s: %v", fk.Table, fk.Column, err)
		}
	}
}

// EnsurePackOwnedField creates or verifies a disabled-history pack-owned field
// without routing through admin-owned metadata writers. History transitions use
// the active-transaction exact-owner batch path.
func (sm *SchemaManager) EnsurePackOwnedField(ctx context.Context, objectAPIName string, field schema.FieldWithContext) error {
	return sm.repo.EnsurePackOwnedField(ctx, objectAPIName, field)
}

func (sm *SchemaManager) ResolvePackOwnedFieldConvergence(ctx context.Context, objectAPIName string, field schema.FieldWithContext) (schema.PackOwnedFieldConvergencePlan, error) {
	return sm.repo.ResolvePackOwnedFieldConvergence(ctx, objectAPIName, field)
}

// 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)
}

func (sm *SchemaManager) DropColumnPhase1(tableName string, colName string, expectedOwnerPack string) error {
	return sm.repo.DropColumnPhase1(tableName, colName, expectedOwnerPack)
}

func (sm *SchemaManager) DropColumnPhase2(tableName string, colName string, expectedOwnerPack string) error {
	return sm.repo.DropColumnPhase2(tableName, colName, expectedOwnerPack)
}

// 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)
}

func (sm *SchemaManager) BatchSaveObjectMetadataWithCustomizationPolicy(ctx context.Context, objs []*models.ObjectMetadata, tx interface{}, preserveCustomized bool) error {
	type customizationPolicyBatchSaver interface {
		BatchSaveObjectMetadataWithCustomizationPolicy(context.Context, []*models.ObjectMetadata, interface{}, bool) error
	}
	if repo, ok := sm.repo.(customizationPolicyBatchSaver); ok {
		return repo.BatchSaveObjectMetadataWithCustomizationPolicy(ctx, objs, tx, preserveCustomized)
	}
	return sm.repo.BatchSaveObjectMetadata(ctx, objs, tx)
}

func (sm *SchemaManager) BatchSaveObjectMetadataWithoutChildCloneRules(ctx context.Context, objs []*models.ObjectMetadata, tx interface{}) error {
	type childCloneRuleSafeBatchSaver interface {
		BatchSaveObjectMetadataWithoutChildCloneRules(context.Context, []*models.ObjectMetadata, interface{}) error
	}
	if repo, ok := sm.repo.(childCloneRuleSafeBatchSaver); ok {
		return repo.BatchSaveObjectMetadataWithoutChildCloneRules(ctx, objs, tx)
	}
	return fmt.Errorf("schema repository does not support ChildCloneRules-safe object metadata save")
}

// 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)
}

func (sm *SchemaManager) AdmitAdminFieldMetadataWrite(ctx context.Context, objectID, objectAPIName, fieldAPIName string, tx interface{}) error {
	repo, ok := sm.repo.(interface {
		AdmitAdminFieldMetadataWrite(context.Context, string, string, string, interface{}) error
	})
	if !ok {
		return fmt.Errorf("schema repository does not support admin field metadata admission")
	}
	return repo.AdmitAdminFieldMetadataWrite(ctx, objectID, objectAPIName, fieldAPIName, tx)
}

func (sm *SchemaManager) ApplyHistoryTrackingTransition(ctx context.Context, fieldID string, desired bool, tx interface{}, preserveCustomized, markCustomized bool) (bool, error) {
	repo, ok := sm.repo.(interface {
		ApplyHistoryTrackingTransition(context.Context, string, bool, interface{}, bool, bool) (bool, error)
	})
	if !ok {
		return false, fmt.Errorf("schema repository does not support history tracking transitions")
	}
	return repo.ApplyHistoryTrackingTransition(ctx, fieldID, desired, tx, preserveCustomized, markCustomized)
}

// 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)
}

// BatchSaveFieldMetadataWithCustomizationPolicy is the active-transaction
// replacement for TrackHistory-bearing EnsurePackOwnedField callers. It also
// lets pack/object-spec callers choose whether customized live rows keep
// spec-owned metadata columns on upsert.
func (sm *SchemaManager) BatchSaveFieldMetadataWithCustomizationPolicy(ctx context.Context, fields []FieldWithContext, tx interface{}, preserveCustomized bool) error {
	if repo, ok := sm.repo.(interface {
		BatchSaveFieldMetadataWithCustomizationPolicy(context.Context, []schema.FieldWithContext, interface{}, bool) error
	}); ok {
		return repo.BatchSaveFieldMetadataWithCustomizationPolicy(ctx, fields, tx, preserveCustomized)
	}
	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)
}