package persistence

import (
	"database/sql"
	"testing"

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

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

	column := domainschema.ColumnDefinition{
		MetadataID: "interactive-field-first", Name: "Replaceable__c", Label: "Replaceable",
		Type: "VARCHAR(255)", LogicalType: string(constants.FieldTypeString), Nullable: true,
	}
	if err := repository.AddColumn("Issue", column); err != nil {
		t.Fatalf("create first field incarnation: %v", err)
	}
	if got := storedInteractiveFieldID(t, db, "Issue", column.Name); got != column.MetadataID {
		t.Fatalf("first stored field identity = %q, want %q", got, column.MetadataID)
	}
	if err := repository.DropColumn("Issue", column.Name); err != nil {
		t.Fatalf("delete first field incarnation: %v", err)
	}

	column.MetadataID = "interactive-field-second"
	if err := repository.AddColumn("Issue", column); err != nil {
		t.Fatalf("recreate field incarnation: %v", err)
	}
	if got := storedInteractiveFieldID(t, db, "Issue", column.Name); got != column.MetadataID {
		t.Fatalf("recreated field identity = %q, want fresh %q", got, column.MetadataID)
	}
}

func storedInteractiveFieldID(t *testing.T, db *sql.DB, objectAPIName, fieldAPIName string) string {
	t.Helper()
	var id string
	if err := db.QueryRow(`SELECT f.Id FROM _System_Field f
		JOIN _System_Object o ON o.Id = f.ObjectId
		WHERE o.ApiName = ? AND f.ApiName = ?`, objectAPIName, fieldAPIName).Scan(&id); err != nil {
		t.Fatal(err)
	}
	return id
}