//go:build integration

package metadata

import (
	"context"
	"fmt"
	"testing"
	"time"

	"github.com/nexuscrm/backend/internal/domain/schema"
	"github.com/nexuscrm/backend/internal/features/events"
	"github.com/nexuscrm/backend/internal/infrastructure/database"
	"github.com/nexuscrm/backend/internal/infrastructure/persistence"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

// TestSchemaManager_Integration_ACID creates a real table and verifies metadata registration
// This test requires a running TiDB instance configured in environment variables
func TestSchemaManager_Integration_ACID(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping integration test in short mode")
	}

	// 1. Setup Connection
	// We use the singleton getter which uses env vars (TIDB_HOST etc.)
	conn, err := database.GetInstance()
	if err != nil {
		t.Skip("Skipping integration test: failed to connect to DB: " + err.Error())
	}
	db := conn.DB()

	repo := persistence.NewSchemaRepository(db, nil)
	sm := NewSchemaManager(repo)
	tableName := fmt.Sprintf("test_acid_obj_%d", time.Now().UnixNano())

	// Helper to cleanup
	cleanup := func() {
		// We use DropTable which also cleans metadata

		if err := sm.DropTable(tableName); err != nil {
			t.Logf("Failed to cleanup table %s: %v", tableName, err)
		}
	}
	defer cleanup()

	// 2. Define Table
	def := schema.TableDefinition{
		TableName:   tableName,
		TableType:   string(constants.TableTypeCustomObject),
		Category:    "test",
		Description: "Test Object for ACID",
		Columns: []schema.ColumnDefinition{
			{Name: constants.FieldID, Type: "VARCHAR(36)", Nullable: false, PrimaryKey: true, LogicalType: "ID"}, // Added ID to satisfy check
			{Name: constants.FieldName, Type: "VARCHAR(255)", Nullable: false, LogicalType: string(constants.FieldTypeString)},
			{Name: "Amount", Type: "DECIMAL(10,2)", Nullable: true, LogicalType: string(constants.FieldTypeCurrency)},
		},
	}

	// 3. Execute Create (Transactional)
	t.Logf("Creating table %s...", tableName)

	// Create Object Metadata explicitly for strict mode
	desc := "Test Object for ACID"
	objMeta := &models.ObjectMetadata{
		APIName:      tableName,
		Label:        "Test Object",
		PluralLabel:  "Test Objects",
		Description:  &desc,
		IsCustom:     true,
		SharingModel: models.SharingModel(constants.SharingModelPublicReadWrite),
	}

	err = sm.CreateTableWithStrictMetadata(context.Background(), def, objMeta)
	assert.NoError(t, err, "CreateTableWithStrictMetadata should succeed")

	// 4. Verify Physical Table Existence
	// Using a direct query to information_schema or checking via SQL
	ctx := context.Background()
	var exists int
	checkTableQuery := "SELECT COUNT(*) FROM information_schema.tables WHERE table_name = ? AND table_schema = DATABASE()"
	err = db.QueryRowContext(ctx, checkTableQuery, tableName).Scan(&exists)
	assert.NoError(t, err)
	assert.Equal(t, 1, exists, "Physical table must exist in database")

	// 5. Verify Metadata (_System_Object)
	var objID string
	var isCustom bool
	checkObjQuery := fmt.Sprintf("SELECT %s, %s FROM %s WHERE %s = ?", constants.ColID, constants.FieldSysObject_IsCustom, constants.TableObject, constants.FieldSysObject_APIName)
	err = db.QueryRowContext(ctx, checkObjQuery, tableName).Scan(&objID, &isCustom)
	assert.NoError(t, err, "Object metadata must exist")
	assert.NotEmpty(t, objID)
	assert.True(t, isCustom, "Should be custom object")

	// 6. Verify Field Metadata (_System_Field)
	// Check 'Name' field
	var fieldID string
	var fieldType string
	checkFieldQuery := fmt.Sprintf("SELECT %s, %s FROM %s WHERE %s = ? AND %s = ?", constants.ColID, constants.FieldSysField_Type, constants.TableField, constants.FieldObjectID, constants.FieldSysField_APIName)

	err = db.QueryRowContext(ctx, checkFieldQuery, objID, "Name").Scan(&fieldID, &fieldType)
	assert.NoError(t, err, "Field 'Name' metadata must exist")
	assert.Equal(t, string(constants.FieldTypeString), fieldType)

	// Check 'Amount' field
	err = db.QueryRowContext(ctx, checkFieldQuery, objID, "Amount").Scan(&fieldID, &fieldType)
	assert.NoError(t, err, "Field 'Amount' metadata must exist")
	assert.Equal(t, string(constants.FieldTypeCurrency), fieldType)

	// 7. Verify System Fields (Auto-registered)
	// ID, CreatedDate, etc.
	err = db.QueryRowContext(ctx, checkFieldQuery, objID, constants.FieldID).Scan(&fieldID, &fieldType)
	assert.NoError(t, err, "System field 'id' metadata must exist")

	t.Log("✅ Integration Test Passed: Table and Metadata created correctly.")
}

// TestBatchCreateFields_Integration verifies the full batch field creation flow
func TestBatchCreateFields_Integration(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping integration test in short mode")
	}

	// 1. Setup
	conn, err := database.GetInstance()
	if err != nil {
		t.Skip("Skipping integration test: failed to connect to DB: " + err.Error())
	}
	db := conn.DB()

	schemaRepo := persistence.NewSchemaRepository(db, nil)
	metaRepo := persistence.NewMetadataRepository(db)

	sm := NewSchemaManager(schemaRepo)
	eventBus := events.NewEventBus()
	ms := NewMetadataService(metaRepo, sm, eventBus)

	tableName := fmt.Sprintf("test_batch_svc_%d", time.Now().UnixNano())

	// Cleanup
	defer func() {
		_ = sm.DropTable(tableName)
	}()

	// 2. Create Object
	def := schema.TableDefinition{
		TableName:   tableName,
		TableType:   string(constants.TableTypeCustomObject),
		Category:    "test",
		Description: "Test Object for Batch Service",
		Columns: []schema.ColumnDefinition{
			{Name: constants.FieldID, Type: "VARCHAR(36)", Nullable: false, PrimaryKey: true, LogicalType: "ID"},
			{Name: constants.FieldName, Type: "VARCHAR(255)", Nullable: false, LogicalType: string(constants.FieldTypeString)},
		},
	}
	desc := "Test Object for Batch Service"
	objMeta := &models.ObjectMetadata{
		APIName:     tableName,
		Label:       "Test Batch Service",
		PluralLabel: "Test Batch Services",
		Description: &desc,
		IsCustom:    true,
	}
	err = sm.CreateTableWithStrictMetadata(context.Background(), def, objMeta)
	require.NoError(t, err, "Failed to create test object")

	// 3. Prepare Batch Fields
	fields := []models.FieldMetadata{
		{
			APIName:  "SvcCol1",
			Label:    "Service Col 1",
			Type:     constants.FieldTypeString,
			Required: false,
		},
		{
			APIName:      "SvcCol2",
			Label:        "Service Col 2",
			Type:         constants.FieldTypeDouble,
			Required:     false,
			DefaultValue: stringPtr("99.9"),
		},
	}

	// 4. Execute BatchCreateFields
	validFields, err := ms.BatchCreateFields(context.Background(), tableName, fields)
	assert.NoError(t, err, "BatchCreateFields should succeed")
	assert.Len(t, validFields, 2, "Should return 2 valid fields")

	// 5. Verify Metadata via Repo
	// We use GetSchemaByAPIName to verify complete metadata retrieval
	obj, err := metaRepo.GetSchemaByAPIName(context.Background(), tableName)
	require.NoError(t, err)

	foundCount := 0
	for _, f := range obj.Fields {
		if f.APIName == "SvcCol1" {
			assert.Equal(t, constants.FieldTypeString, f.Type)
			foundCount++
		}
		if f.APIName == "SvcCol2" {
			assert.Equal(t, constants.FieldTypeDouble, f.Type)
			assert.Equal(t, "99.9", *f.DefaultValue)
			foundCount++
		}
	}
	assert.Equal(t, 2, foundCount, "Both new fields should be found in metadata")

	// 6. Verify Physical Columns via SQL
	var count int
	err = db.QueryRow("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name=? AND column_name=?", tableName, "SvcCol1").Scan(&count)
	assert.NoError(t, err)
	assert.Equal(t, 1, count, "Physical column SvcCol1 must exist")
}

func stringPtr(s string) *string {
	return &s
}