package persistence

import (
	"database/sql"
	"strings"
	"testing"

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

func TestPackOwnedFieldLifecycleOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	preparePackOwnedFieldCatalog(t, db)
	repo := NewSchemaRepository(db, nil)

	field := testPackOwnedFieldContext()
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", field); err != nil {
		t.Fatalf("create pack-owned field: %v", err)
	}
	assertPackOwnedFieldMetadata(t, db, "Issue", field.ColumnDefinition.Name, field.OwnerPack, "Escalation Code", false)
	assertPhysicalColumn(t, db, "Issue", field.ColumnDefinition.Name, "varchar(255)", true)

	if _, err := db.Exec(`ALTER TABLE Issue DROP COLUMN EscalationCode__c`); err != nil {
		t.Fatal(err)
	}
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", field); err != nil {
		t.Fatalf("repair missing physical column: %v", err)
	}
	assertPhysicalColumn(t, db, "Issue", field.ColumnDefinition.Name, "varchar(255)", true)

	if _, err := db.Exec(`ALTER TABLE Issue MODIFY COLUMN EscalationCode__c INT NULL`); err != nil {
		t.Fatal(err)
	}
	field.Field.Label = "Must Not Persist"
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", field); err == nil || !strings.Contains(err.Error(), "physical column shape") {
		t.Fatalf("shape drift error = %v", err)
	}
	assertPackOwnedFieldMetadata(t, db, "Issue", field.ColumnDefinition.Name, field.OwnerPack, "Escalation Code", false)

	tracked := namedPackOwnedField("TrackedOnCreate__c")
	tracked.Field.TrackHistory = true
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", tracked); err == nil || !strings.Contains(err.Error(), "created with trackHistory disabled") {
		t.Fatalf("tracked create error = %v", err)
	}
	assertFieldAndColumnAbsent(t, db, "Issue", tracked.ColumnDefinition.Name)

	history := namedPackOwnedField("HistoryValue__c")
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", history); err != nil {
		t.Fatalf("create disabled-history field: %v", err)
	}
	history.Field.TrackHistory = true
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", history); err == nil || !strings.Contains(err.Error(), "active-transaction exact-owner") {
		t.Fatalf("history convergence error = %v", err)
	}
	var trackedPersisted bool
	if err := db.QueryRow(`SELECT TrackHistory FROM _System_Field WHERE ObjectId = 'obj_issue' AND ApiName = ?`, history.ColumnDefinition.Name).Scan(&trackedPersisted); err != nil {
		t.Fatal(err)
	}
	if trackedPersisted {
		t.Fatal("non-transactional history convergence mutated metadata")
	}
	if _, err := db.Exec(`UPDATE _System_Field SET TrackHistory = 1 WHERE ObjectId = 'obj_issue' AND ApiName = ?`, history.ColumnDefinition.Name); err != nil {
		t.Fatal(err)
	}
	history.Field.TrackHistory = false
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", history); err == nil || !strings.Contains(err.Error(), "active-transaction exact-owner") {
		t.Fatalf("history disable convergence error = %v", err)
	}
	if err := db.QueryRow(`SELECT TrackHistory FROM _System_Field WHERE ObjectId = 'obj_issue' AND ApiName = ?`, history.ColumnDefinition.Name).Scan(&trackedPersisted); err != nil {
		t.Fatal(err)
	}
	if !trackedPersisted {
		t.Fatal("non-transactional history disable mutated metadata")
	}

	admin := namedPackOwnedField("AdminValue__c")
	if _, err := db.Exec(`ALTER TABLE Issue ADD COLUMN AdminValue__c VARCHAR(255) NULL`); err != nil {
		t.Fatal(err)
	}
	if _, err := db.Exec(`INSERT INTO _System_Field
(Id, ObjectId, ApiName, Label, Type, OwnerKind, IsCustomized, IsDeleted, TrackHistory)
VALUES (?, 'obj_issue', ?, 'Admin Value', 'string', 'admin', 1, 0, 0)`, admin.FieldID, admin.ColumnDefinition.Name); err != nil {
		t.Fatal(err)
	}
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", admin); err == nil || !strings.Contains(err.Error(), "OwnerKind") {
		t.Fatalf("admin ownership conflict = %v", err)
	}
	var ownerKind string
	var customized bool
	if err := db.QueryRow(`SELECT OwnerKind, IsCustomized FROM _System_Field WHERE Id = ?`, admin.FieldID).Scan(&ownerKind, &customized); err != nil {
		t.Fatal(err)
	}
	if ownerKind != constants.FieldOwnerKindAdmin || !customized {
		t.Fatalf("admin field mutated to owner=%q customized=%t", ownerKind, customized)
	}
}

func TestPackOwnedFieldConstraintOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	preparePackOwnedFieldCatalog(t, db)
	repo := NewSchemaRepository(db, nil)

	unique := namedPackOwnedField("UniqueCode__c")
	unique.ColumnDefinition.Unique = true
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", unique); err != nil {
		t.Fatalf("create unique field: %v", err)
	}
	if _, err := db.Exec(`UPDATE _System_Field SET IsUnique = 0 WHERE ObjectId = 'obj_issue' AND ApiName = 'UniqueCode__c'`); err != nil {
		t.Fatal(err)
	}
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", unique); err != nil {
		t.Fatalf("refresh unique metadata: %v", err)
	}
	assertPackOwnedFieldMetadata(t, db, "Issue", unique.ColumnDefinition.Name, unique.OwnerPack, unique.Field.Label, true)
	if _, err := db.Exec(`INSERT INTO Issue (Id, UniqueCode__c) VALUES ('unique-one', 'same')`); err != nil {
		t.Fatal(err)
	}
	if _, err := db.Exec(`INSERT INTO Issue (Id, UniqueCode__c) VALUES ('unique-two', 'same')`); err == nil {
		t.Fatal("unique constraint accepted a duplicate value")
	}
	indexName := domainschema.GenerateUniqueIndexName("Issue", unique.ColumnDefinition.Name)
	if _, err := db.Exec("ALTER TABLE Issue DROP INDEX `" + indexName + "`"); err != nil {
		t.Fatal(err)
	}
	plan, err := repo.ResolvePackOwnedFieldConvergence(t.Context(), "Issue", unique)
	if err != nil {
		t.Fatal(err)
	}
	if plan.Action != domainschema.PackOwnedFieldConvergenceFailBeforeMutation || plan.Reason != "physical-constraint-drift" || !strings.Contains(plan.Detail, "unique constraint") {
		t.Fatalf("missing unique plan = %+v", plan)
	}

	if _, err := db.Exec(`INSERT INTO Account (Id) VALUES ('account-one')`); err != nil {
		t.Fatal(err)
	}
	lookup := namedPackOwnedField("AccountId__c")
	lookup.ColumnDefinition.ReferenceTo = []string{"Account"}
	lookup.Field.ReferenceTo = []string{"Account"}
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", lookup); err != nil {
		t.Fatalf("create lookup field: %v", err)
	}
	if _, err := db.Exec(`INSERT INTO Issue (Id, AccountId__c) VALUES ('lookup-good', 'account-one')`); err != nil {
		t.Fatal(err)
	}
	if _, err := db.Exec(`INSERT INTO Issue (Id, AccountId__c) VALUES ('lookup-bad', 'missing-account')`); err == nil {
		t.Fatal("foreign key accepted a missing target")
	}
	fkName := domainschema.GenerateSafeIdentifier("fk_Issue_"+domainschema.GenerateSafeIdentifier(lookup.ColumnDefinition.Name, "col"), "fk")
	if _, err := db.Exec("ALTER TABLE Issue DROP FOREIGN KEY `" + fkName + "`"); err != nil {
		t.Fatal(err)
	}
	plan, err = repo.ResolvePackOwnedFieldConvergence(t.Context(), "Issue", lookup)
	if err != nil {
		t.Fatal(err)
	}
	if plan.Action != domainschema.PackOwnedFieldConvergenceFailBeforeMutation || plan.Reason != "physical-constraint-drift" || !strings.Contains(plan.Detail, "foreign key constraint") {
		t.Fatalf("missing foreign key plan = %+v", plan)
	}

	polymorphic := namedPackOwnedField("RelatedTo__c")
	polymorphic.ColumnDefinition.ReferenceTo = []string{"Account"}
	polymorphic.ColumnDefinition.IsPolymorphic = true
	polymorphic.Field.ReferenceTo = []string{"Account"}
	polymorphic.Field.IsPolymorphic = true
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", polymorphic); err != nil {
		t.Fatalf("create polymorphic field: %v", err)
	}
	if _, err := db.Exec(`INSERT INTO Issue (Id, RelatedTo__c) VALUES ('polymorphic-unbound', 'not-an-account')`); err != nil {
		t.Fatalf("polymorphic field incorrectly received a physical foreign key: %v", err)
	}

	freshUnique := namedPackOwnedField("FreshUnique__c")
	freshUnique.ColumnDefinition.Unique = true
	freshUnique.ColumnDefinition.Nullable = false
	freshUnique.ColumnDefinition.Default = "'same-default'"
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", freshUnique); err == nil || !strings.Contains(err.Error(), "unique constraint") {
		t.Fatalf("fresh unique convergence error = %v", err)
	}
	assertMetadataAbsent(t, db, "Issue", freshUnique.ColumnDefinition.Name)

	freshLookup := namedPackOwnedField("FreshAccountId__c")
	freshLookup.ColumnDefinition.Nullable = false
	freshLookup.ColumnDefinition.Default = "'missing-account'"
	freshLookup.ColumnDefinition.ReferenceTo = []string{"Account"}
	freshLookup.Field.ReferenceTo = []string{"Account"}
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", freshLookup); err == nil || !strings.Contains(strings.ToLower(err.Error()), "foreign key") {
		t.Fatalf("fresh foreign-key convergence error = %v", err)
	}
	assertMetadataAbsent(t, db, "Issue", freshLookup.ColumnDefinition.Name)
}

func TestPackOwnedFormulaStorageOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	preparePackOwnedFieldCatalog(t, db)
	repo := NewSchemaRepository(db, nil)

	formula := testPackOwnedFormulaFieldContext()
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", formula); err != nil {
		t.Fatalf("register active formula: %v", err)
	}
	assertColumnAbsent(t, db, "Issue", formula.ColumnDefinition.Name)
	assertPackOwnedFieldMetadata(t, db, "Issue", formula.ColumnDefinition.Name, formula.OwnerPack, formula.Field.Label, false)

	projection := testPackOwnedProjectionFieldContext()
	if err := repo.EnsurePackOwnedField(t.Context(), "PricebookEntry", projection); err != nil {
		t.Fatalf("register active projection: %v", err)
	}
	assertColumnAbsent(t, db, "PricebookEntry", projection.ColumnDefinition.Name)
	var projectionJSON sql.NullString
	if err := db.QueryRow(`SELECT ProjectionConfig FROM _System_Field WHERE ObjectId = 'obj_pricebookentry' AND ApiName = 'Name'`).Scan(&projectionJSON); err != nil {
		t.Fatal(err)
	}
	if !projectionJSON.Valid || !strings.Contains(projectionJSON.String, "Product2") {
		t.Fatalf("persisted projection = %+v", projectionJSON)
	}

	materialized := testPackOwnedWriteMaterializedFormulaFieldContext()
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", materialized); err != nil {
		t.Fatalf("create write-materialized formula: %v", err)
	}
	assertPhysicalColumn(t, db, "Issue", materialized.ColumnDefinition.Name, "varchar(255)", true)

	unexpected := testPackOwnedFormulaFieldContext()
	unexpected.ColumnDefinition.Name = "UnexpectedFormula__c"
	unexpected.Field.APIName = unexpected.ColumnDefinition.Name
	unexpected.FieldID = "fld_issue_unexpectedformula"
	if _, err := db.Exec(`ALTER TABLE Issue ADD COLUMN UnexpectedFormula__c VARCHAR(255) NULL`); err != nil {
		t.Fatal(err)
	}
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", unexpected); err == nil || !strings.Contains(err.Error(), "metadata-only field") {
		t.Fatalf("unexpected formula column error = %v", err)
	}
	assertMetadataAbsent(t, db, "Issue", unexpected.ColumnDefinition.Name)

	if _, err := db.Exec(`ALTER TABLE Issue ADD COLUMN EscalationScore__c VARCHAR(255) NULL`); err != nil {
		t.Fatal(err)
	}
	if err := repo.EnsurePackOwnedField(t.Context(), "Issue", formula); err == nil || !strings.Contains(err.Error(), "metadata-only field") {
		t.Fatalf("existing formula physical drift error = %v", err)
	}

	frozen := frozenDatetimeFormulaCandidate()
	if _, err := db.Exec(`ALTER TABLE Opportunity ADD COLUMN LastStageChangeDate DATETIME NULL`); err != nil {
		t.Fatal(err)
	}
	if _, err := db.Exec(`INSERT INTO _System_Field
(Id, ObjectId, ApiName, Label, Type, Formula, FormulaMode, ReturnType, OwnerKind, OwnerPack, IsCustomized, IsDeleted, TrackHistory)
VALUES (?, 'obj_opportunity', ?, ?, 'datetime', ?, 'frozen', 'datetime', 'pack', ?, 0, 0, 0)`,
		frozen.FieldID, frozen.Field.APIName, frozen.Field.Label, *frozen.Field.Formula, frozen.OwnerPack); err != nil {
		t.Fatal(err)
	}
	plan, err := repo.ResolvePackOwnedFieldConvergence(t.Context(), "Opportunity", frozen)
	if err != nil {
		t.Fatal(err)
	}
	if plan.Action != domainschema.PackOwnedFieldConvergenceVerifyOnly || plan.Reason != "physical-matches" {
		t.Fatalf("frozen formula promotion plan = %+v", plan)
	}
}

func preparePackOwnedFieldCatalog(t *testing.T, db *sql.DB) {
	t.Helper()
	definitions, err := schemacatalog.SystemTableDefinitions()
	if err != nil {
		t.Fatal(err)
	}
	wanted := map[string]bool{constants.TableObject: false, constants.TableField: false}
	repo := NewSchemaRepository(db, nil)
	for _, definition := range definitions {
		if _, ok := wanted[definition.TableName]; !ok {
			continue
		}
		if err := repo.CreatePhysicalTable(t.Context(), definition); err != nil {
			t.Fatalf("create canonical %s: %v", definition.TableName, err)
		}
		wanted[definition.TableName] = true
	}
	for table, found := range wanted {
		if !found {
			t.Fatalf("system catalog does not declare %s", table)
		}
	}
	for _, object := range []struct{ id, apiName string }{
		{id: "obj_issue", apiName: "Issue"},
		{id: "obj_account", apiName: "Account"},
		{id: "obj_pricebookentry", apiName: "PricebookEntry"},
		{id: "obj_product2", apiName: "Product2"},
		{id: "obj_opportunity", apiName: "Opportunity"},
	} {
		if _, err := db.Exec(`INSERT INTO _System_Object (Id, ApiName, Label) VALUES (?, ?, ?)`, object.id, object.apiName, object.apiName); err != nil {
			t.Fatal(err)
		}
		if _, err := db.Exec("CREATE TABLE `" + object.apiName + "` (Id VARCHAR(255) PRIMARY KEY) " + constants.DefaultTableOptions); err != nil {
			t.Fatal(err)
		}
	}
}

func namedPackOwnedField(name string) domainschema.FieldWithContext {
	field := testPackOwnedFieldContext()
	field.ColumnDefinition.Name = name
	field.ColumnDefinition.Label = name
	field.Field.APIName = name
	field.Field.Label = name
	field.FieldID = domainschema.GenerateFieldID("Issue", name)
	return field
}

func frozenDatetimeFormulaCandidate() domainschema.FieldWithContext {
	ownerPack := "salesforce-crm-standard"
	formula := `IF(OR(ISNEW(), ISCHANGED(StageName)), NOW(), PRIORVALUE(LastStageChangeDate))`
	mode := "frozen"
	returnType := models.FieldType(constants.FieldTypeDatetime)
	return domainschema.FieldWithContext{
		Field: &models.FieldMetadata{
			APIName: "LastStageChangeDate", Label: "Last Stage Change Date", Type: constants.FieldTypeFormula,
			Formula: &formula, FormulaMode: &mode, ReturnType: &returnType,
			OwnerKind: constants.FieldOwnerKindPack, OwnerPack: &ownerPack,
			WriteProtectionPolicy: constants.FieldWriteProtectionTrustedOnly,
		},
		ColumnDefinition: domainschema.ColumnDefinition{
			Name: "LastStageChangeDate", Label: "Last Stage Change Date", Type: "DATETIME",
			LogicalType: string(constants.FieldTypeFormula), Formula: formula, FormulaMode: mode,
			ReturnType: string(constants.FieldTypeDatetime), Nullable: true,
			WriteProtectionPolicy: constants.FieldWriteProtectionTrustedOnly,
		},
		TableName: "Opportunity", ObjectID: "obj_opportunity", FieldID: "fld_opportunity_laststagechangedate",
		OwnerKind: constants.FieldOwnerKindPack, OwnerPack: ownerPack, CapabilitiesDeclared: true,
	}
}

func assertPackOwnedFieldMetadata(t *testing.T, db *sql.DB, objectName, fieldName, ownerPack, label string, unique bool) {
	t.Helper()
	var gotOwnerKind, gotOwnerPack, gotLabel string
	var customized, deleted, gotUnique bool
	if err := db.QueryRow(`SELECT f.OwnerKind, f.OwnerPack, f.Label, f.IsCustomized, f.IsDeleted, f.IsUnique
FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE o.ApiName = ? AND f.ApiName = ?`, objectName, fieldName).
		Scan(&gotOwnerKind, &gotOwnerPack, &gotLabel, &customized, &deleted, &gotUnique); err != nil {
		t.Fatal(err)
	}
	if gotOwnerKind != constants.FieldOwnerKindPack || gotOwnerPack != ownerPack || gotLabel != label || customized || deleted || gotUnique != unique {
		t.Fatalf("metadata %s.%s = owner:%q/%q label:%q customized:%t deleted:%t unique:%t",
			objectName, fieldName, gotOwnerKind, gotOwnerPack, gotLabel, customized, deleted, gotUnique)
	}
}

func assertPhysicalColumn(t *testing.T, db *sql.DB, tableName, columnName, columnType string, nullable bool) {
	t.Helper()
	var gotType, gotNullable string
	if err := db.QueryRow(`SELECT COLUMN_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?`, tableName, columnName).Scan(&gotType, &gotNullable); err != nil {
		t.Fatal(err)
	}
	if !strings.EqualFold(gotType, columnType) || (gotNullable == "YES") != nullable {
		t.Fatalf("physical %s.%s = type:%q nullable:%q", tableName, columnName, gotType, gotNullable)
	}
}

func assertColumnAbsent(t *testing.T, db *sql.DB, tableName, columnName string) {
	t.Helper()
	var count int
	if err := db.QueryRow(`SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?`, tableName, columnName).Scan(&count); err != nil {
		t.Fatal(err)
	}
	if count != 0 {
		t.Fatalf("physical column %s.%s exists", tableName, columnName)
	}
}

func assertMetadataAbsent(t *testing.T, db *sql.DB, objectName, fieldName string) {
	t.Helper()
	var count int
	if err := db.QueryRow(`SELECT COUNT(*) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId
WHERE o.ApiName = ? AND f.ApiName = ?`, objectName, fieldName).Scan(&count); err != nil {
		t.Fatal(err)
	}
	if count != 0 {
		t.Fatalf("metadata field %s.%s exists", objectName, fieldName)
	}
}

func assertFieldAndColumnAbsent(t *testing.T, db *sql.DB, objectName, fieldName string) {
	t.Helper()
	assertMetadataAbsent(t, db, objectName, fieldName)
	assertColumnAbsent(t, db, objectName, fieldName)
}