//go:build integration

package analytics_test

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

	"github.com/nexuscrm/backend/internal/features/analytics"
	"github.com/nexuscrm/backend/internal/features/events"
	"github.com/nexuscrm/backend/internal/features/metadata"
	"github.com/nexuscrm/backend/internal/features/permissions"
	"github.com/nexuscrm/backend/internal/features/records"

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

func TestRollupSummary_Sum(t *testing.T) {
	// Setup Dependencies
	conn, err := database.GetInstance()
	if err != nil {
		t.Skip("Skipping integration test: failed to connect to DB: " + err.Error())
	}

	db := conn.DB()

	// Initialize Services
	eventBus := events.NewEventBus()
	txManager := persistence.NewTransactionManager(conn)

	schemaRepo := persistence.NewSchemaRepository(db, nil)
	schemaMgr := metadata.NewSchemaManager(schemaRepo)

	metadataRepo := persistence.NewMetadataRepository(db)
	metadataSvc := metadata.NewMetadataService(metadataRepo, schemaMgr, eventBus)

	userRepo := persistence.NewUserRepository(db)
	permRepo := persistence.NewPermissionRepository(db)
	permSvc := permissions.NewPermissionService(permRepo, metadataSvc, userRepo)

	recordRepo := persistence.NewRecordRepository(db)
	rollupRepo := persistence.NewRollupRepository(db)
	rollupSvc := analytics.NewRollupService(rollupRepo, metadataSvc, txManager, logging.New(context.Background()))
	outboxRepo := persistence.NewOutboxRepository(db)
	outboxSvc := events.NewOutboxService(outboxRepo, eventBus, txManager)
	validationSvc := metadata.NewValidationService(formula.NewEngine())

	var ps *records.PersistenceService

	ctx := context.Background()
	adminUser := &models.UserSession{
		ID:        "test-admin",
		Name:      "Test Admin",
		ProfileID: constants.ProfileSystemAdmin,
	}

	// 1. Define Parent Object (Invoice)
	parentName := fmt.Sprintf("Invoice_%d", time.Now().UnixNano())
	parentCols := schemaMgr.GetStandardSystemColumns()
	parentCols = append(parentCols,
		schema.ColumnDefinition{Name: "TotalAmount", Type: "DECIMAL(18,2)", Nullable: true},
		schema.ColumnDefinition{Name: "Name", Type: "VARCHAR(255)", Nullable: false},
	)

	parentDef := schema.TableDefinition{
		TableName:   parentName,
		Description: "Master Invoice",
		TableType:   string(constants.TableTypeCustomObject),
		Columns:     parentCols,
	}
	parentMeta := &models.ObjectMetadata{
		APIName:      parentName,
		Label:        "Master Invoice",
		PluralLabel:  "Master Invoices",
		Description:  &parentDef.Description,
		IsCustom:     true,
		SharingModel: models.SharingModel(constants.SharingModelPublicReadWrite),
	}
	require.NoError(t, schemaMgr.CreateTableWithStrictMetadata(context.Background(), parentDef, parentMeta))
	defer func() { _ = schemaMgr.DropTable(parentName) }()

	// 2. Define Child Object (LineItem)
	childName := fmt.Sprintf("LineItem_%d", time.Now().UnixNano())
	childCols := schemaMgr.GetStandardSystemColumns()
	childCols = append(childCols,
		schema.ColumnDefinition{Name: "Amount", Type: "DECIMAL(18,2)", Nullable: false},
		schema.ColumnDefinition{Name: "InvoiceId", Type: "VARCHAR(255)", Nullable: true},
		schema.ColumnDefinition{Name: "Name", Type: "VARCHAR(255)", Nullable: false},
	)

	childDef := schema.TableDefinition{
		TableName:   childName,
		Description: "Invoice Line Item",
		TableType:   "custom_object",
		Columns:     childCols,
	}
	childMeta := &models.ObjectMetadata{
		APIName:      childName,
		Label:        "Invoice Line Item",
		PluralLabel:  "Invoice Line Items",
		Description:  &childDef.Description,
		IsCustom:     true,
		SharingModel: models.SharingModel(constants.SharingModelPublicReadWrite),
	}
	require.NoError(t, schemaMgr.CreateTableWithStrictMetadata(context.Background(), childDef, childMeta))
	defer func() { _ = schemaMgr.DropTable(childName) }()

	// 3. Register Metadata & Rollup

	// 3. Metadata is already registered via CreateTableWithStrictMetadata
	// We just need to update descriptions if needed, but strict create did it.
	// require.NoError(t, schemaMgr.BatchSaveObjectMetadata([]*models.ObjectMetadata{
	// 	{APIName: parentName, Label: "Invoice", Description: &desc},
	// 	{APIName: childName, Label: "Line Item", Description: &desc},
	// }, nil))

	// Register Fields for Parent
	rollupConfig := &models.RollupConfig{
		SummaryObject:     childName,
		SummaryField:      "Amount",
		RelationshipField: "InvoiceId",
		CalcType:          "SUM",
	}

	var parentObjID string
	err = db.QueryRow(fmt.Sprintf("SELECT %s FROM %s WHERE %s = ?", constants.ColID, constants.TableObject, constants.FieldSysObject_APIName), parentName).Scan(&parentObjID)
	require.NoError(t, err)

	var childObjID string
	err = db.QueryRow(fmt.Sprintf("SELECT %s FROM %s WHERE %s = ?", constants.ColID, constants.TableObject, constants.FieldSysObject_APIName), childName).Scan(&childObjID)
	require.NoError(t, err)

	parentFields := []models.FieldMetadata{
		{APIName: "TotalAmount", Label: "Total Amount", Type: constants.FieldTypeCurrency, RollupConfig: rollupConfig},
	}

	// Convert to schema.FieldWithContext
	parentBatch := make([]schema.FieldWithContext, len(parentFields))
	for i, f := range parentFields {
		f.IsSystem = false
		parentBatch[i] = schema.FieldWithContext{
			ObjectID: parentObjID,
			FieldID:  schema.GenerateFieldID(parentName, f.APIName),
			Field:    &f,
		}
	}

	// Execute in transaction
	tx, _ := db.Begin()
	require.NoError(t, schemaMgr.BatchSaveFieldMetadata(context.Background(), parentBatch, tx))

	// Register Fields for Child
	childFields := []models.FieldMetadata{
		{APIName: "Amount", Label: "Amount", Type: constants.FieldTypeCurrency},
		{APIName: "InvoiceId", Label: "Invoice", Type: constants.FieldTypeReference, ReferenceTo: []string{parentName}},
	}
	childBatch := make([]schema.FieldWithContext, len(childFields))
	for i, f := range childFields {
		f.IsSystem = false
		childBatch[i] = schema.FieldWithContext{
			ObjectID: childObjID,
			FieldID:  schema.GenerateFieldID(childName, f.APIName),
			Field:    &f,
		}
	}
	require.NoError(t, schemaMgr.BatchSaveFieldMetadata(context.Background(), childBatch, tx))
	require.NoError(t, tx.Commit())

	// 4. Create Data
	// refresh metadata service to pick up new schemas
	metadataRepo = persistence.NewMetadataRepository(db)
	metadataSvc = metadata.NewMetadataService(metadataRepo, schemaMgr, eventBus)

	permRepo = persistence.NewPermissionRepository(db)
	permSvc = permissions.NewPermissionService(permRepo, metadataSvc, userRepo)
	ps = records.NewPersistenceService(recordRepo, rollupSvc, metadataSvc, permSvc, eventBus, validationSvc, txManager, outboxSvc, logging.New(context.Background()), nil, nil, nil, nil)

	invoiceData := models.SObject{"TotalAmount": 0, "Name": "Invoice 001"}
	invoice, err := ps.Insert(ctx, parentName, invoiceData, adminUser)
	require.NoError(t, err)
	invoiceID := invoice[constants.FieldID].(string)

	checkInvoice := func(expected float64) {
		// Wait for async? No, rollup is synchronous in current implementation if transactional.
		// If we use EventBus async listener, we need to wait.
		// But ps.rollup.ProcessRollups is called synchronously in hooks.
		// However, does ps.Update commit? Yes.

		var val float64
		// We query TotalAmount
		err := db.QueryRow(fmt.Sprintf("SELECT TotalAmount FROM %s WHERE %s = ?", parentName, constants.ColID), invoiceID).Scan(&val)
		require.NoError(t, err)
		assert.Equal(t, expected, val)
	}
	checkInvoice(0)

	// Create Child Line Item 1 ($100)
	item1 := models.SObject{"Amount": 100, "InvoiceId": invoiceID, "Name": "Item 1"}
	_, err = ps.Insert(ctx, childName, item1, adminUser)
	require.NoError(t, err)
	checkInvoice(100)

	// Create Child Line Item 2 ($50)
	item2 := models.SObject{"Amount": 50, "InvoiceId": invoiceID, "Name": "Item 2"}
	_, err = ps.Insert(ctx, childName, item2, adminUser)
	require.NoError(t, err)
	checkInvoice(150)

	// Update Item 1 ($100 -> $200) - need ID
	item3, err := ps.Insert(ctx, childName, models.SObject{"Amount": 25, "InvoiceId": invoiceID, "Name": "Item 3"}, adminUser)
	require.NoError(t, err)
	item3ID := item3[constants.FieldID].(string)
	checkInvoice(175)

	// Update Item 3 ($25 -> $125)
	err = ps.Update(ctx, childName, item3ID, models.SObject{"Amount": 125}, adminUser)
	require.NoError(t, err)
	checkInvoice(275)

	// Delete Item 3
	err = ps.Delete(ctx, childName, item3ID, adminUser)
	require.NoError(t, err)
	checkInvoice(150)

	// ================= REPARENTING TEST =================
	// Create a Second Invoice
	invoice2Data := models.SObject{"TotalAmount": 0, "Name": "Invoice 002"}
	invoice2, err := ps.Insert(ctx, parentName, invoice2Data, adminUser)
	require.NoError(t, err)
	invoice2ID := invoice2[constants.FieldID].(string)

	checkInvoice2 := func(expected float64) {
		var val float64
		err := db.QueryRow(fmt.Sprintf("SELECT TotalAmount FROM %s WHERE %s = ?", parentName, constants.ColID), invoice2ID).Scan(&val)
		require.NoError(t, err)
		assert.Equal(t, expected, val)
	}
	checkInvoice2(0)

	// Move Item 2 ($50) from Invoice 1 to Invoice 2
	// For this test, we reuse Item 2 which was created earlier.
	// We need its ID. Let's find it or just create a new Item 4 for clarity.
	// Decision: Create Item 4 to avoid state dependencies on previous steps.
	item4, err := ps.Insert(ctx, childName, models.SObject{"Amount": 75, "InvoiceId": invoiceID, "Name": "Item 4"}, adminUser)
	require.NoError(t, err)
	item4ID := item4[constants.FieldID].(string)

	// Invoice 1 should be 150 + 75 = 225
	checkInvoice(225) // FAILS IF REPARENTING BROKEN? No, this is insert.

	// Now Reparent Item 4 to Invoice 2
	err = ps.Update(ctx, childName, item4ID, models.SObject{"InvoiceId": invoice2ID}, adminUser)
	require.NoError(t, err)

	// Verify Invoice 2 (Should be 75) - This works because it's the "new" parent
	checkInvoice2(75)

	// Verify Invoice 1 (Should be 150) - This FAILS if old parent isn't recalculated
	checkInvoice(150)
}