package schemacatalog

import (
	"fmt"
	"strings"
	"sync"

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

const RowMutationProtectionAuthorityOwnedWhenAnyNonNull = "authority_owned_when_any_non_null"

// SystemTableRowMutationProtection is the immutable row-mutation fact
// projected from one canonical system-table declaration.
type SystemTableRowMutationProtection struct {
	TableName           string
	Policy              string
	ScopeFields         []SystemTableRowMutationProtectionField
	Fields              []SystemTableRowMutationProtectionField
	RequiredUniqueIndex SystemTableRowMutationProtectionIndex
}

type SystemTableRowMutationProtectionField struct {
	FieldName    string
	PhysicalName string
	SQLType      string
	Nullable     bool
}

type SystemTableRowMutationProtectionIndex struct {
	Name    string
	Columns []string
}

type systemTableRowMutationProtectionInventory map[string]SystemTableRowMutationProtection

var loadSystemTableRowMutationProtectionInventory = sync.OnceValues(func() (systemTableRowMutationProtectionInventory, error) {
	definitions, err := ManagedTableDefinitions()
	if err != nil {
		return systemTableRowMutationProtectionInventory{}, err
	}
	return systemTableRowMutationProtectionInventoryFromDefinitions(definitions)
})

// SystemTableRowMutationProtectionFor resolves the table-level row-mutation
// fact declared by system_tables.json.
func SystemTableRowMutationProtectionFor(tableName string) (SystemTableRowMutationProtection, bool, error) {
	inventory, err := loadSystemTableRowMutationProtectionInventory()
	if err != nil {
		return SystemTableRowMutationProtection{}, false, err
	}
	fact, ok := inventory[normalizeWriteProtectionLookupKey(tableName)]
	if !ok {
		return SystemTableRowMutationProtection{}, false, nil
	}
	return cloneSystemTableRowMutationProtection(fact), true, nil
}

func cloneSystemTableRowMutationProtection(
	fact SystemTableRowMutationProtection,
) SystemTableRowMutationProtection {
	fact.ScopeFields = append([]SystemTableRowMutationProtectionField(nil), fact.ScopeFields...)
	fact.Fields = append([]SystemTableRowMutationProtectionField(nil), fact.Fields...)
	fact.RequiredUniqueIndex.Columns = append(
		[]string(nil),
		fact.RequiredUniqueIndex.Columns...,
	)
	return fact
}

func systemTableRowMutationProtectionInventoryFromDefinitions(
	definitions []schema.TableDefinition,
) (systemTableRowMutationProtectionInventory, error) {
	inventory := make(systemTableRowMutationProtectionInventory)
	for _, definition := range definitions {
		declaration := definition.RowMutationProtection
		if declaration == nil {
			continue
		}
		tableName := strings.TrimSpace(definition.TableName)
		if tableName == "" {
			return nil, fmt.Errorf("row mutation protection has an empty table name")
		}
		if tableName != definition.TableName {
			return nil, fmt.Errorf(
				"row mutation protection table name %q is not canonical",
				definition.TableName,
			)
		}
		tableKey := normalizeWriteProtectionLookupKey(tableName)
		if _, duplicate := inventory[tableKey]; duplicate {
			return nil, fmt.Errorf("duplicate row mutation protection for system table %s", tableName)
		}
		if declaration.Policy != RowMutationProtectionAuthorityOwnedWhenAnyNonNull {
			return nil, fmt.Errorf(
				"system table %s has unsupported row mutation protection policy %q",
				tableName,
				declaration.Policy,
			)
		}
		if len(declaration.Fields) == 0 {
			return nil, fmt.Errorf("system table %s row mutation protection has no fields", tableName)
		}

		columns := make(map[string]schema.ColumnDefinition, len(definition.Columns))
		columnAliases := make(map[string]string, len(definition.Columns)*2)
		for _, column := range definition.Columns {
			canonicalName := strings.TrimSpace(column.Name)
			if canonicalName == "" || canonicalName != column.Name {
				return nil, fmt.Errorf(
					"system table %s row mutation protection has non-canonical field name %q",
					tableName,
					column.Name,
				)
			}
			canonicalKey := normalizeWriteProtectionLookupKey(canonicalName)
			if _, duplicate := columns[canonicalKey]; duplicate {
				return nil, fmt.Errorf(
					"system table %s row mutation protection has duplicate canonical field %s",
					tableName,
					canonicalName,
				)
			}
			physicalName := strings.TrimSpace(column.PhysicalName)
			if physicalName == "" {
				physicalName = canonicalName
			} else if physicalName != column.PhysicalName {
				return nil, fmt.Errorf(
					"system table %s row mutation protection field %s has non-canonical physical name %q",
					tableName,
					canonicalName,
					column.PhysicalName,
				)
			}
			for _, aliasKey := range []string{
				canonicalKey,
				normalizeWriteProtectionLookupKey(physicalName),
			} {
				if existing, duplicate := columnAliases[aliasKey]; duplicate &&
					existing != canonicalName {
					return nil, fmt.Errorf(
						"system table %s row mutation protection has conflicting field alias %s for %s and %s",
						tableName,
						aliasKey,
						existing,
						canonicalName,
					)
				}
				columnAliases[aliasKey] = canonicalName
			}
			columns[canonicalKey] = column
		}
		scopeFields := make(
			[]SystemTableRowMutationProtectionField,
			0,
			len(declaration.ScopeFields),
		)
		seenScopeFields := make(map[string]struct{}, len(declaration.ScopeFields))
		for _, declaredScopeFieldName := range declaration.ScopeFields {
			scopeFieldName := strings.TrimSpace(declaredScopeFieldName)
			scopeFieldKey := normalizeWriteProtectionLookupKey(scopeFieldName)
			if scopeFieldName == "" || scopeFieldName != declaredScopeFieldName {
				return nil, fmt.Errorf(
					"system table %s row mutation protection has non-canonical scope field reference %q",
					tableName,
					declaredScopeFieldName,
				)
			}
			if _, duplicate := seenScopeFields[scopeFieldKey]; duplicate {
				return nil, fmt.Errorf(
					"system table %s row mutation protection repeats scope field %s",
					tableName,
					scopeFieldName,
				)
			}
			seenScopeFields[scopeFieldKey] = struct{}{}
			column, ok := columns[scopeFieldKey]
			if !ok || column.Name != scopeFieldName {
				return nil, fmt.Errorf(
					"system table %s row mutation protection references unknown exact scope field %s",
					tableName,
					scopeFieldName,
				)
			}
			if column.Nullable ||
				!rowMutationProtectionCallerSuppliedScopeColumn(column) {
				return nil, fmt.Errorf(
					"system table %s row mutation protection scope field %s must be a typed, non-nullable, caller-supplied plain physical column",
					tableName,
					scopeFieldName,
				)
			}
			physicalName := strings.TrimSpace(column.PhysicalName)
			if physicalName == "" {
				physicalName = scopeFieldName
			}
			scopeFields = append(scopeFields, SystemTableRowMutationProtectionField{
				FieldName:    scopeFieldName,
				PhysicalName: physicalName,
				SQLType:      strings.TrimSpace(column.Type),
				Nullable:     column.Nullable,
			})
		}
		fact := SystemTableRowMutationProtection{
			TableName:   tableName,
			Policy:      declaration.Policy,
			ScopeFields: scopeFields,
			Fields:      make([]SystemTableRowMutationProtectionField, 0, len(declaration.Fields)),
		}
		seenFields := make(map[string]struct{}, len(declaration.Fields))
		for _, declaredFieldName := range declaration.Fields {
			fieldName := strings.TrimSpace(declaredFieldName)
			fieldKey := normalizeWriteProtectionLookupKey(fieldName)
			if fieldName == "" || fieldName != declaredFieldName {
				return nil, fmt.Errorf(
					"system table %s row mutation protection has non-canonical field reference %q",
					tableName,
					declaredFieldName,
				)
			}
			if _, duplicate := seenFields[fieldKey]; duplicate {
				return nil, fmt.Errorf(
					"system table %s row mutation protection repeats field %s",
					tableName,
					fieldName,
				)
			}
			seenFields[fieldKey] = struct{}{}
			column, ok := columns[fieldKey]
			if !ok {
				return nil, fmt.Errorf(
					"system table %s row mutation protection references unknown canonical field %s",
					tableName,
					fieldName,
				)
			}
			if column.Name != fieldName {
				return nil, fmt.Errorf(
					"system table %s row mutation protection field reference %s does not match canonical field %s",
					tableName,
					fieldName,
					column.Name,
				)
			}
			if !rowMutationProtectionPlainPhysicalColumn(column) {
				return nil, fmt.Errorf(
					"system table %s row mutation protection field %s must be a plain physical column",
					tableName,
					fieldName,
				)
			}
			if !column.Nullable ||
				strings.TrimSpace(column.Default) != "" ||
				column.PrimaryKey ||
				column.AutoIncrement ||
				strings.TrimSpace(column.Type) == "" ||
				strings.TrimSpace(column.WriteProtectionPolicy) != constants.FieldWriteProtectionAuthorityOnly {
				return nil, fmt.Errorf(
					"system table %s row mutation protection field %s must have a type and be nullable, default-free, non-primary, non-generated, and authority_only",
					tableName,
					fieldName,
				)
			}
			physicalName := strings.TrimSpace(column.PhysicalName)
			if physicalName == "" {
				physicalName = fieldName
			}
			fact.Fields = append(fact.Fields, SystemTableRowMutationProtectionField{
				FieldName:    fieldName,
				PhysicalName: physicalName,
				SQLType:      strings.TrimSpace(column.Type),
				Nullable:     column.Nullable,
			})
		}

		indices := make(map[string]schema.IndexDefinition, len(definition.Indices))
		for _, index := range definition.Indices {
			canonicalName := strings.TrimSpace(index.Name)
			if canonicalName == "" {
				if index.Name != "" {
					return nil, fmt.Errorf(
						"system table %s row mutation protection has non-canonical index name %q",
						tableName,
						index.Name,
					)
				}
				continue
			}
			if canonicalName != index.Name {
				return nil, fmt.Errorf(
					"system table %s row mutation protection has non-canonical index name %q",
					tableName,
					index.Name,
				)
			}
			indexKey := normalizeWriteProtectionLookupKey(canonicalName)
			if _, duplicate := indices[indexKey]; duplicate {
				return nil, fmt.Errorf(
					"system table %s row mutation protection has duplicate index name %s",
					tableName,
					canonicalName,
				)
			}
			indices[indexKey] = index
		}

		indexName := strings.TrimSpace(declaration.RequiredUniqueIndex)
		if indexName == "" || indexName != declaration.RequiredUniqueIndex {
			return nil, fmt.Errorf(
				"system table %s row mutation protection has non-canonical required index %q",
				tableName,
				declaration.RequiredUniqueIndex,
			)
		}
		index, ok := indices[normalizeWriteProtectionLookupKey(indexName)]
		if !ok {
			return nil, fmt.Errorf(
				"system table %s row mutation protection requires missing unique index %s",
				tableName,
				indexName,
			)
		}
		if index.Name != indexName {
			return nil, fmt.Errorf(
				"system table %s row mutation protection required index %s does not match canonical index %s",
				tableName,
				indexName,
				index.Name,
			)
		}
		if !index.Unique || index.Primary {
			return nil, fmt.Errorf(
				"system table %s row mutation protection index %s must be unique and non-primary",
				tableName,
				indexName,
			)
		}
		requiredIndexColumns := append(
			append([]string(nil), declaration.ScopeFields...),
			declaration.Fields...,
		)
		if len(requiredIndexColumns) != len(index.Columns) {
			return nil, fmt.Errorf(
				"system table %s row mutation protection index %s must match its exact declared columns",
				tableName,
				indexName,
			)
		}
		physicalColumns := make([]string, 0, len(index.Columns))
		seenIndexColumns := make(map[string]struct{}, len(index.Columns))
		for position, declaredIndexColumn := range index.Columns {
			indexColumn := strings.TrimSpace(declaredIndexColumn)
			indexColumnKey := normalizeWriteProtectionLookupKey(indexColumn)
			if indexColumn == "" || indexColumn != declaredIndexColumn {
				return nil, fmt.Errorf(
					"system table %s row mutation protection index %s has non-canonical field reference %q",
					tableName,
					indexName,
					declaredIndexColumn,
				)
			}
			requiredIndexColumn := strings.TrimSpace(requiredIndexColumns[position])
			if requiredIndexColumn == "" ||
				requiredIndexColumn != requiredIndexColumns[position] ||
				requiredIndexColumn != indexColumn {
				return nil, fmt.Errorf(
					"system table %s row mutation protection index %s column %d must exactly match declared field %s",
					tableName,
					indexName,
					position,
					requiredIndexColumns[position],
				)
			}
			if _, duplicate := seenIndexColumns[indexColumnKey]; duplicate {
				return nil, fmt.Errorf(
					"system table %s row mutation protection index %s repeats field %s",
					tableName,
					indexName,
					indexColumn,
				)
			}
			seenIndexColumns[indexColumnKey] = struct{}{}
			column, ok := columns[indexColumnKey]
			if !ok {
				return nil, fmt.Errorf(
					"system table %s row mutation protection index %s references unknown canonical field %s",
					tableName,
					indexName,
					indexColumn,
				)
			}
			if column.Name != indexColumn {
				return nil, fmt.Errorf(
					"system table %s row mutation protection index %s field reference %s does not match canonical field %s",
					tableName,
					indexName,
					indexColumn,
					column.Name,
				)
			}
			physicalName := strings.TrimSpace(column.PhysicalName)
			if physicalName == "" {
				physicalName = indexColumn
			}
			physicalColumns = append(physicalColumns, physicalName)
		}
		fact.RequiredUniqueIndex = SystemTableRowMutationProtectionIndex{
			Name:    indexName,
			Columns: physicalColumns,
		}
		for _, field := range fact.Fields {
			found := false
			for _, indexColumn := range fact.RequiredUniqueIndex.Columns {
				if normalizeWriteProtectionLookupKey(indexColumn) ==
					normalizeWriteProtectionLookupKey(field.PhysicalName) {
					found = true
					break
				}
			}
			if !found {
				return nil, fmt.Errorf(
					"system table %s row mutation protection index %s omits field %s",
					tableName,
					indexName,
					field.FieldName,
				)
			}
		}

		inventory[tableKey] = fact
	}
	return inventory, nil
}

func rowMutationProtectionPlainPhysicalColumn(column schema.ColumnDefinition) bool {
	formulaStorage, err := formulastorage.Resolve(formulastorage.SpecFromColumn(column))
	return err == nil &&
		!formulaStorage.FormulaBacked &&
		formulaStorage.RequiresPhysicalColumn() &&
		strings.TrimSpace(column.Type) != ""
}

func rowMutationProtectionCallerSuppliedScopeColumn(column schema.ColumnDefinition) bool {
	if !rowMutationProtectionPlainPhysicalColumn(column) ||
		constants.IsSystemField(column.Name) ||
		strings.TrimSpace(column.Default) != "" ||
		column.AutoIncrement ||
		strings.TrimSpace(column.Formula) != "" ||
		strings.TrimSpace(column.FormulaMode) != "" ||
		strings.TrimSpace(column.FormulaStorageMode) != "" ||
		column.ProjectionConfig != nil ||
		column.RollupConfig != nil ||
		column.IsCompound ||
		len(column.ComposedFrom) != 0 ||
		strings.TrimSpace(column.SiblingMaxParentField) != "" ||
		strings.TrimSpace(column.GeneratedForPolymorphicReference) != "" {
		return false
	}

	switch constants.NormalizeFieldType(column.LogicalType) {
	case constants.FieldTypeAutonumber,
		constants.FieldTypeFormula,
		constants.FieldTypeRollupSummary:
		return false
	default:
		return true
	}
}