package schemacatalog

import (
	"testing"

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

func TestRecordTypeCatalogDeclaresRowMutationProtection(t *testing.T) {
	fact, ok, err := SystemTableRowMutationProtectionFor("_system_recordtype")
	if err != nil {
		t.Fatalf("SystemTableRowMutationProtectionFor(): %v", err)
	}
	if !ok {
		t.Fatal("SystemTableRowMutationProtectionFor() = not found")
	}
	if fact.Policy != RowMutationProtectionAuthorityOwnedWhenAnyNonNull {
		t.Fatalf("policy = %q", fact.Policy)
	}
	if len(fact.ScopeFields) != 1 ||
		fact.ScopeFields[0].FieldName != "ObjectId" ||
		fact.ScopeFields[0].PhysicalName != "ObjectId" ||
		fact.ScopeFields[0].SQLType != "VARCHAR(255)" ||
		fact.ScopeFields[0].Nullable {
		t.Fatalf("scope fields = %+v", fact.ScopeFields)
	}
	if len(fact.Fields) != 2 ||
		fact.Fields[0].FieldName != "CatalogNamespaceId" ||
		fact.Fields[0].SQLType != "VARBINARY(128)" ||
		!fact.Fields[0].Nullable ||
		fact.Fields[1].FieldName != "CatalogLocalKey" ||
		fact.Fields[1].SQLType != "VARBINARY(128)" ||
		!fact.Fields[1].Nullable {
		t.Fatalf("fields = %+v", fact.Fields)
	}
	if fact.RequiredUniqueIndex.Name != "uq_record_type_catalog_key" ||
		len(fact.RequiredUniqueIndex.Columns) != 3 ||
		fact.RequiredUniqueIndex.Columns[0] != "ObjectId" ||
		fact.RequiredUniqueIndex.Columns[1] != "CatalogNamespaceId" ||
		fact.RequiredUniqueIndex.Columns[2] != "CatalogLocalKey" {
		t.Fatalf("required unique index = %+v", fact.RequiredUniqueIndex)
	}
}

func TestSystemTableRowMutationProtectionForReturnsIndependentFacts(t *testing.T) {
	first, ok, err := SystemTableRowMutationProtectionFor("_System_RecordType")
	if err != nil {
		t.Fatalf("SystemTableRowMutationProtectionFor(first): %v", err)
	}
	if !ok {
		t.Fatal("SystemTableRowMutationProtectionFor(first) = not found")
	}
	first.ScopeFields[0].FieldName = "MutatedScopeField"
	first.Fields[0].FieldName = "MutatedField"
	first.RequiredUniqueIndex.Columns[0] = "MutatedColumn"

	second, ok, err := SystemTableRowMutationProtectionFor("_System_RecordType")
	if err != nil {
		t.Fatalf("SystemTableRowMutationProtectionFor(second): %v", err)
	}
	if !ok {
		t.Fatal("SystemTableRowMutationProtectionFor(second) = not found")
	}
	if second.ScopeFields[0].FieldName != "ObjectId" {
		t.Fatalf("scope field name = %q after caller mutation", second.ScopeFields[0].FieldName)
	}
	if second.Fields[0].FieldName != "CatalogNamespaceId" {
		t.Fatalf("field name = %q after caller mutation", second.Fields[0].FieldName)
	}
	if second.RequiredUniqueIndex.Columns[0] != "ObjectId" {
		t.Fatalf("index column = %q after caller mutation", second.RequiredUniqueIndex.Columns[0])
	}
}

func TestRowMutationProtectionRequiresNullableAuthorityOnlyFields(t *testing.T) {
	definition := schema.TableDefinition{
		TableName: "NeutralControl",
		Columns: []schema.ColumnDefinition{{
			Name:                  "AuthorityValue",
			Type:                  "VARBINARY(128)",
			Nullable:              true,
			WriteProtectionPolicy: constants.FieldWriteProtectionTrustedOnly,
		}},
		Indices: []schema.IndexDefinition{{
			Name:    "uq_authority",
			Columns: []string{"AuthorityValue"},
			Unique:  true,
		}},
		RowMutationProtection: &schema.RowMutationProtectionDefinition{
			Policy:              RowMutationProtectionAuthorityOwnedWhenAnyNonNull,
			Fields:              []string{"AuthorityValue"},
			RequiredUniqueIndex: "uq_authority",
		},
	}
	if _, err := systemTableRowMutationProtectionInventoryFromDefinitions([]schema.TableDefinition{definition}); err == nil {
		t.Fatal("row mutation protection accepted a non-authority_only field")
	}
}

func TestRowMutationProtectionProjectsPhysicalFieldNamesIntoIndex(t *testing.T) {
	definition := schema.TableDefinition{
		TableName: "NeutralControl",
		Columns: []schema.ColumnDefinition{{
			Name:                  "AuthorityValue",
			PhysicalName:          "authority_value",
			Type:                  "VARBINARY(128)",
			Nullable:              true,
			WriteProtectionPolicy: constants.FieldWriteProtectionAuthorityOnly,
		}},
		Indices: []schema.IndexDefinition{{
			Name:    "uq_authority",
			Columns: []string{"AuthorityValue"},
			Unique:  true,
		}},
		RowMutationProtection: &schema.RowMutationProtectionDefinition{
			Policy:              RowMutationProtectionAuthorityOwnedWhenAnyNonNull,
			Fields:              []string{"AuthorityValue"},
			RequiredUniqueIndex: "uq_authority",
		},
	}
	inventory, err := systemTableRowMutationProtectionInventoryFromDefinitions(
		[]schema.TableDefinition{definition},
	)
	if err != nil {
		t.Fatalf("systemTableRowMutationProtectionInventoryFromDefinitions(): %v", err)
	}
	fact := inventory[normalizeWriteProtectionLookupKey(definition.TableName)]
	if len(fact.Fields) != 1 || fact.Fields[0].PhysicalName != "authority_value" {
		t.Fatalf("fields = %+v", fact.Fields)
	}
	if len(fact.RequiredUniqueIndex.Columns) != 1 ||
		fact.RequiredUniqueIndex.Columns[0] != "authority_value" {
		t.Fatalf("required unique index = %+v", fact.RequiredUniqueIndex)
	}
}

func TestRowMutationProtectionRequiresDeclaredUniqueIndex(t *testing.T) {
	definition := schema.TableDefinition{
		TableName: "NeutralControl",
		Columns: []schema.ColumnDefinition{{
			Name:                  "AuthorityValue",
			Type:                  "VARBINARY(128)",
			Nullable:              true,
			WriteProtectionPolicy: constants.FieldWriteProtectionAuthorityOnly,
		}},
		RowMutationProtection: &schema.RowMutationProtectionDefinition{
			Policy:              RowMutationProtectionAuthorityOwnedWhenAnyNonNull,
			Fields:              []string{"AuthorityValue"},
			RequiredUniqueIndex: "uq_authority",
		},
	}
	if _, err := systemTableRowMutationProtectionInventoryFromDefinitions([]schema.TableDefinition{definition}); err == nil {
		t.Fatal("row mutation protection accepted a missing readiness index")
	}
}

func TestRowMutationProtectionRejectsCollidingPhysicalFieldAliases(t *testing.T) {
	definition := schema.TableDefinition{
		TableName: "NeutralControl",
		Columns: []schema.ColumnDefinition{
			{
				Name:                  "AuthorityNamespace",
				PhysicalName:          "authority_key",
				Type:                  "VARBINARY(128)",
				Nullable:              true,
				WriteProtectionPolicy: constants.FieldWriteProtectionAuthorityOnly,
			},
			{
				Name:                  "AuthorityLocalKey",
				PhysicalName:          "AUTHORITY_KEY",
				Type:                  "VARBINARY(128)",
				Nullable:              true,
				WriteProtectionPolicy: constants.FieldWriteProtectionAuthorityOnly,
			},
		},
		Indices: []schema.IndexDefinition{{
			Name:    "uq_authority",
			Columns: []string{"AuthorityNamespace", "AuthorityLocalKey"},
			Unique:  true,
		}},
		RowMutationProtection: &schema.RowMutationProtectionDefinition{
			Policy:              RowMutationProtectionAuthorityOwnedWhenAnyNonNull,
			Fields:              []string{"AuthorityNamespace", "AuthorityLocalKey"},
			RequiredUniqueIndex: "uq_authority",
		},
	}
	if _, err := systemTableRowMutationProtectionInventoryFromDefinitions([]schema.TableDefinition{definition}); err == nil {
		t.Fatal("row mutation protection accepted colliding physical field aliases")
	}
}

func TestRowMutationProtectionRejectsDuplicateNormalizedIndexNames(t *testing.T) {
	definition := schema.TableDefinition{
		TableName: "NeutralControl",
		Columns: []schema.ColumnDefinition{{
			Name:                  "AuthorityValue",
			Type:                  "VARBINARY(128)",
			Nullable:              true,
			WriteProtectionPolicy: constants.FieldWriteProtectionAuthorityOnly,
		}},
		Indices: []schema.IndexDefinition{
			{Name: "uq_authority", Columns: []string{"AuthorityValue"}, Unique: true},
			{Name: "UQ_AUTHORITY", Columns: []string{"AuthorityValue"}, Unique: true},
		},
		RowMutationProtection: &schema.RowMutationProtectionDefinition{
			Policy:              RowMutationProtectionAuthorityOwnedWhenAnyNonNull,
			Fields:              []string{"AuthorityValue"},
			RequiredUniqueIndex: "uq_authority",
		},
	}
	if _, err := systemTableRowMutationProtectionInventoryFromDefinitions([]schema.TableDefinition{definition}); err == nil {
		t.Fatal("row mutation protection accepted duplicate normalized index names")
	}
}

func TestRowMutationProtectionRejectsDuplicateIndexColumns(t *testing.T) {
	definition := schema.TableDefinition{
		TableName: "NeutralControl",
		Columns: []schema.ColumnDefinition{{
			Name:                  "AuthorityValue",
			Type:                  "VARBINARY(128)",
			Nullable:              true,
			WriteProtectionPolicy: constants.FieldWriteProtectionAuthorityOnly,
		}},
		Indices: []schema.IndexDefinition{{
			Name:    "uq_authority",
			Columns: []string{"AuthorityValue", "AuthorityValue"},
			Unique:  true,
		}},
		RowMutationProtection: &schema.RowMutationProtectionDefinition{
			Policy:              RowMutationProtectionAuthorityOwnedWhenAnyNonNull,
			ScopeFields:         []string{"AuthorityValue"},
			Fields:              []string{"AuthorityValue"},
			RequiredUniqueIndex: "uq_authority",
		},
	}
	if _, err := systemTableRowMutationProtectionInventoryFromDefinitions([]schema.TableDefinition{definition}); err == nil {
		t.Fatal("row mutation protection accepted duplicate index columns")
	}
}

func TestRowMutationProtectionRejectsFormulaBackedAuthorityFields(t *testing.T) {
	definition := validScopedRowMutationProtectionDefinition()
	definition.Columns[1].Formula = "ScopeId"
	definition.Columns[1].FormulaMode = "active"

	if _, err := systemTableRowMutationProtectionInventoryFromDefinitions(
		[]schema.TableDefinition{definition},
	); err == nil {
		t.Fatal("row mutation protection accepted a formula-backed authority field")
	}
}

func TestRowMutationProtectionRejectsPrimaryAuthorityFields(t *testing.T) {
	definition := validScopedRowMutationProtectionDefinition()
	definition.Columns[1].PrimaryKey = true

	if _, err := systemTableRowMutationProtectionInventoryFromDefinitions(
		[]schema.TableDefinition{definition},
	); err == nil {
		t.Fatal("row mutation protection accepted a primary authority field")
	}
}

func TestRowMutationProtectionRequiresNonNullablePhysicalScopeFields(t *testing.T) {
	for _, testCase := range []struct {
		name   string
		mutate func(*schema.TableDefinition)
	}{
		{
			name: "formula backed",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].Formula = "AuthorityValue"
				definition.Columns[0].FormulaMode = "active"
			},
		},
		{
			name: "formula mode without expression",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].FormulaMode = "active"
			},
		},
		{
			name: "projection",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].ProjectionConfig = &models.FieldProjectionConfig{}
			},
		},
		{
			name: "nullable",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].Nullable = true
			},
		},
		{
			name: "defaulted",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].Default = "generated-scope"
			},
		},
		{
			name: "auto increment",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].AutoIncrement = true
			},
		},
		{
			name: "autonumber",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].LogicalType = string(constants.FieldTypeAutonumber)
			},
		},
		{
			name: "rollup summary",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].RollupConfig = &models.RollupConfig{}
			},
		},
		{
			name: "rollup summary logical type",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].LogicalType = string(constants.FieldTypeRollupSummary)
			},
		},
		{
			name: "sibling max default",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].SiblingMaxParentField = "ParentId"
			},
		},
		{
			name: "generated polymorphic discriminator",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].GeneratedForPolymorphicReference = "ParentId"
			},
		},
		{
			name: "compound",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].IsCompound = true
			},
		},
		{
			name: "compound members",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].ComposedFrom = []string{"FirstPart", "SecondPart"}
			},
		},
		{
			name: "empty type",
			mutate: func(definition *schema.TableDefinition) {
				definition.Columns[0].Type = ""
			},
		},
	} {
		t.Run(testCase.name, func(t *testing.T) {
			definition := validScopedRowMutationProtectionDefinition()
			testCase.mutate(&definition)
			if _, err := systemTableRowMutationProtectionInventoryFromDefinitions(
				[]schema.TableDefinition{definition},
			); err == nil {
				t.Fatal("row mutation protection accepted an invalid uniqueness scope field")
			}
		})
	}
}

func TestRowMutationProtectionRejectsPlatformSystemScopeFields(t *testing.T) {
	fieldNames := append(constants.StandardSystemFields(), constants.FieldRecordTypeID)
	for _, fieldName := range fieldNames {
		t.Run(fieldName, func(t *testing.T) {
			definition := validScopedRowMutationProtectionDefinition()
			definition.Columns[0].Name = fieldName
			definition.Indices[0].Columns[0] = fieldName
			definition.RowMutationProtection.ScopeFields[0] = fieldName

			if _, err := systemTableRowMutationProtectionInventoryFromDefinitions(
				[]schema.TableDefinition{definition},
			); err == nil {
				t.Fatal("row mutation protection accepted a platform-supplied system scope field")
			}
		})
	}
}

func TestRowMutationProtectionRequiresExactNonPrimaryReadinessIndex(t *testing.T) {
	t.Run("valid exact scoped index", func(t *testing.T) {
		definition := validScopedRowMutationProtectionDefinition()
		if _, err := systemTableRowMutationProtectionInventoryFromDefinitions(
			[]schema.TableDefinition{definition},
		); err != nil {
			t.Fatalf("exact readiness index rejected: %v", err)
		}
	})

	t.Run("primary index", func(t *testing.T) {
		definition := validScopedRowMutationProtectionDefinition()
		definition.Indices[0].Primary = true
		if _, err := systemTableRowMutationProtectionInventoryFromDefinitions(
			[]schema.TableDefinition{definition},
		); err == nil {
			t.Fatal("row mutation protection accepted a primary readiness index")
		}
	})

	t.Run("extra undeclared column", func(t *testing.T) {
		definition := validScopedRowMutationProtectionDefinition()
		definition.Columns = append(definition.Columns, schema.ColumnDefinition{
			Name:     "Variant",
			Type:     "VARCHAR(64)",
			Nullable: false,
		})
		definition.Indices[0].Columns = []string{"ScopeId", "Variant", "AuthorityValue"}
		if _, err := systemTableRowMutationProtectionInventoryFromDefinitions(
			[]schema.TableDefinition{definition},
		); err == nil {
			t.Fatal("row mutation protection accepted an undeclared readiness-index column")
		}
	})

	t.Run("reordered columns", func(t *testing.T) {
		definition := validScopedRowMutationProtectionDefinition()
		definition.Indices[0].Columns = []string{"AuthorityValue", "ScopeId"}
		if _, err := systemTableRowMutationProtectionInventoryFromDefinitions(
			[]schema.TableDefinition{definition},
		); err == nil {
			t.Fatal("row mutation protection accepted reordered readiness-index columns")
		}
	})
}

func validScopedRowMutationProtectionDefinition() schema.TableDefinition {
	return schema.TableDefinition{
		TableName: "NeutralControl",
		Columns: []schema.ColumnDefinition{
			{
				Name:     "ScopeId",
				Type:     "VARCHAR(255)",
				Nullable: false,
			},
			{
				Name:                  "AuthorityValue",
				Type:                  "VARBINARY(128)",
				Nullable:              true,
				WriteProtectionPolicy: constants.FieldWriteProtectionAuthorityOnly,
			},
		},
		Indices: []schema.IndexDefinition{{
			Name:    "uq_authority",
			Columns: []string{"ScopeId", "AuthorityValue"},
			Unique:  true,
		}},
		RowMutationProtection: &schema.RowMutationProtectionDefinition{
			Policy:              RowMutationProtectionAuthorityOwnedWhenAnyNonNull,
			ScopeFields:         []string{"ScopeId"},
			Fields:              []string{"AuthorityValue"},
			RequiredUniqueIndex: "uq_authority",
		},
	}
}

func TestRowMutationProtectionRejectsNonCanonicalIdentifiersAndEmptyTypes(t *testing.T) {
	for _, tc := range []struct {
		name       string
		tableName  string
		columnName string
		columnType string
		indexName  string
		required   string
	}{
		{
			name:       "table",
			tableName:  " NeutralControl",
			columnName: "AuthorityValue",
			columnType: "VARBINARY(128)",
			indexName:  "uq_authority",
			required:   "uq_authority",
		},
		{
			name:       "column",
			tableName:  "NeutralControl",
			columnName: " AuthorityValue",
			columnType: "VARBINARY(128)",
			indexName:  "uq_authority",
			required:   "uq_authority",
		},
		{
			name:       "type",
			tableName:  "NeutralControl",
			columnName: "AuthorityValue",
			indexName:  "uq_authority",
			required:   "uq_authority",
		},
		{
			name:       "index",
			tableName:  "NeutralControl",
			columnName: "AuthorityValue",
			columnType: "VARBINARY(128)",
			indexName:  " uq_authority",
			required:   " uq_authority",
		},
	} {
		t.Run(tc.name, func(t *testing.T) {
			definition := schema.TableDefinition{
				TableName: tc.tableName,
				Columns: []schema.ColumnDefinition{{
					Name:                  tc.columnName,
					Type:                  tc.columnType,
					Nullable:              true,
					WriteProtectionPolicy: constants.FieldWriteProtectionAuthorityOnly,
				}},
				Indices: []schema.IndexDefinition{{
					Name:    tc.indexName,
					Columns: []string{tc.columnName},
					Unique:  true,
				}},
				RowMutationProtection: &schema.RowMutationProtectionDefinition{
					Policy:              RowMutationProtectionAuthorityOwnedWhenAnyNonNull,
					Fields:              []string{tc.columnName},
					RequiredUniqueIndex: tc.required,
				},
			}
			if _, err := systemTableRowMutationProtectionInventoryFromDefinitions(
				[]schema.TableDefinition{definition},
			); err == nil {
				t.Fatal("row mutation protection accepted a malformed declaration")
			}
		})
	}
}