package packs

import (
	"context"
	"testing"

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

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

	// _System_Field query returns one field owned by "my-pack" on table "foo__c"
	mock.ExpectQuery(`SELECT o.*ApiName.*f.*ApiName.*_System_Field`).
		WithArgs(constants.FieldOwnerKindPack, constants.FieldOwnerKindPackRetained, "my-pack").
		WillReturnRows(sqlmock.NewRows([]string{"ObjectAPIName", "FieldAPIName", "FormulaMode", "Formula", "ProjectionConfig", "FormulaStorageMode"}).
			AddRow("Foo__c", "Bar__c", "", "", nil, ""))

	// INFORMATION_SCHEMA.COLUMNS: Foo__c table exists but Bar__c column is absent
	mock.ExpectQuery(`SELECT TABLE_NAME.*COLUMN_NAME.*INFORMATION_SCHEMA\.COLUMNS`).
		WillReturnRows(sqlmock.NewRows([]string{
			"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME",
		}).AddRow("foo__c", "other_col__c", "varchar(255)", "YES", nil, nil))

	missing, err := InspectLiveInstalledPackMissingPhysicalColumns(context.Background(), db, "my-pack")
	if err != nil {
		t.Fatalf("InspectLiveInstalledPackMissingPhysicalColumns = %v, want nil error", err)
	}
	if len(missing) != 1 || missing[0] != "Foo__c.Bar__c" {
		t.Fatalf("missing = %v, want [Foo__c.Bar__c]", missing)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	// _System_Field query returns one field owned by "my-pack"
	mock.ExpectQuery(`SELECT o.*ApiName.*f.*ApiName.*_System_Field`).
		WithArgs(constants.FieldOwnerKindPack, constants.FieldOwnerKindPackRetained, "my-pack").
		WillReturnRows(sqlmock.NewRows([]string{"ObjectAPIName", "FieldAPIName", "FormulaMode", "Formula", "ProjectionConfig", "FormulaStorageMode"}).
			AddRow("Foo__c", "Bar__c", "", "", nil, ""))

	// INFORMATION_SCHEMA.COLUMNS: Foo__c.Bar__c IS present
	mock.ExpectQuery(`SELECT TABLE_NAME.*COLUMN_NAME.*INFORMATION_SCHEMA\.COLUMNS`).
		WillReturnRows(sqlmock.NewRows([]string{
			"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME",
		}).AddRow("foo__c", "bar__c", "varchar(255)", "YES", nil, nil))

	missing, err := InspectLiveInstalledPackMissingPhysicalColumns(context.Background(), db, "my-pack")
	if err != nil {
		t.Fatalf("InspectLiveInstalledPackMissingPhysicalColumns = %v, want nil error", err)
	}
	if len(missing) != 0 {
		t.Fatalf("missing = %v, want []", missing)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	// _System_Field query returns no rows for "new-pack" (not yet applied)
	mock.ExpectQuery(`SELECT o.*ApiName.*f.*ApiName.*_System_Field`).
		WithArgs(constants.FieldOwnerKindPack, constants.FieldOwnerKindPackRetained, "new-pack").
		WillReturnRows(sqlmock.NewRows([]string{"ObjectAPIName", "FieldAPIName", "FormulaMode", "Formula", "ProjectionConfig", "FormulaStorageMode"}))

	// LoadPhysicalColumnShapes is NOT called when there are no owned fields.
	missing, err := InspectLiveInstalledPackMissingPhysicalColumns(context.Background(), db, "new-pack")
	if err != nil {
		t.Fatalf("InspectLiveInstalledPackMissingPhysicalColumns = %v, want nil error", err)
	}
	if len(missing) != 0 {
		t.Fatalf("missing = %v, want []", missing)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	// _System_Field query: no rows for "my-pack" (the field belongs to "other-pack")
	// The SQL query filters by pack name, so the mock returns no rows.
	mock.ExpectQuery(`SELECT o.*ApiName.*f.*ApiName.*_System_Field`).
		WithArgs(constants.FieldOwnerKindPack, constants.FieldOwnerKindPackRetained, "my-pack").
		WillReturnRows(sqlmock.NewRows([]string{"ObjectAPIName", "FieldAPIName", "FormulaMode", "Formula", "ProjectionConfig", "FormulaStorageMode"}))

	missing, err := InspectLiveInstalledPackMissingPhysicalColumns(context.Background(), db, "my-pack")
	if err != nil {
		t.Fatalf("InspectLiveInstalledPackMissingPhysicalColumns = %v, want nil error", err)
	}
	if len(missing) != 0 {
		t.Fatalf("missing = %v, want [] (fields from other packs must not be reported)", missing)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	mock.ExpectQuery(`SELECT o.*ApiName.*f.*ApiName.*_System_Field`).
		WithArgs(constants.FieldOwnerKindPack, constants.FieldOwnerKindPackRetained, "my-pack").
		WillReturnRows(sqlmock.NewRows([]string{"ObjectAPIName", "FieldAPIName", "FormulaMode", "Formula", "ProjectionConfig", "FormulaStorageMode"}).
			AddRow("Zzz__c", "Field__c", "", "", nil, "").
			AddRow("Aaa__c", "Field__c", "", "", nil, "").
			AddRow("Foo__c", "Bar__c", "", "", nil, "").
			AddRow("Foo__c", "Present__c", "", "", nil, ""))

	// INFORMATION_SCHEMA: only Foo__c.Present__c is present
	mock.ExpectQuery(`SELECT TABLE_NAME.*COLUMN_NAME.*INFORMATION_SCHEMA\.COLUMNS`).
		WillReturnRows(sqlmock.NewRows([]string{
			"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME",
		}).AddRow("foo__c", "present__c", "varchar(255)", "YES", nil, nil))

	missing, err := InspectLiveInstalledPackMissingPhysicalColumns(context.Background(), db, "my-pack")
	if err != nil {
		t.Fatalf("InspectLiveInstalledPackMissingPhysicalColumns = %v, want nil error", err)
	}
	want := []string{"Aaa__c.Field__c", "Foo__c.Bar__c", "Zzz__c.Field__c"}
	if len(missing) != len(want) {
		t.Fatalf("missing = %v, want %v", missing, want)
	}
	for i, v := range want {
		if missing[i] != v {
			t.Fatalf("missing[%d] = %q, want %q", i, missing[i], v)
		}
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

// TestInspectLiveInstalledPackMissingPhysicalColumnsDoesNotExcludeWhitespaceOnlyFormulaField
// verifies that a field with FormulaMode='active' and a whitespace-only Formula
// IS reported as missing when its physical column is absent.
//
// The active-formula check is performed in Go using strings.TrimSpace, which
// strips all Unicode whitespace (tab, newline, etc.) — not just ASCII spaces.
// SQL TRIM() only strips ASCII space (0x20), so "\t \n" would not be treated as
// empty by TRIM() but IS empty after strings.TrimSpace. This test uses "\t \n"
// to prove the fix covers non-space whitespace that SQL TRIM() would miss.
func TestInspectLiveInstalledPackMissingPhysicalColumnsDoesNotExcludeWhitespaceOnlyFormulaField(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	// SQL returns the field including FormulaMode, Formula (tab+space+newline),
	// and ProjectionConfig. Go filter: TrimSpace("\t \n")="" → not active formula
	// → field is included in the physical-column check.
	mock.ExpectQuery(`SELECT o.*ApiName.*f.*ApiName.*_System_Field`).
		WithArgs(constants.FieldOwnerKindPack, constants.FieldOwnerKindPackRetained, "my-pack").
		WillReturnRows(sqlmock.NewRows([]string{"ObjectAPIName", "FieldAPIName", "FormulaMode", "Formula", "ProjectionConfig", "FormulaStorageMode"}).
			AddRow("Foo__c", "WhiteFormula__c", "active", "\t \n", nil, ""))

	// INFORMATION_SCHEMA: the physical column for Foo__c.WhiteFormula__c is absent.
	mock.ExpectQuery(`SELECT TABLE_NAME.*COLUMN_NAME.*INFORMATION_SCHEMA\.COLUMNS`).
		WillReturnRows(sqlmock.NewRows([]string{
			"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME",
		}))

	missing, err := InspectLiveInstalledPackMissingPhysicalColumns(context.Background(), db, "my-pack")
	if err != nil {
		t.Fatalf("InspectLiveInstalledPackMissingPhysicalColumns = %v, want nil error", err)
	}
	if len(missing) != 1 || missing[0] != "Foo__c.WhiteFormula__c" {
		t.Fatalf("missing = %v, want [Foo__c.WhiteFormula__c]", missing)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

// TestInspectLiveInstalledPackMissingPhysicalColumnsExcludesActiveFormulaFields
// verifies that the oracle does not report active-formula fields as missing.
// These fields are metadata-only; the schema layer never creates a physical
// column for them (see schema_pack_owned_field.go packOwnedFieldIsActiveFormula).
//
// SQL now returns all non-deleted pack-owned fields including active-formula
// ones. The Go scan loop filters them out using strings.TrimSpace and
// strings.EqualFold, matching packOwnedFieldIsActiveFormula exactly.
func TestInspectLiveInstalledPackMissingPhysicalColumnsExcludesActiveFormulaFields(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	// SQL returns both an active-formula field and a normal physical field.
	// Go filter removes the active-formula row; only Physical__c reaches the
	// INFORMATION_SCHEMA check.
	mock.ExpectQuery(`SELECT o.*ApiName.*f.*ApiName.*_System_Field`).
		WithArgs(constants.FieldOwnerKindPack, constants.FieldOwnerKindPackRetained, "my-pack").
		WillReturnRows(sqlmock.NewRows([]string{"ObjectAPIName", "FieldAPIName", "FormulaMode", "Formula", "ProjectionConfig", "FormulaStorageMode"}).
			AddRow("Foo__c", "Computed__c", "active", "SomeFormula()", nil, "").
			AddRow("Foo__c", "Physical__c", "", "", nil, ""))

	// INFORMATION_SCHEMA: Physical__c is present.
	mock.ExpectQuery(`SELECT TABLE_NAME.*COLUMN_NAME.*INFORMATION_SCHEMA\.COLUMNS`).
		WillReturnRows(sqlmock.NewRows([]string{
			"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME",
		}).AddRow("foo__c", "physical__c", "varchar(255)", "YES", nil, nil))

	missing, err := InspectLiveInstalledPackMissingPhysicalColumns(context.Background(), db, "my-pack")
	if err != nil {
		t.Fatalf("InspectLiveInstalledPackMissingPhysicalColumns = %v, want nil error", err)
	}
	if len(missing) != 0 {
		t.Fatalf("missing = %v, want [] (active-formula fields must not be reported as missing)", missing)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	mock.ExpectQuery(`SELECT o.*ApiName.*f.*ApiName.*_System_Field`).
		WithArgs(constants.FieldOwnerKindPack, constants.FieldOwnerKindPackRetained, "my-pack").
		WillReturnRows(sqlmock.NewRows([]string{"ObjectAPIName", "FieldAPIName", "FormulaMode", "Formula", "ProjectionConfig", "FormulaStorageMode"}).
			AddRow("Foo__c", "ComputedStored__c", "active", "SomeFormula()", nil, "write_materialized"))

	mock.ExpectQuery(`SELECT TABLE_NAME.*COLUMN_NAME.*INFORMATION_SCHEMA\.COLUMNS`).
		WillReturnRows(sqlmock.NewRows([]string{
			"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME",
		}))

	missing, err := InspectLiveInstalledPackMissingPhysicalColumns(context.Background(), db, "my-pack")
	if err != nil {
		t.Fatalf("InspectLiveInstalledPackMissingPhysicalColumns = %v, want nil error", err)
	}
	if len(missing) != 1 || missing[0] != "Foo__c.ComputedStored__c" {
		t.Fatalf("missing = %v, want [Foo__c.ComputedStored__c]", missing)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}