package persistence

import (
	"fmt"
	"strings"

	"github.com/nexuscrm/backend/internal/domain/schema"
	"github.com/nexuscrm/backend/pkg/formula"
	"github.com/nexuscrm/shared/pkg/constants"
)

// buildColumnDDL generates DDL for a single column
func (r *SchemaRepository) buildColumnDDL(col schema.ColumnDefinition, includeUnique bool) string {
	var sb strings.Builder

	// Formula fields: always create a regular storage column (for SF snapshot values; TiDB View handles dynamic calculation)
	if col.Formula != "" && strings.EqualFold(col.LogicalType, string(constants.FieldTypeFormula)) {
		sqlType := r.MapFieldTypeToSQL(col.ReturnType)
		if sqlType == "" || sqlType == "VARCHAR(255)" {
			if col.ReturnType == "" || col.ReturnType == string(constants.FieldTypeDouble) || col.ReturnType == string(constants.FieldTypeCurrency) || col.ReturnType == string(constants.FieldTypePercent) {
				sqlType = "DECIMAL(18,6)"
			}
		}

		sb.WriteString(fmt.Sprintf("`%s` %s", col.Name, sqlType))
		return sb.String()
	}

	// Standard column DDL
	sqlType := r.MapFieldTypeToSQL(col.Type)

	// Explicit Length handling (Right Code implementation)
	// If Length is specified for Text-like types, use VARCHAR(Length)
	if col.Length > 0 {
		upper := strings.ToUpper(sqlType)
		if strings.HasPrefix(upper, "VARCHAR") || strings.HasPrefix(upper, "TEXT") || strings.HasPrefix(upper, "CHAR") {
			sqlType = fmt.Sprintf("VARCHAR(%d)", col.Length)
		}
	}

	sb.WriteString(fmt.Sprintf("`%s` %s", col.Name, sqlType))

	if !col.Nullable {
		sb.WriteString(" NOT NULL")
	}
	if col.Default != "" {
		sb.WriteString(fmt.Sprintf(" DEFAULT %s", col.Default))
	}
	if col.AutoIncrement {
		sb.WriteString(" AUTO_INCREMENT")
	}
	if col.PrimaryKey {
		sb.WriteString(" PRIMARY KEY")
	}
	// Only add UNIQUE if requested (TiDB doesn't support inline UNIQUE in ALTER TABLE)
	if includeUnique && col.Unique {
		sb.WriteString(" UNIQUE")
	}

	return sb.String()
}

// buildForeignKeyDDL generates DDL for a foreign key constraint
func (r *SchemaRepository) buildForeignKeyDDL(fk schema.ForeignKeyDefinition) string {
	ref := fk.References
	// Quote table name in references (e.g., Group(Id) -> `Group`(Id))
	if idx := strings.Index(ref, "("); idx != -1 {
		tableName := strings.TrimSpace(ref[:idx])
		cols := ref[idx:] // Includes parens
		ref = fmt.Sprintf("`%s`%s", tableName, cols)
	} else {
		// Just table name
		ref = fmt.Sprintf("`%s`", ref)
	}

	ddl := fmt.Sprintf("FOREIGN KEY (`%s`) REFERENCES %s", fk.Column, ref)
	if fk.OnDelete != "" {
		ddl += fmt.Sprintf(" ON DELETE %s", fk.OnDelete)
	}
	if fk.OnUpdate != "" {
		ddl += fmt.Sprintf(" ON UPDATE %s", fk.OnUpdate)
	}
	return ddl
}

// buildIndexDDL generates inline index DDL for CREATE TABLE statement
func (r *SchemaRepository) buildIndexDDL(tableName string, idx schema.IndexDefinition) string {
	// Generate index name if not provided
	indexName := idx.Name
	if indexName == "" {
		indexName = fmt.Sprintf("idx_%s_%s", tableName, strings.Join(idx.Columns, "_"))
	}

	columnList := strings.Join(idx.Columns, "`, `")

	if idx.Unique {
		return fmt.Sprintf("UNIQUE KEY `%s` (`%s`)", indexName, columnList)
	}
	return fmt.Sprintf("KEY `%s` (`%s`)", indexName, columnList)
}

// ValidateFormula validates a formula expression syntax by attempting to compile it
func (r *SchemaRepository) ValidateFormula(formulaStr string, env map[string]interface{}) error {
	// Use the higher-level formula engine to validate (includes built-ins like BCRYPT)
	engine := formula.NewEngine()
	return engine.Validate(formulaStr, env)
}