//go:build integration

package controlplane

import (
	"context"
	"strings"
	"testing"

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// TestOpportunityNetFeesPhysicalColumnRepairAndViewRebuild is the NexusCRM-side
// regression for issue #1076: Opportunity.Net_Fees__c was absent from the
// physical schema, causing v_opportunity rebuild to fail with
// "Unknown column 't.net_fees__c' in 'field list'".
//
// The test proves end-to-end:
//  1. install-pack creates the Net_Fees__c physical column (DECIMAL(65,30))
//  2. A formula view referencing Net_Fees__c compiles and rebuilds successfully
//  3. Dropping the column (simulating prod drift) makes view rebuild fail
//  4. Re-running install-pack repairs the missing column
//  5. View rebuild succeeds again after repair
func TestOpportunityNetFeesPhysicalColumnRepairAndViewRebuild(t *testing.T) {
	ctx := context.Background()
	cfg, cleanup := newTemporaryIntegrationDatabase(t)
	defer cleanup()

	conn := initializeControlPlaneMetadata(t, ctx, cfg)
	defer conn.Close()

	r, err := NewRunner(conn)
	if err != nil {
		t.Fatalf("new runner: %v", err)
	}
	if err := r.RefreshCache(ctx); err != nil {
		t.Fatalf("initial cache refresh: %v", err)
	}
	if err := installSourceBackedCRMPack(t, ctx, r); err != nil {
		t.Fatalf("install CRM pack: %v", err)
	}

	// 1. After fresh install, Net_Fees__c must be a physical column.
	colType := physicalColumnType(t, conn.DB(), "Opportunity", "Net_Fees__c")
	if colType == "" {
		t.Fatal("Opportunity.Net_Fees__c physical column missing after install-pack")
	}
	// currency maps to DECIMAL(65,30).
	if !strings.HasPrefix(colType, "decimal") {
		t.Fatalf("Opportunity.Net_Fees__c physical type = %q, want decimal(65,30)", colType)
	}

	// 2. Create an active formula that references Net_Fees__c so that
	//    v_opportunity DDL must include t.net_fees__c.
	formula := "IF(ISBLANK(Net_Fees__c), 0, Net_Fees__c)"
	mode := "active"
	returnType := constants.FieldTypeDouble
	if err := r.SvcMgr.Metadata().CreateField(ctx, "Opportunity", &models.FieldMetadata{
		APIName:     "NetFeesProof__c",
		Label:       "Net Fees Proof",
		Type:        constants.FieldTypeFormula,
		Createable:  false,
		Updateable:  false,
		Formula:     &formula,
		FormulaMode: &mode,
		ReturnType:  &returnType,
	}); err != nil {
		t.Fatalf("create Opportunity.NetFeesProof__c: %v", err)
	}

	// 3. Formula view preview and rebuild must succeed when the column exists.
	if _, err := r.SvcMgr.FormulaView().PreviewDDL(ctx, "Opportunity"); err != nil {
		t.Fatalf("PreviewDDL before column drop: %v", err)
	}
	if err := r.SvcMgr.FormulaView().RebuildView(ctx, "Opportunity"); err != nil {
		t.Fatalf("RebuildView before column drop: %v", err)
	}

	// 4. Simulate prod drift: drop the physical column while leaving the
	//    formula field metadata intact. This reproduces the exact prod state
	//    described in issue #1076.
	if _, err := conn.DB().ExecContext(ctx, "ALTER TABLE `Opportunity` DROP COLUMN `Net_Fees__c`"); err != nil {
		t.Fatalf("drop Net_Fees__c column: %v", err)
	}

	// 5. With the column gone, view rebuild must fail with an error that
	//    mentions the missing column — confirming the repro.
	rebuildErr := r.SvcMgr.FormulaView().RebuildView(ctx, "Opportunity")
	if rebuildErr == nil {
		t.Fatal("RebuildView after column drop = nil, want error referencing net_fees__c")
	}
	if !strings.Contains(strings.ToLower(rebuildErr.Error()), "net_fees__c") {
		t.Fatalf("RebuildView error after column drop = %v, want error containing 'net_fees__c'", rebuildErr)
	}

	// 6. Re-run install-pack — ReconcileTableColumns must ADD the missing column.
	if err := installSourceBackedCRMPack(t, ctx, r); err != nil {
		t.Fatalf("install CRM pack (repair): %v", err)
	}

	// 7. Column must be back.
	colTypeAfter := physicalColumnType(t, conn.DB(), "Opportunity", "Net_Fees__c")
	if colTypeAfter == "" {
		t.Fatal("Opportunity.Net_Fees__c still missing after install-pack repair")
	}
	if !strings.HasPrefix(colTypeAfter, "decimal") {
		t.Fatalf("Opportunity.Net_Fees__c type after repair = %q, want decimal(65,30)", colTypeAfter)
	}

	// 8. Formula view rebuild must succeed after repair.
	if err := r.SvcMgr.FormulaView().RebuildView(ctx, "Opportunity"); err != nil {
		t.Fatalf("RebuildView after repair: %v", err)
	}
}