package persistence

import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	"github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/formulastorage"
	"github.com/nexuscrm/shared/pkg/models"
)

// --- Managed System Field/Column Classification ---
//
// IsManagedSystemField and IsManagedSystemColumn classify pack-owned fields
// whose physical columns are managed by the platform (non-createable,
// non-updateable, nullable, not formula-view-only). They are generic: no
// object-name or field-name hardcoding appears in these helpers. Any field
// meeting the structural criteria is classified as managed regardless of
// which object or field it belongs to.
//
// The classification supports the live-metadata physical-column repair path:
// when a pack-owned managed field has its physical column dropped (e.g.
// Account.LastActivityDate), the repair path can rebuild it from the live
// _System_Field metadata using BuildManagedFieldColumnDefinition.

// IsManagedSystemField returns true when a FieldMetadata row represents a
// managed system field: non-createable, non-updateable, not required
// (nullable), and not an active formula backed by a view (which has no
// physical column). No object or field name is consulted.
func IsManagedSystemField(field models.FieldMetadata) bool {
	if field.Createable || field.Updateable {
		return false
	}
	if field.Required {
		return false
	}
	resolved, err := formulastorage.Resolve(formulastorage.SpecFromField(&field))
	if err == nil && resolved.FormulaBacked && !resolved.RequiresPhysicalColumn() {
		return false
	}
	return true
}

// IsManagedSystemColumn returns true when a ColumnDefinition represents a
// managed system column: nullable (not required), and not an active
// formula backed by a view. No object or field name is consulted.
func IsManagedSystemColumn(col schema.ColumnDefinition) bool {
	if !col.Nullable {
		return false
	}
	resolved, err := formulastorage.Resolve(formulastorage.SpecFromColumn(col))
	if err == nil && resolved.FormulaBacked && !resolved.RequiresPhysicalColumn() {
		return false
	}
	return true
}

// BuildManagedFieldColumnDefinition reads the live _System_Field metadata
// for (tableName, fieldAPIName) and reconstructs a ColumnDefinition suitable
// for bootstrap.ReconcileTableColumns to recreate the missing physical
// column. This is the single resolver for live-metadata-driven column repair
// of pack-owned managed fields.
//
// Returns an error if:
//   - the _System_Field row is not found
//   - the _System_Object row is not found (cannot resolve objectID)
//   - the field is an active formula backed by a view (no physical column)
func BuildManagedFieldColumnDefinition(ctx context.Context, db *sql.DB, tableName, fieldAPIName string) (schema.ColumnDefinition, error) {
	if db == nil {
		return schema.ColumnDefinition{}, fmt.Errorf("db is unavailable for BuildManagedFieldColumnDefinition")
	}

	// Query _System_Field + _System_Object to get field metadata.
	// Columns: Type, Label, COALESCE(Required, 0), COALESCE(IsUnique, 0),
	// ReferenceTo, RelationshipName, FormulaMode, Formula, FormulaStorageMode,
	// ReturnType, ProjectionConfig, Nullable
	query := fmt.Sprintf(
		"SELECT f.`%s`, f.`%s`, COALESCE(f.`%s`, 0), COALESCE(f.`%s`, 0), "+
			"COALESCE(f.`%s`, ''), COALESCE(f.`%s`, ''), "+
			"COALESCE(f.`%s`, ''), COALESCE(f.`%s`, ''), COALESCE(f.`%s`, ''), "+
			"COALESCE(f.`%s`, ''), "+
			"COALESCE(f.`%s`, ''), "+
			"CASE WHEN COALESCE(f.`%s`, 0) = 0 THEN 1 ELSE 0 END "+
			"FROM `%s` f JOIN `%s` o ON o.`%s` = f.`%s` "+
			"WHERE COALESCE(o.`%s`, 0) = 0 AND COALESCE(f.`%s`, 0) = 0 "+
			"AND o.`%s` = ? AND f.`%s` = ?",
		constants.FieldSysField_Type,
		constants.FieldSysField_Label,
		constants.FieldSysField_Required,
		constants.FieldSysField_IsUnique,
		constants.FieldSysField_ReferenceTo,
		constants.FieldSysField_RelationshipName,
		constants.FieldSysField_FormulaMode,
		constants.FieldSysField_Formula,
		constants.FieldSysField_FormulaStorageMode,
		constants.FieldSysField_ReturnType,
		constants.FieldSysField_ProjectionConfig,
		constants.FieldSysField_Required,
		constants.TableField,
		constants.TableObject,
		constants.FieldSysObject_ID,
		constants.FieldSysField_ObjectID,
		constants.FieldSysObject_IsDeleted,
		constants.FieldSysField_IsDeleted,
		constants.FieldSysObject_APIName,
		constants.FieldSysField_APIName,
	)

	var (
		fieldType, fieldLabel, referenceTo, relationshipName string
		formulaMode, formula, formulaStorageMode             string
		returnType                                           string
		projectionConfig                                     sql.NullString
		required, isUnique                                   int
		nullable                                             int
	)

	if err := db.QueryRowContext(ctx, query, tableName, fieldAPIName).Scan(
		&fieldType, &fieldLabel, &required, &isUnique,
		&referenceTo, &relationshipName,
		&formulaMode, &formula, &formulaStorageMode,
		&returnType,
		&projectionConfig,
		&nullable,
	); err != nil {
		if err == sql.ErrNoRows {
			return schema.ColumnDefinition{}, fmt.Errorf("live _System_Field row for %s.%s not found", tableName, fieldAPIName)
		}
		return schema.ColumnDefinition{}, fmt.Errorf("query _System_Field for %s.%s: %w", tableName, fieldAPIName, err)
	}

	logicalType := strings.TrimSpace(fieldType)

	// Check formula storage — view-only formulas have no physical column.
	resolved, err := formulastorage.Resolve(formulastorage.Spec{
		FormulaMode:         strings.TrimSpace(formulaMode),
		FormulaStorageMode:  strings.TrimSpace(formulaStorageMode),
		Formula:             strings.TrimSpace(formula),
		HasProjectionConfig: projectionConfig.Valid && strings.TrimSpace(projectionConfig.String) != "",
	})
	if err == nil && resolved.FormulaBacked && !resolved.RequiresPhysicalColumn() {
		return schema.ColumnDefinition{}, fmt.Errorf("field %s.%s is a formula-view-only field with no physical column", tableName, fieldAPIName)
	}

	// For formula fields, ReturnType holds the physical storage type (e.g. "date",
	// "currency"). Without it, ResolveColumnType falls back to DECIMAL(18,6) for
	// formula columns whose ReturnType is missing, which produces a wrong SQL type.
	// Fail closed: if a write-materialized formula has no ReturnType, we cannot
	// safely reconstruct the physical column shape from live metadata alone.
	returnTypeTrimmed := strings.TrimSpace(returnType)
	if resolved.IsWriteMaterialized() && returnTypeTrimmed == "" {
		return schema.ColumnDefinition{}, fmt.Errorf("field %s.%s is a write-materialized formula with no ReturnType — cannot safely reconstruct physical column shape from live metadata", tableName, fieldAPIName)
	}

	// Determine the effective type for SQL resolution. For formula fields with
	// a ReturnType, the ReturnType is the physical storage type; for non-formula
	// fields, the logical Type is also the physical type.
	effectiveType := logicalType
	if resolved.IsWriteMaterialized() && returnTypeTrimmed != "" {
		effectiveType = returnTypeTrimmed
	}

	// Map the effective type to a SQL type.
	repo := &SchemaRepository{}
	sqlType := repo.MapFieldTypeToSQL(effectiveType)
	if sqlType == "" || sqlType == "INVALID_TYPE_ERROR" {
		return schema.ColumnDefinition{}, fmt.Errorf("cannot map effective type %q (logical %q) for field %s.%s to SQL type", effectiveType, logicalType, tableName, fieldAPIName)
	}

	colDef := schema.ColumnDefinition{
		Name:        strings.TrimSpace(fieldAPIName),
		Label:       strings.TrimSpace(fieldLabel),
		Type:        sqlType,
		LogicalType: logicalType,
		Nullable:    nullable == 1,
		Unique:      isUnique != 0,
	}

	if strings.TrimSpace(formula) != "" {
		colDef.Formula = strings.TrimSpace(formula)
	}
	if strings.TrimSpace(formulaMode) != "" {
		colDef.FormulaMode = strings.TrimSpace(formulaMode)
	}
	if strings.TrimSpace(formulaStorageMode) != "" {
		colDef.FormulaStorageMode = strings.TrimSpace(formulaStorageMode)
	}
	if returnTypeTrimmed != "" {
		colDef.ReturnType = returnTypeTrimmed
	}

	return colDef, nil
}

// --- System Column Definitions ---

func (r *SchemaRepository) standardSystemColumns(includeRecordTypeID bool) []schema.ColumnDefinition {
	columns := []schema.ColumnDefinition{
		{
			Name:       constants.FieldID,
			Type:       "VARCHAR(255)",
			PrimaryKey: true,
			Nullable:   false,
		},
		{
			Name:     constants.FieldOwnerID,
			Type:     "VARCHAR(255)",
			Nullable: true,
		},
		{
			Name:     constants.FieldCreatedByID,
			Type:     "VARCHAR(255)",
			Nullable: true,
		},
		{
			Name:     constants.FieldLastModifiedByID,
			Type:     "VARCHAR(255)",
			Nullable: true,
		},
		{
			Name: constants.FieldCreatedDate,
			Type: "DATETIME",
			// Default removed to avoid 'Invalid default value' error in strict mode.
			// PersistenceService populates this.
		},
		{
			Name: constants.FieldLastModifiedDate,
			Type: "DATETIME",
			// PersistenceService populates this.
		},
		{
			Name: constants.FieldSystemModstamp,
			Type: "DATETIME",
			// PersistenceService populates this (for sync/replication).
		},
		{
			Name:     constants.FieldIsDeleted,
			Type:     "TINYINT(1)",
			Default:  "0",
			Nullable: false,
		},
	}
	if includeRecordTypeID {
		recordTypeColumn := schema.ColumnDefinition{
			Name:     constants.FieldRecordTypeID,
			Type:     "VARCHAR(255)",
			Nullable: true,
		}
		columns = append(columns[:2], append([]schema.ColumnDefinition{recordTypeColumn}, columns[2:]...)...)
	}
	return columns
}

func ManagedObjectSystemColumns(objectAPIName string, tableType constants.TableType) []schema.ColumnDefinition {
	_ = objectAPIName
	includeRecordTypeID := tableType == constants.TableTypeCustomObject
	return (&SchemaRepository{}).standardSystemColumns(includeRecordTypeID)
}

// GetStandardSystemColumns returns the generic platform-injected columns shared
// by pack, system-object, and standard-object surfaces.
func (r *SchemaRepository) GetStandardSystemColumns() []schema.ColumnDefinition {
	return r.standardSystemColumns(false)
}

// GetCustomObjectSystemColumns returns the platform-injected columns for
// record-typed custom objects, including the standard RecordType relationship
// key required by RecordType.* formulas.
func GetCustomObjectSystemColumns() []schema.ColumnDefinition {
	return (&SchemaRepository{}).standardSystemColumns(true)
}

func (r *SchemaRepository) standardFieldMetadata(includeRecordTypeID bool) []models.FieldMetadata {
	columns := r.standardSystemColumns(includeRecordTypeID)
	fields := make([]models.FieldMetadata, 0, len(columns))

	for _, col := range columns {
		field := models.FieldMetadata{
			APIName:  col.Name,
			Label:    col.Name,
			Type:     models.FieldType(r.mapSQLTypeToLogical(col.Type)),
			Required: col.PrimaryKey || (!col.Nullable && !r.IsSystemColumn(col.Name)),
			IsUnique: col.Unique,
			IsSystem: r.IsSystemColumn(col.Name),
		}

		// Set special flags and refining Types
		if strings.EqualFold(col.Name, constants.FieldID) {
			field.Label = "ID"
		} else if strings.EqualFold(col.Name, constants.FieldIsDeleted) {
			field.Label = "Deleted"
		} else if strings.EqualFold(col.Name, constants.FieldCreatedDate) {
			field.Label = "Created Date"
		} else if strings.EqualFold(col.Name, constants.FieldLastModifiedDate) {
			field.Label = "Last Modified Date"
		} else if strings.EqualFold(col.Name, constants.FieldSystemModstamp) {
			field.Label = "System Modstamp"
		} else if strings.EqualFold(col.Name, constants.FieldOwnerID) {
			field.Label = "Owner"
			field.Type = constants.FieldTypeReference
			field.ReferenceTo = []string{constants.TableUser}
			relationshipName := "Owner"
			field.RelationshipName = &relationshipName
		} else if strings.EqualFold(col.Name, constants.FieldRecordTypeID) {
			field.Label = "Record Type"
			field.Type = constants.FieldTypeReference
			if target, ok := constants.PlatformReferenceTargetForField(col.Name); ok {
				field.ReferenceTo = []string{target}
			}
			relationshipName := constants.ObjectRecordType
			field.RelationshipName = &relationshipName
			field.Createable = true
			field.Updateable = true
		} else if strings.EqualFold(col.Name, constants.FieldCreatedByID) {
			field.Label = "Created By"
			field.Type = constants.FieldTypeReference
			field.ReferenceTo = []string{constants.TableUser}
			relationshipName := "CreatedBy"
			field.RelationshipName = &relationshipName
		} else if strings.EqualFold(col.Name, constants.FieldLastModifiedByID) {
			field.Label = "Last Modified By"
			field.Type = constants.FieldTypeReference
			field.ReferenceTo = []string{constants.TableUser}
			relationshipName := "LastModifiedBy"
			field.RelationshipName = &relationshipName
		}

		fields = append(fields, field)
	}

	return fields
}

func ManagedObjectSystemFieldMetadata(objectAPIName string, tableType constants.TableType) []models.FieldMetadata {
	_ = objectAPIName
	includeRecordTypeID := tableType == constants.TableTypeCustomObject
	return (&SchemaRepository{}).standardFieldMetadata(includeRecordTypeID)
}

// GetStandardFieldMetadata returns field metadata for the generic
// platform-injected columns shared by pack, system-object, and standard-object
// surfaces.
func (r *SchemaRepository) GetStandardFieldMetadata() []models.FieldMetadata {
	return r.standardFieldMetadata(false)
}

// GetCustomObjectFieldMetadata returns field metadata for custom-object system
// columns, keeping the RecordTypeId DDL and live `_System_Field` shape in
// lockstep through one helper.
func GetCustomObjectFieldMetadata() []models.FieldMetadata {
	return (&SchemaRepository{}).standardFieldMetadata(true)
}