package bootstrapimpl

import (
	"context"
	"errors"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/kernel/pkg/formula"
	"github.com/nexuscrm/shared/pkg/constants"
)

// frozenModeQueryPattern is the regex the SQL emitted by validateFrozenFormulasScoped
// must match. The `FormulaMode = 'frozen'` clause is load-bearing: pkg/formula.
// Engine is the runtime evaluator for frozen-mode formulas only; active-mode
// formulas compile to TiDB view SQL via formula_dsl_compiler and must not be
// validated through this engine (would produce false-positive InvalidFormula
// errors on projection-only constructs and relationship paths).
const frozenModeQueryPattern = "FormulaMode`\\s*=\\s*'frozen'"

// metadataOnlyComputedFieldQueryPattern pins the field-column readiness query
// exclusion for active computed fields. Active formulas and projections are
// virtual metadata backed by formula views, not physical table columns.
const metadataOnlyComputedFieldQueryPattern = "(?s)FormulaMode.*active.*Formula.*ProjectionConfig"

// TestAssertFormulaValidityRespectsExplicitDisable asserts that operators can
// opt out via DISABLE_FORMULA_VALIDATION=true. The query must NOT be issued.
func TestAssertFormulaValidityRespectsExplicitDisable(t *testing.T) {
	t.Setenv("DISABLE_FORMULA_VALIDATION", "true")

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(frozenModeQueryPattern).
		WillReturnRows(sqlmock.NewRows([]string{"obj", "field", "expr"}).
			AddRow("Account", "BadFormula", "(((("))

	result := &AssertionResult{}
	assertFormulaValidity(db, result)

	if len(result.Violations) != 0 {
		t.Fatalf("violations = %d, want 0 (check must be skipped under DISABLE_FORMULA_VALIDATION=true)", len(result.Violations))
	}
	if err := mock.ExpectationsWereMet(); err == nil {
		t.Fatal("ExpectationsWereMet returned nil, want unconsumed expectation (query must NOT be issued when DISABLE_FORMULA_VALIDATION=true)")
	}
}

// TestAssertFormulaValidityAcceptsFrozenRecordRelationFormula is the
// public-core greenfield-readiness regression for Codex round-2 finding on
// PR #268: Engine.Validate against empty env rejects every frozen formula
// that references a sibling field, $Record relationship path, or engine
// builtin. The fix (ValidateSyntax with expr.AllowUndefinedVariables) must
// tolerate these forms.
func TestAssertFormulaValidityAcceptsFrozenRecordRelationFormula(t *testing.T) {
	t.Setenv("DISABLE_FORMULA_VALIDATION", "")

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(frozenModeQueryPattern).
		WillReturnRows(sqlmock.NewRows([]string{"obj", "field", "expr"}).
			AddRow("Opportunity", "AccountNameCopy__c", `$Record.Account.Name`))

	result := &AssertionResult{}
	assertFormulaValidity(db, result)

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
	if len(result.Violations) != 0 {
		t.Fatalf("violations = %d, want 0 (frozen formula referencing a runtime-resolved relation must not be flagged); got %+v", len(result.Violations), result.Violations)
	}
}

// TestAssertFormulaValidityIgnoresActiveModeFormulas pins the frozen-mode SQL
// filter that prevents the validator from checking active-mode formulas. With
// the filter, the mock query will not be matched if the SQL lacks the
// FormulaMode predicate.
func TestAssertFormulaValidityIgnoresActiveModeFormulas(t *testing.T) {
	t.Setenv("DISABLE_FORMULA_VALIDATION", "")

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	// Mock returns zero rows — simulates that active-mode formulas are
	// excluded by the FormulaMode='frozen' predicate before the scanner runs.
	mock.ExpectQuery(frozenModeQueryPattern).
		WillReturnRows(sqlmock.NewRows([]string{"obj", "field", "expr"}))

	result := &AssertionResult{}
	assertFormulaValidity(db, result)

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v (the frozen-mode filter is missing from the SQL)", err)
	}
	if len(result.Violations) != 0 {
		t.Fatalf("violations = %d, want 0 on empty frozen-mode result set", len(result.Violations))
	}
}

func TestAssertObjectTableMappingUsesReadSourcePhysicalTable(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("SELECT `ApiName`, `ReadSource` FROM `_System_Object`").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "ReadSource"}).
			AddRow(constants.ObjectRecordType, `{"kind": "physical_table", "physicalTable": "_System_RecordType"}`))
	mock.ExpectQuery("information_schema\\.tables").
		WillReturnRows(sqlmock.NewRows([]string{"table_name"}).AddRow(constants.TableRecordType))

	result := &AssertionResult{}
	assertObjectTableMapping(db, result)

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
	if len(result.Violations) != 0 {
		t.Fatalf("violations = %d, want 0 for readSource-backed object; got %+v", len(result.Violations), result.Violations)
	}
}

func TestAssertFieldColumnMappingUsesReadSourcePhysicalTable(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("(?s)SELECT o\\.`ApiName`, o\\.`ReadSource`, f\\.`ApiName`, f\\.`PhysicalName`").
		WillReturnRows(sqlmock.NewRows([]string{"object", "readSource", "field", "physical"}).
			AddRow(constants.ObjectRecordType, `{"kind": "physical_table", "physicalTable": "_System_RecordType"}`, "DeveloperName", nil))
	mock.ExpectQuery("information_schema\\.columns").
		WillReturnRows(sqlmock.NewRows([]string{"table_name", "column_name"}).
			AddRow(constants.TableRecordType, "DeveloperName"))

	result := &AssertionResult{}
	assertFieldColumnMapping(db, result)

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
	if len(result.Violations) != 0 {
		t.Fatalf("violations = %d, want 0 for readSource-backed fields; got %+v", len(result.Violations), result.Violations)
	}
}

func TestAssertFieldColumnMappingIgnoresMetadataOnlyActiveComputedFields(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(metadataOnlyComputedFieldQueryPattern).
		WillReturnRows(sqlmock.NewRows([]string{"object", "readSource", "field", "physical"}))
	mock.ExpectQuery("information_schema\\.columns").
		WillReturnRows(sqlmock.NewRows([]string{"table_name", "column_name"}))

	result := &AssertionResult{}
	assertFieldColumnMapping(db, result)

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v (active computed field exclusion is missing from the SQL)", err)
	}
	if len(result.Violations) != 0 {
		t.Fatalf("violations = %d, want 0 on empty physical-field result set", len(result.Violations))
	}
}

func TestAssertLookupReferenceTargetsPopulatedFlagsEmptyReferenceTo(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("(?s)ReferenceTo.*IS NULL").
		WithArgs(constants.FieldTypeReference, constants.FieldTypeMasterDetail).
		WillReturnRows(sqlmock.NewRows([]string{"object", "field", "type", "referenceTo"}).
			AddRow(constants.TableUser, "ProfileId", string(constants.FieldTypeReference), nil).
			AddRow("AnyObject", constants.FieldOwnerID, string(constants.FieldTypeReference), "[]"))

	result := &AssertionResult{}
	assertLookupReferenceTargetsPopulated(db, result)

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
	if len(result.Violations) != 2 {
		t.Fatalf("violations = %d, want 2; got %+v", len(result.Violations), result.Violations)
	}
	for _, violation := range result.Violations {
		if violation.Category != "MissingReferenceTo" {
			t.Fatalf("category = %q, want MissingReferenceTo", violation.Category)
		}
		if violation.Severity != constants.SeverityError {
			t.Fatalf("severity = %q, want %q", violation.Severity, constants.SeverityError)
		}
	}
	if !strings.Contains(result.Violations[0].Description, "ProfileId") {
		t.Fatalf("description = %q, want offending field name", result.Violations[0].Description)
	}
}

func TestAssertLookupReferenceTargetsPopulatedScopesToActiveObjects(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("(?s)JOIN `_System_Object` o.*COALESCE\\(f\\.`IsDeleted`, 0\\)\\s*=\\s*0.*COALESCE\\(o\\.`IsDeleted`, 0\\)\\s*=\\s*0.*TRIM\\(f\\.`ReferenceTo`\\)").
		WithArgs(constants.FieldTypeReference, constants.FieldTypeMasterDetail).
		WillReturnRows(sqlmock.NewRows([]string{"object", "field", "type", "referenceTo"}))

	result := &AssertionResult{}
	assertLookupReferenceTargetsPopulated(db, result)

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
	if len(result.Violations) != 0 {
		t.Fatalf("violations = %d, want 0 for rows excluded by active-object scope; got %+v", len(result.Violations), result.Violations)
	}
}

func TestAssertLookupReferenceTargetsPopulatedSkipsRowsOutsideBootstrapOwnership(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("(?s)ReferenceTo.*IS NULL").
		WithArgs(constants.FieldTypeReference, constants.FieldTypeMasterDetail).
		WillReturnRows(sqlmock.NewRows([]string{"object", "field", "type", "referenceTo"}).
			AddRow("Case", "ProfileId", string(constants.FieldTypeReference), nil).
			AddRow("Issue", "AccountId", string(constants.FieldTypeReference), "[]"))

	result := &AssertionResult{}
	assertLookupReferenceTargetsPopulated(db, result)

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
	if len(result.Violations) != 0 {
		t.Fatalf("violations = %d, want 0 for rows outside bootstrap ownership; got %+v", len(result.Violations), result.Violations)
	}
}

// TestAssertFormulaValidityFailsClosedOnQueryError is the global-readiness
// counterpart of the pack-scoped fail-closed tests. assertFormulaValidity
// surfaces infra failures as a SeverityError FormulaValidationInfraFailure
// violation so strict-mode RunAssertions fails closed instead of treating
// zero-violations as proof of pack health.
func TestAssertFormulaValidityFailsClosedOnQueryError(t *testing.T) {
	t.Setenv("DISABLE_FORMULA_VALIDATION", "")

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	queryErr := errors.New("simulated _System_Field query failure")
	mock.ExpectQuery(frozenModeQueryPattern).WillReturnError(queryErr)

	result := &AssertionResult{}
	assertFormulaValidity(db, result)

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
	if len(result.Violations) == 0 {
		t.Fatal("violations = 0 on query failure; assertFormulaValidity is fail-open (regression of round-3 P1)")
	}
	var found bool
	for _, v := range result.Violations {
		if v.Category == "FormulaValidationInfraFailure" && v.Severity == constants.SeverityError {
			if !strings.Contains(v.Description, queryErr.Error()) {
				t.Fatalf("FormulaValidationInfraFailure description = %q, want to contain %q", v.Description, queryErr.Error())
			}
			found = true
			break
		}
	}
	if !found {
		t.Fatalf("expected SeverityError 'FormulaValidationInfraFailure' violation; got %+v", result.Violations)
	}
}

func TestValidateFrozenFormulasScopedRejectsRuntimeUnsupportedFunction(t *testing.T) {
	t.Setenv("DISABLE_FORMULA_VALIDATION", "")

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(frozenModeQueryPattern).
		WillReturnRows(sqlmock.NewRows([]string{"obj", "field", "expr"}).
			AddRow("Issue", "RuntimeUnsupportedFrozen", `REGEX(Subject, ".*")`))

	verdict, violations, err := validateFrozenFormulasScoped(context.Background(), db)
	if err != nil {
		t.Fatalf("validateFrozenFormulasScoped returned err = %v on syntax-only violation; want nil", err)
	}

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
	if len(violations) != 1 {
		t.Fatalf("violations = %d, want 1", len(violations))
	}
	if violations[0].Category != "InvalidFormula" {
		t.Fatalf("category = %q, want %q", violations[0].Category, "InvalidFormula")
	}
	if !strings.Contains(violations[0].Description, "RuntimeUnsupportedFrozen") {
		t.Fatalf("description does not contain offending field name; got: %v", violations[0].Description)
	}
	if verdict != formula.Failed {
		t.Fatalf("verdict = %v, want Failed", verdict)
	}
}

func TestValidateFrozenFormulasScopedFailsClosedOnQueryFailure(t *testing.T) {
	t.Setenv("DISABLE_FORMULA_VALIDATION", "")

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	queryErr := errors.New("simulated _System_Field query failure")
	mock.ExpectQuery(frozenModeQueryPattern).WillReturnError(queryErr)

	_, _, err = validateFrozenFormulasScoped(context.Background(), db)
	if err == nil {
		t.Fatal("validateFrozenFormulasScoped returned nil err on query failure")
	}
	if !errors.Is(err, queryErr) {
		t.Fatalf("err = %v, want chain wrapping the underlying query error (%v)", err, queryErr)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestValidateFrozenFormulasScopedFailsClosedOnContextCancellation(t *testing.T) {
	t.Setenv("DISABLE_FORMULA_VALIDATION", "")

	db, _, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	ctx, cancel := context.WithCancel(context.Background())
	cancel() // cancel before invoking the validator

	_, violations, err := validateFrozenFormulasScoped(ctx, db)
	if err == nil {
		t.Fatal("validateFrozenFormulasScoped returned nil err under cancelled ctx")
	}
	if !errors.Is(err, context.Canceled) {
		t.Fatalf("err = %v, want chain wrapping context.Canceled", err)
	}
	if len(violations) != 0 {
		t.Fatalf("violations = %d, want 0 under cancelled ctx; got %+v", len(violations), violations)
	}
}