//go:build integration

package persistence

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

	"github.com/nexuscrm/backend/internal/domain/schema"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

func TestMetadataLifecycle_Integration(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping integration test in short mode")
	}

	// 1. Setup
	schemaRepo := setupTestDB(t) // Uses common setup from init_test.go
	db := schemaRepo.db
	ctx := context.Background()

	// Unique Object Name for this test run
	tableName := fmt.Sprintf("test_cycle_%d", time.Now().UnixNano())

	// Cleanup ensures test isolation even if it fails mid-way
	defer func() {
		_ = schemaRepo.DropTable(tableName)
	}()

	// 2. Create Object (Table + Metadata)
	t.Logf("Creating Object: %s", tableName)

	// Define base columns (ID is auto-added by schema definition but we explicit here for clarity if needed,
	// typically the Service layer handles default cols. Here we test the Persistence generic capability)
	// We'll mimic a minimal definition.

	cols := []schema.ColumnDefinition{
		{Name: "id", Type: "VARCHAR(255)", Nullable: false, Unique: true},
		{Name: "name", Type: "VARCHAR(255)", Label: "Name"},
	}

	def := schema.TableDefinition{
		TableName: tableName,
		Columns:   cols,
		TableType: "Custom",
	}

	desc := "Integration Test Object"
	objMeta := &models.ObjectMetadata{
		APIName:     tableName,
		Label:       "Test Cycle Object",
		Description: &desc,
		IsCustom:    true,
	}

	err := schemaRepo.CreateTableWithStrictMetadata(ctx, def, objMeta)
	require.NoError(t, err, "Table creation failed")

	// 3. Verify Creation
	// Check Physical Table (Must check PHYSICAL name)
	exists, err := schemaRepo.checkColumnExists(tableName, constants.ColID)
	require.NoError(t, err)
	assert.True(t, exists, "Physical table/column should exist")

	// Check Metadata
	repo := NewMetadataRepository(db)
	actions, err := repo.GetActions(ctx, tableName) // Just checking if metadata queries work
	assert.NoError(t, err)
	assert.Empty(t, actions, "New object should have no actions")

	// 4. Add Field (Schema Evolution)
	newColName := "status_flag"
	t.Logf("Adding Field: %s", newColName)

	newCol := schema.ColumnDefinition{
		Name:     newColName,
		Type:     "VARCHAR(50)",
		Label:    "Status Flag",
		Nullable: true,
	}

	err = schemaRepo.EnsureColumn(tableName, newCol)
	require.NoError(t, err, "Adding column failed")

	// Verify Field Physical
	exists, err = schemaRepo.checkColumnExists(tableName, newColName)
	require.NoError(t, err)
	assert.True(t, exists, "New column should exist physically")

	// 5. Insert Data (Verify Usability)
	t.Log("Inserting Test Data...")
	insertQuery := fmt.Sprintf("INSERT INTO %s (%s, name, %s) VALUES (?, ?, ?)", tableName, constants.ColID, newColName)
	_, err = db.Exec(insertQuery, "rec_1", "Test Record", "Active")
	require.NoError(t, err, "Insert into new table failed")

	// Verify Data
	var statusVal string
	err = db.QueryRow(fmt.Sprintf("SELECT %s FROM %s WHERE %s = ?", newColName, tableName, constants.ColID), "rec_1").Scan(&statusVal)
	require.NoError(t, err)
	assert.Equal(t, "Active", statusVal)

	// 6. Delete Object (Teardown)
	t.Log("Dropping Object...")
	err = schemaRepo.DropTable(tableName)
	require.NoError(t, err, "DropTable failed")

	// Verify Drop
	exists, err = schemaRepo.checkColumnExists(tableName, constants.ColID)
	// checkColumnExists returns false if table doesn't exist (scan error or count 0)
	// Actually implementation returns error if query fails, but count 0 if valid.
	// If table dropped, INFORMATION_SCHEMA returns 0 rows.
	assert.NoError(t, err)
	assert.False(t, exists, "Table should not exist")
}