package packs

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

	"github.com/nexuscrm/db/pkg/schemashape"
	domainschema "github.com/nexuscrm/kernel/pkg/schema"
)

type objectSpecFieldConstraint struct {
	required  bool
	maxLength int
}

type objectSpecRequiredFieldIndex map[string]map[string]objectSpecFieldConstraint

type tableAwareColumnShapeMatcher interface {
	ColumnShapeMatchesForTable(tableName string, existing schemashape.PhysicalColumnShape, desired domainschema.ColumnDefinition) bool
}

func packColumnShapeMatchesForTable(tableName string, actual schemashape.PhysicalColumnShape, desired domainschema.ColumnDefinition) bool {
	if matcher, ok := schemaProvisioner.(tableAwareColumnShapeMatcher); ok {
		return matcher.ColumnShapeMatchesForTable(tableName, actual, desired)
	}
	return schemaProvisioner.ColumnShapeMatches(actual, desired)
}

func newObjectSpecRequiredFieldIndex(specs []ObjectSpec) objectSpecRequiredFieldIndex {
	index := objectSpecRequiredFieldIndex{}
	for _, spec := range specs {
		objectAPIName := strings.ToLower(strings.TrimSpace(spec.APIName))
		if objectAPIName == "" {
			continue
		}
		for _, field := range spec.Fields {
			fieldAPIName := strings.ToLower(strings.TrimSpace(field.APIName))
			if fieldAPIName == "" {
				continue
			}
			if index[objectAPIName] == nil {
				index[objectAPIName] = map[string]objectSpecFieldConstraint{}
			}
			index[objectAPIName][fieldAPIName] = objectSpecFieldConstraint{
				required:  packFieldRequired(field),
				maxLength: field.Length,
			}
		}
	}
	return index
}

func objectSpecRequiredFieldIndexFromArgs(indices []objectSpecRequiredFieldIndex) objectSpecRequiredFieldIndex {
	if len(indices) == 0 {
		return nil
	}
	return indices[0]
}

func (index objectSpecRequiredFieldIndex) required(objectAPIName string, fieldAPIName string) bool {
	if len(index) == 0 {
		return false
	}
	fields := index[strings.ToLower(strings.TrimSpace(objectAPIName))]
	if len(fields) == 0 {
		return false
	}
	return fields[strings.ToLower(strings.TrimSpace(fieldAPIName))].required
}

func (index objectSpecRequiredFieldIndex) maxLength(objectAPIName string, fieldAPIName string) int {
	if len(index) == 0 {
		return 0
	}
	fields := index[strings.ToLower(strings.TrimSpace(objectAPIName))]
	if len(fields) == 0 {
		return 0
	}
	return fields[strings.ToLower(strings.TrimSpace(fieldAPIName))].maxLength
}

func varcharCapacity(columnType string) (int, bool) {
	raw := strings.ToLower(strings.TrimSpace(columnType))
	raw, ok := strings.CutPrefix(raw, "varchar(")
	if !ok {
		return 0, false
	}
	raw, ok = strings.CutSuffix(raw, ")")
	if !ok {
		return 0, false
	}
	capacity, err := strconv.Atoi(raw)
	return capacity, err == nil && capacity > 0
}

func packObjectSpecColumnShapeMatchesWithCatalogLength(objectAPIName string, actual schemashape.PhysicalColumnShape, desired domainschema.ColumnDefinition, requiredFields objectSpecRequiredFieldIndex) bool {
	if packColumnShapeMatchesForTable(objectAPIName, actual, desired) {
		return true
	}
	maxLength := requiredFields.maxLength(objectAPIName, desired.Name)
	if maxLength <= 0 {
		return false
	}
	desiredCapacity, desiredIsVarchar := varcharCapacity(schemaProvisioner.DesiredColumnType(desired))
	actualCapacity, actualIsVarchar := varcharCapacity(actual.ColumnType)
	if !desiredIsVarchar || desiredCapacity != 255 || !actualIsVarchar || actualCapacity < maxLength {
		return false
	}
	compatibleDesired := desired
	compatibleDesired.Type = actual.ColumnType
	compatibleDesired.Length = 0
	return packColumnShapeMatchesForTable(objectAPIName, actual, compatibleDesired)
}

func packObjectSpecColumnShapeMatches(objectAPIName string, actual schemashape.PhysicalColumnShape, desired domainschema.ColumnDefinition, requiredFields objectSpecRequiredFieldIndex) bool {
	if packObjectSpecColumnShapeMatchesWithCatalogLength(objectAPIName, actual, desired, requiredFields) {
		return true
	}
	if actual.Nullable || !desired.Nullable || !requiredFields.required(objectAPIName, desired.Name) {
		return false
	}
	strictDesired := desired
	strictDesired.Nullable = false
	return packObjectSpecColumnShapeMatchesWithCatalogLength(objectAPIName, actual, strictDesired, requiredFields)
}

func packObjectSpecColumnShapeMatchesExceptDefault(objectAPIName string, actual schemashape.PhysicalColumnShape, desired domainschema.ColumnDefinition) bool {
	compatibleDesired := desired
	compatibleDesired.Default = ""
	if actual.DefaultVal.Valid {
		compatibleDesired.Default = actual.DefaultVal.String
		if compatibleDesired.Default == "" {
			compatibleDesired.Default = "''"
		}
	}
	return packColumnShapeMatchesForTable(objectAPIName, actual, compatibleDesired)
}

func packObjectSpecColumnShapeRetryCompatible(ctx context.Context, db *sql.DB, objectAPIName string, actual schemashape.PhysicalColumnShape, desired domainschema.ColumnDefinition, requiredFields objectSpecRequiredFieldIndex) (bool, error) {
	if packObjectSpecColumnShapeMatches(objectAPIName, actual, desired, requiredFields) {
		return true, nil
	}
	// A source-backed pack remains the authority for its physical DEFAULT.
	// Permit adoption/retry when DEFAULT is the only physical difference; the
	// full apply path immediately below this guard runs ReconcileTableColumns,
	// which performs the deterministic ALTER, and pack verification then checks
	// the resulting complete shape. Type, nullability, and any authored
	// collation must still match exactly here.
	if packObjectSpecColumnShapeMatchesExceptDefault(objectAPIName, actual, desired) {
		return true, nil
	}
	if !actual.Nullable || desired.Nullable {
		return false, nil
	}
	relaxedDesired := desired
	relaxedDesired.Nullable = true
	if !packObjectSpecColumnShapeMatchesWithCatalogLength(objectAPIName, actual, relaxedDesired, requiredFields) {
		return false, nil
	}
	if db == nil {
		return false, nil
	}
	if ctx == nil {
		ctx = context.Background()
	}
	var nullCount int
	query := fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s IS NULL", quotePackSQLIdentifier(objectAPIName), quotePackSQLIdentifier(desired.Name))
	if err := db.QueryRowContext(ctx, query).Scan(&nullCount); err != nil {
		return false, fmt.Errorf("count NULL rows before NOT NULL tighten for %s.%s: %w", objectAPIName, desired.Name, err)
	}
	if nullCount > 0 {
		return false, newOwnerlessObjectSpecCompatibilityError("physical column %s.%s is nullable but desired NOT NULL and has %d NULL row(s); data cleanup is required before safe tighten", objectAPIName, desired.Name, nullCount)
	}
	return true, nil
}

func quotePackSQLIdentifier(identifier string) string {
	return "`" + strings.ReplaceAll(identifier, "`", "``") + "`"
}