package bootstrap

import (
	"regexp"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/stretchr/testify/assert"

	"github.com/nexuscrm/backend/internal/infrastructure/database"
)

// expectedOverride mirrors the fieldOverride struct inside
// reconcileDiscoveredFieldOverrides, with the `guarded` flag tracking whether
// the UPDATE is expected to include the `f.IsCustomized = 0 OR IS NULL` guard.
type expectedOverride struct {
	object    string
	field     string
	setClause string
	guarded   bool
}

// allOverrides returns the exact sequence of overrides reconcileDiscoveredFieldOverrides
// issues, in order. Keep this aligned with the slice in discover_tables.go.
func allOverrides() []expectedOverride {
	return []expectedOverride{
		// Required=1 authority overrides (no IsCustomized guard):
		{"Campaign", "Name", "f.Required = 1, f.Label = 'Campaign Name'", false},
		{"Campaign", "OwnerId", "f.Label = 'Campaign Owner'", false},
		{"CampaignMember", "Status", "f.Validator = 'runtime_scoped_picklist', f.ControllingField = 'CampaignId', f.ReferenceTo = '[\"CampaignMemberStatus\"]'", false},
		{"Lead", "LastName", "f.Required = 1", false},
		{"Lead", "Name", "f.Required = 1", false},
		{"Lead", "Company", "f.Required = 1", false},
		{"Lead", "Status", "f.Required = 1", false},
		{"Contract", "AccountId", "f.Required = 1", false},
		{"Contract", "Status", "f.Required = 1", false},
		{"Contract", "StartDate", "f.Required = 1", false},
		{"Contract", "ContractTerm", "f.Required = 1", false},
		{"Quote", "Name", "f.Required = 1", false},
		{"Quote", "Status", "f.Required = 1", false},
		{"Order", "AccountId", "f.Required = 1", false},
		{"Order", "Status", "f.Required = 1", false},
		{"Order", "EffectiveDate", "f.Required = 1", false},
		// Required=0 inference-correction overrides (guarded on IsCustomized):
		{"Campaign", "AmountAllOpportunities", "f.Required = 0", true},
		{"Campaign", "AmountWonOpportunities", "f.Required = 0", true},
		{"Campaign", "NumberOfContacts", "f.Required = 0", true},
		{"Campaign", "NumberOfConvertedLeads", "f.Required = 0", true},
		{"Campaign", "NumberOfLeads", "f.Required = 0", true},
		{"Campaign", "NumberOfOpportunities", "f.Required = 0", true},
		{"Campaign", "NumberOfWonOpportunities", "f.Required = 0", true},
		{"Opportunity", "HasOpenActivity", "f.Required = 0", true},
		{"Opportunity", "HasOpportunityLineItem", "f.Required = 0", true},
		{"Opportunity", "IsClosed", "f.Required = 0", true},
		// OpportunityLineItem fields that should not be required (incorrectly inferred from NOT NULL columns):
		{"OpportunityLineItem", "PricebookEntryId", "f.Required = 0", true},
		{"OpportunityLineItem", "Quantity", "f.Required = 0", true},
		{"OpportunityLineItem", "Product2Id", "f.Required = 0", true},
		{"Order", "StatusCode", "f.Required = 0", true},
		{"User", "Password", "f.Required = 0", true},
		{"User", "FailedLoginAttempts", "f.Required = 0", true},
	}
}

func TestReconcileDiscoveredFieldOverrides_DropsOpportunityNetFeesRepair(t *testing.T) {
	for _, ov := range allOverrides() {
		if ov.object == "Opportunity" && ov.field == "NetFees__c" {
			t.Fatal("Opportunity.NetFees__c must not remain in generic bootstrap overrides")
		}
	}
}

// queryPatternFor returns the exact regex pattern sqlmock should match for a
// single expected override.
func queryPatternFor(exp expectedOverride) string {
	base := "UPDATE `_System_Field` f JOIN `_System_Object` o ON o.Id = f.ObjectId SET " +
		exp.setClause + " WHERE o.ApiName = ? AND f.ApiName = ?"
	if exp.guarded {
		base += " AND (f.IsCustomized = 0 OR f.IsCustomized IS NULL)"
	}
	return regexp.QuoteMeta(base)
}

func TestReconcileDiscoveredFieldOverrides_CompleteSet(t *testing.T) {
	db, mock, err := sqlmock.New()
	assert.NoError(t, err)
	defer db.Close()

	for _, exp := range allOverrides() {
		mock.ExpectExec(queryPatternFor(exp)).
			WithArgs(exp.object, exp.field).
			WillReturnResult(sqlmock.NewResult(1, 1))
	}

	reconcileDiscoveredFieldOverrides(db)

	assert.NoError(t, mock.ExpectationsWereMet())
}

// TestReconcileDiscoveredFieldOverrides_ContractQuoteOrderPresent is a focused
// regression guard for Issue #507: Contract/Quote/Order Required=1 entries must
// be present in the override list. Asserts presence by object/field rather than
// re-running the full SQL mock (which is already covered by CompleteSet).
func TestReconcileDiscoveredFieldOverrides_ContractQuoteOrderPresent(t *testing.T) {
	expectedPresent := []struct{ object, field string }{
		{"Contract", "AccountId"}, {"Contract", "Status"},
		{"Contract", "StartDate"}, {"Contract", "ContractTerm"},
		{"Quote", "Name"}, {"Quote", "Status"},
		{"Order", "AccountId"}, {"Order", "Status"}, {"Order", "EffectiveDate"},
	}
	all := allOverrides()
	for _, want := range expectedPresent {
		found := false
		for _, got := range all {
			if got.object == want.object && got.field == want.field &&
				got.setClause == "f.Required = 1" && !got.guarded {
				found = true
				break
			}
		}
		assert.Truef(t, found,
			"Issue #507 regression: %s.%s must be present as an unguarded Required=1 override",
			want.object, want.field)
	}
}

func TestReconcileDiscoveredFieldOverrides_HandlesNoRowsAffected(t *testing.T) {
	// All queries fire even when no rows match (missing field / already-correct
	// value). Each UPDATE returns 0 rows affected; the function must not
	// surface this as an error.
	db, mock, err := sqlmock.New()
	assert.NoError(t, err)
	defer db.Close()

	for _, exp := range allOverrides() {
		mock.ExpectExec(queryPatternFor(exp)).
			WithArgs(exp.object, exp.field).
			WillReturnResult(sqlmock.NewResult(0, 0))
	}

	reconcileDiscoveredFieldOverrides(db)

	assert.NoError(t, mock.ExpectationsWereMet())
}

// TestDiscoverAndRegisterPhysicalTables_ReconcileRunsOnEarlyReturn is the
// regression test for the primary defect fixed by this PR: when no physical
// tables are missing from _System_Object and no registered objects have zero
// fields, the function used to short-circuit past reconcileDiscoveredFieldOverrides.
// With the deferred call, reconcile must still fire on the early-return path.
func TestDiscoverAndRegisterPhysicalTables_ReconcileRunsOnEarlyReturn(t *testing.T) {
	db, mock, err := sqlmock.New()
	assert.NoError(t, err)
	defer db.Close()

	// Step 1: SHOW TABLES returns only system tables — all filtered out by
	// the `_system_` prefix check, so `missing` is empty.
	mock.ExpectQuery("SHOW TABLES").
		WillReturnRows(sqlmock.NewRows([]string{"Tables_in_nexuscrm"}).
			AddRow("_System_Object").
			AddRow("_System_Field"))

	// Step 2: SELECT ApiName FROM _System_Object returns nothing registered.
	mock.ExpectQuery("SELECT .* FROM `_System_Object`").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName"}))

	// Step 3: LEFT JOIN aggregate for zero-field objects returns nothing.
	mock.ExpectQuery("SELECT .* FROM `_System_Object` o LEFT JOIN `_System_Field` f .* HAVING COUNT").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName"}))

	// Step 4: the deferred reconcile must fire all 28 UPDATEs even though the
	// function takes the early-return path at `len(missing) == 0 && len(needFields) == 0`.
	for _, exp := range allOverrides() {
		mock.ExpectExec(queryPatternFor(exp)).
			WithArgs(exp.object, exp.field).
			WillReturnResult(sqlmock.NewResult(0, 0))
	}

	conn := database.NewConnectionFromDB(db)
	err = DiscoverAndRegisterPhysicalTables(conn)
	assert.NoError(t, err)

	assert.NoError(t, mock.ExpectationsWereMet(),
		"expected 3 reads + 28 UPDATEs (reconcile via defer on early return)")
}

// TestDiscoverAndRegisterPhysicalTables_ReconcileSkippedOnEarlyError confirms
// the `reconcileOK` gate: when SHOW TABLES fails, the deferred reconcile does
// NOT fire (avoids 28 spurious error log lines on an already-unhealthy
// connection).
func TestDiscoverAndRegisterPhysicalTables_ReconcileSkippedOnEarlyError(t *testing.T) {
	db, mock, err := sqlmock.New()
	assert.NoError(t, err)
	defer db.Close()

	// Step 1: SHOW TABLES fails — simulates a severed DB connection.
	mock.ExpectQuery("SHOW TABLES").
		WillReturnError(assert.AnError)

	// No further expectations: if the defer mistakenly fires, sqlmock would
	// report unexpected ExecContext calls and ExpectationsWereMet would fail.

	conn := database.NewConnectionFromDB(db)
	err = DiscoverAndRegisterPhysicalTables(conn)
	assert.Error(t, err, "expected propagated error from failing SHOW TABLES")

	assert.NoError(t, mock.ExpectationsWereMet(),
		"reconcile must be gated off when the initial DB read fails")
}

// TestReconcileDiscoveredFieldOverrides_GuardedMatchesFlag confirms the
// `f.IsCustomized = 0 OR IS NULL` suffix appears iff the override sets
// `guarded: true`. Keys off the flag itself, not on setClause string matching,
// so future compound setClauses (e.g. "f.Required = 0, f.Label = '...'") that
// should still be guarded don't silently slip through.
func TestReconcileDiscoveredFieldOverrides_GuardedMatchesFlag(t *testing.T) {
	guardRe := regexp.MustCompile(`IsCustomized`)
	for _, exp := range allOverrides() {
		pattern := queryPatternFor(exp)
		hasGuard := guardRe.MatchString(pattern)
		assert.Equalf(t, exp.guarded, hasGuard,
			"%s.%s: expected guarded=%v, got guard-in-query=%v (setClause=%q)",
			exp.object, exp.field, exp.guarded, hasGuard, exp.setClause)
	}
}