package persistence

import (
	"fmt"
	"strings"

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

// ResolveColumnType returns the resolved physical SQL type for a column,
// applying logical-to-SQL-type mapping and length normalization.
// Package-level so bootstrap reconciliation can use the same resolution
// as DDL creation without needing a SchemaRepository receiver.
func ResolveColumnType(col schema.ColumnDefinition) string {
	// Formula fields: storage column for SF snapshot values.
	if col.Formula != "" && strings.EqualFold(col.LogicalType, string(constants.FieldTypeFormula)) {
		sqlType, err := ResolveSQLType(col.ReturnType)
		if err != nil || 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)"
			}
		}
		return sqlType
	}
	sqlType, err := ResolveSQLType(col.Type)
	if err != nil {
		// Fallback for unresolvable types — mirrors MapFieldTypeToSQL behavior.
		if col.Type == "" {
			return "TEXT"
		}
		return col.Type
	}
	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)
		}
	}
	return sqlType
}

// ResolveColumnSqlType returns the resolved physical SQL type for a column,
// applying the same formula/length normalization that buildColumnDDL uses.
// Exported so callers (e.g. ADD COLUMN planners) can reason about the
// resolved type without rebuilding the full DDL fragment.
func (r *SchemaRepository) ResolveColumnSqlType(col schema.ColumnDefinition) string {
	// Delegate to the package-level resolver. The receiver path uses
	// MapFieldTypeToSQL which calls ResolveSQLType internally, so
	// the resolution logic is identical.
	sqlType := r.MapFieldTypeToSQL(col.Type)
	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)"
			}
		}
	}
	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)
		}
	}
	return sqlType
}

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

	sqlType := r.ResolveColumnSqlType(col)

	// Formula fields: storage column with no nullability/default decorations
	// (TiDB View materializes the dynamic value).
	if col.Formula != "" && strings.EqualFold(col.LogicalType, string(constants.FieldTypeFormula)) {
		sb.WriteString(fmt.Sprintf("`%s` %s", col.Name, sqlType))
		return sb.String()
	}

	sb.WriteString(fmt.Sprintf("`%s` %s", col.Name, sqlType))
	if strings.TrimSpace(col.CollationName) != "" {
		sb.WriteString(fmt.Sprintf(" COLLATE %s", strings.TrimSpace(col.CollationName)))
	}

	if !col.Nullable {
		sb.WriteString(" NOT NULL")
	}
	if col.Default != "" && SqlTypeAllowsDefaultClause(sqlType) {
		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 {
	columnList := strings.Join(idx.Columns, "`, `")
	if idx.Primary {
		return fmt.Sprintf("PRIMARY KEY (`%s`)", columnList)
	}

	// Generate index name if not provided
	indexName := idx.Name
	if indexName == "" {
		indexName = fmt.Sprintf("idx_%s_%s", tableName, 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)
}

// IsAddColumnTwoPhase reports whether adding the given column to an existing
// table requires the multi-step (add-nullable, backfill, tighten) sequence.
// MySQL/TiDB rejects DEFAULT on JSON/TEXT/BLOB (Error 1101), and adding
// such a column as NOT NULL on a populated table either fails (strict mode)
// or silently inserts a type zero value (lenient mode) that is not the
// metadata default. The two-phase sequence is the only data-safe path.
func IsAddColumnTwoPhase(nullable bool, metaDefault, desiredSqlType string) bool {
	return !nullable && metaDefault != "" && !SqlTypeAllowsDefaultClause(desiredSqlType)
}

// PlanAddColumnStatements returns the ordered SQL statements required to add
// a column to an existing table. Most columns yield a single ALTER TABLE
// ADD COLUMN. Columns flagged by IsAddColumnTwoPhase yield three statements:
//
//	ALTER TABLE t ADD COLUMN c <type>           -- nullable, no DEFAULT
//	UPDATE t SET c = <metaDefault> WHERE c IS NULL
//	ALTER TABLE t MODIFY COLUMN c <type> NOT NULL
//
// The planner is column-shape only — it does not emit UNIQUE / FK / INDEX
// statements; callers continue to issue those separately.
//
// Concurrency note: between the UPDATE backfill and the MODIFY ... NOT NULL,
// a concurrent INSERT that omits the new column can leave a NULL row that
// causes the MODIFY to fail. The bootstrap apply path is protected by the
// nexuscrm_bootstrap_mutation advisory lock, so this race is constrained to
// runtime AddColumn paths invoked from the metadata API. Tracked in the
// L0 completeness backlog (issue #131); fix candidates include a
// runtime-side advisory lock and online-DDL coordination via the metadata
// service. Failures are recoverable: the next reconcile loop sees the
// nullable column, falls into the MODIFY path, and pre-heals plus tightens.
//
// Trust note: metaDefault is interpolated as a SQL literal (not a parameter
// binding). The repository-wide convention is that field-default values are
// pack-author-controlled and validated upstream; this planner inherits that
// trust assumption from the existing buildColumnDDL emitter.
func PlanAddColumnStatements(tableName, columnName, desiredSqlType string, nullable bool, metaDefault string, collationName string) []string {
	if IsAddColumnTwoPhase(nullable, metaDefault, desiredSqlType) {
		return []string{
			fmt.Sprintf("ALTER TABLE `%s` ADD COLUMN %s", tableName, BuildAddColumnInlineDDL(columnName, desiredSqlType, true, "", collationName)),
			fmt.Sprintf("UPDATE `%s` SET `%s` = %s WHERE `%s` IS NULL", tableName, columnName, metaDefault, columnName),
			fmt.Sprintf("ALTER TABLE `%s` MODIFY COLUMN %s", tableName, BuildAddColumnInlineDDL(columnName, desiredSqlType, false, "", collationName)),
		}
	}
	inline := BuildAddColumnInlineDDL(columnName, desiredSqlType, nullable, metaDefault, collationName)
	stmts := []string{fmt.Sprintf("ALTER TABLE `%s` ADD COLUMN %s", tableName, inline)}
	if !nullable && metaDefault != "" {
		stmts = append(stmts,
			fmt.Sprintf("UPDATE `%s` SET `%s` = %s WHERE `%s` IS NULL", tableName, columnName, metaDefault, columnName))
	}
	return stmts
}

// BuildAddColumnInlineDDL returns the inline column definition fragment
// (`Name Type [NOT NULL] [DEFAULT ...]`) suitable for embedding in batched
// ALTER TABLE statements. The DEFAULT clause is suppressed for types that
// cannot carry a physical default; see IsAddColumnTwoPhase for the safe
// add-then-backfill alternative when the column is also NOT NULL.
func BuildAddColumnInlineDDL(columnName, desiredSqlType string, nullable bool, metaDefault string, collationName string) string {
	var sb strings.Builder
	sb.WriteString(fmt.Sprintf("`%s` %s", columnName, desiredSqlType))
	if strings.TrimSpace(collationName) != "" {
		sb.WriteString(fmt.Sprintf(" COLLATE %s", strings.TrimSpace(collationName)))
	}
	if !nullable {
		sb.WriteString(" NOT NULL")
	}
	if metaDefault != "" && SqlTypeAllowsDefaultClause(desiredSqlType) {
		sb.WriteString(fmt.Sprintf(" DEFAULT %s", metaDefault))
	}
	return sb.String()
}

// SqlTypeAllowsDefaultClause reports whether the given resolved SQL type may
// carry a column DEFAULT clause. MySQL/TiDB rejects DEFAULT on JSON, TEXT,
// and BLOB columns (Error 1101), but the same defaults are still meaningful
// at the metadata layer: persistence_insert.go reads _System_Field.DefaultValue
// when materializing new records, which keeps the value-shipping contract
// for multipicklist (JSON) and textarea (TEXT) fields without leaking SQL
// into the per-write path.
//
// Exported so bootstrap.ReconcileTableColumns and the column-shape comparator
// can apply the same rule on the apply / reconcile / verify paths — every DDL
// emitter and drift detector must stay aligned, otherwise verify reports
// phantom drift on JSON/TEXT/BLOB columns whose metadata defaults were
// dropped at write time.
func SqlTypeAllowsDefaultClause(sqlType string) bool {
	upper := strings.ToUpper(strings.TrimSpace(sqlType))
	switch {
	case upper == "JSON":
		return false
	case strings.HasPrefix(upper, "TEXT"), strings.HasPrefix(upper, "TINYTEXT"),
		strings.HasPrefix(upper, "MEDIUMTEXT"), strings.HasPrefix(upper, "LONGTEXT"):
		return false
	case strings.HasPrefix(upper, "BLOB"), strings.HasPrefix(upper, "TINYBLOB"),
		strings.HasPrefix(upper, "MEDIUMBLOB"), strings.HasPrefix(upper, "LONGBLOB"):
		return false
	}
	return true
}